Month 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 Jan 95 104 101 88 132 12
ID: 3276526 • Letter: M
Question
Month
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
Jan
95
104
101
88
132
125
111
127
119
147
Feb
94
100
96
110
109
118
123
129
147
146
Mar
98
99
82
129
101
121
121
132
164
133
Apr
96
88
84
113
111
140
139
108
135
148
May
95
89
85
114
140
141
119
115
124
141
Jun
115
108
124
169
179
201
156
149
168
191
Jul
107
109
134
131
145
152
154
155
159
178
Aug
104
101
109
139
140
138
136
129
137
156
Sep
96
106
121
120
120
137
105
117
149
119
Oct
112
102
111
115
129
138
132
166
159
138
Nov
98
78
101
116
118
144
123
152
175
175
Dec
102
111
112
128
139
148
164
173
195
188
Question: Use exponential smoothing to fit the data. Select an appropriate constant a based on the variation you see in the data. Comment on the appropriateness of exponential smoothing on this data set. Plot the predictions from this model on the graph with the original data. How well does this technique fit the data? Make forecasts for 1997.
Use Optimization (and Solver in Excel) to find the optimal smoothing constant in problem 2 above (by minimizing the Mean Squared Error or MSE).
Month
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
Jan
95
104
101
88
132
125
111
127
119
147
Feb
94
100
96
110
109
118
123
129
147
146
Mar
98
99
82
129
101
121
121
132
164
133
Apr
96
88
84
113
111
140
139
108
135
148
May
95
89
85
114
140
141
119
115
124
141
Jun
115
108
124
169
179
201
156
149
168
191
Jul
107
109
134
131
145
152
154
155
159
178
Aug
104
101
109
139
140
138
136
129
137
156
Sep
96
106
121
120
120
137
105
117
149
119
Oct
112
102
111
115
129
138
132
166
159
138
Nov
98
78
101
116
118
144
123
152
175
175
Dec
102
111
112
128
139
148
164
173
195
188
Explanation / Answer
Exponential smoothing is used for the data. for first to do calculate the forecast for 1997, an exponential smoothin constant is used which is a = 0.5. Then solver is used to minimize the MSE error.
where MSE = ( y - y')^2/ n
where n = (10-1) * 12 = 108
and y' = exponential smoothed value.
Excel screeshot of solver is first attached here where we have to change the value of CELL G1 by minimzing the value of E134.
MSE = 34902.95241/ 108 = 323.175
and Appropriate smoothing constant a = 0.841
Forecast graph with the original data
Here the R = 0.64 so R2 = 0.40 so the data is moderately fit.
Forecasts for 1997.
Year Month Sales(y) Forecast(y') (y-y')^2 Alpha 0.841 1987 Jan 95 95 Feb 94 94 Mar 98 98 Apr 96 96 May 95 95 Jun 115 115 Jul 107 107 Aug 104 104 Sep 96 96 Oct 112 112 Nov 98 98 Dec 102 102 1988 Jan 104 95 81 Feb 100 94 36 Mar 99 98 1 Apr 88 96 64 May 89 95 36 Jun 108 115 49 Jul 109 107 4 Aug 101 104 9 Sep 106 96 100 Oct 102 112 100 Nov 78 98 400 Dec 111 102 81 1989 Jan 101 102.5652493 2.450005352 Feb 96 99.04349953 9.262889384 Mar 82 98.84058325 283.6052444 Apr 84 89.27533396 27.8291484 May 85 89.95650047 24.56689692 Jun 124 109.1159172 221.5359203 Jul 134 108.6811665 641.0433293 Aug 109 101.4782502 56.57671952 Sep 121 104.4058325 275.3663934 Oct 111 103.5941675 54.84635574 Nov 101 81.1883349 392.5020739 Dec 112 109.5652493 5.928011001 1990 Jan 88 101.2495269 175.5499643 Feb 110 96.48518479 182.6502302 Mar 129 84.68467097 1963.848387 Apr 113 84.84097657 792.9306006 May 114 85.79014917 795.7956837 Jun 169 121.627228 2244.17953 Jul 131 129.963754 1.073805826 Aug 139 107.8009071 973.3833956 Sep 120 118.3546118 2.707302208 Oct 115 109.8193863 26.83875852 Nov 116 97.84168883 329.7242644 Dec 128 111.61186 268.5711337 1991 Jan 132 90.11219646 1754.588085 Feb 109 107.8455121 1.332842201 Mar 101 121.9353945 438.2907423 Apr 111 108.5109801 6.195219871 May 140 109.5028774 930.0744869 Jun 179 161.4479869 308.0731648 Jul 145 130.834805 200.6527485 Aug 140 134.0263422 35.68458794 Sep 120 119.7376976 0.068802562 Oct 129 114.1741234 219.8066163 Nov 118 113.1052611 23.95846854 Dec 139 125.3874561 185.3013526 1992 Jan 125 125.3223827 0.103930605 Feb 118 108.8159553 84.34667697 Mar 121 104.3374524 277.640491 Apr 140 110.6032086 864.1713473 May 141 135.138248 34.36013675 Jun 201 176.2019152 614.9450099 Jul 152 142.7418307 85.71369834 Aug 138 139.0476989 1.097673009 Sep 137 119.9581846 290.4234721 Oct 138 126.636507 129.1289729 Nov 144 117.2196967 717.1846469 Dec 148 136.8299326 124.7704068 1993 Jan 111 125.0513932 197.4416509 Feb 123 116.5359095 41.78446615 Mar 121 118.3437109 7.055871764 Apr 139 135.3136592 13.58910856 May 119 140.0655386 443.7569153 Jun 156 197.04677 1684.83733 Jul 154 150.5240928 12.08193095 Aug 136 138.1670208 4.695978932 Sep 105 134.2832493 857.5086871 Oct 132 136.1884689 17.54327202 Nov 123 139.7307712 279.9187052 Dec 164 146.2193042 316.153143 1994 Jan 127 113.2400274 189.3368468 Feb 129 121.9695157 49.42770907 Mar 132 120.576543 130.495369 Apr 108 138.4123355 924.9101534 May 115 122.3581996 54.14310126 Jun 149 162.5435425 183.4275428 Jul 155 153.4458822 2.41528218 Aug 129 136.3454594 53.95577372 Sep 117 109.6682403 53.75470054 Oct 166 132.6677121 1111.041418 Nov 152 125.6671651 693.4181944 Dec 173 161.1654594 140.0563524 1995 Jan 119 124.8064299 33.71462876 Feb 147 127.8792231 365.60411 Mar 164 130.1789097 1143.866151 Apr 135 112.8482355 490.7006685 May 124 116.1730202 61.26161233 Jun 168 151.1590675 283.6170088 Jul 159 154.7522476 18.04340048 Aug 137 130.1709892 46.63538812 Sep 149 115.8311947 1100.169643 Oct 159 160.6862752 2.84352389 Nov 175 147.8021052 739.7254833 Dec 195 171.113376 570.5708038 1996 Jan 147 119.9256422 733.0208523 Feb 146 143.951828 4.195008627 Mar 133 158.6083519 655.7876851 Apr 148 131.4686378 273.2859358 May 141 122.7522484 332.9804399 Jun 191 165.3152733 659.7051831 Jul 178 158.3228371 387.1907383 Aug 156 135.9113413 403.5542071 Sep 119 143.712337 610.6996012 Oct 138 159.2688205 452.3627253 Nov 175 170.6642001 18.7991605 Dec 188 191.1920722 10.18932466 1997 Jan 142.683894 Feb 145.6734871 Mar 137.0824001 Apr 145.364624 May 138.0910028 Jun 186.9054245 Jul 174.8631307 Aug 152.7975314 Sep 122.9395603 Oct 141.3906061 Nov 174.3088009 Dec 188.5088698 SUM 34902.95241