Purchase Solution

Demand forecasting for management policy decision.

Not what you're looking for?

Ask Custom Question

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

Purchase this Solution

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.

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

Purchase this Solution

Free BrainMass Quizzes
Measures of Central Tendency

Tests knowledge of the three main measures of central tendency, including some simple calculation questions.

Measures of Central Tendency

This quiz evaluates the students understanding of the measures of central tendency seen in statistics. This quiz is specifically designed to incorporate the measures of central tendency as they relate to psychological research.

Terms and Definitions for Statistics

This quiz covers basic terms and definitions of statistics.

Know Your Statistical Concepts

Each question is a choice-summary multiple choice question that presents you with a statistical concept and then 4 numbered statements. You must decide which (if any) of the numbered statements is/are true as they relate to the statistical concept.