Cost Allocation User's Guide City of Tigard, Oregon
•
Il
I
•
TIGARD
User Guide to the
Indirect Cost
Allocation Model
February, 2012
CONSULTING SERVICES PROVIDED BY:
• FCS GROUP
Solutions-Oriented Consulting
Redmond Town Center
7525 166th Avenue NE, Suite D-215
Redmond, WA 98052
T: 425.867.1802
F: 425-867-1937
This entire report is made of readily recyclable materials,including the
bronze wire binding and the front and back cover,which are made
from post-consumer recycled plastic bottles. www.fcsgroup.com
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Table of Contents
Page
1. Introduction 1
2. Model Update Procedures 4
3. Model Start-up 6
4. INPUTS 7
5. DATA ENTRY 10
6. CITY MGMT, RISK MGMT, COUNCIL/DESIGN&RECORDS /FINANCE & IT 11
7. SQ FT 15
8. COPIERS 16
9. ALLOCATION FACTORS 17
10. INDIRECT COST POOLS 19
11. INDIRECT SUMMARY 21
12. ALLOCATIONS 22
13. SUMMARY SCHEDULE 24
14. CARRY-FORWARD 25
15. DEPT INDIRECT COST RATES 26
16. LIBRARY/POLICE/PUBLIC WORKS /COMMUNITY DEVELOPMENT/
MUNICIPAL COURT RATE WORKSHEETS 27
•FCS GROUP
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
1. Introduction
The indirect cost allocation model is designed to allocate the costs of the City's overhead
departments (e.g. Mayor & Council, City Management, Administrative Services, Finance and
Information Services, etc.) to its operating departments. To do this, the model uses the following
three pieces of information:
• Indirect Cost Pools: Indirect cost pools represent the City's overhead departments
arranged into functions. There is at least one indirect cost pool for every overhead
department included in the model; often there will be several. For example, the functions
performed by Administrative Services could be divided into several indirect cost pools,
such as Mail, Graphics, Copiers, Records Management, Council Support, etc.
• Direct Service Departments: Direct service departments represent operating departments
and/or funds. They are referred to as "direct service" departments because they provide
direct services to citizens, customers, and/or stakeholders of the City. Similar to indirect
cost pools, there is at least one direct service department for each operating department or
fund included in the model. Direct service departments include operating departments
such as Library, Police, Public Works, Community Development, etc.
• Allocation Factors: Allocation factors represent the basis for allocating the costs of the
indirect cost pools to the direct service departments. Common allocation factors include
total expenditures, FTEs, square footage, etc.
This model allocates the indirect cost pools' costs to the direct service departments using the
allocation factors. This is accomplished by the following four spreadsheets in the model:
• Inputs
• Allocation Factors
• Indirect Cost Pools
• Allocations
The role of each of these four spreadsheets is described below.
Inputs — The inputs spreadsheet is where the names of the indirect cost pools, direct service
departments, and allocation factors are input into the model. The inputs spreadsheet also turns
on the model's federal reimbursement mode (i.e. OMB A-87), identifies the fiscal years of the
financial information and indirect cost plan, is the place where hours are entered to develop the
City's indirect cost rates, and is where combination allocation factors can be created.
Combination allocation factors are percentage-weighted combinations of other allocation factors
that, like regular allocation factors, can be used to allocate costs.
Allocation Factors — The allocation factors spreadsheet is where allocation factor data is entered
into the model and allocation factor percentages are calculated. These allocation factor
percentages are used to allocate the costs of indirect cost pools to direct service departments.
4>FCS GROUP 1
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Indirect Cost Pools — The indirect cost pools spreadsheet is where budgets or actual costs for
each indirect cost pool are entered into the model. These are the costs that will be allocated to
the direct service departments. Adjustments can be made to these costs using this spreadsheet.
Making adjustments on this spreadsheet also provides a record that can be used to trace
adjustments back to the City's financial statements. This spreadsheet can also be used to
calculate the costs eligible for federal reimbursement(i.e. OMB A-87).
Allocations — The allocations spreadsheet is where allocation factors are assigned to each
indirect cost pool, and where the indirect cost pools' costs are allocated to the direct service
departments. Allocations are calculated in a two-step process, which results in direct service
departments receiving costs for both the services they receive from the indirect cost pools, as
well as a share of those indirect cost pools' overhead. In the first step, called the primary
allocation, each indirect cost pool's costs are allocated to both direct service departments and
other indirect cost pools for which they provide services. In the second step, called the
secondary allocation, each indirect cost pool takes the costs allocated to it from other indirect
cost pools and allocates those costs back to the direct service departments.
Other spreadsheets included in the model summarize/report different steps of the indirect cost
allocation, or help with special allocation steps for department budgets. These spreadsheets
include:
• Data Entry—A template to assist staff with data collection and entry. The layout of this
template is the same as the allocation factors spreadsheet, and the data entered into this
template can be copied directly into the allocation factors spreadsheet.
• City Mgmt, Risk Mgmt Council / Design & Communications / Finance & IT — These
spreadsheets re-distribute department costs into cost pools, calculate OMB A-87
allowable costs, and distribute allocation factor data between cost pools for City
Management, Risk Management, City Council, Design, Records, Finance, and IT.
• Sq Ft — This spreadsheet calculates the total square feet maintained by Property
Management for the cost pools and direct service departments. The results of this
spreadsheet are used as allocation factor data to allocate the costs of Property
Management.
• Copiers — This spreadsheet distributes the rental and maintenance costs of the City's
copiers and printers to the cost pools and direct service departments. The results of this
spreadsheet are used as allocation factor data to allocate the costs of copiers/printers.
• Indirect Summary — A summary report of indirect cost pools' costs designated for
allocation.
• Summary Schedule — A summary report that displays the total indirect costs charged to
each direct service depaitiuent. It also shows the amounts charged by each indirect cost
pool for each direct service department.
• Carry-Forward—A summary report that is used to calculate the "carry-forward" for each
direct service department. A "carry-forward" is an adjustment made to the calculated
indirect cost for each direct service department to account for differences between the
estimated and actual costs of a previous indirect cost plan. This adjusted net cost serves
4>FCS GROUP 2
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
as the basis for calculating the direct service department's indirect cost rate. For
example, the "carry-forward" for a 2012 indirect cost plan based on 2010 actual
expenditures would be calculated by subtracting the indirect cost plan amounts used in
2010 (based on 2008 actual expenditures) from the 2010 actual amounts. The net
differences between the planned and actual amounts for 2010 would then be applied as
the "carry-forward" adjustment to the 2012 plan.
• Library / Police / Public Works / Commnty Devl / Municipal Court Rate Worksheets —
Calculates various overhead rates for Library, Police, Public Works, Community
Development, and Municipal Court. The overhead rates include the following rate
components: citywide overhead, department overhead, paid time off, administrative time,
and benefits.
• Dept Indirect Cost Rates — Calculates an indirect cost rate for each direct service
department by dividing their current allocation from the indirect cost plan (adjusted for
"carry-forwards") by salaries or any other acceptable base selected by the user.
This model is designed to run both a full cost and an A-87 plan without having to input the data
for each plan separately into the model. This is accomplished by:
• First, entering the allocation factor data and costs for calculating both the full cost and A-
87 plan and then running the model in full cost mode (i.e. the "Use A-87Allocation" on
the "Inputs"page is turned off) to calculate the full cost plan.
• Second, switching the model to A-87 allocation mode (i.e. the "Use A-87 Allocation" on
the "Inputs"page is turned on).
The remainder of this document provides more detailed information for each spreadsheet in the
model, as well as some general information about enabling macros, entering data, checking
model calculations, and procedures to update the model. Spreadsheet details include:
• Overall description of the spreadsheet
• Procedures to update the spreadsheet
• Data links from other spreadsheets
• Data links to other spreadsheets
4>FCS GROUP 3
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
2. Model Update Procedures
To help with the model update process, detailed steps for three major model update procedures
are listed below along with the respective spreadsheets and chapters from the user guide. A
backup copy of the model has also been provided on CD for your use.
1. To create a plan for a new fiscal year _
User
Guide
Activity Spreadsheet(s) Chapter
1. Delete previous fiscal year's financial, allocation factor, and Various Various
rate data,making sure to check for and save any formulas
that will be used in the plan for the new fiscal year.
2. Update the budget year of the indirect cost plan and the year Inputs 4
of financial information.
3. Review and add/delete indirect cost pools. (Note: make sure Inputs 4
to update the"Dept. Prealloc."codes as well if needed).
4. Review and add/delete direct service departments. (Note: Inputs 4
make sure to update the"Dept. Prealloc./Rate Codes"as
well if needed).
5. Review and add/delete allocation factors. Inputs 4
6. Enter financial data for indirect cost pools and direct service • City Mgmt,Risk 6
departments managed by these spreadsheets. Mgmt, Council
• Design&Records
• Finance&IT
7. Enter financial data for other indirect cost pools. Indirect Cost Pools 10
8. Enter allocation factor data for indirect cost pools and direct • City Mgmt,Risk 6
service departments managed by these spreadsheets. Mgmt, Council
• Design&Records
• Finance&IT
9. Enter allocation factor data for other remaining indirect cost • Allocation Factors 7,8,9
pools and direct service departments. • Sq Ft
• Copiers
10. Review/Update allocation factor numbers used to allocate Allocations 12
costs.
11. Calculate the carry-forward(if this function is being used). Carry-Forward 14
12. Update the rate worksheets Various 16
4>FCS GROUP 4
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
2. To add a direct service department after the plan has been created for the new fiscal
year
User
Guide
Activity Spreadsheet(s) Chapter
1. Identify the location of the new direct service department, Inputs 4
and copy(don't insert)the list of direct service departments
and"Dept. Prealloc./Rate Codes"from that location down
one to make room for the new direct service department.
2. Identify the location of the new direct service department, • Allocation Factors 7, 8, 9
and copy the allocation factor data from that location down • Sq Ft
one to make room for the new direct service department's • Copiers
data.
3. Enter the name of the new direct service department. Inputs 4
4. Enter the new allocation factor data for the new direct • Allocation Factors 7, 8, 9
service department. • Sq Ft
• Copiers _
5. Make adjustments to rate worksheets Various 16
3. To add an indirect cost pool after the plan has been created for the new fiscal year
User
Guide
Activity _Spreadsheet(s) Chapter
1. Identify the location of the new indirect cost pool and copy Inputs 4
(don't insert)the list of indirect cost pools and"Dept.
Prealloc."codes from that location down one to make room
for the new indirect cost pool.
2. Identify the location of the new indirect cost pool, and copy • Allocation Factors 7, 8, 9
the existing allocation factor data from that location down • Sq Ft
one row to make room for the new indirect cost pool's data. • Copiers
3. Identify the location of the new indirect cost pool and Indirect Cost Pools 10
starting from the bottom of the spreadsheet moving upward
copy each of the cost pool's cost data down one box until the
new indirect cost pool's box is reached to make room for the
new indirect cost pool's cost data.
4. Identify the location of the new indirect cost pool and copy Allocations 12
the existing allocation factor numbers from that location one
to the right to make room for the new indirect cost pool.
5. Enter the name of the new indirect cost pool. Inputs 4
6. Enter the new allocation factor data in the appropriate row. • Allocation Factors 7, 8, 9
• Sq Ft
• Copiers
7. Enter the new cost data in the appropriate box. Indirect Cost Pools 10
8. If the indirect cost pool is managed by one of these • City Mgmt,Risk 6
spreadsheets, go to the respective spreadsheet and Mgmt, Council
review/update the data to ensure that the appropriate data is • Design&Records
in the appropriate column for each cost pool. • Finance&IT
9. Enter the allocation factor number for the new indirect cost Allocations 12
pool in the appropriate column.
4>FCS GROUP 5
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
3. Model Start-up
Opening Document
Each time the model is opened in Microsoft Office 2003, the user will be prompted with a
question. The first prompt asks the user to either enable or disable macros contained in the
model. The user should choose "Enable Macros". Enabling macros allows the various control
buttons to work. To enable macros in Office 2007, each time the model is opened there will be a
security warning under the ribbon, stating that "macros have been disabled". Click on the
"options" button next to the security warning, and then click the "enable this content" button to
enable macros.
Areas in the model for data input are colored light grey and have a blue font. Data should only
be input in these grey-colored areas.
As the model evolves, the user should always double-check all calculations as well as both
internal and external links. Excel provides a formula auditing toolbar to trace dependant and
precedent cells throughout the entire model. To view this toolbar in Microsoft Office 2003, click
View > Toolbars > Formula Auditing. To view this toolbar in Microsoft Office 2007, click the
"Formulas" tab at the top of the screen and look for the "Formula Auditing" section of the
ribbon.
The user can find many error checks throughout the model. Error checks are in red, and show
"Check O.K." when formulas are working correctly, and "ERROR!" when there is an error
with a calculation.
Most error checks review the step-by-step calculation occurring on a single spreadsheet. There
are several other error checks in the model, however, that compare the total costs input into the
"Indirect Cost Pools" spreadsheet with total output from the model. The main error check for
the model can be found on the "Inputs" spreadsheet. This main error check reviews the
pertinent spreadsheets in the model and shows if any of them have an active "ERROR!"
message. It also reconciles the total costs input into the model for the allocation to the total
allocated costs to see if any costs were "lost" during the allocation. Other major error checks can
be found on the "Indirect Summary", "Allocations", "Summary Schedule", and "Carry-
Forward" spreadsheets, towards the bottom right hand corner of each.
By reviewing these error checks and reconciling any differences between the totals, the user can
ensure that the model is calculating correctly and that none of the costs input into the beginning
of the model are being"lost" during the allocation process.
When working with the model, please save versions of the model frequently to safeguard work
against unintentional model damage or other mistakes. A backup copy of the model has also
been provided to allow the user to start over in the event of extensive damage to the model.
4>FCS GROUP 6
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
4. INPUTS
Overall Description of Spreadsheet
The purpose of this spreadsheet is to establish the overall assumptions, cost pools,
departments/funds, and allocation factors for the model. These assumptions include the budget
year of the plan, year of the financial information, direct service departments, indirect cost pools,
and allocation factors. This spreadsheet also provides a main error check for the model, as
described in the previous section.
Key Assumptions and Data
The following are key assumptions and data that must be entered into the spreadsheet. When
entering data into this spreadsheet do not insert or delete entire rows, only clear or copy over
the contents of the cell.
Assumptions Current Input Data Source
Budget year of FY 2012-13 Budget year that the cost plan is being developed for.
indirect cost plan
Year of financial FY 2010-11 Fiscal year of the financial information used in the plan.
information
Use A-87 No
Allocation?
As determined by the user. Additional lines are provided for new
Direct Service entries. The numbers in the Dept. Prealloc/Rate Codes. column can be
Departments Various used to assign direct service departments to one of the preallocation
spreadsheets(e.g.Finance&IT,etc.)as well as one of the rate
spreadsheets(e.g.Police Rate Worksheet,etc.)
As determined by the user. Additional lines are provided for new
Citywide Indirect entries. The numbers in the Dept.Prealloc. column can be used to
Cost Pools Various assign cost pools to one of the preallocation spreadsheets(e.g.Finance
&IT,etc.).
Allocation Factors Various As determined by the user. Additional lines are provided for new
entries.
Allocation Factors- As determined by the user,enter the name of the new allocation factor,
Combinations Various along with the number and associated percentage weight of up to four
existing allocation factors.
Hours for Paid Time Total hours paid,average paid time off,and average administrative time
Off and Admin Rates Various for a typical FTE in the following departments: Library,Police,Public
Works,Community Development,and Municipal Court.
Procedures to Update Spreadsheet
• Budget year of indirect cost plan—Enter the budget year(e.g. FY 2012-13).
• Year of financial information—Enter the year of the allocated costs.
• Use A-87 Allocation?—For the model to calculate the OMB A-87 indirect cost
allocation, enter"yes" in the grey box. If"no" is entered or the grey area is left blank,
the model will calculate the full indirect cost allocation.
• Direct Service Departments —Enter/Update the direct service departments. Assigning a
number between one and three in the Dept. Prealloc. Rate Codes column will include the
direct service department in one of the preallocation spreadsheets (e.g. "Finance & IT",
4>FCS GROUP 7
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
etc.) where costs and allocation factor data can be distributed between a department's
indirect cost pools and direct service departments. Assigning a rate code (e.g. M1, M2,
etc.) will include it on one of the rates worksheets (e.g. Library Rate Worksheet, etc.).
For each direct service department, also enter the corresponding department or fund
number.
• Citywide Indirect Cost Pools — Enter/Update the indirect cost pools. To assign an
indirect cost pool to a preallocation spreadsheet, (e.g. "Finance & IT", etc.), enter
numbers one through three in the Dept. Prealloc. column. Preallocation spreadsheets are
where indirect cost pools that come from the same department and/or share resources
among themselves (e.g. the cost pools in Finance) are allocated a share of the costs and
allocation factor data. For each indirect cost pool, also enter their corresponding
department numbers, such as 14.
• Allocation Factors — Enter/Update the allocation factors. The Combinations area
allows you to create an allocation factor that is a combination of weighted percentages of
up to four other existing allocation factors. There are two general ways combinations can
be used in the model:
• Combinations can be used to allocate indirect cost pools using more than one
allocation factor. For example, the City Manager's costs could be allocated using
both agenda items and actual expenditures, with 50% of the cost allocated using
agenda items, and the other 50%using the amount of actual expenditures.
• Combinations can be used to allocate an indirect cost pool's costs differently
depending on where the services are delivered. For example, a department could
spend 59% of its time providing citywide services, and 41% of its time providing
services to a separate agency. By using a citywide allocation factor (such as
FTEs) and weighting it by 59%, and using an allocation factor specific to the
separate agency and weighting it by 41%, the department's costs could be
allocated using a combination allocation factor instead of creating two separate
cost pools.
• Hours for Paid Time Off and Admin Rates — Enter/Update the total hours paid,
average paid time off, and average administrative time for a typical FTE of the
departments for which overhead rates will be developed.
Links from Other Spreadsheets
• None.
Links to Other Spreadsheets
• The Budget year of indirect cost plan and Year of financial information are used
throughout the entire model to provide the information for spreadsheet titles.
• The Direct Service Departments, Citywide Indirect Cost Pools, and Allocation Factors
lists are used throughout the model as titles in the "Data Entry", "Sq Ft", "Copiers",
"Allocation Factors", "Indirect Cost Pools", "Indirect Summary", "Allocations",
"Summary Schedule", "Carry-Forward", and "Dept Indirect Cost Rates"
spreadsheets.
• Indirect cost pools and direct service departments identified by numbers one through
three in the Dept. Prealloc. columns of the Direct Service Departments and Citywide
Indirect Cost Pools areas provide titles for the "City Mgmt, Risk Mgmt, Council",
"Design & Records", and "Finance & IT" spreadsheets, respectively.
4>FCS GROUP 8
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
• The Ttl Hrs Paid,Avg Paid Time Off, and Avg Admin Time are being used to develop the
"Paid Time Off" and"Admin Time"rates of the various rate worksheets (e.g. "Library
Rate Worksheet", etc.).
4>FCS GROUP 9
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
5. DATA ENTRY
Overall Description of Spreadsheet
The purpose of this spreadsheet is to provide a template for data entry that is independent of the
model. A copy of this spreadsheet can be used by other staff to report their allocation factor
data. The spreadsheet is in the same format as the "Allocation Factors" spreadsheet. Because
of this, data entered into a copy of the "Data Entry" spreadsheet can be copied directly into the
"Allocation Factors" spreadsheet.
Procedures to Update Spreadsheet
To create a copy of this spreadsheet to send to other staff for data entry, the name(s) of the
indirect cost pools and direct service departments first need to be entered into the "Inputs"
spreadsheet. Next, enter the name(s) of the allocation factor(s) data that staff will be entering
into the spreadsheet. The name(s) should be entered at the top of the column, replacing the
title(s) currently at the top of the column, such as Data Set#1. Finally, click on the large button
to the far right of the spreadsheet. By clicking on this button, the model will create a copy of this
spreadsheet in a separate, new Excel spreadsheet. Make sure that macros are enabled in the
model. If macros are not enabled, nothing will happen when the button is clicked. For
instructions on how to enable macros in the model,please refer to 2. Model Start-Up.
Once the department has entered data into the spreadsheet, simply copy and paste the entire
column of allocation factor data to the appropriate allocation factor column in the "Allocation
Factors" spreadsheet. Before pasting the entire column of allocation factor data into the
column, make sure that there are no linked cells or formulas in the target column, otherwise
important information or links could be overwritten.
Links from Other Spreadsheets
From "Inputs"
• "Direct Service Departments" and "Citywide Indirect Cost Pools" are used as titles for
the indirect cost pools and direct service departments.
Links to Other Spreadsheets
• None
4>FCS GROUP 10
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
6. CITY MGMT, RISK MGMT, COUNCIL / DESIGN & RECORDS /
FINANCE & IT
Overall Description of Spreadsheets
The purpose of these spreadsheets is to distribute costs into cost pools for some of the
departments in the City, including Mayor & Council, City Management, Administrative
Services, and Finance & Information Services. The cost data can be entered by line item or in
total.
There is a space at the top of these spreadsheets (as shown in the "Personnel Cost Analysis
Worksheet" section from the top half of the example below in Exhibit 1) where personnel costs
are distributed into cost pools. Based on the results of this distribution, personnel cost and FTE
ratios for each cost pool are also calculated. These ratios can be used to distribute non-personnel
costs and allocation factor data among the cost pools.
There is a space in the middle of these spreadsheets (as displayed on the next page in the "Line
Item Apportionment" section towards the middle of the example) where costs other than
personnel costs are distributed into cost pools. For each non-personnel cost entered into these
spreadsheets, corresponding information can also be entered that describes the cost and assigns it
to cost pool(s). The user determines and enters the allocation percentages between cost pools. In
a similar manner, OMB A-87 adjustments can be entered into these spreadsheets and netted
against expenditures (as displayed on the next page in the "A-87 Adjustments" section in the
middle of the example). At the bottom of these spreadsheets, there is also a space for allocation
factor data to be distributed among the cost pools, if needed.
4>FCS GROUP 11
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Exhibit 1
Indirect Cost Pools/Direct Service Departments
Personnel Cost Analysis Worksheet 1 2 3 4 5
Must Line Item- City Mn RiskMngmnt-
ry gmnt-City City Mngmnt Risk Mngmnt•
Employee Name/Title/Description FTEs be Annual G8A Mayor&Council Manager Services Insurance Claims&Misc
100% Personnel Cost Premiums
% $ % $ % $ % $ % $ % $
City Manager-C.Prosser 1.00 100% $ 188,022 - - 100,0% 188,022 - - -
Asst City Mgr/Admin Sucs Dir-L.Newton 1.00 100% $ 149,729 - - 100.0% 149,729 - - -
Exec Asst to City Mgr-J.Bengtson 1.00 100% $ 77,643 - - 1000% 77,643 - - -
WebSeMCesCoordinator-N.Lof 1.00 100% $ 86,806 - - 200% 17,361 00.o% 69,445 - -
AssttotheCityMgr-L Mills 1.00 100% $ 109,758 - - 60.0% 65,855 - - 40.0% 43,903
Sr.Management Analyst-K.Wyatt 1.00 100% $ 97,975 - 20.0% 19,595 50.0% 48,988 - - 30.0% 29,393
Risk Mgmt Tech-G.Smalling 0.80 100% $ 76,209 - - - - - 100.0% 76,209
Mayor&City Council 5.00 100% $ 108,002 - 100.0% 108,002 - - - -
0% _ - -
Adjustment for City Manager 100% $ 38,940 - - a:7% 34,557 11:3% 4,382 - -
JustmentforRisk 100%'5 6,849 - - - - - 100.0% 6,849
0% - - - -
Total Personnel Cost $ 939,933 $ $ 127,597_ $ 582,155 $ 73,827 $ - $ 156,354
Total Personnel Cost/line entry before G&A allocation $ 939,933 $ - $ 127,597' $ 582,155 $ 73,827 $ - $ 156,354
Personnel Cost Ratio before G&A allocation 100.00% 0.00% 13.58% 61 94% 7.85%% 0 00% 16.63%
Reallocate G
ero&A '$ - 5 $ - $ - $ - $ $
Total Personnel Costs after G&A allocation $ 939,933 $ $ 127,597 $ 582,155 $ 73,827 $ $ 156,354
Personnel Cost Ratio after G&A allocation 100.00% 0% 14% 62% 8% 0% 17%.
FTEs before G&A allocation 11.80 1111 0.00 11 5.20 4.30 0.80 0.00 1.50 ill
Reallocate GSA 0.00 0.00 0.00 0.00 0.00 0.00 0.00
FTEs after G&ANlocatlon 11.80 0.00 5.20 4.30 0.80 0.00 1.50
Line Item Apportionment 1 2 3 4 5
Mn
Line Item- City Mngmnt-City City Risk Mngmnt-Web Risk Mngmnt-
Line Item•Description Budget/ACtuals Department Allocation Mayor&Council Manager Services Insurance gmnt-
Claims&Misc
Premiums
0500-Mayor&Council:All Non-Personnel $ 109,364 1 All to Mayor&Council $ 109,364 $ - $ - $ - $ -
1000-City Manager:All Non-Personnel $ 105,813 A City Mgnn $ - $ 93,905 $ 11,909 $ - $ -
1200-Risk Management:Materials,Services,&Capital $ 44,252 j 5 cask MTgnnt-Seine&MSc $ - $ - $ - $ - $ 44,252
1200-Risk Management:Insurance $ 288,778 4 ,k Mngrmt-hsurancey-reniurrs $ - $ - $ - $ 288,778 $ -
Total Line Item Apportionment $ 548,207 I 109,364 93,905 11,909 288,778 44,252
Total Personnel Costs after G&A allocation $ 939,933 I $ 127,597 $ 582,155 I $ 73,827 $ -I $ 156,354
Total Line Item Apportionment $ 548,207 $ 109,364 $ 93,905 $ 11,909 $ 288,778 $ 44,252
Grand Total-Costs I$ 1,488,140 I I I I$ 236,961 1 $ 676,059 L I$ 85,7361 _I$ 288,778 1__I$ 200,6061
A-87 Adjustments 1 2 3 4 5
City Mngmnt-City City Mngmnt-Web RiMrnmnt-
Risk Mngmnt•
Description Bud 9e t/Actuals Department Allocation Mayor&Council Manager Services Claims&Miss
Prp ,me
Remove costs of Council b/c unallowable per A87 guidelines$ (236,961) 1 All to Mayors Council $ (236,961) $ - $ - $ - $ -
City Mngr-Advertising $ (36,522) A Mgmt $ - $ (32,411) $ (4,110) $ - $ -
City Mngr-Dues&Subscriptions(lobbying) $ (33,655) A City Ignt $ $ (29,867) $ (3,788) $ - $ -
CityMngr-Special Dept Expenses(celebrations) $ (16,977) A Sty 146t $ $ (15,066) $ (1,911) $ - $ -
City Mngr-Capital $ (653) A coy M5nt $ - $ (579) $ (73) $ - $ -
Risk-Capital $ (680) 5 task Magrmt-Claims&Mso $ - $ - $ - $ - $ (690)
Total $ (325,457) _ I (236,961) (77,924) (9,882) (690)
Grand Total-Costs $ 1,488,140 $ 236,961 $ 676,059 $ 85,736 $ 288,778 $ 200,606
Total A-87 Adjustments $ (325,457) I $ (236,961) $ (77,924)1 $ (9,882) $ -I $ (690)
Total Net Costs I$ 1,162,683 I I I I$ -1 I$ 598,1351 1$ 75,854 1 1$ 288,778 I 1$ 199,916
Allocation Factor Distributor-All Departments or City Management Only 1 2 3 4 5
Allocation City Mngmnt-City City Mngmnt-We Risk Mngmnt- Risk Mngmnt•
# Allocation Factor Factor Data Department Allocation Mayor&Council Manager Services Insurance Claims&Misc
1 Total Actual Expenditures $ 1,488,140 B Total Expenditures 236,96 _ 676,059 02.73 288,77 200,606
3 FTEs 11.80 C Total FTEs 4 2
4 Web Services 17 A Sty Mgmt 0.0 15.09 1.9 0.0 0.00
6 Property Value $ 370,522 A City Mynt 328,822 41,70
7 Graphics Staff Tree(Minutes) 32,565 A City Mgne 28,900 3,66
8#of Records Requests 7 A City Mani - 6
9 Tree on Records Requests 2.25 A Cay IVO 2
10 Journal Entries 70 A City mgrrt 62
14#of Printers/Copiers 1 A City mgnt 1 _
15 Help Desk Tickets 52 A Sty mg= 46
16 P-Card Transactions 99 A City Mgmt 88 1
20#of Invoices 135 A Sty Mgrrt 120 1
26 3yr Aug#Insurance Incidents 0.67 A City Mgnx - 1 1CM.11 0
Department Allocation
Name
Mayor&Council City Mngmnt-Ciry l City Mngmnt-Web Risk Mngmnt- Risk Mngmnt•
Manager Services Insurance Claims&Misc
A City Mgmt 89% 11%
B Total Expenditures 16% 45% 6% 19% 13%
C Total FTEs 441/3 36% 7% 0% 13%
D Risk
E
F
G
H
J
4>FCS GROUP 12
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Procedure to Update Spreadsheets
• Personnel costs and FTEs can be entered at the top of the spreadsheet in the Personnel
Cost Analysis Worksheet table. First enter a description, FTE information, and total
personnel costs in the Employee Name/Title/Description, FTEs, and Line Item-Annual
Personnel Costs columns, respectively. Next, enter the percentage of time worked for
each cost pool. For example, if an employee worked half of their time for the City
Manager and half their time for the Risk Management, then the user should enter 50% in
each of the City Mngmnt - City Manager and Risk Mngmnt - Claims & Misc columns,
respectively. For department directors or other staff who serve as general overhead to the
department, enter 100% of their time in the G&A column, and their costs will be
distributed among the cost pools based on the total personnel costs assigned to each cost
pool. Finally, make sure all personnel costs have been allocated by reviewing the
formula checks on the far right-hand side of this table. If the formula check says "Check
O.K.", then the personnel costs have been completely allocated to the cost pools.
Using the total personnel costs allocated to each cost pool, a personnel cost ratio is being
calculated at the bottom of the Personnel Cost Analysis Worksheet table. This personnel
cost ratio is used as the "default" allocation of all costs and allocation factor data unless
another option is chosen by the user. An FTE ratio is also being calculated based on the
FTEs entered into the table and the total staff time assigned to each cost pool. This FTE
ratio can also be used to distribute the Department's total allocation factor FTEs data
among its cost pools, if needed, by copying the FTE ratio to the Department Allocation
table and using it to allocate FTEs in the Allocation Factor Distributor table.
• All other costs should be entered in the Line Item Apportionment table. First enter the
costs and any corresponding descriptions in the Line Item - Budget/Actuals and
Description columns, respectively. Next, allocate the costs to the cost pools. As
mentioned above, costs are allocated between the cost pools automatically using a
"default" allocation based on the salary ratio. The "default" percentages can be found
towards the top of the spreadsheet in the Personnel Cost Ratio after G&A allocation row.
To use another percentage allocation other than the default, first enter the percentages in
another row of the Department Allocation table and then enter the corresponding letter
(e.g. "A") in column F of the row of the cost to be allocated. To allocate a cost to a
specific cost pool, enter a number (e.g. "1") in column F of the row of the cost to be
allocated. The number corresponds to a number above each of the cost pools in the
spreadsheet. Finally, make sure that all costs have been allocated by reviewing the
formula checks on the far right-hand side of this table.
• Any adjustments for the OMB A-87 plan should be entered in the A-87 Adjustments
table. This table distributes these adjustments to the cost pools. Use the same process
described above for updating the Line Item Apportionment table to update the A-87
Adjustments table.
• Allocation factors are distributed among the cost pools near the bottom of the spreadsheet
in the Allocation Factor Distributor table. Due to the way the City is organized and the
level of detail of available allocation factor data, there can be several of these Allocation
Factor Distributor tables on a spreadsheet. For example, the "Finance & IT"
spreadsheet has three separate allocation factor distributor tables (i.e. All Divisions or
Finance Only, Utility Billing Only, and IT Only). To update this information, first enter
the allocation factor number and data. Based on the allocation factor number, the
4>FCS GROUP 13
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
allocation factor title should populate automatically. Next, enter either a letter or number
in column F to distribute the allocation factor data. The allocation factor data is
distributed the same way as the cost data, as described above. Finally, review the error
checks at the far right-hand side of the table to make sure that the allocation factor data
has been distributed correctly. Note: In order for the allocation factor data to link
correctly to the "Allocation Factors" spreadsheet, each allocation factor distributed in
the Allocation Factor Distributor table must include its appropriate allocation factor
number.
• The Department Allocation table near the bottom of the spreadsheet is where percentages
are entered for allocating costs and allocation factor data between the cost pools. The
formula checks at the far right hand side of this table say "Check O.K." when the
percentages total 100%.
Links from Other Spreadsheets
From "Inputs"
• "Citywide Indirect Cost Pools" and"Direct Service Departments" are being used to
provide the titles at the top of the spreadsheet, based on the numbers entered into the
"Dept. Prealloc." and"Dept. Prealloc. /Rate Codes" columns, respectively.
Links to Other Spreadsheets
To "Allocation Factors"
• Data for the allocation factors entered in the Allocation Factor Distributor tables are
being distributed and linked to the "Allocation Factors" spreadsheet.
To "Indirect Cost Pools"
• The Grand Total- Costs row provides the total allocable cost amounts for various cost
pools.
• The Total A-87 Adjustments row provides the A-87 adjustments for various cost pools.
4>FCS GROUP 14
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
7. SQ FT
Overall Description of Spreadsheet
The purpose of this spreadsheet is to calculate the total square feet for the cost pools and direct
service departments. The results of this spreadsheet are used as allocation factor data in
allocating the costs of the Property Management cost pool. Going forward, this spreadsheet is
also set up to distribute the total facilities and depreciation costs by building to the indirect cost
pools and direct service departments,based on each buildings' distribution of square feet.
Procedures to update Spreadsheet
As shown in the exhibit below, square feet is entered separately for each building. It should also
be noted that depreciation and facilities costs are not being entered or used at this time.
Exhibit 2
City of Tigard,OR
Indirect Cost Model for the FY 2012-13 Budget Year _
Sq Ft Spreadsheet
City Hall Permit Center
So Ft Depr Facilities Sq Ft Depr Facilities
4,414 I 5,230 I I
100 0500 Mayor&Council $ - $ - $ - $ --11 600 1000 City Mngmnt-City Manager 352 $ - $ - $ - $ -
600 1000 City Mngmnt-Web Services $ - $ - $ - $ -
600 1100 Human Resources $ - $ - 365 $ - $ -
600 1200 Risk Mngmnt-Insurance Premiums $ - $ - $ - $ -
600 1200 Risk Mngmnt-Claims&Misc $ - $ - 306 $ - $ -
600 1300 Design&Comm-Mail 404 $ - $ - $ - $ -
600 1300 Design&Comm-Graphics $ - $ - $ - $ -
600 1300 Design&Comm-Copiers $ - $ - $ - $ -
600 1500 Recorder/Records-Records Management 461 $ - $ - $ - $ -
600 1500 Recorder/Records-Council Support 1.1 $ - $ - $ - $ -
600 1500 Recorder/Records-Records Requests $ - $ - $ - $ -
600 2000 Fin&Info Srvcs-Budget/CIP 327 $ - $ - $ - $ -
600 2100 Fin Ops-Fin Rprting/Mngmnt 696 $ - $ - $ - $ -
Links from Other Spreadsheets
From "Inputs"
• "Direct Service Departments" and"Citywide Indirect Cost Pools" are used as titles for
the indirect cost pools and direct service departments.
Links to Other Spreadsheets
To "Allocation Factors"
• The Total Sq Ft column is used as allocation factor data for"Sq Ft".
<+FCS GROUP 15
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
8. COPIERS
Overall Description of Spreadsheet
The purpose of this spreadsheet is to assign the maintenance and rental costs of the City's copiers
and printers to the indirect cost pools and direct service departments. For the mailroom color
copier and the City Hall black and white copier, the costs are distributed using the number of
copies. For all other copiers and printers, percentages are used to assign a proportional share of
their costs to the indirect cost pools and direct service departments.
Procedures to Update Spreadsheet
For each copier and/or printer, first enter the total rental and maintenance costs. For the
mailroom color copier and the City Hall black and white copier, next enter the number of copies.
For all other copiers/printers, assign percentages to represent which indirect cost pool and/or
direct service department used the copiers/printers and how much they used them, making sure
that the total for each copier/printer equals 100%.
Links from Other Spreadsheets
From "Inputs"
• "Direct Service Departments" and"Citywide Indirect Cost Pools" are used as titles for
the indirect cost pools and direct service departments.
Links to Other Spreadsheets
To "Allocation Factors"
• The allocation factor data for"Total Copier Costs" is the sum of the Total Maintenance
Cost and Total Rental Cost columns.
4>FCS GROUP 16
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
9.ALLOCATION FACTORS
Overall Description of Spreadsheet
The purpose of this spreadsheet is to enter the data needed to calculate the primary and
secondary allocation percentages for each allocation factor entered on the "Inputs" spreadsheet.
This spreadsheet includes both manual data entries and calculated allocation factor percentages.
Calculated values in the white areas will be automatically updated. The user should always
double check the links and data used in the calculations.
Procedures to Update Spreadsheet
6 Enter allocation factor data in the light grey areas of the spreadsheet for each indirect cost
pool and direct service department. Copies of the "Data Entry" spreadsheet can also be
used for data entry. Columns from the "Data Entry" spreadsheet can be copied directly
into the associated allocation factors in this "Allocation Factors" spreadsheet.
• Currently, the majority of the allocation factor data links on this spreadsheet are to the
"City Mgmt, Risk Mgmt, Council", "Design & Records", and "Finance & IT"
spreadsheets. These links are currently being accomplished with a formula that searches
each of these spreadsheets. To create a new link to one of these spreadsheets, simply
copy one of the existing formulas in the Allocation Factor Inputs (identified by a white
cell) to the appropriate column.
• Before copying data into the light grey areas of the spreadsheet, make sure to quickly
double check for formulas or linked cells. Copying over these or other formulas or
linked cells could result in the loss of important information.
• Note: There are many formula checks on this spreadsheet to verify that each step in
the allocation process is working correctly. An "ERROR!" prompt will appear
where there is problem with the allocation methods.
Links from Other Spreadsheets
From "Inputs"
• "Direct Service Departments" and"Citywide Indirect Cost Pools" are used as titles on the
left-hand side of the table.
• "Allocation Factors" are used as titles of Allocation Factor Inputs.
From "City Mgmt,Risk Mgmt, Council", "Design & Records", and "Finance & IT"
• The following allocation factors are currently being linked to this spreadsheet for one or
more of the indirect cost pools above: "Total Actual Expenditures", "CIP budget",
"FTEs", "Web Services", "# of Autos", "Property Value", "Graphics Staff Time
(Minutes)", "# of Records Requests", "Time on Records Requests", "Journal Entries", "#
of Computers", "#of Printers/Copiers", "Help Desk Tickets", "P-Card Transactions",
"Fleet Work Order Expenses", "#of POs", "$ of POs", "#of Invoices", "Agenda Items",
"Electronic Records", "3yr Avg# Insurance Incidents", "GIS Data Layers", and"#of
Records Boxes". These may be modified based on the proceedures described in Section 6
of this user guide.
•FCS GROUP 17
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODLL
From "Sq Ft"
• The "Total Sq Ft" column is used as allocation factor data for Sq Ft.
From "Copiers"
• The allocation factor data for Total Copier Costs is the sum of the "Total Maintenance
Cost" and"Total Rental Cost" columns.
Links to Other Spreadsheets
To "Allocations"
• Allocation Factors are used to allocate the costs of the "Indirect Cost Pools"to "Direct
Service Departments".
To "Dept Indirect Cost Rates"
• Allocation factor data is the source of the base used to calculate the "Indirect Cost Rates".
(Note: The base can be changed by selecting another allocation factor in row seven of the
"Dept Indirect Cost Rates" spreadsheet).
4>FCS GROUP 18
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
10. INDIRECT COST POOLS
Overall Description of Spreadsheet
The purpose of this spreadsheet is to input the budget or actual expenditures for each indirect
cost pool listed on the "Inputs" spreadsheet. The spreadsheet also provides a place to make and
document adjustments made to these expenditures before they are allocated. These include
adjustments made in the less: Non-Allocable Costs and Additional Adjustments columns.
Adjustments for an OMB A-87 plan are also made in this spreadsheet in the A-87 Adjustments
column. Percentages used for combo allocation factors are also calculated to the side of some of
the cost pool boxes.
Procedures to Update Spreadsheet
• Enter descriptions and values for each indirect cost pool in the Operating Cost
Categories and the FY 2010-11 Actuals columns. (Note: this title will update as newer
information is input into the "Inputs" spreadsheet).
• Values from the cost pools of the "City Mgmt, Risk Mgmt, Council", "Design &
Records", and "Finance & IT" spreadsheets do not need to be entered. They will
automatically populate in the last line of their cost pool boxes for both the FY 2010-11
Actuals and the A-87 Adjustments columns.
• Additional expenditures should be added under the FY 2010-11 Actuals column. For
removing expenditures, input the data in the less: Non Allocable Costs column.
Additional adjustments can be made in the Additional Adjustments Column. All negative
adjustments are accomplished using a negative number (e.g. to subtract $500, enter -
$500).
• OMB A-87 adjustments should be entered in the A-87 Adjustments column. These
adjustments are included in the allocation when the A-87 option on the "Inputs"
spreadsheet is turned on.
• A description of each cost or adjustment can be included in the Notes: column.
• It should also be noted that the percentage weighting for some combo allocation factors is
being calculated on the side of cost pool boxes in column L.
Links from Other Spreadsheets
From "Inputs"
• The "Citywide Indirect Cost Pools" list is the source of the title for each indirect cost
pool.
From "City Mgmt,Risk Mgmt, Council", "Design & Records", and "Finance & IT"
• The amounts for the FY 2010-11 Actuals and A-87 Adjustments are coming from the
"Grand Total Costs" and"Total A-87 Adjustments"rows, respectively.
4>FCS GROUP 19
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODLL
Links to Other Spreadsheets
To "Indirect Summary"
• Citywide Allocable Costs from each cost pool are used to create the summary list of
"Total Citywide Allocable Costs".
• "A-87 Allocable Costs" from each cost pool are used to create a summary list of Total
Citywide A-87 Allocable Costs.
To "Inputs"
• Some combo allocation factor percentages are being calculated in column L, and are
being used as percentages in"%Weight" in the "Combinations"table.
4>FCS GROUP 20
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
11. INDIRECT SUMMARY
Overall Description of Spreadsheet
The purpose of this spreadsheet is to summarize the citywide indirect costs that are to be
allocated to direct service departments.
When the OMB A-87 option is turned on, this spreadsheet also displays information about the A-
87 allocation. This includes the total A-87 eligible costs, less any costs allocated to ineligible
indirect cost pools, to calculate the net total A-87 allocated costs by their indirect cost pool.
Some indirect cost pools, such as City Council, are ineligible because their costs are unallowable
according to OMB A-87 guidelines. A-87 allocable costs that are allocated to ineligible cost
pools as part of the primary allocation are not passed on as overhead to the direct service
departments as part of the secondary allocation. As a result, these costs are also excluded from
the cost plan.
Procedures to Update Spreadsheet
• Cells should not be altered on this spreadsheet. No manual data entry is required.
• A check is provided at the bottom of the spreadsheet to verify that the total citywide
indirect costs to be allocated matches the data entered on the "Indirect Cost Pools"
spreadsheet.
Links from Other Spreadsheets
From "Inputs"
• The "Citywide Indirect Cost Pools" list is used as the source of the titles for the cost
pools.
From "Indirect Cost Pools"
• "Citywide Allocable Costs" from each cost pool are used to create a summary list of
Total Citywide Allocable Costs.
• "A-87 Allocable Costs" from each cost pool are used to create a summary list of Total
Citywide A-87 Allocable Costs.
From "Allocations"
• When the Total Citywide A-87 Allocable Costs for cost pools is equal to zero, costs
allocated to these cost pools as part of the primary allocation will not be passed on as part
of the secondary allocation. "Total Allocated" in the "Primary Allocation" table is the
source of these amounts which are shown in the Costs Allocated to Ineligible Cost Pools
column in the A-87 section.
Links to Other Spreadsheets
To "Allocations"
• Total Allocable Citywide Costs are the "Amount Allocated" costs that are being allocated
to the "Indirect Cost Pools" and"Direct Service Departments". When the OMB A-87
option is turned on, Total Citywide A-87 Allocable Costs are the "Amount Allocated"
costs that are being allocated to the "Indirect Cost Pools" and"Direct Service
Departments".
•FCS GROUP 21
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
12.ALLOCATIONS
Overall Description of Spreadsheet
The purpose of the spreadsheet is to allocate citywide indirect costs by choosing their allocation
factors. Once the allocation factors are chosen, the primary and secondary cost allocations are
calculated for each indirect cost pool. The layout of the spreadsheet is separated into three
tables:
• Primary Allocation — This table is where the user determines the allocation factors used
to allocate indirect costs. This method is used for both primary and secondary allocation
steps. In the primary allocation, citywide indirect costs are allocated to all departments
including those departments that provide indirect or support services
• Secondary Allocation — The secondary allocation takes the indirect service departments'
share of those primary allocated costs and reallocates those costs to the direct service
departments.
• Total Allocation—This table summarizes the primary and secondary allocations.
Procedure to Update Spreadsheet
• Enter/Update the allocation factor for each indirect cost pool. A drop down list is
provided in the top left hand corner of the spreadsheet to assist the user in looking up the
allocation factor numbers. Allocation factor numbers are entered in Row 6 of this
spreadsheet.
• There is no further data entry needed on this spreadsheet.
• Note: There are many formula checks on this spreadsheet to verify that each step in
the allocation process is working correctly. An "ERROR!" prompt will appear if
there is problem with the allocation methods.
Links from Other Spreadsheets
From "Indirect Summary"
• "Total Allocable Citywide Costs" are listed as Amount Allocated at the top of the
spreadsheet. When the A-87 option is turned on, "Total Citywide A-87 Allocable Costs"
are listed as Amount Allocated at the top of the spreadsheet. These are the costs
distributed to the Direct Service Departments.
From "Inputs"
• The "Citywide Indirect Cost Pools" and"Direct Service Departments" lists are used as
the source of the titles on the left-hand side of the table.
• The "Allocation Factors" list is used as the source of the titles for the allocation factors
list at the top of the table.
From "Allocation Factors"
• Allocation factor percentages found at the bottom of the "Allocation Factors"
spreadsheet are used to allocate costs to the Indirect Cost Pools and Direct Service
Departments.
4>FCS GROUP 22
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Links to Other Spreadsheets
To "Indirect Summary"
• When the "Total Citywide A-87 Allocable Costs" for cost pools is equal to zero, costs
allocated to these cost pools as part of the primary allocation will not be passed on as part
of the secondary allocation. Total Allocated in the Primary Allocation table is the source
of these amounts which are shown in the "Costs Allocated to Ineligible Cost Pools"
column in the A-87 section.
To "Summary Schedule"
• The final allocations for the direct service departments, found at the bottom of the
spreadsheet in the Total Citywide Allocation table, are the source for the allocations data
on this spreadsheet.
To "Carry-Forward"
• The final allocations for the direct service departments, found at the bottom of the
spreadsheet in the Total Citywide Allocation table, are the source for the reorganized
allocations data on this spreadsheet.
4>FCS GROUP 23
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
13. SUMMARY SCHEDULE
Overall Description of Spreadsheet
The purpose of this spreadsheet is to provide a summary of the total allocation from the indirect
cost pools to the direct service departments. This summary schedule provides a breakdown of
the citywide indirect cost for each direct service department by indirect cost pool. Departments
can use this spreadsheet to review the value of services provided to them by the indirect cost
pools.
Procedure to Update Spreadsheet
This spreadsheet serves as one of the model's reports and is updated automatically.
Links from Other Spreadsheets
From "Allocations"
• The final allocations for direct service departments, found at the bottom of the
spreadsheet in the "Total Citywide Allocation" table, are the source for the allocations
data on this spreadsheet.
From "Inputs"
• The "Citywide Indirect Cost Pools" and"Direct Service Departments" lists are used as
the source of the titles on the top and left-hand side of the table, respectively.
Links to Other Spreadsheets
• None.
4>FCS GROUP 24
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
14. CARRY-FORWARD
Overall Description of Spreadsheet
The purpose of this spreadsheet is to provide a summary report that is used to calculate the carry-
forward for each direct service department. A carry-forward is an amount that either increases or
decreases the value of the plan's calculated indirect cost that serves as the basis for the direct
service departments' indirect cost rate. The carry-forward allows the City to reconcile the
difference between the estimated and actual indirect costs for a previous year's plan. For
example, an indirect cost plan for 2012 based on actual expenditures would use the last available
year of audited expenditures, which for this example is 2010. The carry-forward for the 2012
plan would be calculated by subtracting the values of the 2010 indirect cost plan (which were
based on 2008 actual costs) from the values of the current 2012 plan (which represents the actual
costs for 2010).
This spreadsheet adds the carry-forward from the previous plan to the results of the current plan
to calculate the Proposed Costs in row 52. The Proposed Costs are the indirect costs that are
used to develop the overhead rates.
Procedure to Update Spreadsheet
• In row 50, enter the amounts from the previous cost plan that were used in the same year
as the actual expenditures that the current cost plan is based on. For example, a 2012
plan based on actual expenditures would use actual expenditures from 2010. The carry
forward would be calculated by subtracting the cost plan amounts used in 2010 from the
2010 actuals being used for the 2012 plan.
Links from Other Spreadsheets
From "Allocations"
• The final allocations for direct service departments, found at the bottom of the
spreadsheet in the "Total Citywide Allocation" table, are the source for the reconfigured
allocations data on this spreadsheet.
From "Inputs"
• The "Citywide Indirect Cost Pools" and "Direct Service Departments" lists are used as
the source of the titles on the left hand side and the top of the table, respectively.
Links to Other Spreadsheets
To "Dept Indirect Cost Rates"
• The Total Allocations for the FY 2012-13 Plan (Using FY 2010-11 Actuals) row is the
source for the values in the "Total Allocations for the FY 2012-13 Plan (Using FY 2010-
11 Actuals)" column. (Note: the years and budget phases included in these titles will be
updated as this information is updated on the "Inputs" spreadsheet).
• The Carry-Forward row is the source for the values in the "Carry-Forward" column.
• The Proposed Costs row is the source of values in the "Proposed Total Costs" column.
4>FCS GROUP 25
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
15. DEPT INDIRECT COST RATES
Overall Description of Spreadsheet
The purpose of this spreadsheet is to calculate the indirect cost rates for each direct service
department. For each direct service department, this indirect cost rate represents its total
citywide indirect cost adjusted by the carry-forward, divided by its total salaries and wages.
This indirect cost rate can be used to calculate a charge for indirect services, based on the value
of the direct services provided by the department. For example, if the current calculated indirect
cost rate is 10% for the Police Department, for every dollar of the Police Department's direct
service cost an additional $0.10 could be charged to help recover its citywide indirect costs.
Procedure to Update Spreadsheet
This spreadsheet serves as one of the model's reports and is updated automatically. However,
the costs serving as the rate base can be changed by unhiding row seven and updating the
corresponding allocation factor number.
Links from Other Spreadsheets
From "Carry-Forward"
• The "Total Allocations for the FY 2012-13 Plan(Using FY 2010-11 Actuals)"row is the
source for the values in the Total Allocations for the FY 2012-13 Plan (Using FY 2010-11
Actuals) column. (Note: the years and budget phase included in these titles will be
updated as they are updated on the "Inputs" spreadsheet).
• The "Carry-Forward"row is the source for the values in the Carry-Forward column.
• The "Proposed Costs"row is the source of values in the Proposed Total Costs column.
From "Allocation Factors"
• The values from the allocation factors table are the source of the costs in column I that
are used to calculate the Indirect Cost Rates. The allocation factor used as the base of the
rates can be changed by changing the reference number on row seven of this spreadsheet
(which is currently hidden).
Links to Other Spreadsheets
None
4>FCS GROUP 26
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
16. LIBRARY/ POLICE / PUBLIC WORKS / COMMUNITY
DEVELOPMENT / MUNICIPAL COURT
RATE WORKSHEETS
Overall Description of Spreadsheets
The purpose of these spreadsheets is to calculate overhead rates so that direct service
departments can recover some of the costs of their overhead. These spreadsheets calculate the
following five overhead rates:
• Citywide Overhead Rate: Recovers the allocated costs calculated by this indirect cost
allocation model.
• Department Overhead Rate: Recovers other departmental overhead costs, such as the costs of
the department director.
• Paid Time Off Rate: Recovers the value of paid time off(e.g. vacation, sick leave, etc.).
• Admin Time Rate: Recovers the value of administrative time (e.g. meetings, mandatory
breaks, etc.).
• Benefits Rate: Recovers the costs of benefits.
These rates are designed to be applied to salaries and wages. To calculate the related overhead
costs, simply multiply salary and wage costs by these rates. These rates can be used separately or
added together and used as combined rates.
These rates are designed to recover their related costs over the total salaries and wages or
available billable hours per year. Total salaries and wages were used to calculate the Citywide
Overhead,Dept. Overhead, and Benefits rates, while available billable hours per year were used
to calculate the Paid Time Off and Admin Time rates.
Available billable hours per year is the net of 2,080 hours (i.e. annual paid hours for a typical
FTE) less total hours of paid time off and administrative time. For example, if an employee had
280 hours of paid time off and 200 hours of administrative time, their available billable hours
would be 1,600 (i.e. 2,080 -200 -280 = 1,600). The Paid Time Off and Admin Time rates would
then be designed to recover the full value of paid time off and administrative time over 1,600
hours. City staff provided both the average annual hours of paid time off and the average hours
of administrative time for each department. Using this information, the average available billable
hours were calculated for each department and the Paid Time Off and Admin Time rates
developed.
Procedure to Update Spreadsheets
In order to update the rates, each spreadsheet has three sections/boxes that need to be
reviewed/updated. These include:
4>FCS GROUP 27
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
• Total Department Divisions' Costs: The costs of the department are entered here by
division and cost category. Make sure to enter all of the division costs here, since the
Total Dept. Divisions' Cost at the far right hand side of this box can be used to distribute
overhead costs among the department's divisions. Costs entered into the Dept. Overhead
column are designated to be allocated between the department's divisions. Overhead
costs for divisions can be entered into the Dept Divisions'Indirect Costs. Costs entered
into Direct Salaries and Wages serve as the base of the rates. Costs entered into Total
Benefits are used to develop the benefits rates. Unallowable includes those costs excluded
according to A-87 guidelines, and Expenditures for all other purposes is a catch-all for
any remaining costs.
• Citywide, Department, and Division Overhead Allocation: This box can be used to
allocate department overhead costs among the department's divisions, using allocation
factor data entered into the Department Allocation Data box. Enter the costs that will be
allocated as overhead in the grey area at the top of this box labeled Overhead Costs. Each
row has its own individual column where costs are allocated. To change the way costs are
being allocated, select the desired allocation factor from the drop down list in the grey
box at the bottom of each column.
• Department Allocation Data: This box is where allocation factor data can be entered for
allocating costs across the department's divisions. Direct Salaries and Wages and Total
Dept. Divisions' Costs are also available to be used as allocation factor data, and are
coming from the Total Department Divisions' Costs box.
These sections/boxes are shown below in Exhibit 3.
4>FCS GROUP 28
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Exhibit 3
Overhead Rates (Rate Base=Direct Salaries&Wages)
Total Citywide Dept.
Departmental Divisions Rate Overhead Overhead Paid Time Off Admin Time Benefits
Library Readers'Services 178.54% 83.95% 28.77% 13.36% 14.29% 38.16% Check O.K.
Library Technical Services 154.46% 51.41% 31.27% 13.36% 14.29% 44.13% Check O.K.
Library Circulation 127.88% 38.28% 30.13% 13.36% 14.29% 31.83% Check O.K.
Dept.Average 154.54% 69.52% 20.70% 13.36% 14.29% 36.67%
Library Department:Total Department Divisions'Costs
Dept. Direct Expenditures Total Dept.
Dept. Total
Unallowable Divisions' Salaries for all other Divisions'
Overhead Indirect and Wages Benefits purposes Cost
Departmental Divisions Costs
Library Administration $ 402,480 $ 611,353 $1,013,833
Library Readers'Sendces $ 543,511 $ 851,540 $ 324,990 $ 662,273 $2,382,315
Library Technical Services $ 195,170 $ 327,199 $ 144,395 $ 75,524 $ 742,288
Library Circulation $ 393,036 $ 765,729 $ 243,702 $ 30,372 $1,432,839
Total $ 402,480 $1,743,070 $ - $1,944,468 $ 713,087 $ 768,170 $5,571,275
Citywide,Department,and Division Overhead Allocation
Overhead Costs
Libary Admin-Personnel Costs $ 339,928 $ 339,928 I
Library Admin-M&S $ 62,552 $ 62,552
Back Out Citywide Library Admin OH $ (175,540) $ (175,540)
Reallocate Citywide Library Admin OH $ 175,540 $ 175,540
$ -
$ -I
$ -
Dept. Back Out Reallocate
Divisions' Citywide Libary Admin Library Citywide Citywide
Indirect Overhead -Personnel Admin- Library Library Total
Divisions Costs Allocation Costs M&S Admin OH Admin OH Overhead
Library Administration I$ - $ 175,540 $ - $ - $ (175,540) $ - $ - $ - $ - $
Library Readers'Services $ - $ 714,881 $ 140,759 $ 27,393 $ - $ 76,874 $ - $ - $ - $ 959,908
Library Technical Services $ - $ 168,228 $ 62,240 $ 10,526 $ - $ 29,538 $ - $ - $ - $ 270,532
Library Circulation $ - $ 293,117 $ 136,929 $ 24,633 $ - $ 69,127 $ - $ - $ - $ 523,806
Total $ - $1,351,766 $ 339,928 $ 62,552 $ (175,540) $ 175,540 $ - $ - $ - $1,754,246
Allocate De partmental Indirect Costs Using Direct FTEs DirectSalaries RermneAdmn DirectSalaries
and Wages Citywide OH and Wages
Department Allocation Data
Direct Total Dept. Remove
Salaries and Divisions' Direct FTEs Admin
Wages Cost Citywide OH
Library Administration $ - $1,013,833 1
Library Readers'Services $ 851,540 $2,382,315 14.70
Library Technical Services $ 327,199 $ 742,288 6.50
Library Circulation $ 765,729 $1,432,839 14.30
Total-$1,944,468 $5,571,275 35.50 1 0 0 0
As shown above, the overhead rates are found at the top of the spreadsheet in the table labeled
Overhead Rates. To change the total hours paid, average hours of paid time off, and average
hours of administrative time that are used to develop the Paid Time Off and Admin Time rates, go
to the "Hours for Paid Time Off and Admin Rates" table in the "Inputs" spreadsheet.
It should be noted that the overhead rates for Municipal Court are calculated on a simpler
worksheet, since its costs did not need to be allocated among divisions. Municipal Court's costs
should be entered in the same way that the other departments' costs are entered on their rate
worksheets. However since there is only a department-level rate for Municipal Court, no costs
needed to be allocated between divisions.
4>FCS GROUP 29
CITY OF TIGARD,OREGON
INDIRECT COST ALLOCATION MODEL
Links from Other Spreadsheets
From Inputs
• The names of the divisions are being assigned to these spreadsheets using the rate codes
(i.e. Ml, M2, M3, M4) in the "Dept. Realloc. /Rate Codes" column.
• The "Ttl Hrs Paid", "Avg Paid Time Off', and"Avg Admin Time" are being used to
develop the Paid Time Off and Admin Time rates in the formulas of the Overhead Rates
table.
From Allocations
• When the A-87 mode of the model is turned off, the costs in the Citywide Overhead
Allocation column come from the "Total Allocated" column of the "Total Citywide
Allocation"table at the bottom of this spreadsheet.
From Dept Indirect Cost Rates
• When the A-87 mode of the model is turned on, the costs in the Citywide Overhead
Allocation column come from the "Proposed Total Costs" column, in order to incorporate
any carry forward calculations.
Links to Other Spreadsheets
• None
4>FCS GROUP 30