Question 1 contains the actual values for 12 periods (listed in order, 1-12). In
ID: 3259003 • Letter: Q
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) 54 (7) 64 (8) 59 (9) 72 (10) 66 (11) 64 (12) 78
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?
Found it!
Explanation / Answer
1) Using a 5 period simple moving average, the forecast for period 13 will be: 67.8.
[ Forecast for period 13 = (59+72+66+64+78) / 5. ]
2) Using the 4 period weighted moving average, the forecast for period 13 will be: 69.98.
[ Forecast for period 13 = 0.63*72 + 0.26*66 + 0.08*64 + 0.03*78. ]
3) With exponential smoothing, the forecast for period 13 will be: 65.76.
[ Formula : St+1=0.23*yt+(1 - 0.23)*St ,t>0.
Here, S5 = 53.
S6 = 0.23*55 + (1-0.23)*53
S7 = 0.23*54 + (1-0.23)*S6 and so on. ]
Period
Values
5-period MA forecasts
Exponential Smoothing
1
45
2
52
3
48
4
59
5
55
53
6
54
51.8
53.46
7
64
53.6
53.5842
8
59
56
55.97983
9
72
58.2
56.67447
10
66
60.8
60.19934
11
64
63
61.53349
12
78
65
62.10079
13
67.8
65.75761
4) With linear regression, the forecast for period 13 will be: 75.19.
[Select DATA tab - select DATA ANALYSIS - choose REGRESSION - click on OK - select VALUES column in INPUT Y RANGE - select PERIOD column in INPUT X RANGE - Tick the labels - click on OK.
On regressing the actual values on the periods, we get the regression equation :
Values = 44.12 + 2.39*Period
Putting Period = 13, we get the required value. ]
Period
Values
5-period MA forecasts
Exponential Smoothing
1
45
2
52
3
48
4
59
5
55
53
6
54
51.8
53.46
7
64
53.6
53.5842
8
59
56
55.97983
9
72
58.2
56.67447
10
66
60.8
60.19934
11
64
63
61.53349
12
78
65
62.10079
13
67.8
65.75761
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.