Service Activities - How to Perform a Rate Calculation Using the Template

1. Populate the Exp Detail tab.
   1. Copy the expenditures data from the Exp Detail tab of the EDDIE Rate Calculation Financial Report and paste into the corresponding Exp Detail tab of the Rate Calculation Template.
2. Populate the Expenditures tab.
   1. Populate the 3E Expenditures section with the 1xxxxx level account codes data.
   2. Populate the Personnel Expenditures section with the 21xxxx level account codes data.
   3. Populate the Transfer section with the 415xxx level account codes data.
   4. Copy the data from the Expenditures tab of the EDDIE Rate Calculation Financial Report into the corresponding sections of the Expenditure tab of the Rate Calculation Template for fields:
      1. Expenditure Account Code
      2. Expenditure Account Title
      3. Base Year Banner Expenditures Total
      4. Do not include the header or totals rows.
   5. Add rows as needed by right clicking the row number and selecting insert.
3. Reconcile Expenditures to Mobius View.
   1. Ensure that the Total Expenditures for Base Year from Banner (or Total 3E Expenditures for Internal Rate + Total Personnel Expenditures + Total Transfers) matches the Mobius View report Expenditure Control line CM Ending Bal.
   2. NOTE: This analysis can also be done on the Service Detail tab in the Reconciliation – Mobius View to Rate Calculation Financial Report.
4. Adjustments and Corrections – Review all expenditures and make needed adjustments and corrections.
   1. These may include corrections for misclassified transactions or adjustments that are allowable but that should be excluded from the rate calculation for accuracy.
   2. Enter amounts as negative values (or reverse the sign of the transaction. If the original transaction is a negative number, list as a positive number).
   3. Types of expenditures that should be adjusted or corrected include the following:
      1. Timing or errors
      2. Prior year expenditures
      3. Correcting entries
      4. Negative total for an account code
      5. Expenses for an individual customer
      6. Capitalized equipment expenses (such as 128xxx, 163xxx, 164xxx accounts)
   4. Equipment depreciation will be included in the Rate Calculation on the Equip tab.
5. Exclusion of Unrelated Expenditures – Review all expenditures for unrelated expenditures.
   1. NOTE: All expenditures must be directly related to and benefit the service activity’s operations. A table explaining how to process exclusions by type is at the bottom of this tab for reference.
   2. These may include expenses which are unrelated to the service and mistakenly posted to the fund.
   3. These expenses must be removed from the rate calculation, and a JV transaction must be processed to remove them from the fund, if not done so already.
   4. Enter amounts as negative values (or reverse the sign of the transaction. If the original transaction is a negative number, list as a positive number).
   5. These types of expenditures include the following:
      1. Expenses which do not benefit the purpose of the fund
      2. Salaries and wages for employees who do not support the service
6. Exclusion of Unallowables for Internal Rates – Review all expenditures for unallowable expenditures.
   1. NOTE: All expenditures must comply with all federal, state and University policies. A table explaining how to process exclusions by type is at the bottom of this tab for reference.
   2. These may include expenses unallowable for inclusion in internal rates but may be included in external rates.
   3. Enter amounts as negative values (or reverse the sign of the transaction. If the original transaction is a negative number, list as a positive number).
   4. These types of expenditures include the following:
      1. Credit Card fees
      2. Bad debt
      3. Unallowable expenses due to being included in F&A rates
      4. Unallowable expenses for internal customers due to policy
      5. Refer to University policy.
   5. NOTE: These exclusions should also be included in the External section Additional Expenditures for External Rates Only.
7. Projections section – Include any material and relevant projections.
   1. These may include any significant anticipated changes in expense or additional future expenses.
   2. Enter an addition of expenditures as a positive number and a decrease of expenditures as a negative number in the Adjustments and Corrections column of the Projections section.
   3. Projections should include supporting documentation in the Rate Calculation Template, such as a screenshot of the quote, PO, invoice, etc.
8. Notes – Add relevant notes in the Notes section.
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection.
9. Complete the EXTERNAL – Additional Related Expenditures Unallowable in Internal Rates, for External Rates Only section (if applicable).
   1. Include any expenditures unallowable for internal customers per University policy, or which are unique to external customers only.
      1. Expenditures in the Exclusion of Unallowables for Internal Rates column in the 3E section of this tab should be re-entered as positive values in the Inclusion of Unallowables for External Rates column.
      2. Include any additional expenditures paid on unrestricted or State funds which support the service activity and are billable to external customers.
10. Allocate the expenditure totals for each section to the lines of service.
    1. If activity codes were used in Banner, then a pivot table can be created to allocate expenses to the lines of service.
    2. Expenditures which benefit all lines of service may be allocated by usage base or another method that is reasonable, equitable, and supportable.
    3. Verify that the total in the Total Allocated column matches the Total Adjusted Expenditures column.
11. Verify the Cash Expenditures for Base Year box totals.
    1. Verify the totals are accurate and linking to the correct cells in the spreadsheet.
    2. This amount is used on the Fund Balance tab to calculate the 60 Day Working Capital Reserve amount.

1. Populate the Salaries Detail tab.
   1. Copy the Salaries and Wages data from the Salaries Detail tab of the EDDIE Rate Calculation Financial Report and paste into the corresponding Salaries Detail tab on the Rate Calculation Template.
2. Populate the Salaries tab, 3E Salaries section.
   1. Copy the data from the Salaries tab of the EDDIE Rate Calculation Financial Report and paste into the 3E Salaries section of the Salaries tab on the Rate Calculation Template for fields:
      1. Employee Name
      2. Employee UIN
      3. Position Title
      4. Base Year 3E Salaries Total
   2. Do not include the header or totals rows.
   3. Add rows as needed by right clicking the row number and selecting insert.
3. Populate the Current Banner Annual Salary, Projected Percentage Increase, and Percentage of FTE on Service for each employee.
   1. The Current Banner Annual Salary can be obtained on the Banner PEIESUM page or from the service activity fund manager.
   2. Any Projected Percentage Increase can be obtained from the service activity fund manager.
   3. The Percentage of FTE on Service should match the Banner appointment for the employees’ Full Time Equivalent value.
      1. The FTE should accurately represent the amount of the employees’ time benefiting the service activity.
      2. This should be only the FTE portion which is paid from the 3E fund and not from any other funding sources.
4. Determine if there should be an Exclusion of Salaries Over the HHS Salary Rate Limit.
   1. Salaries charged to U.S. Department of Health and Human Services (HHS) cannot exceed the Salary Rate Limit (SRL), whether charged directly, as part of the indirect cost rates, or through a service activity.
   2. NOTE: Contact System Government Costing if the service activity bills sponsored projects to verify that HHS grants are not billed salaries over the HHS Salary Rate Limit.
5. Review and make needed adjustments, corrections, exclusions, and projections for the coming fiscal year.
   1. Any terminated employees should be excluded from the Total Projected 3E Salaries total by setting the Percentage of FTE on Service to 0%.
   2. Any new hires should be added with projected salary data. Their Base Year 3E Salaries Total should remain $0.
6. Notes – Add relevant notes in the Notes section.
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection.
7. Complete the EXTERNAL – Additional Related Non-3E Salaries & Wages for External Rates Only section (if applicable).
   1. Include any employees who contribute to the service activity but are paid from non-3E funds, such as unrestricted or State funds.
      1. Add rows as needed by right clicking the row number and selecting insert.
   2. Provide the same information as for the 3E Salaries section:
      1. Banner Annual Salary
      2. Projected Percentage Increase
      3. The Percentage of FTE on Service for only the FTE portion paid from funds other than the 3E fund (not the FTE portion appointed to the 3E fund)
8. Allocate total projected salaries to the lines of service.
   1. Allocate salaries based on the time spent on each line of service using reasonable and supportable data.
   2. Employees who generally benefit all lines of service may be allocated based on usage base or another method that is reasonable, equitable, and supportable.
   3. NOTE: It is best practice to allocate salaries and wages based on a time study.
   4. Verify that the total in the Total Allocated column matches the Projected State Salaries – For External Rates column.

NOTE: Not all of the equipment listed on the Equip Org tab of the EDDIE Rate Calculation Financial Report may be related to the service activity. This tab includes all equipment listed in the Org code in Banner.

1. Populate the 3E Equipment section of the Equip Detail tab.
   1. Review all equipment listed on the Equip Service tab of the EDDIE Rate Calculation Financial Report that has a Fund Type Code of 3E.
      1. This tab identifies equipment that has the service activity fund listed as Fund Src Fin Fund Code or the Fund Attribute Value.
   2. Verify with the service activity fund manager that the equipment listed is used in the service activity and not used in any other service activity.
   3. Copy the equipment data and paste into the Equip Detail tab, 3E Equipment section of the Rate Calculation Template.
      1. Add rows as needed by right clicking the row number and selecting insert.
2. Populate the Non-3E Equipment – Internal Rates section of the Equip Detail tab.
   1. Review all equipment on the Equip Service and Equip Org tab of the EDDIE Rate Calculation Financial Report that has a Fund Type Code of anything other than 3E.
      1. Verify with the service activity fund manager that the equipment listed is used in the service activity and not used in any other service activity.
   2. NOTE: If any non-3E equipment is identified that is used in the service activity but does not have a Fund Attribute Value assigned, then contact UAFR Property Accounting to update the Fund Attribute Value fields with the service activity fund number.
   3. Copy that data and paste into the Equip Detail tab, Non-3E Equipment – Internal Rates section of the Rate Calculation Template.
      1. Add rows as needed by right clicking the row number and selecting insert.
3. Verify the allowability of non-3E equipment for internal rates using the Equipment Fund Type Allowability Table at the bottom of the Equip Detail tab.
   1. Verify that Equipment Fund Type code is allowable for internal rates to be included in the Equip Detail tab section Non-3E Equipment – Internal Rates.
   2. Verify that each piece of equipment has 3100 or 3110 Entity code listed in the Entities Attribute Value field.
   3. NOTE: Any equipment that is not entity coded or was purchased using an unallowable fund type cannot be included in internal rates and must be removed from the Non-3E Equipment – Internal Rates section of the Equip Detail tab.
      1. Unallowable equipment for internal rates may be included in the Non-3E Equipment – External Rates Only section if it meets the requirements in the Equipment Fund Type Allowability table for external rates.
4. Complete the Projected Equipment Depreciation section of the Equip Detail tab (if applicable).
   1. Enter the information for the fields: Depreciation Start Date, Estimated Life in Years, Fund Src Amount, Ptag Number (if known), and Asset Description.
      1. Use the asset’s commodity code to determine the Estimated Life in Years (refer to UAFR’s list of commodity codes).
   2. Projections should include supporting documentation in the Rate Calculation Template, such as a screenshot of the quote, PO, invoice, etc.
      1. NOTE: Projected equipment depreciation takes into account the half year convention depreciation method per University policy.
5. Populate the Non-3E Equipment – External Rates Only section of the Equip Detail tab (if applicable).
   1. May include equipment that is unallowable for internal rates due to Fund Type or lacking an Entity Code, if it meets the requirements in the Equipment Fund Type Allowability table for external rates.
   2. Fully depreciated 3E equipment still in use may be included with a standard year of depreciation (Fund Src Amount / Estimated Life in Years).
6. Populate the Equip tab with the equipment listed in the Equip Detail tab.
   1. Populate the detail in each of the sections:
      1. 3E Equipment section
      2. Non-3E Equipment section
      3. Projected Equipment section
      4. EXTERNAL – Equipment Depreciation Summary for External Rates Only section
   2. Populate the sections by copying the data from the respective sections on the Equip Detail tab for the fields:
      1. Ptag Number
      2. Asset Description
      3. Base Year Depreciation Expense Total
   3. Do not include the header or totals rows.
   4. Add rows as needed by right clicking the row number and selecting insert.
   5. Include any exclusions in the Depreciation Exclusions column as negative values.
7. Notes – Add relevant notes in the Notes section.
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection.
8. Allocate total depreciation to the respective lines of service.
   1. If activity codes were used in Banner, then a pivot table can be created to allocate depreciation to the lines of service.
   2. Equipment that benefits all lines of service may be allocated by a machine usage report, a time study, usage base, or another method that is reasonable, equitable, and supportable.
   3. Verify that the total in the Total Allocated column matches the Total Adjusted Base Year Depreciation column.
9. Verify the Fund Balance Adjustment for Net Asset Value (NAV).
   1. This is the total NAV amount for all 3E equipment on the Equip tab.
10. Calculate the Fund Balance Adjustment for Non-3E Accumulated Depreciation (if applicable).
    1. Compile the total of non-3E depreciation billed to customers for the life of all non-3E equipment.
       1. These may be obtained from previous rate calculations or other records.
    2. Verify and enter any amounts transferred from the 3E fund to a plant fund since the last rate calculation.
       1. The Expenditures tab Transfers section may include transfers from 415xxx accounts.
    3. If no rate calculation was performed 2 years ago, then contact System Government Costing for guidance.
    4. NOTE: Contact System Government Costing to verify applicable policy before including a fund balance adjustment for non-3E accumulated depreciation.

NOTE: Banner records a positive fund balance as a deficit, and a negative fund balance as a surplus. All areas labeled as Fund Balance or Over/Under Recovery must be consistent with this convention.

1. Populate the Fund Balance tab.
   1. Copy the Fund Balance data from the Fund Balance tab of the EDDIE Rate Calculation Financial Report and paste into the Fund Balance for Base Year section of the Fund Balance tab on the Rate Calculation Template.
      1. Do not include the header or totals rows.
      2. Add rows as needed by right-clicking the row number and selecting insert.
2. Make sure the Fund Balance total cell references the End of Year Fund Balance field in the Fund Balance Over/Under Recovery Calculation table.
3. Verify the Unrelated / Unallowable Expenditures adjustment amount.
   1. This is the total of the unrelated expenditures that should be transferred off the fund as well as the exclusion of unallowable expenditures for internal rates.
   2. The total should match the Expenditures tab total for the Exclusion of Unrelated Expenditures and Exclusion of Unallowables for Internal Rates section.
      1. Enter amount as a negative value (or reverse the sign of the sum of transactions. If the sum of the original transactions is a negative number, list as a positive number).
4. Verify the 3E Equipment Net Asset Value adjustment amount.
   1. Comes from Equip tab, Fund Balance Adjustment – 3E Equipment NAV total.
      1. Enter amount as a negative value.
5. Verify the Non-3E Equipment Accumulated Depreciation adjustment amount.
   1. Comes from the Equip tab, section Fund Balance Adjustment – Non-3E Accumulated Depreciation Billed to Customers, the total for Fund Balance Adjustment for Non-3E Accumulated Depreciation.
      1. Enter amount as a positive value.
   2. NOTE: Contact System Government Costing to verify applicable policy before including a fund balance adjustment for non-3E accumulated depreciation.
6. Verify the Revenue Posted to 307921 External Rate Differential adjustment amount.
   1. Comes from the Rev tab, Revenue Posted to 307921 (Revenue from Incremental Upcharges to External Customers) total.
      1. Enter amount as a positive value.
   2. NOTE: The Adjusted Fund Balance includes the End of Year Fund Balance, as well as the ‘addition’ of the other adjustments displayed on this tab: Unrelated / Unallowable Expenditures, Non-3E Equipment Accumulated Depreciation, and Revenue Posted to 307921 External Revenue Differential.
7. Verify the Total Cash Expenditures total.
   1. Comes from the Expenditures tab, Total Cash Expenditures.
   2. This amount only includes the historical expenditures and adjustments, without projections.
   3. NOTE: This should not include any projections as these are not Cash expenditures.
8. Verify the 60 Day Working Capital Reserve calculation.
   1. The 60 Day Working Capital Reserve is an allowance of 2 months’ worth of the cash expenditures for the year and can be excluded from a surplus balance as a reserve. It is calculated as Total Cash Expenditures / 12 x 2.
9. Calculate the Over/Under Recovery by comparing the Adjusted Fund Balance (AFB) to the 60 Day Working Capital Reserve.
   1. If the AFB is in deficit (positive number) – Then the Over/Under Recovery is the entire amount of the AFB (the 60 Day Working Capital Reserve only applies to surplus balances).
      1. Denoted as the fund is Under Recovered.
   2. If the AFB is in surplus (negative number) and smaller than the 60 Day Working Capital Reserve – Then the Over/Under Recovery is $0.
      1. Denoted as the fund is at Break-Even.
   3. If the AFB is in surplus (negative number) and larger than the 60 Day Working Capital Reserve – Then the Over/Under Recovery is the AFB less the 60 Day Working Capital Reserve.
      1. Denoted as the fund is Over Recovered.
10. Calculate the amount of the Over/Under Recovery to be applied to the rates.
    1. NOTE: Since service activities are required to perform a formal rate calculation at least every 2 years, the unit may choose to include half of the Over/Under Recovery in their rate calculation, and therefore recover it over 2 years.
    2. Select 1 or 2 from the dropdown menu in the Over/Under Recovery Taken Over 1 or 2 Years? field.
    3. The Applied Over/Under Recovery amount will update automatically based on the selection.
11. Notes – Add relevant notes in the Notes section.
    1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection.
12. Allocate the Applied Over/Under Recovery to the lines of service based on the profitability of each line of service.
    1. Use the Net Income Analysis for Allocation of Over/Under Recovery box to calculate the net income/loss for each line of service.
       1. Verify that all formulas and cell references are entered correctly to pull the revenue, expenditures, salaries and depreciation totals from their respective tabs.
    2. NOTE: The allocation should ideally be based on the profitability of each line of service – Allocation by Net Income is preferred. However, if the profitability by line does not align with the Over/Under Recovery amount, then it may not be possible to allocate by Net Income.
13. Review and select the most appropriate allocation method for the Over/Under Recovery.
    1. NOTE: Revenue can never be used as an allocation method.
    2. NOTE: The most accurate allocation method is to use codes to track true profitability by line of service. But this requires that all revenue, expenditures, and fund balances be consistently tracked using activity codes. If you would like to use this method, contact System Government Costing to discuss how to implement.
14. Verify that the total in the Total Allocated column matches the total in the Applied Over/Under Recovery column.

1. Verify all formulas and cell references used on the Rates – Internal tab.
   1. Ensure all formulas properly reference all fields in the tabs:
      1. Non-Personnel Expenditures
      2. Salaries & Wages
      3. Equipment Depreciation – 3E & Projected
      4. Equipment Depreciation – Non-3E
      5. Over/Under Recovery Adjustment
      6. Usage Base Units
2. Review the Final Calculated Internal Rates.
   1. Enter the current internal billing rates in the Internal Billing Rates During Base Year row.
   2. Compare Final Calculated Internal Rates to the base year billing rates for reasonableness. Verify that significant changes are not due to errors in expense totals or formulas in the template.
   3. Review the Final Calculated Rates with the service activity fund manager and the department budget office (if required).
   4. NOTE: If the service or department intends to subsidize rates, contact System Government Costing to review applicable policy and determine appropriate documentation and transaction methods.
3. Verify all formulas and cell references used on the Rates – External tab.
   1. Ensure all formulas properly reference all fields in the tabs:
      1. Non-Personnel Expenditures
      2. Salaries & Wages
      3. Equipment Depreciation – 3E & Projected
      4. Equipment Depreciation – Non-3E
      5. Over/Under Recovery Adjustment
      6. Usage Base Units
   2. Also ensure all formulas and references are accurate in the Additional Expenses for External Rates Only section rows:
      1. Inclusion of Unallowables for External Rates
      2. Non-3E Salaries & Wages
      3. Equipment Depreciation for External Rates
   3. Select the Campus of the service center to apply the respective F&A Rates for each line of service.
      1. Decide which F&A rate is most applicable for the services provided by the service activity, Organized Research, Sponsored Instruction, or Other Sponsored Activities.
   4. Review the Effective Date Range.
      1. NOTE: If external rates will be used past the end of the effective date, then the external rates must be recalculated to include the new F&A rates.
4. Review the Final Calculated External Rates.
   1. Enter the current external billing rates in the External Billing Rates During Base Year row, and the Market Rates in the Current Market Rates row (if applicable).
   2. Compare Calculated External Rates to the base year billing rates for reasonableness. Verify that significant changes are not due to errors in expense totals or formulas in the template.
   3. NOTE: If a market exists for your services, external customers should be billed market rates to avoid unfair competition with the private sector.
5. Ensure supporting documentation is maintained.
   1. Verify documentation is complete and easily accessible for all aspects of the rate calculation.
      1. Including: the Rate Calculation Template, EDDIE Rate Calculation Financial Report, Mobius View Financial Report, Base Detail data, allocation methodologies, and projection and adjustment support.
   2. NOTE: All supporting documentation must be retained and readily available in the event of an external audit, internal audit, review by System Government Costing, or request by another regulatory or funding agency.
6. Notify Customers and Post New Rates.
   1. Decide when the calculated rates will go into effect.
   2. Determine how and when to notify customers of price changes.
   3. Post approved rates internally (department records or website), for customers, and in the SPA Rate Database.
7. Post-Calculation Tasks – Coordinate with the service activity fund manager and relevant offices to complete final administrative tasks after the rate calculation has been completed:
   1. Journal Vouchers (JVs) – Perform any needed Journal Vouchers to move unrelated expenditures off the service activity fund.
   2. Fund Type Updates – If most customers are external to the University, contact UAFR to change the self-supporting fund type code from 3E to 3Q.
      1. Submit an email request to uas@uillinois.edu.
   3. Revenue Account Code – Review the revenue account code being used for internal rates and make sure that the external rate differential is being posted to revenue account code 307921.
      1. 307921 – Revenue from Incremental Upcharges to External Customers for Overhead Expenses
   4. Equipment Fund Attribute Values – Contact UAFR Property Accounting to update FAV fields for non-3E equipment used in the service.
      1. Submit an email request to obfsuafrproperty@uillinois.edu.
   5. Plant Fund Transfers – Complete any needed transfers to plant fund.
   6. Subsidy Program Codes – Contact UAFR to set up a subsidy program code with SNS as the A-21 code (if applicable).
      1. Fill out the Banner Fund, Program, Index Code Request Form.
      2. Submit an email request to uas@uillinois.edu.