Question

3. The Bank of Humber is interested in finding a good technique of forecasting short term changes in branch demands to better plan their staffing allocation. To accomplish this objective, the bank has been reviewing information on the number of customers doing business at the Rexdale branch on a daily basis. The following table shows the number of customers that visited the bank each weekday over the last four weeks (1=Monday, 2 = Tuesday, 3 = W ednesday,
   4 = Thursday, 5=Friday):
   

Week	Day	Customers	Week	Day	Customers
1	1	184	3	1	180
	2	140		2	146
	3	171		3	174
	4	191		4	192
	5	208		5	211
2	1 2	176 142	4	1 2	190 146
	3	170		3	165
	4	184		4	188
	5	214		5	207

Use Excel to answer the following questions. Do not round intermediate results. Round MADs to 4 decimal places.

1. Using 5-period moving averages, calculate the forecasts and compute MAD. [5 point]
   
2. Using 5-period weighted moving averages, with weights 0.1, 0.1, 0.25, 0.25, and 0.3 respectively, calculate the forecasts and compute MAD.      [5 point]
   
3. Use exponential smoothing with α = 0.4 to calculate forecasts and compute MAD. [5 point]
   
4. Which method is the best, comparing a), b), and c)?    [5 point]
   
5. Calculate daily seasonal indexes (4 decimals), assuming there is no trend. [5 point]
   
6. The bank manager has just ordered 900 wrapped gifts to be given to customers during the customer appreciation week. Use your results from part e) to estimate how many should gifts should be allocated to each week day.      [5 point]

Answer 1

1. Using 5-period moving averages, calculate the forecasts and compute MAD. [5 point]

Period	Demand		Forecast	Error	Absolute
Period 1	184
Period 2	140
Period 3	171
Period 4	191
Period 5	208
Period 6	176		178.8	-2.8	2.8
Period 7	142		177.2	-35.2	35.2
Period 8	170		177.6	-7.6	7.6
Period 9	184		177.4	6.6	6.6
Period 10	214		176	38	38
Period 11	180		177.2	2.8	2.8
Period 12	146		178	-32	32
Period 13	174		178.8	-4.8	4.8
Period 14	192		179.6	12.4	12.4
Period 15	211		181.2	29.8	29.8
Period 16	190		180.6	9.4	9.4
Period 17	146		182.6	-36.6	36.6
Period 18	165		182.6	-17.6	17.6
Period 19	188		180.8	7.2	7.2
Period 20	207		180	27	27
			Total	-3.4	269.8
			Average	-0.22667	17.9867
				Bias	MAD
Next period	179.2

1. Using 5-period weighted moving averages, with weights 0.1, 0.1, 0.25, 0.25, and 0.3 respectively, calculate the forecasts and compute MAD.      [5 point]

Period	Demand	Weights		Forecast	Error	Absolute
Period 1	184	0.1	5 periods ago
Period 2	140	0.1	4 periods ago
Period 3	171	0.25	3 periods ago
Period 4	191	0.25	2 periods ago
Period 5	208	0.3	1 periods ago
Period 6	176			185.3	-9.3	9.3
Period 7	142			183.65	-41.65	41.65
Period 8	170			174.8	-4.8	4.8
Period 9	184			170.4	13.6	13.6
Period 10	214			171.6	42.4	42.4
Period 11	180			184.5	-4.5	4.5
Period 12	146			184.7	-38.7	38.7
Period 13	174			177.7	-3.7	3.7
Period 14	192			173.5	18.5	18.5
Period 15	211			177	34	34
Period 16	190			187.4	2.6	2.6
Period 17	146			189.75	-43.75	43.75
Period 18	165			180.65	-15.65	15.65
Period 19	188			173.8	14.2	14.2
Period 20	207			174.25	32.75	32.75
				Total	-4	320.1
				Average	-0.26667	21.34
					Bias	MAD
Next period	183.95

1. Use exponential smoothing with α = 0.4 to calculate forecasts and compute MAD. [5 point]

Period	Demand		Forecast	Error	Absolute
Period 1	184
Period 2	140		184	-44	44
Period 3	171		166.4	4.6	4.6
Period 4	191		168.24	22.76	22.76
Period 5	208		177.344	30.656	30.656
Period 6	176		189.6064	-13.6064	13.6064
Period 7	142		184.1638	-42.1638	42.16384
Period 8	170		167.2983	2.701696	2.701696
Period 9	184		168.379	15.62102	15.62102
Period 10	214		174.6274	39.37261	39.37261
Period 11	180		190.3764	-10.3764	10.37643
Period 12	146		186.2259	-40.2259	40.22586
Period 13	174		170.1355	3.864484	3.864484
Period 14	192		171.6813	20.31869	20.31869
Period 15	211		179.8088	31.19121	31.19121
Period 16	190		192.2853	-2.28527	2.285271
Period 17	146		191.3712	-45.3712	45.37116
Period 18	165		173.2227	-8.2227	8.222698
Period 19	188		169.9336	18.06638	18.06638
Period 20	207		177.1602	29.83983	29.83983
			Total	12.74026	425.2436
			Average	0.67054	22.3812
				Bias	MAD
Next period	189.096103

1. Which method is the best, comparing a), b), and c)?    [5 point]

5-period moving averages method is the best since it has a lower MAD value.

1. Calculate daily seasonal indexes (4 decimals), assuming there is no trend. [5 point]

Seasonal

Indexes

0.9945

1.0377

0.9865

0.9813

1. The bank manager has just ordered 900 wrapped gifts to be given to customers during the customer appreciation week. Use your results from part e) to estimate how many should gifts should be allocated to each week day.      [5 point]

232

258

214

196