Service Activities - Expenditures and Transfers

The university Government Costing offices are responsible for developing service activity policies, monitoring departmental compliance, and selecting and conducting service fund reviews. In addition, Government Costing administers training for service center personnel and provides guidance.

Use the outline below to navigate to the job aid listed.

Expenditures and Transfers: Running the Webi Report

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.

TEST
Step Task

1.

Go to the Service Activities Resource Page.

2.

Click Expenditures and Transfers under the Job Aids and Training Materials

section to expand the drop-down menu.

3.

Click Expenditures and Transfers Report Template (Webi).

4.

Select the Open option in the dialog box to open the report.

5.

Once the report opens, click the small X at the top right corner to close the report (leave Webi open, do not click on the large X at the very top).

6.

Click the Web Intelligence menu on the top left.

7.

Select Login As.

8.

Log in with your ID and password.

9.

Click the Login button.

10.

Click the Expenditures and Transfers.wid report.

11.

Click Design button at the top right (not the drop-down arrow). This adds tabs at the top of the screen and will allow for filtering in future steps, but the overall look of your screen will not change.

12.

Click the Refresh button at the top.

13.

Click Enter Fiscal Year under Prompts Summary.

14.

Type the fiscal year in the Type a value field.

15.

Click Enter COA under Prompts Summary.

16.

Type the Chart of Account in the Type a value field.

17.

Click Enter Fund Code under Prompts Summary.

18.

Type the Fund Code in the Type a value field.

19.

Click the OK button.

NOTE: The report may not refresh immediately based on the amount of information and server connections.

20.

To export the data to Excel, click the down arrow next to the Save button.

21.

Select Save As.

22.

Locate a place to save on your computer.

23.

Name your report in the File Name field. For example, you can add the fund number and fiscal year to the file name.

24.

Select Excel for Files of Type.

25.

On the right side of the Save As window, you will see a window to Select Reports. Here you can select the tabs that you want to be in your saved Excel file. For this file, you will save the “Expenditures for Rate” and “Detail Expenditures for Rate” report. You may save the others if necessary.

26.

Click the Save button.

27.

Click the small X at the top right corner to close the report. You can click No in the dialog box that pops up asking to save changes to the Webi report.

28.

Refer to the Expenditures and Transfers Populating the Service Activity Rate Template job aid to populate the template with the expenditure and transfer information.

 

 

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.

 

TEST
Step Task

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 for Rate tab.

4.

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.

5.

Open the Service Activity Rate Calculation Template or open a previously saved version.

6.

Click on the Expenditures 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 highlighted rows 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 for Rate 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.

Enter the total for any transfers from account codes beginning with “415.”

21.

Go back to the Expenditures for Rate tab in the Expenditures and Transfers report file in Excel.

22.

Highlight and copy any rows where the account code begins with “4.”

23.

On the Expenditures tab, in the Service Activity Rate Calculation template, click the cell under the heading Transfers.

NOTE: Add rows, if necessary.

24.

Click the Paste button. You should now have data in the Account Code, Expenditure Description, and Total Expenditures columns for Transfers.

25.

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.

26.

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.

27.

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.

28.

In the Expenditures and Transfers Webi Report Excel file, click on the Detail Expenditures for Rate tab.

29.

Highlight the table (except for the header row) and include the sum rows.

30.

Click on the Copy button.

31.

Click the Expenditures Detail tab in the Service Activity Rate Calculation Template excel file.

32.

Click on the first cell under the Header Row for Expenditure Detail.

33.

Click the Paste button.

34.

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.

35.

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.