Using a simple moving average model, we forecast the next value(s) in a time series based on the average of a fixed finite number m of the previous values. Thus, for all i > m
Example 1: Calculate the forecasted values of the time series shown in range B4:B18 of Figure 1 using a simple moving average with m = 3.
Figure 1 – Simple Moving Average Forecast
To produce the values on the left side of Figure 1, insert the formulas =AVERAGE(C4:C6), =ABS(B7-C7) and = (B7-C7)^2 in cells C7, D7 and E7 respectively, and then highlight the range C7:E18 and press Ctrl-D.
The values for MAE (cell D21) and MSE (cell E21) are then calculated using the formulas =AVERAGE(D4:D18) and =AVERAGE(E4:E18), respectively. Alternatively, these values can be calculated using the formulas
=SUMPRODUCT(ABS(B7:B18-C7:C18))/COUNT(B7:B18) =SUMXMY2(B7:B18,C7:C18)/COUNT(B7:B18)
We next highlight the range B3:C18 and select Insert > Charts|Line to create the chart on the right side of Figure 1. Note that the chart of forecasted values (pred in red) smooths out the chart of y values (in blue). The higher the value of m, the more smoothing that occurs.
Note that we can forecast the next value in the time series (cell C19) as 74.33, by using the formula =AVERAGE(B16:B18).
Excel Data Analysis Tool: Excel provides the Moving Average data analysis tool to simplify the calculations described above.
To use this tool for Example 1, select Data > Analysis|Data Analysis and choose Moving Average from the menu that appears. Fill in the dialog box that appears as shown in Figure 2.
Figure 2 – Moving Average dialog box
The output is shown in columns D and E of Figure 3 along with the chart.
Figure 3 – Moving Average data analysis
In this version of the model, the forecast is shifted left by one time unit. In fact, the chart compares yi with ŷi+1. Note that the forecast for the first m–1 time periods is #N/A.
This model also adds a standard error, which can be calculated by inserting the formula =SQRT(SUMXMY2(B6:B8,P6:P8)/3) in cell Q8, highlight the range Q8:Q18 and press Ctrl-D. Note that the s.e. values for the first 2m–2 time periods is #N/A.
The original approach has the forecasted values lagging behind the original values. The Excel approach corrects this somewhat (at the cost of not being able to directly predict the value for i = 16).
Note too that if the Intervals field in Figure 2 is not filled in, it defaults to 3.
Real Statistics Data Analysis Tool: The Real Statistics Resource Pack also provides a Simple Moving Averages data analysis tool.
To use this tool for Example 1, press Ctr-m and select Time Series from the dialog that appears. This brings up the dialog box shown in Figure 4.
Figure 4 – Time Series dialog box
Click on the Basic forecasting methods option from the menu and then fill in the dialog box that appears as shown in Figure 5 (the values for # of Lags, # of Seasons and # of Forecasts are the defaults).
Figure 5 – Forecasting dialog box
When you click the OK button, the output shown in Figure 6 will appear.
Figure 6 – Simple Moving Average data analysis
The value in cell F21 is the forecast at time 16.
Note that you can optionally omit the time values from the input (i.e. by inserting only B3:B18 in the Input Range in Figure 5). In this case, the output will only contain two columns (i.e. the t column won’t appear in Figure 6).
The Alpha, Beta, Gamma, # of Seasons, # of Forecast and Weights Range fields in Figure 5 are not used for simple moving averages.
