Question

Question 4 [12 marks]

Sales of commercial elevators at Armsteel Co. over the 12 months were as below:

Month	Sales	Month	Sales
January	898	July	900
February	1325	August	1502
March	1250	September	1311
April	1008	October	899
May	1497	November	1311
June	1186	December	1074

1. Use three-period weighted moving average with weights 0.5, 0.3, 0.2 (0.5 = most recent) to forecast the forecast for April and May. Show your works in the Excel. (label the tab in Excel spreadsheet as Q4a) [3 marks]

MONTH	SALES	FORECAST
January	898
February	1325
March	1250
April	1008
May	1497

2. Use exponential smoothing with α = 0.2 to forecast January next year’s sales demand in your Excel spreadsheet. Assume that the initial forecast for January is 900. To measure forecast error, include computation of Mean Absolute Percent Error (MAPE). (label the tab in Excel spreadsheet as Q4b) [6 marks]
3. Determine the optimal value of α that minimize MAPE and then forecast January next year’s sales demand. (label the tab in Excel spreadsheet as Q4c) [3 marks]

Answer 1

1.

Month	Sales	Forecast
1	898
2	1325
3	1250
4	1008	1202.1
5	1497	1144

2.

Month	Sales	Forecast
1	898	900
2	1325	899.6
3	1250	984.68
4	1008	1037.744
5	1497	1031.795
6	1186	1124.836
7	900	1137.069
8	1502	1089.655
9	1311	1172.124
10	899	1199.899
11	1311	1139.719
12	1074	1173.976
1		1153.98

Jan forecast = 1153.98

Month	Sales	Forecast	error	abs error	APE
1	898	900	-2	2	0.222717
2	1325	899.6	425.4	425.4	32.10566
3	1250	984.68	265.32	265.32	21.2256
4	1008	1037.744	-29.744	29.744	2.950794
5	1497	1031.795	465.2048	465.2048	31.0758
6	1186	1124.836	61.16384	61.16384	5.157153
7	900	1137.069	-237.069	237.0689	26.34099
8	1502	1089.655	412.3449	412.3449	27.45305
9	1311	1172.124	138.8759	138.8759	10.59313
10	899	1199.899	-300.899	300.8993	33.47044
11	1311	1139.719	171.2806	171.2806	13.06488
12	1074	1173.976	-99.9755	99.97555	9.30871
1		1153.98

MAPE with alpha =0.2 = 17.7474

(iii) MAP with alpha = 0.1

Month	Sales	Forecast	error	abs error	APE
1	898	900	-2	2	0.222717
2	1325	899.8	425.2	425.2	32.09057
3	1250	942.32	307.68	307.68	24.6144
4	1008	973.088	34.912	34.912	3.463492
5	1497	976.5792	520.4208	520.4208	34.76425
6	1186	1028.621	157.3787	157.3787	13.26971
7	900	1044.359	-144.359	144.3592	16.03991
8	1502	1029.923	472.0768	472.0768	31.42988
9	1311	1077.131	233.8691	233.8691	17.83898
10	899	1100.518	-201.518	201.5178	22.41578
11	1311	1080.366	230.634	230.634	17.59222
12	1074	1103.429	-29.4294	29.42944	2.740171
1		1100.486

MAPE with alpha =0,1 =18.0401

Similarly,

MAPE for alpha =0.3 =18.0734

for alpha =0.4 =19.0254

for alpha =0.5 =19.8366

for alpha =0.6 =20.6129

for alpha =0.7=21.678

for alpha =0.8 =22.722

for alpha =0.9 =24.145

It can be seen that the lowest value comes with alpha =0.2