Using this file

PART A
TREND ANALYSIS
	SALES	HR DEMAND	ERR (Employee Requirement Ratio)
YEAR	($ THOUSANDS)	(# OF EMPLOYEES)	(SALES/EMPLOYEE)
2018	$2,800	155
2019	$3,050	171
2020	$3,195	166
2021	$3,300	177
FORECAST
2022	$3,500
2023	$3,600
2024	$3,850
PART B
TREND ANALYSIS
	SALES	HR DEMAND	ERR (Employee Requirement Ratio)
YEAR	($ THOUSANDS)	(# OF EMPLOYEES)	(SALES/EMPLOYEE)
2018	$2,800	155
2019	$3,050	171
2020	$3,195	166
2021	$3,300	177
FORECAST
2022	$3,500
2023	$3,600
2024	$3,850

Create a spreadsheet using the data displayed below

• Calculate the ERR (Employee Requirement Ratio) for the known years 2018-2021. Use the index for 2021 for each of the upcoming years 2022-2024.
• Calculate the forecasted HR DEMAND (# OF EMPLOYEES) for each of these years, using the ERR and the forecasted SALES figures. If your formulas are correct you should get the figures shown below.
• Include the excel formula for each yellow box.

INDEX/TREND ANALYSIS
	SALES	HR DEMAND	PRODUCTIVITY INDEX
YEAR	($ THOUSANDS)	(# OF EMPLOYEES)	(SALES/EMPLOYEE)
2018	$2,800	155	18.06
2019	$3,050	171	17.84
2020	$3,195	166	19.25
2021	$3,300	177	18.64
FORECAST
2022	$3,500	188	18.64
2023	$3,600	193	18.64
2024	$3,850	207	18.64

PART A: Note the changes in HR demand when the forecasted sales figures change.

PART B

• Change the forecasted SALES for:

2022 to $3,750

2023 to $4,200

2024 to $4,450

• For this one use a productivity index for the years 2022-2024 that will be the average productivity over the years 2018-2021.

The HR demand forecast figures will change again.

Remember: PART A uses the ERR for 2021

PART B uses the average ERR for 2018-2021