Question

1. A restaurant wants to forecast its weekly sales. Historical data (in dollars) for 15 weeks are shown below. Time Period Observation 1 1,586 2 1,622 3 1,417 4 1,310 5 1,228 6 1,342 7 1,580 8 1,303 9 1,283 10 1,559 11 1,386 12 1,586 13 1,665 14 1,638 15 1,361 MSE values based on the two- and three-period moving average are 33,931.58 and 31,048.14, respectively. Find the best single exponential smoothing model by evaluating the MSE from 0.1 to 0.9, in increments of 0.1. Do not round intermediate calculations. Round your answers to two decimal places. Alpha MSE 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 A. The model based on a smoothing constant of (0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9) is the best. How does this model compare with the moving average models? B. The (single exponential smoothing, 2-period moving average, 3-period moving average) model has the lowest MSE value.

Answer 1

Moving Average (MA)

The 2-period Moving Average (MA) forecast is obtained in excel by following these steps,

Step 1: Write the data values in excel

Step 2: DATA > Data Analysis > Moving Average > OK.

Step 3: Select Input Range: Sales column, Interval: 2. the screenshot is shown below,

The forecast is obtained.

Observation	MA(2)
1,586
1,622
1,417	1,604
1,310	1,520
1,228	1,364
1,342	1,269
1,580	1,285
1,303	1,461
1,283	1,442
1,559	1,293
1,386	1,421
1,586	1,473
1,665	1,486
1,638	1,626
1,361	1,652

The mean square error is obtained using the following formula,

Where, is the forecast and is the actual observation value.

Observation	MA(2)	SE(2)
1,586
1,622
1,417	1,604	34969
1,310	1,520	43890.25
1,228	1,364	18360.25
1,342	1,269	5329
1,580	1,285	87025
1,303	1,461	24964
1,283	1,442	25122.25
1,559	1,293	70756
1,386	1,421	1225
1,586	1,473	12882.25
1,665	1,486	32041
1,638	1,626	156.25
1,361	1,652	84390.25
	MSE	33931.57692

Similarly, the 3-period Moving Average (MA) forecast is obtained in excel and the mean square is calculated. The result is shown below,

Observation	MA(3)	SE
1,586
1,622
1,417
1,310	1,542	53669.44
1,228	1,450	49136.11
1,342	1,318	560.11
1,580	1,293	82177.78
1,303	1,383	6453.44
1,283	1,408	15708.44
1,559	1,389	29013.44
1,386	1,382	18.78
1,586	1,409	31211.11
1,665	1,510	23921.78
1,638	1,546	8525.44
1,361	1,630	72181.78
	MSE	31048.14

The Exponential smoothing forecast method,

For smoothing factor

The Exponential smoothing forecast is obtained in excel by following these steps,

Step 1: Write the data values in excel

Step 2: DATA > Data Analysis > Exponential Smoothing > OK.

Step 3: Select Input Range: Sales column, Damping factor = 0.9. the screenshot is shown below,

The forecast is obtained.

Observation	ES(0.1)
1,586	#N/A
1,622	1,586
1,417	1589.6
1,310	1572.34
1,228	1546.106
1,342	1514.295
1,580	1497.066
1,303	1505.359
1,283	1485.123
1,559	1464.911
1,386	1474.32
1,586	1465.488
1,665	1477.539
1,638	1496.285
1,361	1510.457

The mean square error is obtained using a similar formula,

Observation	ES(0.1)	SE
1,586	#N/A
1,622	1,586	1296
1,417	1589.6	29790.76
1,310	1572.34	68822.28
1,228	1546.106	101191.4
1,342	1514.295	29685.7
1,580	1497.066	6878.072
1,303	1505.359	40949.28
1,283	1485.123	40853.85
1,559	1464.911	8852.738
1,386	1474.32	7800.407
1,586	1465.488	14523.16
1,665	1477.539	35141.58
1,638	1496.285	20083.08
1,361	1510.457	22337.3
	MSE	30586.12

Now, the exponential smoothing forecast for smoothing factor 0.2 to 0.9 is obtained in excel by taking the damping factor value 0.8 to 0.1. And the MSE is calculated for each ES method. The result is shown below,

Smoothing factor	MSE
0.1	30586.12
0.2	19167.33
0.3	15115.63
0.4	12295.77
0.5	10246.09
0.6	8671.24
0.7	7423.66
0.8	6456.83
0.9	5788.26

A. The model based on a smoothing constant of 0.9 is the best. (lowest MSE correspond to smoothing constant = 0.9)

The moving average method with period and 3 shows a larger MSE compared to the exponential method when the higher smoothing constant is considered.

B. The single exponential smoothing model has the lowest MSE value.