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 calculate the employer contribution for employees who elected to participate in the companys retirement plan. If the employee said Yes in the Participates in Retirement Plan column, then the Employer Contribution would be the employees 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 PivotTable. You should place your results on a different sheet than the Employee Data sheet.
2. Create a PivotTable to show the average salary by Regionand Job Title. Which job title has the highest average salary and what region is it in? 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 Data 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?