Data collected over time is likely to show some form of random variation. "Smoothing techniques" can be used to reduce or cancel the effect of these variations. These techniques, when properly applied, will “smooth” out the random variation in the time series data to reveal any underlying trends that may exist.  

In this simple technique each observation is assigned an equal weight.  Additional observations are forecasted by using the average of the previous observations. If we have the time series X1, X2, X3, ....., Xt,  then this technique will predict Xt+k as follows : St = Average (xt-k+1, xt-k+2, ....., xt), t= k, k+1, k+2, ...N  where k is the smoothing parameter. The XLMiner Analysis Toolpak Add-on allows a parameter value between 2 and t-1 where t is the number of observations in the dataset.  Care should be taken when choosing this parameter as a large parameter value will oversmooth the data while a small parameter value will undersmooth the data.  Using the past three observations are typically enough to predict the next observation.  This technique should not be applied when seasonality is present in the dataset.

The example time series dataset below contains the monthly airline passengers in thousands for a small regional airline.  Create a forecast using this historical data as input. 

Moving Average Example Dataset

To generate the forecast: 

On the XLMiner Analysis ToolPak pane, click Moving Average

  1. Click the Input Range field and then enter the cell range B1:B25.
  2. Leave "Labels in First Row" selected since the first row in the data range includes the column label.
  3. Enter a value for Interval, in this example we'll use 3.
  4. Click the Output Range field and then enter cell D1. 
  5. Keep Chart Output selected in order to display a chart of the output. 
  6. Keep Standard Errors selected to display the standard errors in the report. 
  7. Click OK. 

Moving Average Pane

The results are below.

Moving Average Results

The results of the Moving Average smoothing method Cells are inserted into cells E2:E25.  Cells F2:F25 contain the standard errors. 

Note the N/A errors in the first cell of column E and the first four of column F are due to insufficient historical values needed to project a forecast or calculate a standard error.