Run the Rate Calculation Financial Report and Populate the Rate Calculation Template

1. Run the Rate Calculation Financial Report in EDDIE
   1. See the job aid “Service Activities – How to Run the Rate Calculation Financial Report in EDDIE”
2. Download the Rate Calculation Template for the number of lines of service in the service activity
   1. Rate Calculation Template_5 Lines
   2. Rate Calculation Template_10 Lines
   3. Rate Calculation Template_20 Lines
   4. Rate Calculation Template_50 Lines
   5. Rate Calculation Template_Markup
   6. Rate Calculation Template_Markup by Direct Labor
      1. BusFin Service and Storeroom Centers
   7. NOTE: The Rate Calculation Templates can be modified as needed but be careful not to overwrite needed formulas or cell references
3. Populate the Rate Calculation Template with the Banner financial data from Rate Calculation Financial Report
   1. See the job aid “Service Activities – How to Populate the Rate Calculation Template”

Instructions – Service Detail

1. Populate the “Service Detail” tab in the rate calculation template with the data on the “Summary” tab of the EDDIE Rate Calculation Financial Report
   1. Including the Service Activity Fund code and title, Chart of Account, College, Department codes, and Base Fiscal Year of the rate calculation
2. Complete the “Service Activity Fund Details & Description” section
   1. Enter information about the Service Activity Fund, answer the questions, and provide a brief description of operations and services provided
3. Attach the Mobius View Financial Statement
   1. Run the Mobius View Financial Statement for period 14 of the base fiscal year
   2. Include a screen shot in the “Mobius View Financial Statement” section
   3. See the job aid “Using Mobius View”
4. Reconcile Mobius View to the EDDIE Rate Calculation Financial Report
   1. NOTE: The Mobius View Reconciliation can be completed after the “Exp” and “Fund Balance” tabs have been populated with the data from the Rate Calculation Financial Report
   2. Verify that the “CM Ending Bal” total for “Expenditure Control” from Mobius View matches the “Total Expenditures” total on the “Exp” tab of the EDDIE report
   3. Verify that the “CM Ending Bal” total for “Total Fund Balance” from Mobius View matches the “Fund Balance” total on the “Fund Balance” tab of the EDDIE report

Instructions – Base

1. Populate the Usage Base Detail data on the "Base Detail" tab
   1. Include the full supporting usage base data for the service activity in the “Base Detail” tab
   2. NOTE: This detail is not available in Banner and therefore not on the Rate Calculation Financial Report. It must be obtained from the Service Manager
2. Compile the usage base data into totals by line of service
   1. Compile the usage base data on the “Base Detail” tab with the full supporting usage base data for the service activity
   2. A pivot table may be used to compile this information depending on the available information for each line of service
3. Populate the "Base" tab, "Usage Base Summary and Lines of Service" section
   1. Complete the detail information for all Lines of Service columns, columns: “Activity/Program Code," “Lines of Service/Product Descriptions,” and “Units of Measure”
   2. Enter the total usage base for each line of service in the "Total Usage Base in Base Year" column (these amounts must be supported by the data on the "Base Detail" tab)
4. If applicable, adjust for any errors or non-billable units in the "Adjustments, Corrections and Projections" field (these may include maintenance, downtime, failed tests, etc.)
   1. Any base projected changes must be supported by proper documentation
5. “Notes” column
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” column

Instructions – Revenue

1. Populate the "Revenue Detail" tab
   1. Copy the revenue data from the “Revenue Detail” tab of the EDDIE Rate Calculation Financial Report and paste into the corresponding “Revenue Detail” tab of the Rate Calculation Template
2. Populate the "Revenue" tab
   1. Copy the data from the “Revenue” tab of the EDDIE Rate Calculation Financial Report and paste into the corresponding “Revenue” tab of the Rate Calculation Template workbook
      1. Include columns: “Revenue Account Code,” “Revenue Account Title,” and “Total Banner Revenue in Base”
      2. Do not include the header or totals rows
      3. Add rows as needed by right-clicking the row number and selecting “insert”
3. Record any revenue posted to 307921 in the cell next to “Revenue Posted to 307921”.
   1. This amount is used in the “Fund Balance” tab as a Fund Balance Adjustment
4. Allocate the revenue totals to the lines of service
   1. Allocate the revenue total to the respective lines of service using the billing information from the service manager and/or billing system
   2. Verify that the total in the “Total Allocated” column matches the “Total Adjusted Revenue” column
5. “Notes” column
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” column
6. Reconcile the Banner Revenue to the Revenue from Usage Base
   1. Enter the billing rates in the "Internal Billing Rates During Base Year" row
   2. Verify that the Usage Base units have been entered on the "Base" tab
   3. Enter any adjustments in the "Adjustments and Mid-Year Rate changes" row
      1. Any items listed should be explained in the "Notes" column
   4. Ensure the “Total Banner Revenue” matches the “Calculated Revenue from Usage Base”
   5. Provide a detailed explanation of any amount of “Unreconciled Revenue”
7. NOTE: The Account Code Search tool can be used to verify the most accurate revenue account code is being used

Instructions – Expenditures

1. Populate “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 “Exp” tab
   1. Copy the data from the “Exp” tab of the EDDIE Rate Calculation Financial Report into the corresponding sections of the “Exp” tab of the Rate Calculation Template
      1. Include columns: “Account Code”, “Expenditure Description”, and “Total Expenditures”
      2. Do not include the header or totals rows
      3. Add rows as needed by right-clicking the row number and selecting “insert”
   2. Populate the “3E Expenditures” section with the 1xxxxx level account codes data
   3. Populate the “Personnel Expenditures” section with the with the 21xxxx level account codes data
   4. Populate the “Transfers” section with the with the 415xxx level account codes data
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"
4. Review all expenditures and make needed adjustments, corrections, exclusions, and projections
   1. NOTE: All expenditures must be directly related to and benefit the service activity’s operations, as well as comply with System policies, federal regulations, and state regulations
5. “Adjustments and Corrections” column
   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. Examples of these types of expenditures 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)
      7. Equipment depreciation will be included in the Rate Calculation on the “Equip” tab
6. “Exclusion of Unrelated Expenditures” column
   1. These may include expenses which are unrelated to the service and mistakenly posted to the fund.
   2. 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
   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. Examples of 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
7. “Exclusion of Unallowables for Internal Rates” column
   1. These may include expenses unallowable for inclusion in internal rates but may be included in external rates
   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. Examples of 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
   4. These exclusions should also be included in the “External” section “Additional Expenditures for External Rates Only”
8. “Projections” section
   1. These may include any significant anticipated changes in expense or additional future expenses
   2. Enter amounts as positive values 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.
9. “Notes” column
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” columns
10. 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
    2. Expenditures in the “Exclusion of Unallowables for Internal Rates” column should be re-entered as positive values in the “Inclusion of Unallowables for External Rates” column
    3. Include any additional expenditures paid on unrestricted or State funds which support the service activity and are billable to external customers
11. Allocate the expenditure totals for each section to the lines of service
    1. If activity codes were used in Banner than 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
12. 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

Instructions – Salaries and Wages

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
      1. Include columns: “Personnel Name”, “Title”, “UIN”, “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 for 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. Add new hires with projected salary data. Their “Base Year 3E Salaries Total” should remain $0.
6. “Notes” column
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” columns
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 “3E Salaries section”, in the “Banner Annual Salary” and “Projected Percentage Increase” columns
   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. Allocated 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 – allocation may be 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

Instructions – Equipment Depreciation

NOTE: The depreciation tabs of the EDDIE Rate Calculation Financial Report return all equipment for the Org code in Banner (Equip by Org, 3E Equip, Non-3E Equip)

1. Populate the “Equip Detail” tab, “3E Equipment” section
   1. Copy the data from the “3E Equip” tab of the EDDIE Rate Calculation Financial Report and paste into the “Equip Detail” tab – “3E Equipment” section of the Rate Calculation Template
   2. Include only 3E equipment related to the service activity, which can be identified using the “Fund Src Fin Fund Code”, matching the service activity fund code
      1. Verify with the service activity fund manager that the equipment is not used by another service activity
      2. Add rows as needed by right-clicking the row number and selecting “insert”
2. Populate the “Equip Detail” tab, “Non-3E Equipment - Internal Rates” section
   1. Copy the data from the “Non-3E Equip” tab of the EDDIE Rate Calculation Financial Report and paste into the “Equip Detail” tab – “Non-3E Equipment – Internal Rates” section of the Rate Calculation Template
   2. Include only non-3E equipment related to the service activity, which can be identified using the “Fund Attribute Value” (FAV) matching the service activity fund code
      1. If the FAV field is blank, verify with the service activity fund manager what equipment is used in the service activity
      2. NOTE: A list of any non-3E equipment that is used in the service activity without a FAV should be compiled and sent to UAFR Property Accounting to update the FAV fields
      3. Verify with the service activity fund manager that the equipment is not used by another service activity
      4. Add rows as needed by right-clicking the row number and selecting “insert”
3. Verify Allowability of non-3E equipment for internal rates, “Equip Detail” tab section “Non-3E Equipment – Internal Rates”
   1. Verify that “Equipment Fund Type” code is allowable for internal rates, refer to the “Equipment Fund Type Allowability” table at the bottom of the “Equip Detail” tab
   2. Verify that each piece of equipment has “3100” or “3110” Entity code listed in the “Entities Attribute Value” field
      1. 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
   3. 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. Populate the “Non-3E Equipment – External Rates Only” section (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”)
5. Complete the “Projected Equipment Depreciation” section (if applicable)
   1. Enter information for “Asset Description”, “Acquisition Date”, “Depreciation Start Date”, “Fund Src Amount”
   2. Use the asset’s commodity code to determine the “Estimated Life in Years” (refer to UAFR’s list of commodity codes)
   3. 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
6. Populate the “Equip” tab, with the equipment listed in the “Equip Detail” tab
   1. Populate the sections “3E Equipment”, “Non-3E Equipment”, “Projected Equipment”, and “EXTERNAL – Equipment Depreciation Summary for External Rates Only” by copying the data from the respective sections on the “Equip Detail” tab
      1. Include columns: “Ptag Number”, “Equipment Description”, “Base Year Depreciation Expense”
      2. Add rows as needed by right-clicking the row number and selecting “insert”
   2. Include any exclusions in the “Depreciation Exclusions” column with negative values
7. “Notes” column
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” column
8. Allocate total depreciation to their respective lines of service
   1. If activity codes were used in Banner than 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 amounts for all 3E equipment on the on the “Equip Depr” 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 “Exp” 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

Instructions – Fund Balance

NOTE: Banner records a positive fund balance as a deficit, and a negative fund balance as a surplus. All columns 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. Copy the Fund Balance total to the “End of Year Fund Balance” field
3. Calculate the Adjusted Fund Balance by verifying the correct amounts for each Fund Balance Adjustment
   1. NOTE: The Fund Balance tab contains numerous formulas referencing other areas of the Rate Calculation Template. These formulas can be modified as needed but verify that totals are correct before manually overwrite needed formulas or cell references
4. “Unrelated / Unallowable Expenditures”
   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 sum of the totals on the “Exp” tab, columns “Exclusion of Unrelated Expenditures” and “Exclusion of Unallowables for Internal Rates”
      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)
5. “3E Equipment Net Asset Value”
   1. Comes from “Equip” tab, “Fund Balance Adjustment – 3E Equipment NAV” total
      1. Enter amount as a negative value
6. “Non-3E Equipment Accumulated Depreciation”
   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
7. “Revenue Posted to 307921 External Rate Differential”
   1. Comes from the “Revenue” tab, "Revenue Posted to 307921 (Revenue from Incremental Upcharges to External Customers)" total
      1. Enter amount as a positive value
8. Calculate the 60 Day Working Capital Reserve
   1. Verify the “Total Cash Expenditures” total and calculation in the “Cash Expenditures for Base Year” box on the “Exp” tab
   2. NOTE: This should not include any projections as these are not “Cash” expenditures
   3. 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
    1. NOTE: Since services 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” column
    1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” column
12. Allocate the “Applied Over/Under Recovery” to the lines of service
    1. The allocation should be based on the profitability of each line of service
    2. Use the “Net Income Analysis for Allocation of Over/Under Recovery” box to calculation the net income or 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: 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
       3. The table calculates allocation percentages for each line of service by Net Income and by Expenditures
    3. 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
    4. Verify that the total in the “Total Allocated” column matches the total in the “Applied Over/Under Recovery” column

Instructions – Review Final Calculated Rates

1. Verify all formulas and cell references used on the “Rates – Internal” tab
   1. Ensure all formulas and references are accurate in the “Non-Personnel Expenditures,” “Salaries & Wages,” “Equipment Depreciation – 3E & Projected,” “Equipment Depreciation – Non 3E,” “Over/Under Recovery Adjustment,” and “Usage Base Units” rows
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 Calculated Internal Rates to the current 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 and references are accurate in the “Non-Personnel Expenditures,” “Salaries & Wages,” “Equipment Depreciation – 3E & Projected,” and “Equipment Depreciation – Non-3E” rows
   2. Also ensure all formulas and references are accurate in the “Additional Expenses for External Rates Only” section, rows “Inclusion of Unallowables for External Rates”, “Non-3E Salaries & Wages”, “Equipment Depreciation for External Rates”, “Over/Under Recovery Adjustment”, and “Usage Base Units”
   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 current 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. Maintain supporting documentation
   1. Ensure 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 adjustments 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 requested 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 and items to discuss with the service activity fund manager
   1. Coordinate with the service activity fund manager and relevant offices to complete final administrative tasks:
   2. Journal Vouchers (JVs) - Perform any needed Journal Vouchers to move unrelated expenditures off the service activity fund
   3. Fund Type Update - 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
   4. 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 - "Revenue from Incremental Upcharges to External Customers for Overhead Expenses”
   5. 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
   6. Plant Fund Transfers - Complete any needed transfers to plant fund
   7. Subsidy Program Codes - Contact UAFR to set up a subsidy program code with “SNS” as the A-21 code (if applicable). Fill out the “Banner Fund, Program, Index Code Request Form”
      
      1. Submit an email request to uas@ullinois.edu

Markup Rate Calculations

In addition to the instructions for Rate Calculations, a Markup Rate Calculation includes the following items:

Service Detail Tab (Markup Rate) – Include Beginning and Ending Inventory

• The “Reconciliation – Mobius View to Rate Calculation Financial Report” section includes two additional fields to populate
  • Enter the “Beginning Inventory” amount from the total in the in the "PY Ending Bal" for account 55000 "Inventory for Resale" field of the Mobius View report
  • Enter the “Ending Inventory” amount from the total in the in the "CM Ending Bal" for account 55000 "Inventory for Resale" field of the Mobius View report
  • NOTE: The Mobius View report must be for period 14 for these amounts to be accurate

Expenditures Tab (Markup Rate) – Remove POGR

• “Adjustments and Corrections” column includes adjustments for out the Purchase of Goods for Resale (POGR)
  • Fully adjust out the Purchase of Goods for Resale, account code 187100
  • Also adjust for any expenditures which were erroneously classified as operating expenditures when they should have posted to 187100 "Purchase of Goods for Resale"

Fund Balance Tab (Markup Rate) – Include POGR in the 60 Day Working Capital Reserve Calculation

• The 60 Day Working Capital Reserve, on the “Fund Balance” tab, will be calculated using Purchase of Goods for Resale (POGR) as well as the Total Cash Expenditures
  • Verify the “Purchases of Goods for Resale" amount. This is the total for account 187100 "Purchase of Goods for Resale" from the “Exp” tab along with any POGR adjustments for "Reclassified Expenses to COGS", "Year End Fact Sheet Inventory Adjustment Reversal" on the COGS tab
  • NOTE: The Purchase of Goods for Resales is considered part of cash expenditures and should be included in that total to calculate the 60 Day Working Capital Reserve

Instructions - COGS (Markup Rate)

NOTE: The Cost of Goods Sold is not recorded in Banner and must be calculated manually

1. Complete the applicable Cost of Goods Sold adjustments in the "Cost of Goods Sold Calculation" table on the “COGS” tab
2. "Add: Beginning Inventory" amount
   1. Populate from the Mobius View report for period 14 for the base fiscal year. The Beginning Inventory amount is the total in the in the "PY Ending Bal" for account 55000 "Inventory for Resale"
3. "Add: Purchases of Goods for Resale" amount
   1. Populate from the "Exp" tab, total expenditure in the fiscal year for account 187100 "Purchase of Goods for Resale"
4. "Add: Reclassified Expenses to COGS" amount
   1. Populate from the "Exp" tab. Any expenditures which were erroneously classified as operating expenditures when they should have been listed as 187100 "Purchase of Goods for Resale"
5. "Add: Freight" amount
   1. Populate from the "Exp" tab, include any additional Freight charges that were not included in purchase price of inventory for resale
6. "Less: Shrinkage, Obsolescence, Spoilage, etc." amount
   1. Populate from the "Exp" tab, include any adjustments made to inventory for resale. For example, shrinkage, obsolescence, spoilage, etc.
7. "Less: Estimated Inventory Credits" amount
   1. Populate with any credits against inventory for resale – such as purchase returns
8. "Less: Year End Fact Sheet Inventory Adjustment Reversal" amount
   1. Populate with any transactions listed in the "Exp Detail" tab in account 187100 "Purchase of Goods for Resale" as Fact Sheet Inventory Adjustments on the last day of the fiscal year
   2. NOTE: Reverse the sign of the transaction. For example, if the adjustment is a negative number, list as a positive number on this tab, and vice versa
9. "Less: Ending Inventory" amount
   1. Populate from the Mobius View report for period 14 for the base fiscal year. The Ending Inventory amount is the total in the in the "CM Ending Bal" for account 55000 "Inventory for Resale"
10. “Notes” column
    1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” columns

Markup Rate Calculation Based on Direct Labor

In addition to the instructions for Rate Calculations, a Markup rate calculation based on direct labor includes the following modified instructions for Salaries and Wages:

Instructions – Salaries and Wages (Markup Rate Based on Direct Labor)

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. Separate the employees and salaries expense into "Indirect" and "Direct" categories
   1. NOTE: The "Direct 3E Salaries" section is used to capture the employees' salaries that are directly billed to customers as the base charge for services. These employees must be appointed to the 3E service activity fund
3. Populate both the both "Indirect 3E Salaries" and "Direct 3E Salaris" areas for the “Salaries” tab, “3E Salaries” section
   1. Copy the data from the “Salaries” tab of the EDDIE Rate Calculation Financial Report into the “Indirect 3E Salaries” and “Direct 3E Salaries” sections of the “Salaries” tab on the Rate Calculation Template
      1. Include columns: “Personnel Name”, “Title”, “UIN”, “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”
4. Populate both the "Indirect 3E Salaries" and "Direct 3E Salaris" areas for the “Current Banner Annual Salary”, “Projected Percentage Increase”, “Percentage of FTE on Service”, and "Percentage of Salaries Classified as Indirect or Direct" 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. The "Percentage of Salaries Classified as Indirect or Direct" is the percentage of salaries classified as Indirect or Direct
   5. NOTE: For employees whose salaries are split between Indirect and Direct, the sum of both parts must match the total projected 3E salaries expense
   6. Verify that the "Direct 3E Salaries" total is accurate
   7. NOTE: Direct Salaries are included in the denominator of the Markup Rates. They represent the base charge, which is marked up to include the remaining indirect expenses to support the service activity
5. Determine if there should be an exclusion for 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
6. 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. Add new hires with projected salary data. Their “Base Year 3E Salaries Total” should remain $0.
7. “Notes” column
   1. Provide a clear and concise explanation for every adjustment, correction, exclusion, or projection in the “Notes” columns
8. Complete both the "Indirect Non-3E Salaries" and "Direct Non-3E Salaris" areas of the “EXTERNAL - Additional Related Non-3E Salaries & Wages for External Rate Only” section (if applicable)
   1. Separate the non-3E employees and salaries expense into "Indirect" and "Direct" categories
   2. 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”
   3. Provide the same information for both “Indirect Non-3E Salaries” and “Direct Nom-3E Salaries” areas for the “3E Salaries section”, in the “Banner Annual Salary”, “Projected Percentage Increase”, and "Percentage of Salaries Classified as Indirect or Direct" columns for each employee
      1. 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)
      2. The "Percentage of Salaries Classified as Indirect or Direct" is the percentage of salaries classified as Indirect or Direct
   4. NOTE: Contact System Government Costing if non-3E employee salaries are included in the Direct Non-3E Salaries to verify applicable policy and allowability

Relevant Links

EDDIE Rate Calculation Financial Report

https://eddie.ds.uillinois.edu/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=ATWk8zA3EEdJhnIqqi9iELI

EDDIE Rate Calculation Financial Report with Depreciation

https://eddie.ds.uillinois.edu/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=ASQYeM9OPqxCmTgDk9xNF1I

EDDIE Equipment Depreciation Report

https://eddie.ds.uillinois.edu/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=AeYk64fX8i1PuoXZ2Pl.WbA

Mobius View

https://mobiusview.apps.uillinois.edu/mobius/view

BusFin Service and Storeroom Centers

https://www.busfin.uillinois.edu/accounting_budgeting/service_and_storeroom_centers

Job Aid “Using Mobius View”

https://answers.uillinois.edu/training/120759

Job Aid “Service Activities - How to Run the Rate Calculation Financial Report in EDDIE”

https://answers.uillinois.edu/training/134969

Job Aid “Service Activities - How to Populate the Rate Calculation Template”

https://answers.uillinois.edu/training/134975

Job Aid “Service Activities - How to Perform a Rate Calculation Using the Template”

https://answers.uillinois.edu/training/119618

Service Activity Basics Training

https://answers.uillinois.edu/training/120769

Service Activity Advanced Certification Track

https://answers.uillinois.edu/training/120770

Unallowable Expenditures

https://www.busfin.uillinois.edu/cms/One.aspx?portalId=1993898&pageId=2103503

Commodity Codes

https://www.busfin.uillinois.edu/buying_contracts/procurement_methods/commodity_codes

Facilities & Administrative (F&A) Rates

https://www.busfin.uillinois.edu/accounting_budgeting/rate_schedules_development/facilities_administrative_rates

Account Code Search

https://accountcodesearch.uillinois.edu/

Banner

https://apps.uillinois.edu/banner

SPA Service Activity Rate Database

https://servicerates.research.illinois.edu/default.aspx

UAFR “Banner Fund, Program, Index Code Request Form”

https://www.busfin.uillinois.edu/forms/accounting_budgeting_forms/accounting-financial-reporting

Uniform Guidance

https://www.ecfr.gov/current/title-2/subtitle-A/chapter-II/part-200

Contact System Government Costing

obfsgcocosting@uillinois.edu

André Britten - UIUC

217-244-1245

abritten@uillinois.edu

gcocostuiuc@uillinois.edu

Fred Kirstein - UIC

217-333-1088

kirstein@uillinois.edu

gcocostuic@uillinois.edu