Excel Extra Credit Project 2:
Sacred Heart Hospital
Problem
You are designing an employee payroll spreadsheet that will provide valuable information about the employees at Sacred Heart Hospital. You will use conditional formatting and if statements to create this spreadsheet.
Instructions
- Set up your spreadsheet with the information shown below. Make sure you give your spreadsheet a title first.
Last Name First Name Position Salary Hours Worked (Per Week) Cox Perry Residency Director $150,000 70
Dorian John Internal Medicine $70,000 55 Reid Elliot Private Practice $140,000 40 Espinosa Carla Nurse $50,000 60 Turk Chris Surgeon $60,000 45 Itor Jan Janitor $75,000 25 Kelso Bob Chief of Medicine $250,000 30 Buckland Ted Lawyer $45,000 45 Dudemeister Keith Intern $18,000 39 Roberts Laverne Nurse $45,000 50 Sullivan Jordan Board Member $100,000 15 Quinlan Todd Surgeon $60,000 50 Mickhead Walter Attending Physician $120,000 60 Winston Randall Janitor $40,000 35 Steadman Jeffrey Attending Physician $90,000 42 Zeltzer Paul Oncologist $125,000 60 Hedrick John Grief Counselor $85,000 30 Murphy Doug Pathologist $65,000 55 Wen Kim Chief Surgeon $165,000 60 Flowers Paul Nurse $40,000 45 - Create a new column called Weekly Pay and determine how much each employee earns a week using a formula.
- Create another column called Hourly Rate that determines how much each employee earns per hour using a formula.
- Create a column called Expendable that uses if statements to determine an employee's job status. Use the information in the table below to create your nested if statements.
40 or More Hours Worked Hard Worker Position is Chief of Medicine or Board Member Untouchable Hourly Rate is more than $20 Fire Salary is less than $100,000 Possible to Fire - Make a final column called Coat/Scrubs Color that will use if statements. Use the table below to fill out this column.
Position Coat/Scrubs Color Residency Director, Private Practice, Chief of Medicine, Oncologist, Grief Counselor, Attending Physician White Internal Medicine, Pathologist, Intern Blue Surgeon, Chief Surgeon Green Janitor Grey Nurse Pink Lawyer, Board Member None - Now, use conditional formatting to color the Coats/Scrubs Color column to make the color of the cell match the color listed. For "None" choose a color that isn't used.
- Create data bars in the following columns: Salary, Hours Worked, Weekly Pay, and Hourly Rate.
- Next, sort the data by last name.
- Finally, select the Salary column and create a Doughnut Chart, which can be found under the Other Charts button. After creating and labeling the chart, explain in a short sentence or two explaining whether or not you think a doughnut chart would be good chart to use in this situation. Also describe what you think a doughnut chart might be good for.
- Make sure your spreadsheet looks nice, put your name in the title, and turn it in to Mr. Miller's Neighborhood.