Excel Workbook for Allocating Program
Support: OMBI Method
The Ministry provides an Excel workbook to assist municipalities in allocating Program Support to other functions using the allocation method developed by the Ontario CAO’s Municipal Benchmarking
Initiative (OMBI). Municipalities with populations greater than or equal to 100,000 are required to use the OMBI method.
The workbook is for the use of the municipality only and is not to be submitted to the province.
WHAT IS ALLOCATED?
The amount of Program Support in SLC 40 0260 13 is automatically calculated and equals the sum of the following cells times -1:
Salaries, wages and employee benefits SLC 40 0260 01
Materials SLC 40 0260 03
Contracted services SLC 40 0260 04 Rents and financial expenses SLC 40 0260 05 Interfunctional adjustments SLC 40 0260 12
Municipalities enter the amount of Program Support allocated to other sub-functions as positive values in column 13 (Allocation of Program Support). Note that Program Support may be allocated to the General Government sub-functions of Governance and Corporate Management, if applicable. The total for column 13 will be zero since the sum of the amounts allocated to individual sub-functions is a positive number which is equal in absolute value to the negative number in SLC 40 0260 13.
OVERVIEW
The OMBI method of allocation breaks Program Support into 23
categories. See the table at the end of this chapter. Program Support expenses are divided among these categories.
Each Program Support category is assigned a type of unit or “driver” to measure consumption by the services (sub-functions) in Schedule 40. Consumption is the basis of allocating Program Support expenses. Note that drivers differ by Program Support category.
For each category of Program Support, the percentage of driver units consumed by each sub-function is calculated. The percentage of driver units is then multiplied by the dollar amount assigned to that Program Support category. The product is the amount to be allocated to the sub-function for that Program Support category.
The total amount of Program Support to be allocated to each
sub-function equals the sum of the amounts of Program Support for all 23 Program Support categories.
PURPOSE OF WORKBOOK
The Excel workbook will calculate the total amount of Program Support to be allocated to all sub-functions. The municipality then enters the amount of Program Support to be allocated in Schedule 40, column 13 on line 0240 (Governance) and/or line 0250 (Corporate Management), if applicable, and on lines 0410 (Fire) through 1898 (Planning and
Development: Other).
WORKSHEETS
The worksheets contain lines that correspond to the sub-functions in Schedule 40. Note that the line for Program Support is not shown because the objective is to determine how much Program Support will be allocated to other sub-functions.
In general, two Program Support categories are reported on each tab of the Excel workbook. The last tab in the workbook automatically
calculates Total Program Support for all categories of Program Support, by sub-function.
SCHEDULE LINE REFERENCES
To the left of each worksheet are columns containing a Schedule and Line reference.
Copy data from the final worksheet, “Total Program Support” to SLC 40 xxxx 13 (Allocation of Program Support) to the line for the appropriate sub-function.
REMOVING PROTECTION ON WORKSHEETS
No password was used to protect worksheets. To remove protection on a sheet, use the following sequence for each sheet:
1. Review tab/Unprotect Sheet
2. To unlock individual cells, highlight the cells and right click. Select Format Cells. Go to the tab titled “Protection”. Remove the check mark in the box labelled “Locked”.
COLUMNS
Each Program Support category contains 4 columns:
a Total
The municipality enters total expenses for each Program Support category in column “a”, line 9999. This is the amount that will be apportioned among sub-functions.
b Number of driver units per sub-function
For each sub-function, the municipality reports the number of “driver” units consumed.
Total units are automatically calculated and entered in line 9999, column “b”.
c %
The percentage represents the percentage of total driver units consumed by each sub-function. It is automatically calculated. The percentage in column “c” equals the number of units for a
sub-function in column “b” divided by total units entered in line 9999 of column “b”. The quotient is multiplied by 100 to express the result as a percentage.
Note that the percentage in column “c” can be entered directly if a municipality does not enter driver units in column “b”. To overwrite the formula in column “c”, remove Protection from the individual worksheet. (Review tab/Unprotect Sheet)
d The amount allocated to a sub-function is automatically calculated and equals total expenses for a Program Support category from column “a”, line 9999, times the percentage of driver units in column “c”.
Steps
1. Total Program Support to be allocated equals (SLC 40 0260 13) times (-1).
2. Divide Program Support in (SLC 40 0260 13) times (-1) among all Program Support categories.
In the Excel workbook, two Program Support categories are generally listed per tab.
In column “a” on line 9999 (Total), enter expenses for each Program Support category. All other rows are grey and locked.
3. For each Program Support category, enter the number of “driver” units consumed by each sub-function in column “b”.
On the line for each sub-function, enter the number of driver units consumed. Repeat for every Program Support category.
4. For each Program Support category, calculate total driver units in column “b”, line 9999.
Total driver units in column “b”, line 9999, is automatically calculated and equals the sum of driver units for all sub-functions.
5. For each sub-function and category of Program Support, express driver units as a percentage of total units in column “c”.
Driver units for each sub-function are automatically expressed as a percentage of total driver units in column “c”. To calculate the
percentage, driver units for each sub-function in column “b” are divided by total driver units in column “b”, line 9999.
Note that the percentage in column “c” can be entered directly if a municipality does not enter driver units in column “b”. To overwrite the formulas in column “c”, unprotect the individual worksheet.
6. For each sub-function and category of Program Support, determine the amount of Program Support to be allocated, entering the result in column “d”.
This calculation is automatic. For every sub-function, the percentage of driver units in column “c” is multiplied by the amount of Program Support in column “a”, line 9999. This is repeated for every category of Program Support.
7. For each sub-function, add the amounts of Program Support to be allocated for all categories of Program Support.
For every sub-function, the total Program Support to be allocated is automatically calculated in the last tab of the workbook: Total Program Support. The tab sums the amount of Program Support to be allocated in column “d” for all 23 Program Support categories.
8. Enter Program Support for each sub-function in Schedule 40 in column 13.
For every sub-function, obtain the total amount of Program Support to be allocated from the “Total Program Support” tab of the workbook. Enter the amount as a positive value in Schedule 40, column 13.
Program Support Category (Please see the chapter on Functional Categories for definitions)
Allocation “Driver” (type of units) used to track usage by each sub-function
Accounts payable Number of invoices paid / Transactions processed Accounts receivable Number of transactions
Budgeting Estimated time
Compensation management Average head count (full-time, part-time, casual) Facilities and property
management
Floor space (on a facility by facility basis) or Average head count (full-time, part-time and casual) in each building
Fleet Time and material
Health and Safety Average headcount (full-time, part-time and casual)
HR Counselling Average headcount (full-time, part-time and casual)
IT Application delivery & data management
Estimated time
Infrastructure tool access (Data and Voice)
Number of PC=s or Phone lines
Insurance/risk management administration
Insurance premiums or Claims
Labour and employee relations Number of grievances or Average headcount of employees (full-time, part-time and casual)
Mailroom Program cost
Payroll Number of cheques / direct deposits
Printing and graphics Number of impressions, including photocopies
Program accounting Estimated time
Program specific communication Estimated time Program legal support Estimated time
Purchasing Volume of transactions
Records management Number of records
Staffing Number of job postings (both internal and
external) Stores & commodity
management
Value of goods processed or Number of stores requisitions processed
Training and development Average headcount (full-time, part-time and casual)
