Question

1) A company has recorded the demand for a new type of tire for the last five months. Month 1 2 3 4 5 Demand ('00s) 14 17 19 23 24 a) Use a two month moving average to generate a forecast for demand in month 6. b) Apply exponential smoothing with a smoothing constant of 0.9 to generate a forecast for demand for demand in month 6. c) Which of these two forecasts do you prefer and why? 2) Syg Company has collected the following sales data. Month 1 2 3 4 5 6 Demand 15 12 13 17 15 19 a) What would the forecast for the 6th month if made at the end of 5th month, using exponential smoothing with a = .2 and a forecast for 1st month of 11 units? b) What's the three-period moving average forecast for month 5 made at the end of month 4? c) If the base at the end of month 4 were 15 and the trend at the end of month 4 were 4 (a=0.2, ß= 0.4), what would be the forecast for month 6 made at the end of month 5? 3) A magazine ranks Football teams by value each year. The following data are the value of the Indianapolis Colts from 1998 to 2008 (Forbes website). Year Period Value ($millions) 1998 1 227 1999 2 305 2000 3 332 2001 4 367 2002 5 419 a) Plot the given data. b) Forecast for period 6 using naïve method and 3-month moving average. c) Forecast for period 6 using trend projection. d) Assume the base value at the end of period 4 was 390 million and the trend was -27. What would be the forecast for the period 7 made at the end of period 5. (a=0.2, B=0.4) e) Use the model you recommended to forecast the value of the Colts in 2005.

Answer 1

1. a) forecast for demand in month 6 = 23.5 using two month moving average
b)
Using exponential smoothing forecast in month 6 = 23.8577

c)

We can see Exponential smoothing is best as Forecast Error measurements such as MAD,MSE,MAPE all have lower values than for 2 month moving average

Calculation

H 2 Month A B C D E F 2 month moving average Demand Error Abs(Error) Error^2 (Abs(Error)/Demand)*100 1 14 2 17 19 15.5 3.5 3.5 12.25 18.42105263 55 25 21.73913043 5 24 21 339 12.5 23.5 10 MAD 3.833333 15.41667 17.55339 12 MAPE 13 14 15 Month (Abs(Error)/Demand)*100 16 Demand 1 14 2 17 19 23 24 Exponential smoothing Error Abs(Error) Error^2 14 14 3 3 9 16.7 2.3 2.3 5.29 18.77 4.23 4.23 17.8929 22.577 1.423 1.423 2.024929 23.8577 17.64705882 12.10526316 18.39130435 5.929166667 23 MAD 24 MSE 25 MAPE 26 2.73825 8.551957 13.5182

Formula

F 2 month moving average Abs(Error) Demand Error Error 2 (Abs(Error)/Demand)*100 2 Month 3 1 42 5 3 =AVERAGE(B3:34) =AVERAGE(B4:B5) =AVERAGE(B5:36) AVERAGE(B6:37) =B5-C5 =B6-C6 =B7-C7 =ABS(D5) =ABS(D6) =ABS(D7) =E5^2 =E6^2 =E7^2 =(E5/B5)*100 =(E6/B6)*100 =(E7/B7)*100 9 10 MAD 11 MSE 12 MAPE 13 =AVERAGE(E5:E7) AVERAGE(F5:F7) =AVERAGE(G5:47) 14 Exponential smoothing Abs(Error) Demand Error Error^2 (Abs(Error)/Demand)*100 14 15 Month 16 1 17 2 18 3 19 4 20 5 21 6 =0.9*B16+0.1*C16 =0.9*B17+0.1*017 =0.9*B18+0.1*C18 =0.9*B19+0.1*c19 =0.9*B20+0.1*020 =B17-C17 =B18-C18 =B19-C19 =B20-C20 =ABS(017) =ABS(D18) =ABS(D19) =ABS(D20) =E1712 =E18^2 =E19^2 =E20-2 =(E17/B17)*100 =(E18/B18)*100 =(E19/B19) *100 =(E20/B20)*100 23 MAD 24 MSE 25 MAPE =AVERAGE(E17:E20) =AVERAGE(F17:F20) =AVERAGE(G17:520)