Introduction

This assignment is designed to evaluate the following course learning and essential

employability outcomes:

$$ Describe the HR forecasting process and compare and contrast various HR

demand forecasting techniques and methods of determining internal and

external supply

$$ Execute mathematical operations accurately

Overview

For this assignment you are acting as the HR manager for Asha Co$.,$ a horse blanket

manufacturing company which sells its products to various retailers throughout North

America. Customer demand for horse blankets has been quite steady over the past

several years and this is not expected to change. You have been provided with sales

projections and have been tasked to forecast the labour demand and supply

requirements of the organization for the next three years.

Instructions

PART A: FORECASTING DEMAND

$1.$ Open the Excel file HRP $$ FORECASTING ASSIGNMENT $($on DC Connect$)$ and navigate

to the first sheet TREND$/$RATIO ANALYSIS $($See the explanation of trend$/$ratio

analysis in the text$)$

$$ Calculate the ERR $($Employee Requirement Ratio$)$ for the $$known$$ years $2020-$

$2022.$

$$ Calculate the forecasted HR DEMAND $($# OF EMPLOYEES$)$ for years $2024-2026$

using the $2023$ ERR and the forecasted SALES figures. Here is the data:

TREND$/$RATIO ANALYSIS

SALES HR DEMAND ERR

YEAR $($$ THOUSANDS$)($# OF EMPLOYEES$)($SALES$/$EMPLOYEE$)$

$2020$ $$2,800155$

$2021$ $$3,050171$

$2022$ $$3,195166$

$2023$ $$3,300177$

FORECAST

$2024$ $$3,500$

$2025$ $$3,600$

$2026$ $$3,850$

Human Resources Planning Forecasting Assignment

$$ Next, the management team has provided you with an additional set of sales

projections and have asked you to determine HR demand based on these new

figures using a productivity index for the years $2024-2026$ that will be the

average ERR over the years $2020-2023.$ The new forecasted SALES are:

$2024$ to $$3,750$

$2025$ to $$4,200$

$2026$ to $$4,450$

You will need to amend the spreadsheet data to reflect these new numbers.

$$ Remember: the initial sales projections require you to use the ERR for $2023$; the

second set of projections uses the average ERR for $2020-2023$

$$ To receive full marks for this section your spreadsheet must include formulas $$

no static numbers $$ as modelled in$-$class during week $5.$

PART B: FORECASTING SUPPLY

$2.$ Now that you have forecasted the demand for labour, you turn your attention to

ascertaining HR supply. You have decided to use a MARKOV ANALYSIS. Markov

analysis looks at historical patterns of employee movement between jobs to

determine our internal supply for a particular job type, as well as seeing where there

might be problems with too much or too little movement between jobs.

$3.$ Navigate to the second sheet of the Excel Forecasting Assignment file named

MARKOV ANALYSIS, you will be updating the data as per below $($See Chapter $6$

slides for an example of a Markov analysis$).$

$$ Enter the initial numbers of employees per job family $($Job A$,$ B$,$ C$,$ D$).$ Use these

numbers and the movement percentages in the transition probability matrix to

obtain forecasted numbers of employees who will be in job families A$,$ B$,$ C$,$ and

D at the end of this year. As with trend analysis, we can track percentages of

employee movement based on last year or on an average of a few previous

years.

Human Resources Planning Forecasting Assignment

MARKOV ANALYSIS

TRANSITION PROBABILITY MATRIX

START OF YEAR END OF YEAR

JOB A JOB B JOB C JOB D EXIT

JOB A $80\%10\%5\%0\%5\%$

JOB B $10\%70\%0\%10\%10\%$

JOB C $0\%0\%90\%5\%5\%$

JOB D $0\%0\%0\%90\%10\%$

EXPECTED MOVEMENTS OF EMPLOYEES

END OF YEAR

INITIAL # OF

EMPLOYEES JOB A JOB B JOB C JOB D EXIT

JOB A $200$

JOB B $70$

JOB C $60$

JOB D $100$

PREDICTED # OF EMPLOYEES

AT YEAR END

$$ Next, complete the second table $($Markov Analysis #$2)$ but for this forecast, the

management team wants you to include numbers for an additional plant.

Change the initial numbers of employees per job type to:

Job A: $1,478$

Job B: $886$

Job C: $592$

Job D: $1,017$

$$ To receive full marks for this section your spreadsheet must include formulas $$

no static numbers $$ as modelled in$-$class during week $5.$

Human Resources Planning Forecasting Assignment

PART C: ANALYSIS

$4.$ Asha Co$.$ decided to use a quantitative method for forecasting demand. Do you

agree with that decision? Why? Your answer should include a comparison between

quantitative and qualitative methods.

$5.$ What conclusions can you draw from the demand analysis?

$6.$ What is the Markov Analysis telling you in terms of supply? Are there any areas of

concern or gaps? What are the external hiring needs and$/$or gap strategies that may

be employed