Due Date: Before Class of Week 7

Submit to: The Assignment 1Opens in a new tab submission folder. Submission folders can be accessed by selecting Course Tools and then Assignments on the course navigation bar.

Description

1. The question this assignment has you work to answer is how much does our workforce contribute to the profitability of our company? You will use some key employment metrics to help you measure that, which will help increase your knowledge of how HR contributes to a companys financial position.
2. This is an individual assignment.
3. There are six main assignment questions to answer with data in Excel (including some calculations you will have to perform to answer these questions. Create as many worksheet tabs, i.e., Q1-Q5 if necessary, to answer them and a 3-slide PowerPoint Presentation.

Rationale

This assignment will evaluate the following course learning outcomes:

1. Introduction to Human Resources Metrics and Analytics
   1. Discuss the current landscape of metrics and analytics associated with the HR function.
   2. Practice basic statistical methods applicable to HR, using Excel as the analysis tool.
   3. Illustrate basic data modelling techniques with Excel, including IF, vLookUp and Pivot tables.

1. HRIS Metrics and Analytics
   1. Devising data from an HRIS to understand how to work with and combine data from other sources.
   2. Evaluate multiple regressions and clustering as a way to better understand and anticipate company employment requirements.
   3. Examine data mining techniques for both numerical and textual data.
   4. Analyze the financial benefits of metrics and analytics to organisations decision support.
2. HRIS Metrics and Analytics in Action
   1. Develop data models in Excel using Match, Offset, Scenario and Solver to better understand the uses of HR metrics for decision making.
   2. Identify data sources and facts to model the following:
      1. Revenue per employee
      2. Employee Churn Rate
      3. Average Employee Tenure
      4. Recruitment and Selection Analytics
   3. Interpret the results of metrics and analytics.

Directions

BACKGROUND

Every Monday morning your company has a manager meeting to discuss ongoing issues and the upcoming week. You have been in the HR position at Headgames Inc. (a manufacturer of inserts for all kinds of helmets) for six months, and you are starting to feel comfortable about your role in the company. Benefits, compensation, job descriptions, all these core HR features are your comfort zone. But when the CEO, John Daily, asked you a couple of pointed questions around human resources, you were stumped. And everyone else in the boardroom knew it. Profits were down, and mangers were looking for ways to cut costs or to improve the revenue on products, and thats when John Daily turned to you and asked: What is the total cost of our workforce and how does it contribute to our revenues?

There were many things you were investigating from an HR perspective, but this wasnt one of them. You flipped through your notes, checked the company intranet on your laptop, and then said: Sorry, John, I will have to get back to you with those numbers.

John said: If we dont know what our workforce costs us, how do we know what to price our services at for better revenue?

The redness you felt in your face was something you never wanted to experience again.

Back in your office you got to work on calculating the required metrics for next weeks meeting.

CALCULATIONS

1. Using the workbook EMPLOYEES, and the Employees worksheet, calculate the HCVA (Human Capital Value Added) and the HCROI (Human Capital Return on Investment) using the formulas supplied below.
   1. The formula for HCVA is: HCVA=Operating Profit+Employment CostFTEHCVA=Operating Profit+Employment CostFTE.
      • Operating Profit is in the tab labelled: Operating Profit.
      • Employment Costs = Pay + Benefits
      • FTE stands for average number of full-time employees (or full-time equivalents). In other words, if a company has 9 full-time employees and 2 part-time employees who work half days, then FTE would be equal to 9 + 0.5 + 0.5.
   2. The formula for HCROI is: Revenue(Costs(Compensation+Benefits))Compensation+BenefitsRevenueCosts-Compensation+BenefitsCompensation+Benefits.
   3. The formula for the HCROI percentage is HCROI%=Revenue(Costs(Compensation+Benefits))Compensation+Benefits1HCROI%=Revenue-Costs-Compensation+BenefitsCompensation+Benefits1.
   4. The employee base salary is labeled Annual Salary 2018.
   5. The annual bonus in the Job Codes worksheet is the percentage increase on the annual salary. Use a VLOOKUP to help calculate this amount.
   6. The benefits are determined using a VLOOKUP and matching with the benefits plan lookup table. This is a per month value, so you will have to convert it to a yearly cost by multiplying it by 12.
   7. To calculate the metric Revenue per Employee: revenue/no. of employees.
      • Note that only FT & PT employees are counted.
   8. Use the VLOOKUP function to bring in the positions from the jobcode table.
   9. Note that some employees have left the company (employed = NO) and should not be included in calculations.
   10. Compensation in the formula includes salaries, benefits, and bonuses.

NOTES

1. The Excel file, Employees Database for HCVA Assignment 1, contains all the data you will need to perform the calculations.
2. The figures on the Operating Profit tab have been given to you from the Finance department.
3. Contract Employees are an Operating Expense not labour and will need to be added in appropriately.
4. New employee hiring costs in 2018 is estimated to be 60% of their annual salary.

How Your Assignment Will be Graded

There are six questions you need to answer so that you are prepared for next weeks meeting:

1. What is the Human Capital Value Add for our company? In other words, what is the financial impact of the employees on the organisation? It is one things to know the revenue and costs, but how much do the employees (and all their associated costs) impact our profitability too?
2. What is the Human Capital Return on Investment for our company? This metric is used to help evaluate the employee return on investment. It is best used in comparison to other organisation HCROIs to help measure how effective a company is at controlling the costs of their workforce.
3. Calculate the Revenue per employee. Compare this number to the HCVA, and explain:
   1. The difference between what the two numbers represent.
   2. Why you think one metric is more valuable than the other?
4. Your U.S. parent company HR Manager (with similar revenue, expenses, and employee numbers) has provided you with their equivalent figures. How does your company compare? What recommendations can you make based on the comparisons of HCVA and HCROI between the two companies?
5. Create a scenario with 2 possible scenarios for employee costs and benefit costs. Use the revenue and total costs you already calculated, and the base employee costs and benefit costs.
   1. How does an increase in revenues of 10% with a base EM cost affect the HCVA and HCROI?
   2. How does an increase in employment costs/benefits of 10% with a base revenue affect the HCVA and HCROI?
6. Evaluate the metrics calculated. In other words, what would you explain to the CEO next meeting? Create a maximum of three (3) PowerPoint slides to highlight your answer to his question from last weeks meeting.

Assignment 1: Marking Rubric

Student Names: _____________________________________ Student ID# _____________________________

Question	Comments		Mark
1. What is the Human Capital Value Add for our company? In other words, what is the financial impact of the employees on the organisation? It is one things to know the revenue and costs, but how much do the employees (and all their associated costs) impact our profitability too?			/ 10
2. What is the Human Capital Return on Investment for our company? This metric is used to help evaluate the employee return on investment. It is best used in comparison to other organisation HCROIs to help measure how effective a company is at controlling the costs of their workforce.			/ 10
3. Calculate the Revenue per employee. Compare this number to the HCVA, and explain: The difference between what the two numbers represent. Why you think one metric is more valuable than the other?			/ 5
4. Your U.S. parent company HR Manager (with similar revenue, expenses, and employee numbers) has provided you with their equivalent figures. How does your company compare? What recommendations can you make based on the comparisons of HCVA and HCROI between the two companies?			/ 10
5. Create a scenario with 5 possible scenarios for employee costs and benefit costs. Use the revenue and total costs you already calculated, and the base employee costs and benefit costs. As a minimum, it should look like this (example on the next page). How does an increase in revenues of 10% with a base EM cost affect the HCVA and HCROI? How does an increase in employment costs/benefits of 10% with a base revenue affect the HCVA and HCROI?			/ 20
6. Evaluate the metrics calculated. In other words, what would you explain to the CEO next meeting? Create a maximum of three (3) PowerPoint slides to highlight your work.			/ 10
TOTAL MARK			/ 65