Question

1. The table below shows the monthly sales of a motorcycle store during the last two years.

Month	Sales		Month	Sales
1	195		13	155
2	210		14	120
3	190		15	135
4	170		16	110
5	180		17	122
6	156		18	97
7	134		19	85
8	155		20	110
9	145		21	85
10	165		22	78
11	128		23	66
12	135		24	45

50. Compute an exponential smoothing and an adjusted exponential smoothing for the data using:

α = 0.2 and β = 0.6

50. Calculate MSE for Ft and Aft
50. Identify the best method to forecast the motorcycle sales using MSE.

Answer 1

EXPONENTIAL SMOOTHING is an averaging method that gives more weight to the most recent data. The forecast will be able to react better with changes in demand(sales). It is useful if the recent changes in the data are significant and unpredictable. It is a popular and frequently used forecasting technique.

The weighting factor is called the smoothing constant (Alpha).

The formula for computing Exponential Smoothing Forecast:

where = The forecast for the next period

= Actual demand in the present period

= Previously determined the forecast for the present period.

= Weighting factor or Smoothing Constant

a) In Excel,

Below are the steps to calculate Exponential Smoothing Forecast

Step 1:

The output is as below :

Month	Sales	Exponential Smoothing Forecast	Error	Error^2
1	195	0	0.0	0.0
2	210	195	15.0	225.0
3	190	207	-17.0	289.0
4	170	193.4	-23.4	547.6
5	180	174.7	5.3	28.3
6	156	178.9	-22.9	526.1
7	134	160.6	-26.6	706.9
8	155	139.3	15.7	245.9
9	145	151.9	-6.9	47.1
10	165	146.4	18.6	347.0
11	128	161.3	-33.3	1107.2
12	135	134.7	0.3	0.1
13	155	134.9	20.1	402.8
14	120	151.0	-31.0	960.1
15	135	126.2	8.8	77.5
16	110	133.2	-23.2	540.1
17	122	114.6	7.4	54.1
18	97	120.5	-23.5	553.6
19	85	101.7	-16.7	279.1
20	110	88.3	21.7	469.1
21	85	105.7	-20.7	427.2
22	78	89.1	-11.1	124.0
23	66	80.2	-14.2	202.4
24	45	68.8	-23.8	568.6
			MSE	379.5058

Error = Actual - Forecast

Error^2 = Square of Error value

MSE = Average of all the errors values = Sum of all errors/ 23 = 379.50

23 because we do not take the 1st period for forecasting as the error will be zero.

Similarly,

For Adjusted Exponential Smoothing Forecast,

The adjusted Exponential Smoothing Forecast consists of the exponential smoothing forecast with a trend adjustment factor to it.

where T = an exponentially smoothed trend factor

The trend factor is computed much the same as the exponentially smoothed forecast.

Forecast model for trend:

where = The last period's trend factor

= Smoothing constant for trend

Month	Sales	Exponetial Forecast	Trend Factor	AdjustedExpoential Forecast	Error	Error^2
1	195	195	0	195	0.0	0.0
2	210	195	0	195	15.0	225.0
3	190	207	7.2	214.2	-24.2	585.6
4	170	193.4	-5.3	188.1	-18.1	328.3
5	180	174.7	-13.3	161.3	18.7	348.3
6	156	178.9	-2.8	176.2	-20.2	406.1
7	134	160.6	-12.1	148.5	-14.5	209.2
8	155	139.3	-17.6	121.7	33.3	1108.5
9	145	151.9	0.5	152.3	-7.3	54.0
10	165	146.4	-3.1	143.3	21.7	472.1
11	128	161.3	7.7	169.0	-41.0	1679.0
12	135	134.7	-12.9	121.8	13.2	175.2
13	155	134.9	-5.0	129.9	25.1	628.0
14	120	151.0	7.6	158.6	-38.6	1491.7
15	135	126.2	-11.8	114.4	20.6	425.2
16	110	133.2	-0.5	132.7	-22.7	517.0
17	122	114.6	-11.4	103.3	18.7	350.0
18	97	120.5	-1.0	119.5	-22.5	507.0
19	85	101.7	-11.7	90.0	-5.0	25.1
20	110	88.3	-12.7	75.6	34.4	1180.4
21	85	105.7	5.3	111.0	-26.0	675.2
22	78	89.1	-7.8	81.3	-3.3	11.2
23	66	80.2	-8.5	71.8	-5.8	33.2
24	45	68.8	-10.2	58.6	-13.6	185.8
					MSE	505.3

Error = Actual - Adjusted Forecast

Error^2 = Square of Error value

MSE = Average of all the errors values = Sum of all errors/ 23 = 505.3

23 because we do not take the 1st period for forecasting as the error will be zero.

Graph of Adjusted Exponential Smoothing Forecast

The best method for Forecast Motorcycle sales is "Exponential Smoothing" as MSE is less.