Tuesday, October 2, 2007

Quantitative Methods Of Forecasting: Moving Averages

Once demand and sales are understood to be dynamic and not static, different methods of forecasting are needed. One such method is the moving average.

The moving average is used as a way to smooth forecasts into a more likely model. Consider the PDA example from the previous post. In that post we discussed how sales of PDAs from last year have dropped by 40%. (Read article HERE). Well, with the use of a moving average, the forecasted sales for the coming time period will not be lowered by the same amount as the previous periods loss in sales. Perhaps the actual equation for moving averages would be helpful here:

Using this equation, you can find the moving average. This can be spaced over various time periods. You can look at a moving average over days, months, quarters, or years. Consider that many technical analysts in the stock market use a 200 Day Moving Average in helping them consider whether to make investments. In sales, often the number of units sold monthly can be a demand indicator.

As an example, lets look at sales data for Batman comic books over 2006. I found the unit sales data for each month and plugged it into Excel so it could do most of the hard work for me. I've included the source of my data next to each month:

I want to look at the 3-month moving average. We should now apply the formula to the first month.

This can be continued by pulling down on the corner of the cell. I continued this with the 4 month moving average:

Finally, lets put our results into a visual form, the line graph:

As you can see, the sales jumped in July. However, due to smoothing created by the use of a moving average, we can find a trend that will help us predict future sales (assuming that the market remains fairly steady). Once we have a long enough pattern, we may be able to apply a weight to the results to provide an even more accurate trend for forecasting. The formula for figuring out the weights is as follows:

I do not have enough time to do an excel example of the weighted moving average today. I think I'm going to stretch this into another blog entry dedicated to the weighted moving average. I hope my information has been helpful. Thank you for visiting and please drop me a comment.
------------Sincerely, Trevor Stasik.

To return to initial post about forecasting, click HERE.
To visit the next part of my forecasting series, click HERE.

View Trevor Stasik's profile on LinkedIn

Post a Comment