Use the information on the Employee Data sheet in cells a4:i60 to answer the following three questions. 1. Determine how much the employer will have to contribute to the retirement plan for each department. Which department requires the highest total employer contribution? Hints: On the Employee Data sheet, use an IF function formula to colculate the employer contribution for employees who elected to participate in the company's retirement plan. If the employee soid "Yes" in the Participates in Retirement Plan column, then the Employer Contribution would be the employee's Salary times the contribution rate shown in i2. Otherwise, the employer contribution would be zero. If needed, copy the formula. Format your formula results appropriately. There are several ways to find the total contributions by departments, such as using SUMIF formulas or a PivotToble. You should place your results on a different sheet than the Employee Data sheet. 2. Create a PivotTable to show the average salary by Region and Job Title. Which job in which region has the highest average salary? If you were a Registered Nurse, how does the average salary in the South compare to the other regions? Hints: You can answer this question with one PivotTable and/or Pivot Chart. Place your results on a different sheet than the Employee Dato sheet. 3. On the Employee Data sheet, add conditional formatting to highlight the highest 5 salaries and the lowest 5 salaries. Show only the records and total number of medical staff in the iCU and Cardiology Departments who have worked here 10 years or more. Sort the list by Job Title in order of seniority (Registered Nurse, Physical Therapist, Surgical Tech and Paramedic) and then by Year Hired in ascending (smallest to largest) order. Hide the gender column as this information is confidential. Review the results and summarize your findings on the Documentation sheet. How many employees were on the final list? Did any of the employees have the highest and lowest salaries? Do not delete any records or columns from the list as this is your original source data for all questions. Hints: Your results should be displayed on the Employee Dato sheet. Turn the data into o table. Set two Conditional Formatting using Top/Bottom Rules - one for top 5 and another for bottom 5. Use sort and filters on the existing data. Use o totol row to show the count of the records displayed that Florida Hospital System Use the information on the Employee Data sheet in cells a4:i60 to answer the following three questions. 1. Determine how much the employer will have to contribute to the retirement plan for each department. Which department requires the highest total employer contribution? Hints: On the Employee Data sheet, use an IF function formula to colculate the employer contribution for employees who elected to participate in the company's retirement plan. If the employee soid "Yes" in the Participates in Retirement Plan column, then the Employer Contribution would be the employee's Salary times the contribution rate shown in i2. Otherwise, the employer contribution would be zero. If needed, copy the formula. Format your formula results appropriately. There are several ways to find the total contributions by departments, such as using SUMIF formulas or a PivotToble. You should place your results on a different sheet than the Employee Data sheet. 2. Create a PivotTable to show the average salary by Region and Job Title. Which job in which region has the highest average salary? If you were a Registered Nurse, how does the average salary in the South compare to the other regions? Hints: You can answer this question with one PivotTable and/or Pivot Chart. Place your results on a different sheet than the Employee Dato sheet. 3. On the Employee Data sheet, add conditional formatting to highlight the highest 5 salaries and the lowest 5 salaries. Show only the records and total number of medical staff in the iCU and Cardiology Departments who have worked here 10 years or more. Sort the list by Job Title in order of seniority (Registered Nurse, Physical Therapist, Surgical Tech and Paramedic) and then by Year Hired in ascending (smallest to largest) order. Hide the gender column as this information is confidential. Review the results and summarize your findings on the Documentation sheet. How many employees were on the final list? Did any of the employees have the highest and lowest salaries? Do not delete any records or columns from the list as this is your original source data for all questions. Hints: Your results should be displayed on the Employee Dato sheet. Turn the data into o table. Set two Conditional Formatting using Top/Bottom Rules - one for top 5 and another for bottom 5. Use sort and filters on the existing data. Use o totol row to show the count of the records displayed that Florida Hospital System