Question

Two different forecasting techniques (F1 and F2) were used to forecast demand for cases of bottled water. Actual demand and the two sets of forecasts are as follows: PREDICTED DEMAND Period Demand 68 75 70 74 69 72 80 78 F1 67 F2 60 62 70 72 75 75 76 85 73 74 70 71 75 a. Compute MAD for each set of forecasts. Given your results, which forecast appears to be more accurate? (Round your answers to 2 decimal place.) MAD F1 MAD F2 5.45 F1 appears to be more accurate b. Compute the MSE for each set of forecasts. Given your results, which forecast appears to be more accurate? (Round your answers to 2 decimal places.) MSE FI MSE F2 43 c. In practice, either MAD or MSE would be employed to compute forecast errors. What factors might lead a manager to choose one rather than the other? Either one might already be in use, familiar to users, and have past values for comparison. If be natural; if tracking signals are used, MAD would be more natural control charts are used, MSE would d. Compute MAPE for each data set. Which forecast appears to be more accurate? (Round your intermediate calculations to 2 decimal places and and final answers to 2 decimal places.) MAPE F1 MAPE F2 F1appears to be more accurate

Answer 1

As you have correctly calculated MAD, I am making calculations for only MSE and MAPE..

MSE - Mean Square Error

Period (A)	Demand (B)	F1 (C)	Squared Deviation	Formula for Squared Deviation
1	68	67	1	=(C2-B2)^2
2	75	66	81	=(C3-B3)^2
3	70	73	9	=(C4-B4)^2
4	74	66	64	=(C5-B5)^2
5	69	74	25	=(C6-B6)^2
6	72	70	4	=(C7-B7)^2
7	80	71	81	=(C8-B8)^2
8	78	75	9	=(C9-B9)^2
		MSE	5.85234995535981	=SQRT(AVERAGE(E2:E9))

Period (A)	Demand (B)	F2 (C)	Squared Deviation	Formula for Squared Deviation
1	68	60	64	=(C2-B2)^2
2	75	62	169	=(C3-B3)^2
3	70	70	0	=(C4-B4)^2
4	74	72	4	=(C5-B5)^2
5	69	75	36	=(C6-B6)^2
6	72	75	9	=(C7-B7)^2
7	80	76	16	=(C8-B8)^2
8	78	85	49	=(C9-B9)^2
		MSE	6.58596993615975	=SQRT(AVERAGE(E2:E9))

MSE for F1 = 5.85

MSE for F2 = 6.59

_____________________________________________________________________________

MAPE

Formula for Squared 1 Period Demand F1 68 75 70 74 69 72 80 78 Squared Deviation Deviation 0.0147 0.12 0.0429 0.1081 0.0725 0.0278 0.1125 0.0385 0.0671 67 -ABS((C2-B2)/B2) -ABS( (C3-B3)/B3) -ABS((C4-B4)/B4) -ABS((C5-B5)/B5) -ABS((C6-B6)/B6) -ABS((C7-B7)/B7) -ABS((C8-B8)/B8) -ABS((C9-B9)/B9) -AVERAGE(E2:E9) 4 3 73 74 70 71 75 MAPE 9 8 10

Formula for Squared 1 Period Demand F2 68 75 70 74 69 72 80 78 60 62 70 72 75 75 76 85 MAPE Squared Deviation Deviation 0.1176 0.1733 0 0.027 0.087 0.0417 0.05 0.0897 0.0733 -ABS( (C2-B2)/B2) -ABS( (C3-B3)/B3) -ABS((C4-B4)/B4) -ABS((C5-B5)/B5) -ABS((C6-B6)/B6) -ABS((C7-B7)/B7) -ABS((C8-B8)/B8) -ABS((C9-B9)/B9) -AVERAGE(E2:E9) 4 3 10

MAPE for F1 = 0.0671 = 6.71%

MAPE for F2 = 0.0733 = 7.33%

_____________________________________________________________________

As you are already calculating MAD, MSE and MAPE, I have not explained the concepts of these calculations. From the workings done above, you can easily calculate MSE and MAPE using the formula and table as has been used by me.