ASSIGNMENT . Clearly state the first- and second-year costs to the City depending on the scenario chosen. Part I: Excel Exercise - Salary Increase Estimates Be creative in your response and showcase your research about the City of Dallas. Attached is an Excel file ("BMS Exercise Data") * with salary data for all departments on the "Employee Part III: Excel Exercise - City Department Reorganization Proposa Data" sheet. Use attached Excel file "Budget Exercise Data" and sheet "Lookup," which has position numbers in column B. Use the position numbers in column B to complete missing columns: The City Manager wants to provide a pay increase for all employees. Using the data in the Excel file, 1. Department please calculate the additional costs for each of the scenarios below. Provide a consolidated 2. Position summary for each scenario in a single table (pivot tables are preferable)- 3. Annual Salary; and 4. Hire Date 1. Cost to provide a 5%% salary increase for all employees effective October1; provide the first- After adding missing information in columns, A, C, D and E, please answer the questions below. and second-year costs. 2. Cost to provide a 59% salary increase for all employees effective January1; provide the first- The City Manager wants to consolidate BMS, CCO, and POM into one department. What is and second-year costs. the new salary total and pension total for the consolidated department? 3. Costs to provide a 5% salary increase for civilian employees effective October 1" and a 5% salary increase for uniformed employees effective April 1; provide the first- and second- 2. The City Manager wants to consolidate PER, CVS, and ORM into one department. What is year costs. 4. Costs for a 59% salary increase for all employees effective October 1" split out by fund type: the new salary total and pension total for the consolidated department? a. Enterprise funds Your answers should include the following pension rates: b. General fund Use 28.95% for uniformed employees' salary b. Use 11.93% for civilian employees' salary Your answers should include the following pension rates associated with merit not the full salary amount: *Employee data is fictional so do not be confused by the fact that there are job titles with salaries 3. Use 28.95% for uniformed employees' salary b. Use 11.93% for civilian employees' salary that don't seem to match and jobs in departments where they don't really make a lot of sense. 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 City's fiscal year begins October 1" 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 (along with the appropriate pension rate), not the total salary cost. `Employee dato is fictional so do not be confused by the fact that there are job titles with salaries that don't seem to match and jobs in departments where they don't really make a lot of sense. Part II: Written Exercise - Salary Increase Recommendation Attached is a Word memo template to provide a recommendation to the Director of Budget and Management Services for one of the four scenarios above. Be sure to: Balance your recommendation with fiscal prudence along with maintaining employee morale Assume the first year of the biennial has larger concern with constrained resources due to a rebounding economy expected in the second year