Question i contains the actual values for 12 periods (listed in order, 1-12) In
ID: 3244309 • Letter: Q
Question
Question i 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: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.