HR Metrics: aSSIGNment 3 (15%) PROJECT SUMMARY This is a group project with a maximum of 3 students per group. You must sign up for a group through the group sign up page to receive a mark (Management>>Groups). If you cannot see the Assignment 3 upload folder, it means you have not signed up to a group. Once you do, you'll see the folder. You are to create these reports based on your groups unique dataset within the Assignment 3 folder. return on investment calculations and report a report identifying specific employees found within a group and providing financials of a program to reduce voluntary turnover a report calculating recruitment metrics a report indicating the most useful selection tests for future employees based on actual performance IF YOU DOWNLOAD AND USE THE WRONG DATAFILE, YOUR GROUP WILL RECEIVE A GRADE OF ZERO (0) AND THERE WILL NOT BE AN OPPORTUNITY TO REDO THE SUBMISSION FOR MARKS (you can upload the corrected version for feedback only). It is your groups responsibility to double check the correct file is downloaded and used. DUE DATE AND SUBMISSION Please refer to Slate for the due date. Only 1 person in the group needs to upload the file to the Assignment Folder labelled, Assignment 3. If submitted late a penalty of 10% off per 24 hours will be applied for up to three (3) 24 periods. After that you will receive a grade of 0 for the assignment. HOWEVER, if the professor has graded and published feedback and or uploaded videos on how to complete parts of the assignment, then no late submissions are accepted regardless of the previous statement. INSTRUCTIONS Download and review your groups unique dataset. This file contains employee information about a fictional company. Submit only one file by using multiple tabs for your answers. Do not submit separate files for different questions. 25 marks will be taken off if this instruction is not followed. To begin, collect the data from the different division sheets into the All Divisions sheet. THIS CAN ONLY BE DONE THIS USING THE =VSTACK() FUNCTION. IF YOU DO NOT USE VSTACK TO COLLECT DATA IN ONE SHEET (e.g. if you copy and paste the divisions into the All Divisions sheet) YOU WILL RECEIVE A GRADE OF ZERO (0) FOR THE PROJECT. NOTE: when reporting percentages, only show the data to 1 decimal place (marks will be deducted if this formatting is not followed). Seriously, read the last sentence again, I cant begin to tell you how many students lost marks for this in previous assignments!!!!! Part B: Joining Multiple Tables Together The company has specific concerns about voluntary turnover for a specialized group of employees. These roles are critical to the company and the knowledge these employees have must be retained. These employees work on secret government projects and are grouped either into Security Clearance Level 1 or Security Clearance Level 2. Senior management has told you to stop all voluntary turnover going forward by throwing cash at the problem. They have told you that all these specialized employees are to receive one-time extra retention bonuses outlined in the sheet labelled, Part B-VT Reduction. Senior management believes this should, in part with other initiatives, eliminate most of the voluntary turnover. They have asked you to create a report outlining the total cost for this intervention. This problem will require the use of advanced Excel functions. Consider using formulas such as IF(), VLOOKUP() or XLOOKUP(), and a combination of INDEX() and MATCH() along with OR(). NOTE: You must use the =IFERROR() function so that any error codes like #N/A! do not appear in the sheet. Steps needed to create the report: Bring together all the data from different divisions into the All Divisions sheet using VSTACK. Once done, in this All Divisions sheet, create a new column and use VLOOKUP() to identify employees that are Level 1, Level 2 or neither from the Lvl 1 and 2 Employee List sheet. Create a new column to retrieve the Security Bonus %. Create a new column at the end of the existing data with the label, Issued Stock. Besides the Issued Stock column, create a new column at the end of the existing data with the label, Cash Bonus. This Cash Bonus is the Security Bonus % multiplied by yearly salary. And finally, included a Total Payout column which includes the Issued Stock and Cash Bonus. DO NOT include salary remember management only wants to know the incremental cost of retaining these employees. Use the formulas indicated above (or others you know of) to fill in the data for the two new columns. NOTE: You must use formulas for these columns that are applied to all employees. For example, the formula used for the first employee must be used for all employees in each column. If you use filters to sort the data and input numbers or use inconsistent formulas, you will receive a grade of zero (0) for this entire question. Once done, use a pivot table to extract data to create a professional looking report. Make sure the pivot table includes Sub Totals and Grand Totals for the report. The report must contain the following data: Security Level Occupation Headcount by Security Level and Occupation Total Issued Stock (in Currency format and rounded to the nearest dollar) Total Cash Bonus $ (in Currency format and rounded to the nearest dollar) Total Retention $ (in Currency format and rounded to the nearest dollar) (this is the total of Issued Stock and Cash Bonus) ****When creating the pivot table, first place the field, Security Level in Rows and then put Occupation in Rows underneath Security Level. Note: the picture to the right does not show the complete pivot table setup. Deliverables for Part B Ensure all steps to create the report have been followed and all formulas can be verified. Once the report has been created, ensure that it contains only the necessary data management is concerned with and is professionally formatted to draw the readers attention to the totals by Security Level and all Grand Totals. Performance Rating Tenure 1 2 3 4 0 0% 20% 40% 60% 5 5% 25% 45% 65% 10 10% 30% 50% 70% 20 15% 35% 55% 75% 30 20% 40% 60% 80% on the excel sheet i have both performance and tenure