Question

The manager of a travel agency has been using a seasonally adjusted forecast to predict demand for packaged tours. the actual and predicted values are as follows:

Period	Demand	Predicted
1	124	113
2	184	200
3	144	150
4	79	102
5	74	80
6	119	135
7	114	128
8	141	124
9	106	109
10	161	150
11	116	94
12	101	80
13	136	140
14	146	128

a) Compute MAD for the fifth period, then update it period by period using exponential smoothing with a=.10 (round to 3 decimal places.)

t (period)	A (demand)	MADt
1	124	-
2	184	-
3	144	-
4	79	-
5	74	12.4
6	119
7	114
8	141
9	106
10	161
11	116
12	101
13	136
14	146	13.43

b) compute a tracking signal for periods 5 through 14 using the initial and updated MADs (round to 3 decimal places.

t (period)	A (demand)	Tracking signal
1	124	-
2	184	-
3	144	-
4	79	-
5	74
6	119
7	114
8	141
9	106
10	161
11	116
12	101
13	136
14	146

Answer 1

The forecast using Exponential smoothing, MADs and Tracking signals are computed in the following spreadsheet:

FORMULAS:

Period (t)	Demand (At)	Predicted F1	Exponential Smoothing F2	ADt based on F1	ADt based on F2	MADt	FEt based on F1	FEt based on F2	RSFEt based on F1	RSFEt based on F2	Tracking Signal
1	124	113	=B2	=ABS(B2-C2)	=ABS(B2-D2)		=B2-C2	=B2-D2
2	184	200	=D2+(B2-D2)*0.1	=ABS(B3-C3)	=ABS(B3-D3)		=B3-C3	=B3-D3	=SUM(H$2:H3)	=SUM(I$2:I3)
3	144	150	=D3+(B3-D3)*0.1	=ABS(B4-C4)	=ABS(B4-D4)		=B4-C4	=B4-D4	=SUM(H$2:H4)	=SUM(I$2:I4)
4	79	102	=D4+(B4-D4)*0.1	=ABS(B5-C5)	=ABS(B5-D5)		=B5-C5	=B5-D5	=SUM(H$2:H5)	=SUM(I$2:I5)
5	74	80	=D5+(B5-D5)*0.1	=ABS(B6-C6)	=ABS(B6-D6)	=AVERAGE(E$2:E6)	=B6-C6	=B6-D6	=SUM(H$2:H6)	=SUM(I$2:I6)	=G6/J6
6	119	135	=D6+(B6-D6)*0.1	=ABS(B7-C7)	=ABS(B7-D7)	=AVERAGE(E$2:E7)	=B7-C7	=B7-D7	=SUM(H$2:H7)	=SUM(I$2:I7)	=G7/K7
7	114	128	=D7+(B7-D7)*0.1	=ABS(B8-C8)	=ABS(B8-D8)	=AVERAGE(E$2:E8)	=B8-C8	=B8-D8	=SUM(H$2:H8)	=SUM(I$2:I8)	=G8/K8
8	141	124	=D8+(B8-D8)*0.1	=ABS(B9-C9)	=ABS(B9-D9)	=AVERAGE(E$2:E9)	=B9-C9	=B9-D9	=SUM(H$2:H9)	=SUM(I$2:I9)	=G9/K9
9	106	109	=D9+(B9-D9)*0.1	=ABS(B10-C10)	=ABS(B10-D10)	=AVERAGE(E$2:E10)	=B10-C10	=B10-D10	=SUM(H$2:H10)	=SUM(I$2:I10)	=G10/K10
10	161	150	=D10+(B10-D10)*0.1	=ABS(B11-C11)	=ABS(B11-D11)	=AVERAGE(E$2:E11)	=B11-C11	=B11-D11	=SUM(H$2:H11)	=SUM(I$2:I11)	=G11/K11
11	116	94	=D11+(B11-D11)*0.1	=ABS(B12-C12)	=ABS(B12-D12)	=AVERAGE(E$2:E12)	=B12-C12	=B12-D12	=SUM(H$2:H12)	=SUM(I$2:I12)	=G12/K12
12	101	80	=D12+(B12-D12)*0.1	=ABS(B13-C13)	=ABS(B13-D13)	=AVERAGE(E$2:E13)	=B13-C13	=B13-D13	=SUM(H$2:H13)	=SUM(I$2:I13)	=G13/K13
13	136	140	=D13+(B13-D13)*0.1	=ABS(B14-C14)	=ABS(B14-D14)	=AVERAGE(E$2:E14)	=B14-C14	=B14-D14	=SUM(H$2:H14)	=SUM(I$2:I14)	=G14/K14
14	146	128	=D14+(B14-D14)*0.1	=ABS(B15-C15)	=ABS(B15-D15)	=AVERAGE(E$2:E15)	=B15-C15	=B15-D15	=SUM(H$2:H15)	=SUM(I$2:I15)	=G15/K15

Answer 2

To compute the Mean Absolute Deviation (MAD) and update it using exponential smoothing with a = 0.10, follow these steps:

a) Compute MAD for the fifth period:

MAD = |Actual Demand - Predicted Demand| MAD5 = |74 - 80| = 6

b) Update MAD period by period using exponential smoothing with a = 0.10:

MADt = (1 - a) * |Actual Demand - Predicted Demand| + a * MADt-1

Using the formula, let's update MAD for the sixth period and then calculate the tracking signal for periods 5 to 14:

For t = 6: MAD6 = (1 - 0.10) * |119 - 135| + 0.10 * 6 MAD6 = 0.90 * 16 + 0.60 MAD6 = 14.4 + 0.6 MAD6 = 15

For t = 7: MAD7 = (1 - 0.10) * |114 - 128| + 0.10 * 15 MAD7 = 0.90 * 14 + 1.5 MAD7 = 12.6 + 1.5 MAD7 = 14.1

For t = 8: MAD8 = (1 - 0.10) * |141 - 124| + 0.10 * 14.1 MAD8 = 0.90 * 17 + 1.41 MAD8 = 15.3 + 1.41 MAD8 = 16.71

For t = 9: MAD9 = (1 - 0.10) * |106 - 109| + 0.10 * 16.71 MAD9 = 0.90 * 3 + 1.671 MAD9 = 2.7 + 1.671 MAD9 = 4.371

For t = 10: MAD10 = (1 - 0.10) * |161 - 150| + 0.10 * 4.371 MAD10 = 0.90 * 11 + 0.4371 MAD10 = 9.9 + 0.4371 MAD10 = 10.3371

For t = 11: MAD11 = (1 - 0.10) * |116 - 94| + 0.10 * 10.3371 MAD11 = 0.90 * 22 + 1.03371 MAD11 = 19.8 + 1.03371 MAD11 = 20.83371

For t = 12: MAD12 = (1 - 0.10) * |101 - 80| + 0.10 * 20.83371 MAD12 = 0.90 * 21 + 2.083371 MAD12 = 18.9 + 2.083371 MAD12 = 20.983371

For t = 13: MAD13 = (1 - 0.10) * |136 - 140| + 0.10 * 20.983371 MAD13 = 0.90 * 4 + 2.0983371 MAD13 = 3.6 + 2.0983371 MAD13 = 5.6983371

For t = 14: MAD14 = (1 - 0.10) * |146 - 128| + 0.10 * 5.6983371 MAD14 = 0.90 * 18 + 0.56983371 MAD14 = 16.2 + 0.56983371 MAD14 = 16.76983371

Now, let's calculate the Tracking Signal for periods 5 to 14:

Tracking Signal = (Actual Demand - Predicted Demand) / MADt

For t = 5: Tracking Signal5 = (74 - 80) / 6 Tracking Signal5 = -1

For t = 6: Tracking Signal6 = (119 - 135) / 15 Tracking Signal6 = -1.066666667

For t = 7: Tracking Signal7 = (114 - 128) / 14.1 Tracking Signal7 = -0.992907801

For t = 8: Tracking Signal8 = (141 - 124) / 16.71 Tracking Signal8 = 1.02189781

For t = 9: Tracking Signal9 = (106 - 109) / 4.371 Tracking Signal9 = -0.685106378

For t = 10: Tracking Signal10 = (161 - 150) / 10.3371 Tracking Signal10 = 1.063125558

For t = 11: Tracking Signal11 = (116 - 94) / 20.83371 Tracking Signal11 = 1.059156437

For t = 12: Tracking Signal12 = (101 - 80) / 20.983371 Tracking Signal12 = 1.001036292

For t = 13: Tracking Signal13 = (136 - 140) / 5.6983371 Tracking Signal13 = -0.703401095

For t = 14: Tracking Signal14 = (146 - 128) / 16.76983371 Tracking Signal14 = 1.075816688

Remember that the MAD values have been updated using exponential smoothing for each period, and the tracking signal is a measure of forecast accuracy. Values close to 0 indicate good accuracy, while larger values suggest forecasting errors.