In: Statistics and Probability
Please run Moving Average with 2 and 3 periods; Exponential Smoothing with a smoothing factor, or alpha, of 0.1 , 0.5 and 0.9; and Classical Decomposition for the data listed below.
Which model is best? Please explain how you were able to run each model using Microsoft Excel.
Year | Quarter | Sales | Advertising |
1 | 1 | 144 | 41 |
2 | 151 | 51 | |
3 | 134 | 32 | |
4 | 151 | 45 | |
2 | 1 | 145 | 48 |
2 | 145 | 34 | |
3 | 141 | 29 | |
4 | 166 | 43 | |
3 | 1 | 151 | 40 |
2 | 164 | 51 | |
3 | 151 | 39 | |
4 | 176 | 54 | |
4 | 1 | 170 | 41 |
2 | 180 | 52 | |
3 | 156 | 48 | |
4 | 187 | 47 | |
5 | 1 | 166 | 44 |
2 | 182 | 48 | |
3 | 154 | 44 | |
4 | 169 |
36 |
The 2 period Moving Average (MA) forecast for sales
The data values are,
Period | Sales |
1 | 144 |
2 | 151 |
3 | 134 |
4 | 151 |
5 | 145 |
6 | 145 |
7 | 141 |
8 | 166 |
9 | 151 |
10 | 164 |
11 | 151 |
12 | 176 |
13 | 170 |
14 | 180 |
15 | 156 |
16 | 187 |
17 | 166 |
18 | 182 |
19 | 154 |
20 | 169 |
The average of two period is obtained by taking the average of the current year and the previous three year.
Period | Sales | MA period =2 |
1 | 144 | ### |
2 | 151 | =(144+151)/2=147.5 |
3 | 134 | =(151+134)/2=142.5 |
4 | 151 | =(134+151)/2=142.5 |
5 | 145 | =(1151+145)/2=148 |
6 | 145 | =(145+145)/2=145 |
7 | 141 | =(145+141)/2=143 |
8 | 166 | =(141+166)/2=153.5 |
9 | 151 | =(166+151)/2=158.5 |
10 | 164 | =(151+164)/2=157.5 |
11 | 151 | =(164+151)/2=157.5 |
12 | 176 | =(151+176)/2=163.5 |
13 | 170 | =(176+170)/2=173 |
14 | 180 | =(170+180)/2=175 |
15 | 156 | =(156+180)/2=168 |
16 | 187 | =(156+187)/2=171.5 |
17 | 166 | =(187+166)/2=176.5 |
18 | 182 | =(166+182)/2=174 |
19 | 154 | =(154+182)/2=168 |
20 | 169 | =(154+169)/2=161.5 |
In forecast one of the way to measure error is mean square error (MSE). The means square error is the average of the squared differences between the forecast and observed values.
Period | Sales | MA period =2 | Difference | Difference^2 |
1 | 144 | ### | ||
2 | 151 | 147.5 | 3.5 | 12.25 |
3 | 134 | 142.5 | -8.5 | 72.25 |
4 | 151 | 142.5 | 8.5 | 72.25 |
5 | 145 | 148 | -3 | 9 |
6 | 145 | 145 | 0 | 0 |
7 | 141 | 143 | -2 | 4 |
8 | 166 | 153.5 | 12.5 | 156.25 |
9 | 151 | 158.5 | -7.5 | 56.25 |
10 | 164 | 157.5 | 6.5 | 42.25 |
11 | 151 | 157.5 | -6.5 | 42.25 |
12 | 176 | 163.5 | 12.5 | 156.25 |
13 | 170 | 173 | -3 | 9 |
14 | 180 | 175 | 5 | 25 |
15 | 156 | 168 | -12 | 144 |
16 | 187 | 171.5 | 15.5 | 240.25 |
17 | 166 | 176.5 | -10.5 | 110.25 |
18 | 182 | 174 | 8 | 64 |
19 | 154 | 168 | -14 | 196 |
20 | 169 | 161.5 | 7.5 | 56.25 |
MSE = | 1467.75 |
Similarly,
The 3 period Moving Average (MA) forecast is obtained below,
Period | Sales | MA period =2 | Difference | Difference^2 |
1 | 144 | ### | ||
2 | 151 | ### | ### | |
3 | 134 | 143 | -9 | 81 |
4 | 151 | 145.3333333 | 5.666666667 | 32.11111111 |
5 | 145 | 143.3333333 | 1.666666667 | 2.777777778 |
6 | 145 | 147 | -2 | 4 |
7 | 141 | 143.6666667 | -2.666666667 | 7.111111111 |
8 | 166 | 150.6666667 | 15.33333333 | 235.1111111 |
9 | 151 | 152.6666667 | -1.666666667 | 2.777777778 |
10 | 164 | 160.3333333 | 3.666666667 | 13.44444444 |
11 | 151 | 155.3333333 | -4.333333333 | 18.77777778 |
12 | 176 | 163.6666667 | 12.33333333 | 152.1111111 |
13 | 170 | 165.6666667 | 4.333333333 | 18.77777778 |
14 | 180 | 175.3333333 | 4.666666667 | 21.77777778 |
15 | 156 | 168.6666667 | -12.66666667 | 160.4444444 |
16 | 187 | 174.3333333 | 12.66666667 | 160.4444444 |
17 | 166 | 169.6666667 | -3.666666667 | 13.44444444 |
18 | 182 | 178.3333333 | 3.666666667 | 13.44444444 |
19 | 154 | 167.3333333 | -13.33333333 | 177.7777778 |
20 | 169 | 168.3333333 | 0.666666667 | 0.444444444 |
MSE = | 1115.777778 |
The Exponential smoothing forecast method,
For smoothing factor
Forecast for second month is,
Period | Sales | Exponential Smoothing, alpha = 0.1 | Difference | Difference^2 |
1 | 144 | |||
2 | 151 | 144 | 7 | 49 |
3 | 134 | 144.7 | -10.7 | 114.49 |
4 | 151 | 143.63 | 7.37 | 54.3169 |
5 | 145 | 144.367 | 0.633 | 0.400689 |
6 | 145 | 144.4303 | 0.5697 | 0.32455809 |
7 | 141 | 144.48727 | -3.48727 | 12.16105205 |
8 | 166 | 144.138543 | 21.861457 | 477.9233022 |
9 | 151 | 146.3246887 | 4.6753113 | 21.85853575 |
10 | 164 | 146.7922198 | 17.20778017 | 296.1076984 |
11 | 151 | 148.5129978 | 2.487002153 | 6.185179709 |
12 | 176 | 148.7616981 | 27.23830194 | 741.9250924 |
13 | 170 | 151.4855283 | 18.51447174 | 342.785664 |
14 | 180 | 153.3369754 | 26.66302457 | 710.9168792 |
15 | 156 | 156.0032779 | -0.003277887 | 1.07445E-05 |
16 | 187 | 156.0029501 | 30.9970499 | 960.8171026 |
17 | 166 | 159.1026551 | 6.897344911 | 47.57336682 |
18 | 182 | 159.7923896 | 22.20761042 | 493.1779606 |
19 | 154 | 162.0131506 | -8.013150622 | 64.21058289 |
20 | 169 | 161.2118356 | 7.78816444 | 60.65550535 |
MSE = | 4454.83008 |
For smoothing factor
Forecast for second month is,
Period | Sales | Exponential Smoothing, alpha = 0.5 | Difference | Difference^2 |
1 | 144 | |||
2 | 151 | 144 | 7 | 49 |
3 | 134 | 147.5 | -13.5 | 182.25 |
4 | 151 | 140.75 | 10.25 | 105.0625 |
5 | 145 | 145.875 | -0.875 | 0.765625 |
6 | 145 | 145.4375 | -0.4375 | 0.19140625 |
7 | 141 | 145.21875 | -4.21875 | 17.79785156 |
8 | 166 | 143.109375 | 22.890625 | 523.9807129 |
9 | 151 | 154.5546875 | -3.5546875 | 12.63580322 |
10 | 164 | 152.7773438 | 11.22265625 | 125.9480133 |
11 | 151 | 158.3886719 | -7.388671875 | 54.59247208 |
12 | 176 | 154.6943359 | 21.30566406 | 453.9313211 |
13 | 170 | 165.347168 | 4.652832031 | 21.64884591 |
14 | 180 | 167.673584 | 12.32641602 | 151.9405318 |
15 | 156 | 173.836792 | -17.83679199 | 318.1511486 |
16 | 187 | 164.918396 | 22.081604 | 487.5972354 |
17 | 166 | 175.959198 | -9.959197998 | 99.18562476 |
18 | 182 | 170.979599 | 11.020401 | 121.4492382 |
19 | 154 | 176.4897995 | -22.4897995 | 505.7910815 |
20 | 169 | 165.2448997 | 3.75510025 | 14.10077789 |
MSE = | 3246.02019 |
For smoothing factor
Forecast for second month is,
Sales | Exponential Smoothing, alpha = 0.5 | Difference | Difference^2 |
144 | |||
151 | 144 | 7 | 49 |
134 | 150.3 | -16.3 | 265.69 |
151 | 135.63 | 15.37 | 236.2369 |
145 | 149.463 | -4.463 | 19.918369 |
145 | 145.4463 | -0.4463 | 0.19918369 |
141 | 145.04463 | -4.04463 | 16.35903184 |
166 | 141.404463 | 24.595537 | 604.9404403 |
151 | 163.5404463 | -12.5404463 | 157.2627934 |
164 | 152.2540446 | 11.74595537 | 137.9674676 |
151 | 162.8254045 | -11.82540446 | 139.8401907 |
176 | 152.1825404 | 23.81745955 | 567.2713796 |
170 | 173.618254 | -3.618254045 | 13.09176233 |
180 | 170.3618254 | 9.638174596 | 92.89440953 |
156 | 179.0361825 | -23.03618254 | 530.665706 |
187 | 158.3036183 | 28.69638175 | 823.4823253 |
166 | 184.1303618 | -18.13036183 | 328.7100199 |
182 | 167.8130362 | 14.18696382 | 201.2699424 |
154 | 180.5813036 | -26.58130362 | 706.565702 |
169 | 156.6581304 | 12.34186964 | 152.3217462 |
MSE = | 5043.68737 |
From each forecast method applied above foe sales forcast,
Hence, the moving average forcast method for period = 3 is best fit for the sales data.
Similarly the forecast method can be applied to advertising data.
I am working on rest of the part. I'll upload it soon