ASSIGNMENT Part I: Excel Exercise Salary Increase Estimates Attached is an Excel file (BMS Exercise Data) * with salary data for all departments on the Employee Data sheet. The City Manager wants to provide a pay increase for all employees. Using the data in the Excel file, please calculate the additional costs for each of the scenarios below. Provide a consolidated summary for each scenario in a single table (pivot tables are preferable). 1. Cost to provide a 5% salary increase for all employees effective October 1; provide the first- and second-year costs. 2. Cost to provide a 5% salary increase for all employees effective January 1; provide the first- and second-year costs. 3. Costs to provide a 5% salary increase for civilian employees effective October 1 st and a 5% salary increase for uniformed employees effective April 1; provide the first- and second- year costs. 4. Costs for a 5% salary increase for all employees effective October 1 st split out by fund type: a. Enterprise funds b. General fund

Your answers should include the following pension rates: a. Use 28.95% for uniformed employees salary b. Use 11.93% for civilian employees salary

For purposes of this exercise, assume the following: AVI (Aviation), CCT (Convention Center), DWU (Dallas Water Utilities) are the only Enterprise Fund Departments Uniformed departments are DFDU (Dallas Fire Department Uniformed) and DPDU (Dallas Police Department Uniformed). All other departments are civilian departments. The Citys fiscal year begins October 1 st of each year and operates on a biennial budget. Do not assume a secondary merit increase in the second year of the biennial budget. Make sure to provide the cost of the pay increase ONLY, not the total cost. * Employee data is fictional so do not be confused by the fact that there are job titles with salaries that dont seem to match and jobs in departments where they dont really make a lot of sense.