Question

following table contains data for a corporation's units of production by m of this The year. The two-month forecast is meant to represent an average of the tveh for previous production. The moving average calculation: Units of Month (000) Average Forecast 3,000 2,500 3.500 March Apri May 3.200 2,100 June 3,500 (a) List the two-month average forecast value for January, February, March, April, May, and June. (Use "NA" when it is not possible to calculate an accurate result.) (b) If there is a more meaningful forecast analysis, detail your recommendations and explain w the results would enhance decision-making. Otherwise, defend the use of the moving average technique in this situation

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3		Two month moving average can be calculated as follows:
4
5		Ft+1 = (Dt+Dt-1)/2
6
7		a)
8		Month	Units of production (000)	Two month Average forecast
9		January	3000	N/A
10		February	2500	N/A
11		March	3500	=(D9+D10)/2	=(D9+D10)/2
12		April	3200	=(D10+D11)/2
13		May	2100	=(D11+D12)/2
14		June	3500	=(D12+D13)/2
15
16		b)
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33		It can be seen from the chart that there is lag between the forecasted value and the actual value.
34		Exponential moving average method gives more weightage to the recent data
35		and hence helps in reducing the lag.
36
37		In exponential moving average, forecasted value at time t, F(t) in can be written as follows:
38
39
40		For 0<α<1 and t>=3
41
42		Where Y(t-1) is the actual value at t-1 and F(t-1) is the forecasted value at t-1
43
44		For t=1, F(1)= Y(1).
45
46		Given the following data:
47
48		α	0.2
49
50		Month	Units of production (000)	Forecasted value
51		January	3000	=D51
52		February	2500	=$D$48*D51+(1-$D$48)*E51	=$D$48*D51+(1-$D$48)*E51
53		March	3500	=$D$48*D52+(1-$D$48)*E52
54		April	3200	=$D$48*D53+(1-$D$48)*E53
55		May	2100	=$D$48*D54+(1-$D$48)*E54
56		June	3500	=$D$48*D55+(1-$D$48)*E55