Reference no: EM133027425

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 that's 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 wasn't 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 don't 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 week's 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+BenefitsRevenue-Costs-Compensation+BenefitsCompensation+Benefits.
   3. The formula for the HCROI percentage is HCROI%=Revenue-(Costs-(Compensation+Benefits))Compensation+Benefits-1HCROI%=Revenue-Costs-Compensation+BenefitsCompensation+Benefits-1.
   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.