Excel workbook for allocation of program support - OMBI method The Ministry is providing 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 of 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? Municipalities allocate the amount of program support in SLC 40 0260 13 (-1) to all other functions, including governance in SLC 40 0240 13 and corporate management in SLC 40 2050 13. Note that the amount of program support is automatically calculated and entered in SLC 40 0260 13 as a negative value. Therefore, the amount to be allocated equals SLC 40 0260 13 (-1). The amounts assigned to subfunctions are entered in column 13 (Allocation of program support) as positive amounts. The column total will be zero. The amount of program support allocated equals negative one times the sum of the following columns: 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 Inter-functional adjustments SLC 40 0260 12 This formula may be revised in the 2006 FIR to include external transfers. Therefore, the Treasurer's Group of the Ontario Municipal CAO's Benchmarking Association (OMBI) asks that municipalities review any external transfers reported as program support in SLC 40 0260 06. External transfers related to program areas should not be reported on any line for general government. If external transfers are not related to a specific program, they should be reported on the line for corporate management in SLC 40 0250 06. Purpose of workbook The workbook will calculate the total amount of program support allocated to all subfunctions. The municipality must copy the amounts from the workbook into Schedule 40, column 13 on lines 0240, 0250 and 0410 through 1898. Amounts entered in Schedule 40 for the allocation of program support will be carried forward to Schedule 91 for general government (governance plus corporate management), fire, police, waste collection, waste disposal, recycling, parks, recreation programs, recreation facilities and libraries. These lines are identical to the service areas used for efficiency measures. This workbook also calculates amounts allocated to narrowly defined service areas which are used in the efficiency measures for paved roads, unpaved roads, winter control (excluding the clearing of sidewalk and parking lots), wastewater collection, wastewater treatment and disposal, urban storm water management, rural storm water management, drinking water treatment, and drinking water distribution. Municipalities should enter the amount for the allocation of program support for these services directly into Schedule 91, column 22 (Allocation of program support). SUMMARY OF OMBI METHOD 1. Total program support to be allocated equals: SLC 40 0260 13 (-1) The amount in SLC 40 0260 13 equals the sum of salaries, wages and employee benefits; materials; contracted services; rents and financial expenses and interfunctional adjustments for program support and is automatically entered as a negative number. 2. Divide total program support in SLC 40 0260 13 (-1) among program support categories. For each program support category: 3. Determine number of driver units consumed by each subfunction and narrower service area in Schedule 91. Enter the number of driver units consumed by each subfunction and narrower service area in column "b". 4. Determine total driver units. Total driver units are automatically calculated in column "b", line 9999. 5. For each line, express driver units as a percentage of total driver units. Driver units are automatically expressed as a percentage of total driver units in column "c". 6. Determine amount allocated to each subfunction and narrower service area in Schedule 91. The amount allocated to each line is automatically calculated in column "d". (Program support from step 2 is multiplied by the percentage of total driver units from step 5.) 7. Determine total program support allocated to each subfunction and narrower service area in Schedule 91. Total program support is automatically calculated by adding the amount allocated from column "d" for all 23 program support categories. The sum appears in the last tab: "Total program support". 8. Enter program support for each line in Schedule 40 or 91, using column 13. Obtain the amount of program support from the "Total program support" tab and enter the amount as a positive value on the appropriate line in Schedule 40, column 13 (Allocation of program support) or Schedule 91, column 22 (Allocation of program support). WORKSHEETS The OMBI method of allocation breaks program support into 23 categories. Expenditures for each program support category are allocated based on the percentage of total "driver" units used by each subfunction. The worksheets contain lines that correspond to the subfunctions in Schedule 40 and more detailed service areas in Schedule 91. In general, two program support categories are reported on each tab of the workbook. The last tab in the workbook contains a worksheet which calculates the total program support allocated to each subfunction in Schedule 40 and detailed service area in Schedule 91. See the table at the end of the instructions for a list of the program support categories and the "driver" units. SCHEDULE LINE REFERENCES To the left of each worksheet is a column containing a Schedule and Line reference. If Schedule 40 is listed, the line number refers to a subfunction. If Schedule 91 is listed, the line number refers to a narrower service category used in the efficiency measures. Copy data from the final worksheet, "Total program support" to SLC 40 xxxx 13 (Allocation of program support) or SLC 91 xxxx 22 (Allocation of program support) as appropriate. REMOVING PROTECTION ON WORKSHEETS No password was used to protect worksheets. Therefore, to remove protection on a sheet use the following sequence: 1. Tools/Protection/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 service category contains 4 columns: a Total The municipality enters total expenditures for each program support category in column "a", line 9999. This is the amount that will be apportioned among subfunctions. b Number of driver units per subfunction For each subfunction, 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 subfunction and narrower service area. It is automatically calculated. Note that the percentage in column "c" can be entered directly if a municipality does not enter driver units in column "b". This will overwrite the formulas in column "c". If the Schedule Line reference at the left side of the sheet refers to Schedule 40, the percentage equals the number of units for a subfunction divided by total units entered in line 9999, column "b". The quotient is multiplied by 100 to express the result as a percentage. If the Schedule Line reference refers to Schedule 91, the percentage equals the number of units reported for the narrow service area divided by the number of units reported in column "b" for the subfunction used in Schedule 40. For example, the percentage of total "driver" units for paved roads equals the number of units reported for paved roads on line 2105 of column "b", divided by the number of units for roadways on line 0610 of column "b", times 100. d Amount allocated The amount allocated to a subfunction is automatically calculated and equals total expenditures for a program support category from column "a", line 9999, times the percentage of driver units in column "c". TOTAL ALLOCATED TO A SUBFUNCTION The last tab in the Excel workbook is entitled "Total program support". The amount allocated to each subfunction is automatically calculated and equals the sum of the amounts entered in column "d" (Amount allocated) for each program support category. Copy the amount of total program support allocated to each subfunction to Schedule 40 or each efficiency measure in Schedule 91, as appropriate, using the Schedule Line reference provided on the worksheet. If the Schedule Line reference indicates Schedule 40, a municipality copies data from the final worksheet, "Total program support", and enters it in Schedule 40, column 13 (Allocation of program support) on the line for the subfunction. If the Schedule Line reference indicates Schedule 91, a municipality copies data from the "Total program support" worksheet and enters it in Schedule 91, column 22 (Allocation of program support) on the line for the efficiency measure. OMBI ALLOCATION METHOD IN DETAIL 1. Total program support to be allocated equals: SLC 40 0260 13 (-1) The amount in SLC 40 0260 13 equals the sum of salaries, wages and employee benefits; materials; contracted services; rents and financial expenses and interfunctional adjustments for program support and is automatically entered as a negative number. Since the entry in SLC 40 0260 13 is negative, the total to be allocated is a positive number: SLC 40 0260 13 (-1). This ensures that the column total is zero. 2. Divide total program support in SLC 40 0260 13 (-1) among program support categories. There are 23 categories of program support. In column "a", line 9999 (Total), enter expenditures for each program support category. Consider that the amount allocated should consist only of: Salaries, wages and employee benefits Materials Contracted services Rents and financial expenses Interfunctional adjustments FOR EACH PROGRAM SUPPORT CATEGORY: 3. Determine number of driver units consumed by each subfunction and narrower service area in Schedule 91. Enter the number of driver units consumed by each subfunction and narrower service area in column "b". Where the Schedule Line reference indicates Schedule 91, examine the number of units consumed by the subfunction in Schedule 40 and determine how many pertain to each narrower service area. For example, on line 0810 report the number of units consumed by the sanitary sewer system. On line 3106, report the number of units consumed by collection services and on line 3104 report the number of units consumed by treatment and disposal. 4. Determine total driver units. Total driver units are automatically calculated in column "b", line 9999. Total driver units equal the sum of driver units for each subfunction in Schedule 40. To avoid double counting, units for narrow service areas in Schedule 91 are not counted. 5. For each line, express driver units as a percentage of total units. Driver units are automatically expressed as a percentage of total driver units in column "c". If the Schedule Line reference for a subfunction refers to Schedule 40, the percentage equals the number of units reported in column "b" for a subfunction divided by total units entered in line 9999, column "b". The quotient is multiplied by 100 to convert the result into a percentage. If the Schedule Line reference refers to Schedule 91, the percentage equals the number of units reported in column "b" for the narrow service area divided by the number of units reported in column "b" for the subfunction in Schedule 40. For example, the percentage of total units for paved roads equals the number of units reported for paved roads on line 2105, column "b", divided by the number of units for roadways on line 0610, column "b", times 100. Note that the percentage in column "c" can be entered directly if a municipality does not enter driver units in column "b". This overwrites the formulas in column "c". 6. Determine amount allocated to each subfunction and narrower service area in Schedule 91. The amount allocated to each line is automatically calculated in column "d" and equals program support from column "a", line 9999 (Step 2), times the percentage of driver units in column "c" (Step 5). 7. Determine total program support allocated to each subfunction and narrower service area in Schedule 91. Total program support is automatically calculated by adding the amount allocated from column "d" for all 23 program support categories. The sum appears in the last tab: "Total program support". 8. Enter program support for each line in Schedule 40 or 91, using column 13. Obtain the amount of program support from the "Total program support" tab and enter the amount as a positive value on the appropriate line in Schedule 40, column 13 (Allocation of program support) or Schedule 91, column 22 (Allocation of program support). Use the Schedule Line reference as a guide. PROGRAM SUPPORT Program support category Allocation "driver" (type of units) used to track consumption by each subfunction Accounts payable Number of invoice paid/Transactions processed Accounts receivable Number of transactions Budgeting Estimated time Compensation management Average head count (full-time, part-time, casual) Average head count refers to the average annual number of actual employees (not FTEs) employed by a business unit. Facilities and property management Floor space (on a facility by facility basis) or Average headcount (full-time, part-time and casual) in each building Fleet Time and material Health and Safety Average headcount (full-time, part-time and casual) Average head count refers to the average annual number of actual employees (not FTEs) employed by a business unit. HR Counselling Average headcount (full-time, part-time and casual) Average head count refers to the averge annual number of actual employees (not FTEs) employed by a business unit. 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 Please see the Instructions to the Financial Information Return and the chapter on Functional Classification of Expenditures and Revenues for definitions of each program support category. PROGRAM SUPPORT Program support service category Allocation "driver" (type of units) used to track consumption by each subfunction Labour and employee relations Number of grievance or Average headcount of employees (full-time, part-time and casual) Municipalities may use an average headcount of union employees if activities are predominantly related to unionized activities. Municipalities may use an average headcount of all employees if activities are directed to all employees. For better precision, the number of grievances per program may be used to allocate the costs of grievances and arbitration administration. 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) Average head count refers to the average annual number of actual employees (not FTEs) employed by a business unit.