Question

A restaurant wants to forecast its weekly sales. Historical data (in dollars) for 15 weeks are shown below. Observation Time Period 1,620 1,533 1,419 1,234 1,278 1,371 1,563 1,375 1,386 1,594 1,387 1,533 1,648 1,654 1,399 MSE values based on the two- and three-period moving average are 25,543.12 and 24,466.09, 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.

Answer 1

Forecast and MSE for moving averages are calculated as below:

Time Period co E Two week moving Three week moving average average Squared Squared Observation Forecast Forecast Error Error 1620 1533 1419 1576.50 24806.25 1234 1476.00 58564.00 1524.00 84100.00 1278 1326.50 2352.25 1395.33 13767.11 1371 1256.00 13225.00 1310.33 3680.44 1563 1324.50 56882.25 1294.33 72181.78 1375 1467.00 8464.00 1404.00 841.00 1386 1469.00 6889.00 1436.33 2533.44 1594 1380.50 45582.25 1441.33 23307.11 1387 1490.00 10609.00 1451.67 4181.78 1533 1490.50 1806.25 1455.67 5980.44 1648 1460.00 35344.00 1504.67 20544.44 1654 1590.50 4032.25 1522.67 17248.44 1399 1651.00 63504.00 1611.67 45227.11 MSE 25543.12 MSE 24466.09

EXCEL FORMULA:

Two week moving average Three week moving average Time Period Observation Forecast Squared Error Forecast Squared Error 1620 1533 1419 8 6 1563 11 9 12 10 1386 1594 1387 1533 1648 1654 1399 =AVERAGE(B3:34) =AVERAGE(B4:B5) =AVERAGE(B5:36) =AVERAGE(B6:37) =AVERAGE(B7:38) =AVERAGE(B8:B9) =AVERAGE(B9:B10) =AVERAGE(B10:B11) =AVERAGE(B11:B12) =AVERAGE(B12:B13) =AVERAGE(B13:B14) =AVERAGE(B14:B15) =AVERAGE(B15:B16) = ($B5-C5)^2 =($B6-C6)^2 =($B7-C7)^2 =($B8-C8)^2 =($B9-C9)^2 =($B10-C10)^2 =($B11-C11)^2 =($B12-C12)^2 =($B13-C13)^2 =($B14-C14)^2 =($B15-C15)^2 =($B16-C16)^2 =($B17-C17)^2 =AVERAGE(B3:35) =($B6-E6)^2 =AVERAGE(B4:36) =($B7-E7)^2 =AVERAGE(B5:37) =($B8-E8)^2 =AVERAGE(B6:38) =($B9-E9)^2 =AVERAGE(B7:39) 1=($B10-E10)^2 =AVERAGE(B8:B10) =($B11-E11)^2 =AVERAGE(B9:B11) =($B12-E12)^2 =AVERAGE(B10:B12) =($B13-E13)^2 =AVERAGE(B11:B13) =($B14-E14)^2 =AVERAGE(B12:B14) =($B15-E15)^2 =AVERAGE(B13:B15) =($B16-E16)^2 =AVERAGE(B14:B16) =($B17-E17)^2 15 13 17 15 MSE =AVERAGE(D5:D17) MSE =AVERAGE(F6:F17)

--------------------------------------------------------------

Exponential smoothing forecast and MSE is calculated as below:

B 1 Exponential Smoothing 9 Alpha= 0.1 Time Squared Observation Forecast Period Error 1620 1620.00 2 1533 1620.00 7569.00 73 1419 1611.30 36979.29 8 4 1234 1592.07 128214.12 5 1278 1556.2677430.30 10 61 13711528.44 24786.31 11 7 1563 1512.69 2530.79 8 1375 1517.72 20370.06 9 1386 1503.45 13794.82 101 1594 1491.71 10464.02 11 1387 1501.94 13210.19 12 1533 1490.44 1811.18 13 1648 1494.70 23501.55 14 1654 1510.03 20727.92 15 1399 1524.43 15731.49 MSE 28365.79

EXCEL FORMULA:

в 1 Exponential Smoothing Alpha= 0.1 4 Time Period Observation Forecast Squared Error 5 1 000 m + 1620 1533 1419 1234 1278 1371 1563 1375 =B5 =B5*$C$3+C5*(1-$C$3) =B6*$C$3+C6*(1-$C$3) =B7*$C$3+C7*(1-$C$3) =B8*$C$3+C8*(1-$C$3) =B9*$C$3+C9*(1-$C$3) =B10*$C$3+C10*(1-$C$3) =B11*$C$3+C11*(1-$C$3) =B12*$C$3+C12*(1-$C$3) =B13*$C$3+C13*(1-$C$3) =B14*$C$3+C14*(1-$C$3) =B15*$C$3+C15*(1-$C$3) =B16*$C$3+C16*(1-$C$3) =B17*$C$3+C17*(1-$C$3) =B18*$C$3+C18*(1-$C$3) 11 7 12 8 13 9 14 10 15 11 16 12 17 13 1=($B6-C6)^2 =($B7-C7)^2 =($B8-C8)^2 1=($B9-C9)^2 =($B10-C10)^2 =($B11-C11)^2 =($B12-C12)^2 =($B13-C13)^2 =($B14-C14)^2 =($B15-C15)^2 =($B16-C16)^2 =($B17-C17)^2 =($B18-C18)^2 = ($B19-C19)^2 1386 1594 1387 1533 1648 1654 1399 MSE =AVERAGE(D6:D19)

-------------------------------------------------------------------

Change the alpha values in cell C3 and note down MSE for each value of alpha.

А C D E 1 Exponential Smoothing Time Period 3 4 Alpha= 0.2 Squared Observation Forecast Error 1620 1620.00 1533 1620.00 7569.00 1419 1602.60 33708.96 1234 1565.88 110144.33 1278 1499.50 49064.02 1371 1455.20 7090.18 1563 1438.36 15534.49 1375 1463.29 7795.13 1386 1445.63 3555.98 1594 1433.71 25694.28 1387 1465.76 6203.85 1533' 1450.01 6887.07 1648 1466.61 32902.59 1654 1502.89 22835.01 1399) 1533.11 17985.48 7 10 11 12 13 14 15 MSE 24783.60

C D E A 1 Exponential Smoothing Alpha= 0.3 Time Observation Forecast Squared Error Period 3 8 9 10 11 1620 1533 1419 1234 1278 1371 1563 1375 1386 1594 1387 1533 1648 1654 1399 1620.00 1620.00 1593.90 1541.43 1449.20 1397.84 1389.79 1441.75 1421.73 1411.01 1465.91 1442.23 1469.46 1523.02 1562.32 7569.00 30590.01 94513.20 29309.78 720.42 30002.23 4455.82 1276.37 33485.91 6226.14 8238.44 31875.14 17154.52 26672.54 12 14 15 MSE 23006.40

A 1 Exponential Smoothing Time Period Alpha= 0.4 Squared Observation Forecast Error 1620 1620.00 1533 1620.00 7569.00 1419 1585.20 27622.44 1234 1518.72 81065.48 1278 1404.83 16086.36 1371 1354.10 285.64 1563 1360.86 | 40860.77 1375 1441.72 4450.99 1386 1415.03 842.71 1594 1403.42 36321.63 1387 1479.65 8584.13 1533 1442.59 8173.90 1648 1478.75 28644.14 1654 1546.45 11566.46 1399 1589.4736279.40 MSE 22025.22

с 1 Exponential Smoothing 2 Time Period Alpha= 0.5 Squared Observation Forecast Error 1620 1620.00 1533 1620.00 7569.00 1419 1576.50 24806.25 1234 1497.75 69564.06 1278 1365.88 7722.02 1371 1321.94 2407.13 15631 1346.4746885.78 1375 1454.73 6357.57 1386 1414.87 833.31 1594 1400.43 37467.95 1387 1497.22 12147.74 1533 1442.11 8261.28 1648 1487.55 25742.85 1654 1567.78 7434.39 1399 1610.89 44896.76 MSE 21578.29

C D A 1 Exponential Smoothing Alpha= 0.6 Observation Forecast Time Period 1 Squared Error 1620 1620.00 1533 1620.00 1419 1567.80 1234 1478.52 1278 1331.81 1371 1299.52 1563 1342.41 1375 1474.76 1386 1414.91 1594 1397.56 1387 1515.42 1533' 1438.37 1648 1495.15 1654 1586.86 1399 1627.14 7569.00 22141.44 59790.03 2895.30 5108.93 48660.27 9952.80 835.53 38587.81 16492.95 8954.85 23363.74 4507.89 52049.54 11 MSE 21493.58

D A BC C 1 Exponential Smoothing Time Period Alpha= 0.7 Squared Observation Forecast Error 1620 1620.00 1533 1620.00 7569.00 1419 1559.10 | 19628.01 1234 1461.03 51542.62 1278 1302.11 581.24 1371 1285.23 7356.03 1563 1345.27 47406.44 1375 1497.68 15050.61 1386 1411.80 665.86 1594 1393.74 40103.55 1387 1533.92 21586.19 1533 1431.08 10388.36 1648 1502.42 21192.66 1654 1604.33 2467.42 1399 1639.10 57647.08 MSE 21656.08

C D E А 1 Exponential Smoothing Alpha= Time Observation Period 0.8 Squared Forecast Error 1620.00 1620.00 7569.00 1550.40 17265.96 1445.28 44639.24 1276.26 3.04 1277.658714.00 1352.33 44381.75 1520.87 21276.90 1404.17 330.27 1389.63 41765.20 1553.13 27598.16 1420.23 12718.11 1510.45 18921.36 1620.49 1122.99 1647.30 61651.80 1620 1533 1419 1234 1278 1371 1563 1375 1386 1594 1387 1533 1648 1654 1399 7 10 11 12 15 MSE 21996.98

A C D E 1 Exponential Smoothing Time Period 3 4 6 7 8 9 10 11 12 13 14 15 Alpha= 0.9 Squared Observation Forecast Error 1620 1620.00 1533 1620.00 7569.00 1419 1541.70 15055.29 1234 1431.27 38915.45 1278 1253.73 589.18 1371 1275.57 9106.37 1563 1361.46 40619.47 1375 1542.85 28172.19 1386 1391.78 33.46 1594 1386.5843023.70 1387 1573.26 34691.99 1533 1405.63 16224.19 1648 1520.26 16316.85 1 654 1635.23 352.45 1399 1652.12 64071.06 MSE 22481.48

------------------------------

Alpha	MSE
0.1	28365.79
0.2	24783.60
0.3	23006.40
0.4	22025.22
0.5	21578.29
0.6	21493.58
0.7	21656.08
0.8	21996.98
0.9	22481.48

--------------------------------------

The lowest value of MSE is 21,493.58 pertaining to alpha of 0.6

The model based on a smoothing constant of 0.6 is the best.

The Exponential smoothing model has the lowest MSE value.