Question

In: Statistics and Probability

Please run Moving Average with 2 and 3 periods; Exponential Smoothing with a smoothing factor, or...

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

Solutions

Expert Solution

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

orchestra answered 2 years ago
Next > < Previous

Related Solutions

Please run Moving Average with 2 and 3 periods; Exponential Smoothing with a smoothing factor, or...
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...
Of the three quantitative forecasting techniques (moving average, weighted moving average, and exponential smoothing), which do...
Of the three quantitative forecasting techniques (moving average, weighted moving average, and exponential smoothing), which do you think provides the most accurate forecast and why?
a. Show the naive forecast, an exponential smoothing forecasts using α = 0.2, and a 3-month moving average forecast.
Month137244335450534630750829936103511411245a.  Show the naive forecast, an exponential smoothing forecasts using α = 0.2, and a 3-month moving average forecast.b. Compare the MFE, MSE, and MAPE on the modelsc.  Make a conclusion on which model to use.d. Find the alpha (smoothing constant) that minimizes the MSE.
Use 5 months moving average and exponential smoothing method (using alpha=0.3) on the following sale of...
Use 5 months moving average and exponential smoothing method (using alpha=0.3) on the following sale of Maggi. You are supposed to forecast the sale of Maggi in the 13th Month. Compare whether there is any difference in forecast based on two methods and Comment. Period (Month) Actual Sale of Maggi 1 1100 2 800 3 1000 4 1050 5 1500 6 750 7 700 8 650 9 1400 10 1200 11 900 12 1000 13
This passge below require critical analysis and breakdown Moving averages, weighted moving averages, and exponential smoothing...
This passge below require critical analysis and breakdown Moving averages, weighted moving averages, and exponential smoothing methods are forecasting methods that are used for a time series with a horizontal pattern. These methods provide a high level of accuracy for short-range forecasts, such as a forecast for the next time period and are used to smooth out random fluctuations in the time series and have a high level of accuracy for short-range forecast. The moving Average method uses the average...
This passge below require critical analysis and breakdown Moving averages, weighted moving averages, and exponential smoothing...
This passge below require critical analysis and breakdown Moving averages, weighted moving averages, and exponential smoothing methods are three forecasting methods that are appropriate for a time series with a horizontal pattern. These methods are easy to use and generally provide a high level of accuracy for short-range forecasts, such as a forecast for the next time period. “The moving averages method uses the average of the most recent k data values in the time series as the forecast for...
Under what conditions would you prefer a simple exponential smoothing model to the moving averages method...
Under what conditions would you prefer a simple exponential smoothing model to the moving averages method for forecasting a time series? Explain your reasoning.
Forecasting methods studied in this class are quantitative techniques including moving average, weighted moving average, exponential...
Forecasting methods studied in this class are quantitative techniques including moving average, weighted moving average, exponential smoothing, linear regression, and exponential smoothing with trend component. You are given a set of past demand data and asked to forecast demand for the future. What would you FIRST do to decide which demand forecasting method to use and why? Please choose one of the forecasting methods listed above.
Using the stock prices in Problem 3, calculate the exponential three-month moving average for both stocks...
Using the stock prices in Problem 3, calculate the exponential three-month moving average for both stocks where two-thirds of the average weight is placed on the most recent price. Amazon Google $169.64 $600.36 $173.29 $613.40 $180.13 $586.76 $195.81 $544.10 $196.69 $529.02 $204.49 $506.38 $222.52 $603.69 $215.23 $540.96 $216.23 $515.04 $213.51 $592.64 $192.29 $599.39 $173.10 $645.90
In your environment (business or personal), please give an application of exponential smoothing and WHY you...
In your environment (business or personal), please give an application of exponential smoothing and WHY you would use only this technique. In your environment (business or personal), please give an application of trend projection and WHY you would use only this technique. In your environment (business or personal), please give an application of moving average and WHY you would use only this technique.
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT