Problem: A company has been experiencing growth in demand for its principal product over the past several years and has collected the following data (demand in millions of units):
1999 2000 2001 2002 2003 2004
Quarter 1 3.47 4.06 4.27 5.88 9.44 14.25
Quarter 2 3.12 6.90 5.24 8.99 7.75 14.89
Quarter 3 3.97 3.60 6.39 4.12 9.91 14.22
Quarter 4 4.50 6.47 5.45 6.68 9.14 15.56
Q1. Plot the demand over time (number the consecutive quarters 1 to 24). Fit a linear trend line to the data. What do you observe (in general terms)?
Q2. Project the value for the first quarter of 2005 using a naïve forecast and a 4 period moving average.
Q3. Build the formulas for fitting exponential smoothing to all of the historical data, using the naïve forecast as the beginning forecast for the 2nd quarter of 1999. Use Solver to find the value of alpha that minimizes the resulting Minimum Absolute Deviation (MAD) for the 12 quarters of 2000 -2002. What is the resulting MAD for the 8 quarters of 2003-2004?
Q4. Build the formulas for fitting double exponential smoothing (Holt) to all of the historical data, using the naïve forecast as the beginning forecast for the 2nd quarter of 1999 and an initial trend estimate of zero. Use Solver to find the values of alpha and beta that minimize the resulting Minimum Absolute Deviation (MAD) for the 12 quarters of 2000 -2002. What is the resulting MAD for the 8 quarters of 2003-2004?
Q5. Plot the Holt historical forecast series and projection for the first quarter of 2005 on the chart you created at step a). What do you observe (in general terms)?
Q6. You now have 4 forecasts for the first quarter of 2005 (not counting the trend line). Which did best for the 8 quarters of 2003-2004 (based on MAD)? Which one do you think would do best for first quarter of 2005? Why?