Seminole State College of Florida Florida Central Hospital System Questions

Employee Data – Questions 1 – 3

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 has the highest total employer      contribution?

   Hints: Use an IF function formula to calculate the      employer contribution for employees who elected to participate in the      company’s retirement plan. If the employee said “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 PivotTable. You should place your      results on a different sheet than the Employee Datasheet. 

2. What is      the average salary by Gender and Job Title? Do males or females make more      money? Is this the same for all job titles?

   Hints: You can answer this question with one Pivot Table and/or Pivot Chart. Place your results on a different sheet      than the Employee Datasheet.
    

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 the 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. Add conditional formatting to highlight the highest 5 salaries and the lowest 5 salaries.

   Review the results and summarize your findings on the Documentation sheet. How many employees were on the report? Did any of the employees on the final report have the highest or lowest salaries? Do not delete any records or columns from the list as this is your original source data for all questions.