HR Demand examples Ratio Analysis PDF

Preview

Summary

HR Planning at Giganet Corporation...

Description

HR Planning at Giganet Corporation: ANSWERS A. Using the Ratio Analysis, determine the demand for employees in 2020, 2021 and 2022. Year 2017 2018 2019 2020 – predicted 5% growth 2021 – projected 8% base-line growth 2022 – forecast 3% growth

Production units 1,125,000 1,235,000 1,420,000

# of employees 325 345 363

1,491,000 1,610,280 1,658,588

To be determine d based on a ratio

Production/ ees 3461.54 3579.71 3911.85 381

Avg = 3651.03 408

412 424

Ees/ Production .000289 .000279 .000256 381

Avg = . 000275 409.5

441

412

442.2

454

424

455.5

The ratio may either be # of production units per employee or # of employees per production unit. First: calculate the anticipated demand of production units: 2020: 5% times 2017 1,420,000 = 1,491,000 2021: 8% times 2018 = 1,610,280 2022: 3% times 2019 = 1,658,588 Then you may apply either the 2019 ratio – assuming the most recent experience is expected to be trend for the next 3 years: 3911.85 or .000256 OR assuming the average of the past 3 years is expected for the next 3 years: 3651.03 or .000275. To determine the # of employees for 2020, 2021 and 2022; use either ratio: Using a ratio of Production / # ees 2020: 1,491,000 divided by 3911.85 = 381 OR 1,491,000 divided by 3651.03 = 408

Using a ratio of # ees/ Production units: 2020: 1,491,000 times .000256 = 381 OR 1,491,000 times .000275 = 409.5

2021: 1,610,280 divided by 3911.85 = 412 OR 1,610,280 divided by 3651.03 = 441

2021: 1,610,280 times .000256 = 412 OR 1,610,280 times .000275 = 442

2022: 1,658,588 divided by 3911.85 = 424 OR 1,658,588 divided 3651.03 = 454

2022: 1,658,588 times .000256 = 424 OR 1,658,588 times .000275 = 455.5

B. Using a regression analysis model, what is the demand for Sales Team in 2020, 2021 and 2022? The production has generated the following revenue: the ‘x’ Year Actual Production units Revenue @ $135 per unit 2017 1,125,000 $151,875,000 2018 1,235,000 $166,725,000 2019 1,420,000 $191,700,000 2020 2021 2022

1,491,000 1,610,280 1,658,588

Forecast units* 110,000 185,000 205,000

the ‘y’ # of Sales Team 24 26 30

71,000 119,280 48,308

31 33 34

* 2015 to 2016 based on actuals 2017 forecast = 205,000

Based on 1 Sales Rep for each additional 50,000 units forecast 2019 to 2020 = 71,000 more units results in 1 more rep 2020 to 2021 = 119,280 more units has 2 more reps 2021 to 2022 = 48,308 more units is 1 more rep. Based on the known “x” = revenue per units and “y” = Sales Reps, a line of best fit can be determined:

For 2020: Sales Revenue of $201,285,000 (the x) The # of Sales Reps “y” = 1.51648E-07 times $201,285,000 + 0.871391 = 31. COMPARING THE PRACTISE APPROACH VERSUS A REGRESSION MODEL In 2020, following the practise of adding an additional Sales Rep for each 50,000 production units forecast 34; however, the regression indicates a need for an additional Sales Rep in 2020 (total of 35) thereby identifying the need to raise the staffing budget by an additional $100,000 cost.

REGRESSION MODEL

The excel file sets out the following:

b =I NDEX( L I NE ST ( C5 : C7 , A5 : A7 9 ) , 2 ) m =INDEX(LINEST(C5:C7,A5:A7),1) Where C5:C7 is the # of staff column and A5:A7 is the new production revenue column....