Explore BrainMass

Demand forecasting for management policy decision.

Generico has been manufacturing videotapes since 1982. The 3-hour VHS format is by far the largest component of their product mix. Historical demand for these items since 1992 is listed below <data attached>.

Don Wirtz, general managing partner of Generico is concerned about the state of the current market for VHS format tapes. If demand is seen to be falling, Wirtz may need to consider diversification, if demand appears to be strong, Wirtz may need to consider ways to enhance the competitiveness of his manufacturing enterprise.

QUESTION: Please provide Don with monthly forecasts in Excel for the years 2004 and 2005, while considering all relevant phenomena present in the historical demand pattern....

We have just started the semester, so the techniques we have discussed are limited to:

1) Time series analysis: SimpleMovingAverage, WMA, Exponential Smoothing
2) Differencing
3) Forecast errors: absolute deviation, mean absolute deviation (MAD), and tracking signal
4) Single linear regression
5) Seasonalized time series regression

I am trying to set this up in Excel, but not really sure how to do it...


Solution Preview

In order to estimate a forecast for years 2004 and 2005, and given that we don't have any data related to factors that might affect demand (income of customers, marketing expenditure, etc), the best we can do is find the trend of the demand and extrapolate it to years 2004 and 2005. If there appears to be some seasonality in the sales data (such as "Christmas effect") we should adjust this trend for seasonality.

In the attached Excel workbook, you will find several worksheets, please check them along with this answer.

The first worksheet (Data) is simply a copy of your data in one column, so that it's easier to handle in Excel.

The second worksheet is a plot of your data. If you read "Ene-92", "Ene-93" and so on, please interpret it as "January-92", "January-93", etc. I'm using a Spanish version of Excel. Two thing become clear in this graph: 1) the sales are declining through the years, so a good forecast should be that sales will be lower in 2004 than in 2003, and lower in 2005 than in 2004; and 2) "inside" each year, sales are low at the beggining of the year and become higher towards the end of it. ...

Solution Summary

The following question involves making a demand forecast in excel. The process includes the following:
- Deseasonalize the data
- Find the trend of the deseasonalized data
- Extrapolate this trend to years 2004 and 2005
- "Re-seasonalize" the data in these years to get the actual forecast.