See all Microsoft Excel tips
Strategy: You need two months of history before you can begin calculating a three-month moving average.
1) In cell C4, use the formula =AVERAGE(B2:B4).
Note that when you enter this formula, Excel will be concerned because your formula ignores similar data in cell B5. In this case, you are smarter than Excel, so you can use the dropdown on the Exclamation sign to tell Excel to ignore the error, as shown in Fig. 389.
2) Double-click the Fill handle in C4 to copy the formula down to the rest of your dataset.
Result: Moving averages are
good if your underlying data has spikes in the sales. It is hard for an automatic system to predict spikes. The moving average smoothes these spikes out of the system, as shown in Fig. 390. A forecast based on the moving average line may be more accurate than a forecast based on the original data.
Summary: Use the AVERAGE function to create a three-month moving average to be used for forecasting.
Functions Discussed: =AVERAGE()
See all Microsoft Excel tips