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.
a. The formula for HCVA is: HCVA = Operating Profit + Employment Cost/FTE
i. Operating Profit is in the tab labelled: Operating Profit.
ii. Employment Costs = Pay + Benefits
iii. 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.
b. The formula for HCROI is: (Revenue – (Costs – (Compensation + Benefits))/(Compensation + Benefits)
c. The formula for the HCROI percentage is (Revenue – (Costs – (Compensation + Benefits))/(Compensation + Benefits).
d. The employee base salary is labeled Annual Salary 2020.
e. The annual bonus in the Job Codes worksheet is the percentage increase on the annual salary. Use a VLOOKUP to help calculate this amount.
f. 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.
g. To calculate the metric Revenue per Employee: revenue/no. of employees. Note that only FT & PT employees are counted.
h. Use the VLOOKUP function to bring in the positions from the jobcode table.
i. 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. The U.S. numbers have already been converted to Cdn$.
3. Contract Employees are an Operating Expense not compensation and will need to be added in appropriately.
ASSIGNMENT QUESTIONS
There are four questions you need to answer so that you are prepared for next week’s meeting:
1. What is the Human Capital Value Add for our company? In your explanation explain what this number is, that is, what does it measure?
2. What is the Human Capital Return on Investment for our company? In your explanation explain what this number is, that is, what does it measure?
3. Calculate the Revenue per employee. Compare this number to the HCVA, and explain:
a. The difference between what the two numbers represent. Do they both measure the same thing?
b. Do you think one metric is more valuable than the other? Think about this before you answer.
4. Your CFO has provided you with their equivalent figures for a similar U.S. sister division (with similar revenue, expenses, and employee numbers). How does your company compare? What recommendations can you make to your CEO based on the comparisons of HCVA and HCROI between the two companies? Will the issue of reduced revenues be solved through employee layoffs?
5. 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 week’s meeting. Use the presentation notes on each slide to add the text to explain/support your slides where necessary.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
