Question

Consider the following time series data

Month 1 2 3 4 5 6 7 8 9 10 11 12

Value 90 89 86 91 90 91 88 86 91 93 90 88

 

(a) Develop a five month average forecast. Compute MSE and a forecast for month 13.

(b) Use α = 0.2 to compute the exponential smoothing values for the time series. Compute MSE and a forecast for month 13.

( c) Compare the result for the five month average and exponential smoothing. Which appears to provide a better forecast based on MSE? Explain. (Make sure to adjust the MSE of the exponential smoothing). Please show steps in excel.

Answer 1

5 month moving average

x	y	5 MA	Squared error
1	90
2	89
3	86
4	91
5	90
6	91	89.2	3.24
7	88	89.4	1.96
8	86	89.2	10.24
9	91	89.2	3.24
10	93	89.2	14.44
11	90	89.8	0.04
12	88	89.6	2.56
13		89.6
		MSE	5.102857143

forecast for month 13 = 89.6

formula

x	y	5 MA	Squared error
1	90
=1+A2	89
=1+A3	86
=1+A4	91
=1+A5	90
=1+A6	91	=AVERAGE(B2:B6)	=(C7-B7)^2
=1+A7	88	=AVERAGE(B3:B7)	=(C8-B8)^2
=1+A8	86	=AVERAGE(B4:B8)	=(C9-B9)^2
=1+A9	91	=AVERAGE(B5:B9)	=(C10-B10)^2
=1+A10	93	=AVERAGE(B6:B10)	=(C11-B11)^2
=1+A11	90	=AVERAGE(B7:B11)	=(C12-B12)^2
=1+A12	88	=AVERAGE(B8:B12)	=(C13-B13)^2
13		=AVERAGE(B9:B13)
		MSE	=AVERAGE(D7:D13)

exponential

x	y	exponential	Squared error
1	90	90
2	89	90	1
3	86	89.8	14.44
4	91	89.04	3.8416
5	90	89.432	0.322624
6	91	89.5456	2.115279
7	88	89.83648	3.372659
8	86	89.46918	12.03524
9	91	88.77535	4.94908
10	93	89.22028	14.2863
11	90	89.97622	0.000565
12	88	89.98098	3.924273
13		89.58478
		MSE	5.480693

forecast =

89.58478

formula

x	y	exponential	Squared error
1	90	90
=1+A2	89	=C2+0.2*(B2-C2)	=(C3-B3)^2
=1+A3	86	=C3+0.2*(B3-C3)	=(C4-B4)^2
=1+A4	91	=C4+0.2*(B4-C4)	=(C5-B5)^2
=1+A5	90	=C5+0.2*(B5-C5)	=(C6-B6)^2
=1+A6	91	=C6+0.2*(B6-C6)	=(C7-B7)^2
=1+A7	88	=C7+0.2*(B7-C7)	=(C8-B8)^2
=1+A8	86	=C8+0.2*(B8-C8)	=(C9-B9)^2
=1+A9	91	=C9+0.2*(B9-C9)	=(C10-B10)^2
=1+A10	93	=C10+0.2*(B10-C10)	=(C11-B11)^2
=1+A11	90	=C11+0.2*(B11-C11)	=(C12-B12)^2
=1+A12	88	=C12+0.2*(B12-C12)	=(C13-B13)^2
13		=C13+0.2*(B13-C13)
		MSE	=AVERAGE(D3:D13)

c)

5 month MA is better as its MSE is lower