A moving average with window width w means averaging across each set of w consecutive values. For visualizing a time series, we typically use a centered moving average with w = season. In a centered moving average, the value of the moving average at time t (MAt) is computed by centering the window around time t and averaging across the w values within the window. For example, if we have daily data and we suspect a day-of-week effect, we can suppress it by a centered moving average with w=7, and then plotting the MA line.
An observant participant in my online course Forecasting discovered that Excel’s moving average does not produce what we’d expect: Instead of averaging over a window that is centered around a time period of interest, it simply takes the average of the last w months (called a “trailing moving average”). While trailing moving averages are useful for forecasting, they are inferior for visualization, especially when the series has a trend. The reason is that the trailing moving average “lags behind”. Look at the figure below, and you can see the difference between Excel’s trailing moving average (black) and a centered moving average (red).
The fact that Excel produces a trailing moving average in the Trendline menu is quite disturbing and misleading. Even more disturbing is the documentation, which incorrectly describes the trailing MA that is produced:
“If Period is set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.”
For more on moving averages, see here: