Share
Explore BrainMass

Forecasting - Management Science

Hi this is a forecasting problem that can be solved using Excel with the solver-add in on it. Please answer all parts to the question . If you need any information or have a question Please let me know thanks.

3. 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

a) 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)?
b) Project the value for the first quarter of 2005 using a naà¯ve forecast and a 4 period moving average.
c) 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?
d) 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?
e) 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)?
f) 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?

Please see attached.

Attachments

Solution Preview

a) A linear trendline doesn't seem to be appropiate to approximate this data set. Demand experienced a very slow growth the first years and accelerated during 2003/2004; hence the fact that almost all of the data points before 2003 are below the trendline, while after 2003 they are above it.

b) The naive forecast consists simply of repeating the value of the previous period. Therefore, a naive demand forecast for Q1 of 2005 is 15.56, which is equal to the value in Q4 of 2004.

The moving average forecast is the last value in the moving average series; so the forecast for Q1 of 2005 would be 14.73

c) The formula for exponential smoothing is F(t) = alpha*d(t-1) + (1-alpha)*F(t-1), where F(t) is the forecasted value at period t, and d(t-1) ...

Solution Summary

Quantitative Decison Making for business is examined.

$2.19