Topics Map > Grants and Sponsored Projects > Service Activities

System Government Costing - Expenditures and Transfers: Populating the Service Activity Rate Template

Expenditures and Transfers: Populating the Service Activity Rate Template

To complete the Expenditures and Expenditures Detail tabs in the Service Activity Rate Calculation Template, you need to run the Expenditures and Transfers Webi Report first. Then, copy the appropriate data from the Webi report and paste it into the Service Activity Rate Calculation Template.

  1. You should have already run the Webi report to determine the Service Activity Expenditures.

NOTE: See the Expenditures and Transfers Running the Webi Report job aid for assistance.

  1. Open the saved Expenditures and Transfers report file in Excel.
  2. Click on the Expenditures for Rate tab.
  3. Highlight the table (except for the top (titles) and bottom (sum) rows). Do not highlight rows with account codes beginning with “2” or “4.”

NOTE: Notice how many rows are highlighted.

  1. Open the Service Activity Rate Calculation Template or open a previously saved version.
  2. Click on the Expenditures tab.
  3. Add enough rows (if necessary) to the Service Activity Rate Calculation template to fit the data that will be copied from the Webi report.
  4. Drag and drop the formulas present in the Adjusted Total Non-Personnel Expenditures – External Rate, Total Non-Personnel Expenditures – Internal Rate, and Total Allocated columns after adding rows.
  5. From the Expenditures and Transfers report, click the Copy button to copy the highlighted rows from the saved Expenditures and Transfers report.
  6. On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Account Code.
  7. Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns.
  8. Delete any blank or unnecessary rows.
  9. Ensure the formulas for the Total Non-Personnel Expenditures row are correct. If not, adjust the formulas to add the appropriate cells to sum all the rows of data included.
  10. Go back to the Expenditures for Rate tab in the Expenditures and Transfers report file in Excel.
  11. Highlight and copy any rows where the account code begins with “2.”
  12. On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Personnel Expenditures.

NOTE: Add rows, if necessary.

  1. Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns for Personnel Expenditures.
  2. Delete any blank or unnecessary rows.
  3. Ensure the formula for the Total Salaries & Wages Expenditures row is correct. If not, adjust the formula to add the appropriate cells to sum all the rows of data included.
  4. Enter the total for any transfers from account codes beginning with “415.”
  5. Go back to the Expenditures for Rate tab in the Expenditures and Transfers report file in Excel.
  6. Highlight and copy any rows where the account code begins with “4.”
  7. On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Transfers.

NOTE: Add rows, if necessary.

  1. Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns for Transfers.
  2. Ensure the formula for the Total Transfers row is correct. If not, adjust the formula to add the appropriate cells to sum all the rows of data included.
  3. Verify that the Total FY Expenditures Per Banner Operating Ledger matches the CM Ending Balance amount on the Expenditure Control line of your MobiusView/View Direct statement.
  4. Replace the titles Rate #1, Rate #2, etc., to something more descriptive. For example, Microscope Rate, X-Ray Rate, Truck Rate, etc. You may add or delete rate columns, as necessary.
  5. In the Expenditures and Transfers Webi Report Excel file, click on the Detail Expenditures for Rate tab.
  6. Highlight the table (except for the header row) and include the sum rows.
  7. Click on the Copy button.
  8. Click the Expenditures Detail tab in the Service Activity Rate Calculation Template excel file.
  9. Click on the first cell under the Header Row for Expenditure Detail.
  10. Click the Paste button.
  11. Click the Save button if adding data to an existing Service Activity Rate Calculation template.

NOTE: If creating a new template, click the File button, Select Save As, locate a place to save, name your Service Activity Rate Calculation template, and then click the Save button.

  1. You have now populated the template. You will need to fill out the remaining columns on the Expenditures tab as you proceed in your rate calculation:
    • Adjustments/Corrections
    • Projections
    • Exclusion of Unallowables for Internal Rate
    • Rate

NOTE: The Adjusted Total Non-Personnel Expenditures – External Rate, Total Non-Personnel Expenditures-Internal Rate, and Total Allocated columns will populate automatically based on the formulas.




Keywords:Expenditures and Transfers: Populating the Service Activity Rate Template   Doc ID:119623
Owner:Erik O.Group:University of Illinois Training and Development Resources
Created:2022-07-13 13:39 CDTUpdated:2022-07-13 13:44 CDT
Sites:University of Illinois Training and Development Resources
CleanURL:https://answers.uillinois.edu/training/system-government-costing-expenditures-and-transfers-populating-the-service-activity-rate-template
Feedback:  0   0