System Government Costing - Salaries and Wages: Populating the Service Activity Rate Template

Calculate and project the salaries and wages that can be included in the Service Activity. Also calculate an external rate.

1. You should already have run the Webi report to determine the Salaries and Wages.
   NOTE: See the Salaries and Wages: Running the Webi Report job aid for assistance.
2. Open the saved Salaries and Wages file in Excel.
3. Click on the Detail tab and copy the data.
4. Open the Service Activity Rate Calculation Template or open a previously saved version if adding data to an existing rate calculation Excel file.
5. Click on the Salary and Wages Detail tab.
6. Paste the data.
7. Click on the Salaries and Wages tab.
8. Under the 3E Fund header, type in the Personnel Name, UIN, and FY 3E Salary Expenses.
   NOTE: Add rows if necessary. Drag and drop the formulas present in the Projected Salary Increase, Projected 3E Salaries to include in Internal Rate, and Total Allocated columns after adding rows.
9. Each line should have a different name and salary amount. Add together amounts for the same employee if applicable. (This applies for any employees listed under the State Fund section as well.)
10. Delete any blank or unnecessary rows.
11. 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.
12. 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.
13. You have now populated the template. For the 3E Fund section, you will need to fill out the remaining columns, including: Title, Annual Salary, Projected Percentage Increase, Percentage of FTE on Service, and the various Rate columns on the Salaries and Wages tab as you proceed in your rate calculation utilizing data from Banner and/or your department.
    NOTE: The Projected Salary Increase, Projected 3E Salaries to Include in Internal Rate and Total Allocated columns will populate automatically based on the formulas.
14. You have now populated the template. For the State Fund section, you will need to fill out all columns, including: Personnel Name, Title, UIN, FY Total Annual Salary, Projected Percentage Increase, Percentage of FTE on Service, and the various Rate columns on the Salaries and Wages tab as you proceed in your rate calculation utilizing data from Banner and/or your department.
    NOTE: The Projected Salary Increase, Projected 3E Salaries to Include in External Rate and Total Allocated columns will populate automatically based on the formula.