EXCEL INDEPENDENT PROJECT DATA INSTRUCTIONS
Read all assignment instructions carefully! When finished, compare your results to the grading rubric before submitting. You may only submit this project one time. Your instructor will grade and provide feedback within 7 days after the due date.
Student Learning Outcome
Students will apply what they have learned in a real-world scenario, utilize critical thinking skills to analyze data, and stretch the limits of their knowledge by summarizing data results to support a decision.
Project Overview
You work in the Human Resources department for Florida Hospital System. Your boss has given you the latest employment information on the some of the medical staff and has asked you to analyze the data and answer three questions. You will summarize the results to all questions and support your conclusions using Excel data analysis techniques and skills such as formulas, sorting, filtering, total rows, PivotTables and/or PivotCharts.
Project Instructions
Open the IP_Med_Data.xlsx workbook.
Save the file as Lastname_Firstname_IP_Med_Data.xlsx.
Read the three questions given below. Use your Excel skills to find the answer to the questions. Each question should be treated separately. In other words, question 1 is unrelated to the answer to question 2 or question 3.
You can organize any new results (pivot tables, pivot charts, summary formulas, etc.) however you would like, but make sure to identify all information so your results are clear. You may add new sheet(s) as needed to support the results of your analysis.
Format all sheets so they are professional looking and easy to read. Make sure each sheet is set to print on 1 page. This assignment is worth 100 points.
Documentation Sheet
Enter your name and the date.
After finishing your data analysis, come back to this sheet and summarize the answers to each question in a few sentences. Add a link to the “place in this document” that shows your supporting data analysis. (Hint: A link has already been entered for question 3.)
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 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 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 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 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?
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 Data sheet. Turn the data into a 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 a total row to show the count of the records displayed that meet the criteria. Use custom sort for the Job Title.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
