Forecasting: Weighted Moving Averages

In my previous entry, I discussed the importance of a moving average and how it can be found. The moving average is an excellent tool at smoothing out all of the spikes and dips that actually occur in sales and demand. This can hopefully help us achieve a more accurate and more consistent forecast. However, once you begin to think about it, isn't more recent data also more relevant data. The recent data may (but not always) be more accurate when looking to forecast future trends. Therefore, it sometimes makes sense to add a weight to various data when calculating your moving averages.

In my last blog post I introduced the Weighted Moving Average and left you with this equation to think about:
Now lets consider how to use this equation in practice. We will be using the data I collected last time about unit sales of Batman comic books in 2006 and adding weights ranging from 1 to 3 for a 3 month average, or 1 to 4 for a 4 month average. I've given the more recent comics a higher weight. Remember that the denominator of our equation is going to be the sum of the weights for only the months we are looking at. Now we apply the equation as a formula in Excel to the first cell for the 3 month weighted moving average.

Then we can continue applying it for the entire run of 3 month data. I also filled in the WMA for the 4 month data while I was at it.

The next thing we will do is apply that data to a line graph. This will allow us to see how our data compares against the moving averages.

So, knowing all of this, we can take the data we have been given and forecast what the sales will be in January of 2007. Under the 3 month moving average, I predict that DC Comics will sell 84425 units of the Batman comic book. If we instead chose to use the 4 month moving average, we would come up with a value of 85988.

This simple method of Weighted Moving Averages may be better than a standard moving average, but it could still be adjusted to make a more accurate and useful time series model. The next part of my forecasting series will deal with exponential smoothing inside a moving average.

Thanks for visiting. ---------Sincerely, Trevor Stasik.

---

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

---

Batman,Weighted Moving Average, Forecasting, Time Series, Excel