HR Analytics Assignment You will often hear the phrase “employees are the company’s greatest asset” and yet individual people are not something that can be owned or actually placed on a balance sheet AND employees are the company’s greatest expense or liability, so how can an organization change this? The greatest way to shift this thinking and to shift the thinking around the value of the Human Resource function in an organization is to show the value. In these assignments, you are going to develop the skills to turn the HR into a strategic function for an organization and use the data to improve the ROI (return on investment). You may have heard that this assignment is challenging, however, if you approach the assignment with an open mindset and determination to learn, you will be extremely successful. Please note that you will need to use Excel, Google Sheets does not offer the same functions that Excel does. **Please note, Part 1 and 2 are individual assignments! Metrics Assignment Part 1 You will be provided a data set for an anonymous organization that you will use to analyse the business and eventually make recommendations. For the first assignment, you are required to prepare the following: 1. Review your data set and make sure the information is “clean” a. List your assumptions on an assumptions page for all changes you make (if you list your assumption there is no wrong assumption) 2. Complete the metric calculations for each metric in the green shaded area. a. Once this is complete, you need to calculate additional variations as listed in the variation’s column - i.e. 2 = 2 additional variations plus the base calculation. 3. Complete analysis across the 3 reporting periods. The reporting periods will be annual. For the analysis discuss what is happening year over year. Are there any trends that you can see, what are the potential implications of the trends etc. 4. Determine how management can use this metric: a. Is this metric a: Doable, Deliverable, Performance Enhancer or Enabler - or a combination? i. Doable = management can control ii. Deliverable = can determine HR performance iii. Performance Enhancer = can improve company bottom line iv. Enabler = may be seen as an expense, however, supports a strategy 5. Finally, you will need to develop an effective presentation or reporting package of the metrics for senior management. a. PLEASE SAVE YOUR FILE with this convention: Last Name_First Name Initatial_Metric Exercise. b. File must be saved as Excel and submitted as an Excel document c. All formulas must be available (do not just insert the answer) or you will receive a 0 for that metric Tip: to get started I would calculate the FTE and Headcount for each reporting period. Remember FTE is different than Headcount. Full Time Equivalent = 1 full year of service. So, if some one only works 3 months what would the FTE =? FTE=3/12 = .25 Headcount is the number of employees employed at the end of the reporting period. Tip: remember your accounting principles: while you have the salaries of each employee that might not be their total cost for the reporting period as if they were only employed for 3 months or .25FTE then what is the payroll cost. Also remember any changes you make to the raw data should be recorded and listed in your assumptions. The metrics you will calculate for part 1 is: Total FTE Total Headcount Functional Staffing Breakdown Total First Year Resignations Turnover Expense per FTE Revenue per FTE HR Expense per FTE Average annual salary per FTE Retention Rate Metrics Assignment Part 2 Your metrics assignment part 2 is a follow up to part one and is an individual assignment. 1. Provide an updated analysis and evaluation of the metrics you calculated in part 1. (10 marks) • For a full assessment and evaluation, you may need to compare to industry or provide research to support your analysis. (cite your sources) 2. Looking at Part 1, you are now required to select 10 new strategic metrics with 3 variations each over a 3-year period that you think will help you to create a “story” of what might be happening within this company. In addition to the analysis; however, you need to state why you chose that metric. Marking Criteria # of marks 10 measures • Correct calculation 10 • Provide metric determination 10 • Rational for 10 selecting metric 3 variations for each measure • Correct calculation 30 • Rational for selecting variation 30 OVERALL PRESENTATION SCORE = 30 (How the metrics package is presented, ie formatting, style, any research that might be brought in on the analysis sections. Presented as a report with instructions followed (ie. Naming convention))