Reference no: EM133741853

Human Resources Metrics

PROJECT SUMMARY

You are to create these reports based on your group's 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

Part A: Return on Investment
Refer to the sheet labelled, "Part A-ROI" for the mini case and details.

Deliverables for Part A
In the Excel file, create a professional looking report outlining both the costs and benefits. Formatting should be set to "Currency" and rounded to the nearest dollar.
Underneath the report, calculate the Return on Investment (ROI) of this training course. Write your answer to 1 decimal place.
Based on your ROI calculation and taking into consideration the company average ROI, indicate if you would you recommend that this training continue and why?

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)

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 reader's attention to the totals by Security Level and all Grand Totals.

Part C: Recruitment Metrics

Senior management has asked you to provide them with recruitment metrics to understand this function's effectiveness.
For this task use the tab labelled, "Part C-Recruitment" to complete this question.
Within the raw data, add columns and calculate the metrics listed below.

Using a pivot table, calculate the average number of days for each metric broken down by Permanent Full Time and Temporary Full Time. The metrics should be rounded to 1 decimal place.

Part D: Selection and Correlations
The company is examining the different tools they use to help determine which pool of candidates should be hired. This is important because HR is trying to reduce the costs associated with poor hires and their underwhelming productivity.
In a study conducted over the years, HR has tracked the test scores of employees just before they were hired and their first-year work performance score.
You have been asked to create a report outlining each selection test and its correlation to the first-year work performance. This report should also rank the selection tests based on their correlation score. For example, a test should be ranked from 1 to 5 with 1 being the best test to predict future performance and 5 is the least likely.
For this exercise use the tab labelled, "Part D-Selection" to complete this question.
Use the =CORREL() formula to complete the correlations between the different selection tool scores and performance scores. Because of the scale when reporting correlations (between +1 and -1), all of your correlations should be reported with 2 decimal places.

The report must contain the following data:
Test name
Correlation between selection test and actual performance (2 decimal places)
Selection test ranking