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 with UIN file in Excel.
3. Click on the Salaries Detail tab and copy all the data under the Salaries & Wages Detail heading (include headers and total rows).
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 under the Salaries and Wages Detail Heading.
7. In the Salaries and Wages with UIN file, click on the Salaries Summary by UIN tab.
   NOTE: the Summary by Account tab is for reference only and not used in the Service Activity Rate Template.
8. Copy cells for each Employee’s Name, Position Title, Employee UIN, and 3E Fund Expense Amount.
9. Click on the Salaries and Wages tab.
10. Under the 3E Fund header, paste data starting in the Personnel Name column to populate columns for Personnel Name, Title, UIN, and FYxx 3E Salary Expenses.
    NOTE: Add rows if necessary. Drag down the formulas present in the Projected Salary, Projected 3E Salaries to include in Internal Rate, and Total Allocated columns after adding rows. Update FYxx heading to appropriate FY.
11. 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.)
12. Delete any blank or unnecessary rows.
13. 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.
14. You have now populated the template. For the 3E Fund section, you will need to fill out the remaining columns, including: 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, Projected 3E Salaries to Include in Internal Rate and Total Allocated columns will populate automatically based on the formulas.
15. 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, Projected 3E Salaries to Include in External Rate and Total Allocated columns will populate automatically based on the formula.
16. 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.