Read all assignment instructions carefully! When finished, compare your results to the grading rubric before submitting them. For a printable copy of these instructions, open the Excel_IP_Data_Instructions_2020.pdf
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.
Open the IP_Med_Data.xlsx
You work in the Human Resources department for Florida Central Hospital System. Your boss has given you the latest employment information on 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.
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.
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.
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.
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.
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
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.Hints: Your results should be displayed on the Employee Datasheet. Turn the data into a table. 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. Set two Conditional Formatting using Top/Bottom Rules – one for top 5 and another for bottom
Place this order or similar order and get an amazing discount. USE Discount code “GWEXDDSRGCF10” for 10% discount
This question has been answered by our writers. you can buy the answer below or order your 0% plagiarized answer
Order your 0% plagiarized answer