Question

You want to compare how two forecasting methods would perform on some historical sales data. You will forecast the sales for months 4 through 19, calculate the mean absolute deviation (MAD) for both methods, and you can claim that the one that has lower MAD performed better, at least for the historical data.

a) The first method is known as the moving average method. The forecast for a month will be the average sales of three previous months. So, forecast for month 4 will be average sales of months 1 through 3. Forecast for month 5 will be average sales of months 2 through 4 and so on. So the forecast for month 19 will be the average sales of 16, 17 and 18. Use an appropriate Excel formula to calculate the moving average forecasts in column C.

b) In column D calculate the absolute deviation and mean absolute deviation for the moving average forecasts.

c) The second method is going to be linear regression. Write the slope and intercept values of the regression in cells F1 and F2.

d) Calculate regression forecasts for months 4 through 19 in column E.

e) In column F calculate the absolute deviation and mean absolute deviation for the linear regression forecasts.

f) What conclusion can you draw about the of the two forecasting methods? Please write your answer in the blue cell (i.e., D28).

Slope Intercept Month Sales Moving Average Absolute Deviation Regression Absolute Deviation 120 153 124 125 124 MAD: Partf)

Answer 1

a) to e) are generated in the excel and screenshots of the data as well as formula are attached here.

f) Regression is a better method to forecast as MAD of regression is less than the MAD of Moving Average.

Slope Intercept 2.3877 88.6491 Month Sales Moving Average Absolute Deviation Regression Absolute Deviation 106 121 911 1371 90.000 102.67 101.33 108.67 110.67 107.33 96.00 86.00 101.00 116.00 134.33 128.00 126.67 129.00 131.00 134.00 MAD: 1201 25.00 0.67 7.67 12.33 18.67 32.33 5.00 51.00 19.00 30.00 16.33 12.00 26.33 5.00 6.00 10.00 17.33 98.20 100.59 102.98 105.36 107.75 110.14 112.53 114.91 117.30 119.69 122.08 124.46 126.851 129.24 131.63 134.02 16.80 1.41 6.02 15.64 15.75 35.14 21.53 22.09 2.70 26.31 4.08 8.46 26.15 5.24 6.63 10.02 14.00 146 124

Formula:

AB Slope Intercept 2.3877 88.6491 Month Sales Moving Average Absolute Deviation Regression Absolute Deviation 7/2 8 3 94 10 5 116 127 13 8 14 9 15 10 77 106 87 115 102 109 121 92 75 91 137 120 146 118 116 153 124 125 124 16 11 =AVERAGE(B6:38) =AVERAGE(B7:39) =AVERAGE(B8:B10) =AVERAGE(B9:B11) FAVERAGE(B10:B12) =AVERAGE(B11:B13) =AVERAGE(B12:B14) =AVERAGE(B13:B15) AVERAGE(B14:B16) =AVERAGE(B15:B17) =AVERAGE(B16:B18) =AVERAGE(B17:319) =AVERAGE(B18:B20) =AVERAGE(B19:121) =AVERAGE(B20:322) =AVERAGE(B21:B23) MAD: =ABS(B9-09) =ABS(B10-C10) =ABS(B11-C11) =ABS(B12-C12) =ABS(B13-C13) =ABS(B14-C14) =ABS(B15-C15) =ABS(B16-C16) =ABS(B17-C17) =ABS(B18-C18) =ABS(B19-C19) =ABS(B20-C20) =ABS(B21-C21) =ABS(B22-C22) =ABS(B23-C23) =ABS(B24-024) =AVERAGE(D9:D24) =$F$2+$F$1*A9 =$F$2+$F$1*A10 =$F$2+$F$1*A11 =$F$2+$F$1*A12 =$F$2+$F$1*A13 =$F$2+$F$1*A14 =$F$2+$F$1*A15 =$F$2+$F$1*A16 =$F$2+$F$1*A17 =$F$2+$F$1*A18 =$F$2+$F$1*A19 =$F$2+$F$1*A20 =$F$2+$F$1*A21 =$F$2+$F$1*A22 =$F$2+$F$1*A23 =$F$2+$F$1*A24 =ABS(B9-E9) =ABS(B10-E10) =ABS(B11-E11) =ABS(B12-E12) =ABS(B13-E13) =ABS(B14-E14) =ABS(B15-E15) =ABS(B16-E16) =ABS(B17-E17) =ABS(B18-E18) =ABS(B19-E19) =ABS(B20-E20) =ABS(B21-E21) =ABS(B22-E22) =ABS(B23-E23) =ABS(B24-E24) =AVERAGE(F9:F24) 21 16 24 19