ab-logo

Code. Models. Analysis. Decisions.


Excel Moving Average Forecasting

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

This is Part I in a series on forecasting. Part II can be found here, and Part III can be found here.

Excel Forecasting

This is a video demonstration of Excel moving average forecasting. Moving averages use a set number of look-back periods to compute an average that is used to forecast the next period. Moving average is probably most noteworthy for its use in technical analysis as a signal for when to buy or sell a security. Moving Average forecasts are most accurate when future flucation is about the same in the future as it has been historically. Moving averages are used on stationary time series, or those only exhibiting random noise, as oppesed to time series that exhibit a trend or repeating patterns.

Moving average forecasts can use any number of look-back periods, however the greater the look back period, the more reliant the forecast is on older data. Additionally, a larger look-back period will respond to changes more slowly than forecasts made with a smaller, or shorter look-back periods.

This video demonstrates "dynamic" Excel forecasting. Excel also provides a static forecasting tool in the data analysis toolpak that is not discussed in this tutorial.

This tutorial demonstrates the calculation and interpretation of moving average forecasts for 5-week and 10-week look-backs. It also includes calculation and discussion of common error measures used to evaluate forecasts. You can download the spreadsheet used in this tutorial here.