HR Metrics: Excel Activity 7
This activity is due by June 4th at 11:59pm.
Our employee database has been partially corrupted and we need to fix the raw data and provide our VP with answers to a few of her questions. As seen in the screen shot below, all the calculations between Columns K to R have been erased along with employee data below row 10.
The file has 6 tabs that you will need to use.
First you need to complete the Raw Data tab with all the necessary calculations. For some of the columns you will need to link data contained in the Reference Tables tab. This prep work is required to answer the questions in the final tab, “Questions & Answers”.
Use first 10 rows of the Raw Data tab to work out your formulas and double check to make sure you are doing them correctly (try replacing them to see if you get the same results that are already there). Once you are confident they are correct, copy the formulas down to complete this tab.
Then use the pivot tables to answer each question found in the Questions & Answers tab. Make sure you use the Q.1 Pivot tab to complete and show your work to answer Question 1 in the Questions & Answers tab. Use the Q.2 Pivot for Question 2 and Q.3 Pivot for Question 3.
Once done, save the completed file and upload it to the Assignment folder named, “Excel Activity 7”.
Marks: Completing the Raw Data tab=1 mark; Showing work in all 3 Pivot tabs=3 marks; Answered Questions tab=1 mark (0.25 marks each).
You will need to incorporate the following functions: =VLOOKUP(), =INDEX(), =MATCH()
You will also need to group data in the Pivot Table and remember how to calculate averages.
Solved by X. D. in 15 mins