Think about it, there are only 6 things that can change an employee's job status: Death, Disability, Retirement, Voluntary Separation, Involuntary Separation, and Promotion.

For the exercise, you are an HR consultant who has been brought in to work with the company on their HR planning. Think about this as a 3-year planning scenario.

Plug in the following percentages: Death =6%,

Disability = 8%, Retirement = 20%, Voluntary

Separation = 60%, Involuntary Separation = 3%,

and Promotion = 6%. The Growth Rate is

estimated to be 50% over the next 3 years.

Punch the numbers into the spreadsheet and tell us what the results imply for your company.

Questions:

1) Will it be easy to meet the needs or will it be difficult?

2) Describe the situation and discuss your concerns and recommendations - what you think the company should be doing from an HR perspective?

3) What rates are too high and what rates are too low? What should the organization do to lower some of the rates and increase other rates?

4) What rates do you recommend which would be more realistic and help the organization succeed in the long run?

Once you do your analysis in Excel, examine the before and after cost of turnover estimate and the impact on turnover cost using Bonusly's Turnover Calculator

 

Transcribed Image Text:

C-Suite Executives Middle Managers Supervisors Team Leaders Entry Level Workers Occurance Death Disability Retirement Voluntary Separation Involuntary Separation Promotion Rate Company Growth Rate Level C-Suite Executives Middle Managers Supervisors Team Leaders Entry Level Workers Total Number of FTES 21 63 189 567 847 7 Rate 6% 6% 30% 60% 3% 5% 40% # of FTES 7 21 63 189 567 847 Death Rate 94% 94% 94% 94% 94% Changes -49 -45 -224 -316 -5 12 338.8 7 20 60 178 533 798 Disability Rate 94% 94% 94% 94% 94% 30 89 7 265 19 794 1188 57 168 502 753 Think about it, there really are only 6 things that can change an employees job status: Death, Disability, Retirement, Voluntary Separation, Involuntary Separation, and Promotion. Estimate the rates and insert your estimates in B11 to B16. Also, estimate your company's growth rate since this can dramatically affect your hiring Growth Rate Required Available Need 140% 10 3 7 140% 6 140% 18 140% 51 140% 130 208 One the next Sheet, I have included some rates for you to plug in to see what could happen under different scenarios. Look below and and you will find your results! 24 needs. 71 Retirement Rate 70% 70% 70% 70% 70% 214 664 5 14 40 118 352 529 980 Vol. Sep. Rate 40% 40% 40% 40% 40% 2 6 16 48 141 213 Invol. Sep. Invol. Sep. Promotion Rate Rate 97% 97% 97% 97% 97% Result 2 6 16 47 137 208 5% 5% 5% 5% 5% ** 1 1 3 7 12 Plug in your rates in the cells to the left. Promotion Result 3 6 18 51 130 208 C-Suite Executives Middle Managers Supervisors Team Leaders Entry Level Workers Occurance Death Disability Retirement Voluntary Separation Involuntary Separation Promotion Rate Company Growth Rate Level C-Suite Executives Middle Managers Supervisors Team Leaders Entry Level Workers Total Number of FTES 21 63 189 567 847 7 Rate 6% 6% 30% 60% 3% 5% 40% # of FTES 7 21 63 189 567 847 Death Rate 94% 94% 94% 94% 94% Changes -49 -45 -224 -316 -5 12 338.8 7 20 60 178 533 798 Disability Rate 94% 94% 94% 94% 94% 30 89 7 265 19 794 1188 57 168 502 753 Think about it, there really are only 6 things that can change an employees job status: Death, Disability, Retirement, Voluntary Separation, Involuntary Separation, and Promotion. Estimate the rates and insert your estimates in B11 to B16. Also, estimate your company's growth rate since this can dramatically affect your hiring Growth Rate Required Available Need 140% 10 3 7 140% 6 140% 18 140% 51 140% 130 208 One the next Sheet, I have included some rates for you to plug in to see what could happen under different scenarios. Look below and and you will find your results! 24 needs. 71 Retirement Rate 70% 70% 70% 70% 70% 214 664 5 14 40 118 352 529 980 Vol. Sep. Rate 40% 40% 40% 40% 40% 2 6 16 48 141 213 Invol. Sep. Invol. Sep. Promotion Rate Rate 97% 97% 97% 97% 97% Result 2 6 16 47 137 208 5% 5% 5% 5% 5% ** 1 1 3 7 12 Plug in your rates in the cells to the left. Promotion Result 3 6 18 51 130 208

Answer rating: 100% (QA)

To analyze the HR planning scenario and address the questions I will use the provided data and percentages However the spreadsheet you mentioned is not visible in the text so I will provide a textual ... View the full answer