Due Date: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 company’s 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 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.

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 week’s 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 week’s meeting.

The promotion of Ursula Burns to the top position at Xerox Corporation marked two milestones: the first time an African- American woman was named CEO of a major American corporation and the first time a woman succeeded another woman in the top posi- tion at a company of this size. She replaced Anne Mulcahy, who held the job for several years.34 After the promotion, Burns had to endure countless interviews from media representatives. They wanted to know if she was ready to cope with the pressures that come with the top job. Her response was, “I’m a black lady from the Lower East Side of New York; not a lot intimidates me.”35 Burns is known to be tough and someone who has the courage to speak her mind. In the early 1990s, her candor at a meeting of the top Xerox executives set the stage for a major promo- tion. She and other managers were invited to attend the meeting and instructed to sit off to the side. Paul Allaire, then president of the company, would often declare, “We have to stop hiring.” Yet Xerox continued to hire new employees, month after month. At one of the meetings, Burns raised her hand and said, “I’m a little confused. If you keep saying, ‘No hiring’ and we hire a thousand people every month, who can say ‘No hiring’ and make it actually happen?” Soon after the meeting, Allaire invited her into his office, thanked her for her candor, and made her his ex- ecutive assistant.36 Burns is working hard to create a culture of candor where leaders communicate honestly. She doles out radical honesty when needed, but has learned to temper her outspokenness. Burns says she is the beneficiary of good coaching and feed- back from her business associates.

1. Why is a culture of candor especially important tant when a company is nearing bankruptcy?
2. The early years offered Ursula Burns many challenges. She was the middle of three chil- dren of a single mother who took in ironing and babysat in a Manhattan project. How can these early life challenges help prepare someone for the executive ranks of a major corporation?