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.
2. Open the saved Expenditures and Transfers report file in Excel.
3. Click on the Expenditures Summary tab.
4. Highlight the table (3 columns, do not include top headers and bottom totals rows). Do not highlight rows with account codes beginning with “2” or “4.”
   NOTE: Notice how many rows are highlighted.
5. Open the Service Activity Rate Calculation Template or open a previously saved version.
6. Click on the Expenditures Summary tab.
7. Add enough rows (if necessary) to the Service Activity Rate Calculation template to fit the data that will be copied from the Webi report.
8. 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.
9. From the Expenditures and Transfers report, click the Copy button to copy the previously highlighted table from the saved Expenditures and Transfers report.
10. On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Account Code.
11. Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns.
12. Delete any blank or unnecessary rows.
13. 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.
14. Go back to the Expenditures Summary tab in the Expenditures and Transfers report file in Excel.
15. Highlight and copy any rows where the account code begins with “2.”
16. On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Personnel Expenditures.
    NOTE: Add rows, if necessary.
17. Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns for Personnel Expenditures.
18. Delete any blank or unnecessary rows.
19. 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.
20. Go back to the Expenditures Summary tab in the Expenditures and Transfers report file in Excel.
21. Highlight and copy any rows where the account code begins with “4.”
22. On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Transfers.
    NOTE: Add rows, if necessary.
23. Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns for Transfers.
24. 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.
25. Verify that the Total FYxx Expenditures Per Banner Operating Ledger box matches the CM Ending Balance amount on the Expenditure Control line of the MobiusView/View Direct year end statement.
26. 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.
27. In the Expenditures and Transfers Webi Report Excel file, click on the Expenditures Detail tab.
28. Highlight the table including the headers row and total row.
29. Click on the Copy button.
30. Click the Expenditures Detail tab in the Service Activity Rate Calculation Template excel file.
31. Click on the first cell under the Header Row for Expenditure Detail.
32. Click the Paste button.
33. 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.
34. 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
    • Allocations to Service Lines
      

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.