Service Activities - How to Populate the Rate Calculation Template

1. Populate the Service Detail tab in the rate calculation template with the data on the Summary tab of the Rate Calculation Financial Report. Listed as UR_SA_Rate Calculation Financial Report with Depreciation in EDDIE.
   1. NOTE: This report can also be accessed using the Service Activity Resources page.
   2. Service Activity Fund code
   3. Service Activity Fund title
   4. Chart of Account
   5. College Code
   6. Department Code
   7. Base Fiscal Year
2. Complete the questionnaire for the service activity in the Service Activity Fund Details & Description section.
   1. NOTE: To answer these questions, use the drop-down menu to select either Yes or No.
3. Complete the Description of Service Activity Operations and Services Provided section.
   1. Enter information about the Service Activity Fund and provide a brief description of operations and services provided.
4. Attach the Mobius View Financial Statement.
   1. Run the Mobius View Financial Statement for period 14 of the base fiscal year.
   2. Include a screenshot in the Mobius View Financial Statement section.
   3. Visit the job aid Using Mobius View.
5. Populate Mobius View information in the Reconciliation – Mobius View to Rate Calculation Financial Report section.
   1. Enter Expenditure Control, Transfer Control, and Total Fund Balance for the CM Ending Bal in their respective places in the Mobius View column of the table.
   2. NOTE: The Final Calculated Rates Sheet section will automatically display the completed data from the other tabs of the template.

1. Populate the full supporting Usage Base Detail data on the Base Detail tab.
   1. NOTE: This detail is not available in Banner and therefore not in 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.
2. Populate the Base tab – Usage Base Summary & Lines of Service section.
   1. Complete the Detail information for all Lines of Service columns:
      1. Activity/Program Code (if applicable)
      2. Line of Service/Product Description
      3. Unit 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).
3. If applicable, adjust for any errors or non-billable units in the Adjustments, Corrections and Projections column.
   1. These may include maintenance, downtime, failed tests, etc.
   2. Any projected base changes must be supported by proper documentation.

1. Populate the Rev Detail tab.
   1. Copy the revenue data from the Rev Detail tab of the EDDIE Rate Calculation Financial Report and paste into the corresponding Rev 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.
   2. NOTE: Do not include the header or totals rows.
      1. Revenue Account Code
      2. Revenue Account Title
      3. Base Year Revenue Total
      4. 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. NOTE: The Revenue Reasonableness Review table will automatically update to display the revenue based on the Base tab totals and the rates billed during the fiscal year from the Rates – Internal tab.

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. NOTE: The 3E Expenditures section should include 1xxxxx level account codes, the Personnel Expenditures section should include 21xxxx level account codes, and the Transfer section should include 415xxx level account codes.
   2. 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.
   3. NOTE: Do not include the header or totals rows.
      1. Expenditure Account Code
      2. Expenditure Account Title
      3. Base Year Banner Expenditures Total
      4. Add rows as needed by right clicking the row number and selecting insert.
   4. NOTE: If there are expenditures related to the service posted to other funding sources, they may be included in the EXTERNAL – Additional Related Expenditures Unallowable in Internal Rates, for External Rates Only section. Additional analysis will need to be completed to verify allowability of inclusion of these expenses. Provide backup documentation for these expenditures within the Exp Detail tab or an additional tab.

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.
   2. NOTE: Do not include the header or totals rows.
      1. Employee Name
      2. Employee UIN
      3. Position Title
      4. Base Year 3E Salaries Total
      5. Add rows as needed by right clicking the row number and selecting insert.
   3. NOTE: The Rate Calculation Financial Report will only pull salaries that were posted in Banner for this service activity fund. If there are salaries related to the service posted to other funding sources, they may be included in the EXTERNAL – Additional Related Non-3E Salaries & Wages for External Rates Only section. Provide backup documentation for these salaries within the Salaries Detail tab or an additional tab.

NOTE: The Equip Org tab of the EDDIE Rate Calculation Financial Report displays all equipment for the Org code in Banner and not all the equipment may be related to the service activity.

1. Populate the Equip Detail tab, 3E Equipment section.
   1. Go through all pieces of equipment on the Equip Service tab of the Rate Calculation Financial Report and get a listing of all applicable equipment used in your service that has a Fund Src Fin Fund Code matching your fund.
      1. Verify with the service activity fund manager that the equipment is not used by another service activity.
   2. Copy that 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 Equip Detail tab, Non-3E Equipment – Internal Rates section.
   1. Go through all pieces of equipment on the Equip Service and Equip Org tab of the Rate Calculation Financial Report and get a listing of all applicable equipment used in your service that has a Fund Attribute Value matching your fund.
   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.
      1. If the FAV field is blank, verify with the service activity fund manager what equipment is used in the service activity.
      2. Verify with the service activity fund manager that the equipment is not used by another service activity.
   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. Populate the Equip tab, with the equipment listed in the Equip Detail tab.
   1. Populate the sections by copying the data from the respective sections on the Equip Detail tab.
      1. 3E Equipment
      2. Non-3E Equipment
      3. Projected Equipment
      4. EXTERNAL – Equipment Depreciation Summary for External Rates Only
   2. NOTE: Do not include the header or totals rows.
   3. Include columns:
      1. Permanent Tag Number
      2. Asset Description
      3. Base Year Depreciation Expense Total
      4. Add rows as needed by right clicking the row number and selecting insert.
   4. Include any exclusions in the Depreciation Exclusions column with negative values.

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 it 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.