Question 1 contains the actual values for 12 periods (listed in order, 1-12). In
ID: 2931597 • 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) 58 (7) 64 (8) 61 (9) 71 (10) 66 (11) 66 (12) 77
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?
Explanation / Answer
time
time^2
data
ma
w.Av
expo
linear
quad
1
1
45
46.9359
46.61538
2
4
52
45
49.34149
49.1958
3
9
48
46.61
51.74709
51.74126
4
16
59
46.9297
54.15268
54.25175
5
25
55
47.48
49.705869
56.55828
56.72727
6
36
58
51.8
51.61
50.92351913
58.96387
59.16783
7
49
64
54.4
51.72
52.55110973
61.36946
61.57343
8
64
61
56.8
58.03
55.18435449
63.77506
63.94406
9
81
71
59.4
56.68
56.52195296
66.18065
66.27972
10
100
66
61.8
60.19
59.85190378
68.58625
68.58042
11
121
66
64
63.84
61.26596591
70.99184
70.84615
12
144
77
65.6
64.15
62.35479375
73.39744
73.07692
13
169
-
68.2
69.48
65.72319119
75.80303
75.27273
Mt is the predicted value at time t and Yt is the actual value
Moving average for period of 5 is calculated by: (45+52+48+59+55)/5 = 51.8 = M6
M7 = (52+48+59+55+58)/2 = 54.4
And so on
Weighted moving average for period 4 is calculated by : (0.63*45+0.26*52+0.08*48+0.03*59)=M5 = 47.48
M6 = (0.63*52+0.26*48+0.08*59+0.03*55)=51.61
And so on.
Exponential smoothing with alpha = 0.23
M3 = alpha* 48 + (1-alpha)*45 = 46.61
M4 = alpha* 59 + (1-alpha)*46.61 = 46.9297
And so on.
Linear regression:
Regress time on Yt . use excel data toolpack, data > data analysis > regression.
Input Y(data) range and x(time) range
The model is given by:
Coefficients
Standard Error
t Stat
P-value
Lower
95%
Upper
95%
Lower 95.0%
Upper 95.0%
Intercept
44.53030303
2.254139
19.754
9
2.42E-09
39.507
77
49.552
84
39.50777
49.55284
time
2.405594406
0.306277
04
7.8543
1.38E-05
1.723166
3.088023
1.723166
3.088023
Y = 44.53030 + 2.40559*time
Substitute time =13
Y13 = 75.80303
Quadratic regression:
Regress time(x) and time-square(x2) on Y.
Input Y(data) range and x and x2 together.
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept
44
3.984031
11.04409
1.56E-06
34.9875
53.0125
34.9875
53.0125
time
2.632867
1.409062
1.868525
0.094521
-0.55465
5.820386
-0.55465
5.820386
time^2
-0.01748
0.105515
-0.16569
0.872065
-0.25617
0.221209
-0.25617
0.221209
Y = 44 + 2.6328*time – 0.01748*time2
Substitute x=13and x2 =169
Y = 75.2727
MSD is calculated as:
MSD = average of (Mt – Yt)2 t =6,7,…,12
time
msd_ma
msd_wma
msd_exp
msd_lin
msd_qua
6
38.44
40.8321
29.69041
0.929044
1.363832
7
92.16
150.7984
77.71561
6.91972
5.888259
8
17.64
8.8209
20.05291
7.700948
8.667465
9
134.56
205.0624
124.28
23.22611
22.28104
10
17.64
33.7561
22.41108
6.688674
6.658565
11
4
4.6656
13.28753
24.91848
23.48521
12
129.96
165.1225
127.1664
12.97847
15.39053
average
62.05714
87.008286
59.22914
11.90878
11.96213
Linear regression method has the lowest MAD = 11.9087.
time
time^2
data
ma
w.Av
expo
linear
quad
1
1
45
46.9359
46.61538
2
4
52
45
49.34149
49.1958
3
9
48
46.61
51.74709
51.74126
4
16
59
46.9297
54.15268
54.25175
5
25
55
47.48
49.705869
56.55828
56.72727
6
36
58
51.8
51.61
50.92351913
58.96387
59.16783
7
49
64
54.4
51.72
52.55110973
61.36946
61.57343
8
64
61
56.8
58.03
55.18435449
63.77506
63.94406
9
81
71
59.4
56.68
56.52195296
66.18065
66.27972
10
100
66
61.8
60.19
59.85190378
68.58625
68.58042
11
121
66
64
63.84
61.26596591
70.99184
70.84615
12
144
77
65.6
64.15
62.35479375
73.39744
73.07692
13
169
-
68.2
69.48
65.72319119
75.80303
75.27273
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.