Problem: I have 36 months of sales data. In order to create a prediction of sales, I want to calculate a three-month moving average. Later, I will create a trendline from the moving average.
- A moving average might show a trend in this data.
Strategy: You need two months of history before you can begin calculating a three-month moving average. When you have that, follow these steps:
- In cell C4, enter the formula =AVERAGE(B2:B4). Note that when you enter this formula, Excel will be concerned because the formula will ignore similar data in cell B5. In this case, you are smarter than Excel, so you can use the Caution (exclamation point) dropdown to tell Excel to ignore the error.
- Double-click the fill handle in C4 to copy the formula down to the rest of your data set.
Results: Moving averages are good if the underlying data has spikes in the sales. It is difficult for an automatic system to predict spikes. A moving average smooths these spikes out of the system. A forecast based on the moving average line may be more accurate than a forecast based on the original data.
- The three month moving average shows a forecastable trend.