This example illustrates how to use Analytic Solver Data Science's Moving Average Smoothing technique to uncover trends in a time series that contains seasonality. On the Data Science ribbon, from the Applying Your Model tab, select Help - Examples, then Forecasting/Data Mining Examples, and open the example data set, Airpass.xlsx. This data set contains the monthly totals of international airline passengers from 1949-1960.

After the example data set opens, click a cell in the data set, then on the Data Science ribbon, from the Time Series tab, select Partition to open the Time Series Partition Data dialog.

Select Month as the Time Variable, and Passengers as the Variables in the Partition Data. Click OK to partition the data into Training and Validation Sets. (Partitioning is optional. Smoothing techniques may be run on full unpartitioned data sets.)

Moving Average Smoothing Dialog 

Click the Data_PartitionTS worksheet, then on the Data Science ribbon, from the Time Series tab, select Smoothing - Moving Average to open the Moving Average Smoothing dialog.

Month has already been selected as the Time variable. Select Passengers as the Selected variable. Since this data set is expected to include some seasonality (i.e., airline passenger numbers increase during the holidays and summer months), the value for the Interval parameter - weight should be the length of one seasonal cycle (i.e., 12 months). As a result, enter 12 for Interval, and select Produce forecast on validation.

Moving Average Smoothing Dialog 

Click OK to apply the smoothing technique to the partitioned data set.

Two worksheets, MASmoothingOutput and MASmoothing_Stored, are inserted immediately to the right of the Data_PartitionTS worksheet. For more information on the MASmoothing_Stored worksheet, see the Applying Your Model - Scoring New Data section.

Click the MASmoothingOutput worksheet. The Time Plot of Actual Vs. Forecast (Training Data) and (Validation Data) charts show that the Moving Average Smoothing technique does not result in a good fit, as the model does not effectively capture the seasonality in the data set. The summer months -- where the number of airline passengers are typically high -- appear to be under forecasted, and the months where the number of airline passengers are low, the model results in a forecast that is too high. A moving average forecast should never be used when the data set includes seasonality. An alternative would be to perform a regression on the model and then apply this technique to the residuals.

Moving Average Smoothing Output:  Time Plot of Actual Vs. Forecast (Training Data)

   Moving Average Smoothing Output:  Training Error Measures

 Moving Average Smoothing Output:  Time Plot of Actual Vs Forecast (Validation Data)

Moving Average Smoothing  Output:  Validation Error Measures

The next example does not include seasonality. On the Data Science ribbon, from the Applying Your Model tab, select Help - Examples, then select Forecasting/Data Mining Examples, and open the example data set Income.xlsx. This data set contains the average income of tax payers by state. First, partition the data set into Training and Validation Sets using Year as the Time Variable, and CA as the Variables in the Partition Data.

Time Series Partition Data 

Click OK to accept the partitioning defaults and create theTraining and Validation Sets. The worksheet Data_PartitionTS is inserted immediately to the right of the Income worksheet.

Click the Data_PartitionTS worksheet, then on the Data Science ribbon, from the Time Series tab, select Smoothing - Moving Average to open the Moving Average Smoothing dialog. Year has automatically been selected as the Time variable. Select CA as the Selected variable, and under Output Options, select Produce forecast.

Moving Average Smoothing Dialog 

Click OK to run the Moving Average Smoothing technique. Two worksheets, MASmoothingOutput and MASmoothing_Stored, are inserted to the right of the Data_PartitionTS worksheet. For more information on the MASmoothing_Stored worksheet, see the Applying Your Model - Scoring New Data section.

The results of the Moving Average Smoothing technique on this data set indicate a much better fit.

Moving Average Smoothing Dialog   

Moving Average Smoothing Output:  Training Error Measures

Moving Average Smoothing Output:  Time Plot of Actual Vs Forecast (Validation Data)

Moving Average Smoothing  Output:  Validation Error Measures