Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 13

Using Moving Averages

IN THIS CHAPTER

Deciding on a length

Figuring out how many baseline values to use

Charting your moving-average trendline

When you decide to look at a baseline’s moving-average values — whether to get a better idea of the baseline’s behavior or to make a forecast — the number of values you choose to put into each average has consequences, some of them undesirable. For example, the more values in an average, the smaller the number of averages.

And your choice has an effect on how smooth or rough the moving averages are. Generally, the fewer the data points you include in a moving average, the more it will jump around — but the faster it will react to changes in the baseline. The greater the number of data points, the smoother it becomes — but the slower it will react. This effect can represent a difficult trade-off because we often assume that the smoother a set of moving averages, the better it represents the signal in a baseline.

Excel’s Data Analysis add-in has a Moving Averages tool that you can use to put the averages into a chart, along with the original baseline. In this chapter, I show you some ways to improve the tool’s effectiveness.

Choosing the Length of the Moving Average

If you’ve worked with moving averages before, you may think them too basic to discuss in a book about a topic as grand as forecasting. The humble moving average?

No. Although the basic idea of moving averages is simple and intuitive, they play a starring role in ...