Question

 

Question 1 contains the actual values for 12 periods (listed in order, 1-12). In Excel, create forecasts for periods 6-13 using each of the following methods: 5 period simple moving average; 4 period weighted moving average (0.63, 0.26, 0.08, 0.03); exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53); linear regression with the equation based on all 12 periods; and quadratic regression with the equation based on all 12 periods.  Round all numerical answers to two decimal places.

1.The actual values for 12 periods (shown in order) are:(1) 45  (2) 52 (3) 48 (4) 59  (5) 55  (6) 55  (7) 64  (8) 58  (9) 73  (10) 66  (11) 69  (12) 74

Using a 5 period simple moving average, the forecast for period 13 will be:

2.Using the 4 period weighted moving average, the forecast for period 13 will be:

3.With exponential smoothing, the forecast for period 13 will be:

4.With linear regression, the forecast for period 13 will be:

5.With quadratic regression, the forecast for period 13 will be:

6.Considering only the forecasts for period 6-12, what is the lowest MAD value for any of the methods?

Answer 1

t	y	MA	4 period	exponential
1	45
2	52
3	48
4	59
5	55		55.16	53
6	55	51.8	55.39	53.46
7	64	53.8	55.11	53.8142
8	58	56.2	60.79	56.15693
9	73	58.2	59.23	56.58084
10	66	61	67.84	60.35725
11	69	63.2	67.12	61.65508
12	74	66	68.21	63.34441
13		68	72.03	65.7952

1)

68

2)

72.03

3)

65.7952

formulas

t	y	MA	4 period	exponential
1	45
=1+A2	52
=1+A3	48
=1+A4	59
=1+A5	55		=0.63*B5+0.26*B4+0.08*B3+0.03*B2	53
=1+A6	55	=AVERAGE(B2:B6)	=0.63*B6+0.26*B5+0.08*B4+0.03*B3	=E6+0.23*(B6-E6)
=1+A7	64	=AVERAGE(B3:B7)	=0.63*B7+0.26*B6+0.08*B5+0.03*B4	=E7+0.23*(B7-E7)
=1+A8	58	=AVERAGE(B4:B8)	=0.63*B8+0.26*B7+0.08*B6+0.03*B5	=E8+0.23*(B8-E8)
=1+A9	73	=AVERAGE(B5:B9)	=0.63*B9+0.26*B8+0.08*B7+0.03*B6	=E9+0.23*(B9-E9)
=1+A10	66	=AVERAGE(B6:B10)	=0.63*B10+0.26*B9+0.08*B8+0.03*B7	=E10+0.23*(B10-E10)
=1+A11	69	=AVERAGE(B7:B11)	=0.63*B11+0.26*B10+0.08*B9+0.03*B8	=E11+0.23*(B11-E11)
=1+A12	74	=AVERAGE(B8:B12)	=0.63*B12+0.26*B11+0.08*B10+0.03*B9	=E12+0.23*(B12-E12)
13		=AVERAGE(B9:B13)	=0.63*B13+0.26*B12+0.08*B11+0.03*B10	=E13+0.23*(B13-E13)

4)

linear regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.913610467
R Square	0.834684085
Adjusted R Square	0.818152493
Standard Error	4.036661826
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	822.7202797	822.7202797	50.49024376	3.27323E-05
Residual	10	162.9463869	16.29463869
Total	11	985.6666667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	44.24242424	2.484393613	17.80813797	6.65437E-09	38.70685031
t	2.398601399	0.33756262	7.105648722	3.27323E-05	1.64646501

y^ = 44.2424 + 2.3986*t

t = 13

y^ = 44.24 + 2.3986*13

= 75.42

5)

quadratic

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.913611
R Square	0.834684
Adjusted R Square	0.797948
Standard Error	4.255011
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	2	822.7206	411.3603	22.72067	0.000304
Residual	9	162.9461	18.10512
Total	11	985.6667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	44.22727	4.397676	10.05696	3.41E-06	34.27904	54.17551	34.27904	54.17551
t	2.405095	1.555359	1.546328	0.156428	-1.11337	5.92356	-1.11337	5.92356
t^2	-0.0005	0.11647	-0.00429	0.996672	-0.26397	0.262974	-0.26397	0.262974

y^ = 44.23 + 2.405 t -0.0005 t^2

y^ = 44.23 + 2.405 *13 -0.0005 *13^2

= 75.41