Question

The manager of a travel agency asked you to come up with a forecasting technique that will best fit to the actual demand for packaged tours. You have observed and recorded the actual demand for the last 10 periods. You also identified two possible techniques for consideration: 2-month moving averages (F1), and exponential smoothing (F2) with a smoothing constant of 0.40. Using Cumulative Forecasting Error (CFE) and Mean Absolute Deviation (MAD) as your performance measures you will determine the technique that will best fit to the actual demand data provided in the following table.

STEP 1: Given start forecast values in period 3, compute forecast values from period 4 to 10. You are asked to provide the forecast values for period 6 and 10 for both techniques.

		2-Month MA	Exponential
Period	Demand	F1	F2
1	128	--	--
2	172	--	--
3	89	150	129
4	143
5	72
6	140	Blank 1	Blank 2
7	129
8	140
9	98
10	174	Blank 3	Blank 4

STEP 2: Using data from period 3 to period 10,

Provide the performance measures for F1 technique:

            CFE = Blank 5                        MAD = Blank 6

Provide the performance measures for F2 technique:

            CFE = Blank 7            MAD = Blank 8

Based on these measures, which technique best fit to your data? (Enter F1 or F2) = Blank 9
NOTE: All computed forecast values should be rounded to the nearest integer (no decimal, for example 190). Performance measures (CFE and MAD) must be rounded to the nearest hundredth (two decimals after the dot, for example 30.99).

Answer 1

2-month MA forecast for period 4=AVERAGE(C4:C5)=131

(Drag this cell formula for rest of the months till period 10)

Exponential forecast for period 4=E5+0.4*(C5-E5)=113

(Drag this cell formula for rest of the months till period 10)

Forecast error for F1 for period 4 =C5-D5=-61

(Drag this cell formula for rest of the months till period 10)

Absolute deviation for F1 for period 4 =ABS(F5)=61

(Drag this cell formula for rest of the months till period 10)

Similarly for forecasting technique F2.

CFE for F1=SUM(F5:F12)=-13

MAD for F1=AVERAGE(G5:G12)=33.75

CFE for F2=SUM(H5:H12)=27

MAD for F2=AVERAGE(I5:I12)=34.50

Based on these measures,F1 technique best fits the data.

Answer 2

To compute the forecast values and performance measures, we'll follow the given steps:

STEP 1: Compute forecast values for period 4 to 10 using the 2-month moving averages (F1) and exponential smoothing (F2) techniques.

For the 2-Month MA (F1) technique:

• Period 4 forecast: (Demand in Period 3 + Demand in Period 2) / 2 = (89 + 172) / 2 = 130.5 (rounded to 131)

• Period 5 forecast: (Demand in Period 4 + Demand in Period 3) / 2 = (143 + 89) / 2 = 116 (rounded to 116)

• Period 6 forecast: (Demand in Period 5 + Demand in Period 4) / 2 = (72 + 143) / 2 = 107.5 (rounded to 108)

• Period 7 forecast: (Demand in Period 6 + Demand in Period 5) / 2 = (140 + 72) / 2 = 106 (rounded to 106)

• Period 8 forecast: (Demand in Period 7 + Demand in Period 6) / 2 = (129 + 140) / 2 = 134.5 (rounded to 135)

• Period 9 forecast: (Demand in Period 8 + Demand in Period 7) / 2 = (140 + 129) / 2 = 134.5 (rounded to 135)

• Period 10 forecast: (Demand in Period 9 + Demand in Period 8) / 2 = (98 + 140) / 2 = 119 (rounded to 119)

For the Exponential Smoothing (F2) technique with a smoothing constant of 0.40:

• Period 4 forecast: F2 = (0.40 * Demand in Period 3) + (0.60 * F2 in Period 3) = (0.40 * 89) + (0.60 * 129) = 99.6 (rounded to 100)

• Period 5 forecast: F2 = (0.40 * Demand in Period 4) + (0.60 * F2 in Period 4) = (0.40 * 143) + (0.60 * 100) = 115.8 (rounded to 116)

• Period 6 forecast: F2 = (0.40 * Demand in Period 5) + (0.60 * F2 in Period 5) = (0.40 * 72) + (0.60 * 116) = 93.6 (rounded to 94)

• Period 7 forecast: F2 = (0.40 * Demand in Period 6) + (0.60 * F2 in Period 6) = (0.40 * 140) + (0.60 * 94) = 108.4 (rounded to 108)

• Period 8 forecast: F2 = (0.40 * Demand in Period 7) + (0.60 * F2 in Period 7) = (0.40 * 129) + (0.60 * 108) = 115.2 (rounded to 115)

• Period 9 forecast: F2 = (0.40 * Demand in Period 8) + (0.60 * F2 in Period 8) = (0.40 * 140) + (0.60 * 115) = 125.0 (rounded to 125)

• Period 10 forecast: F2 = (0.40 * Demand in