An Excel Implementation of Time Series Forecasting

Forecasting the trend of demand is an important part in supply chain management and planning future marketing strategies. The accuracy of the forecast depends on the precise quantification of past statistics of consumer behavior.

Univariate forecasting method is an effective way of forecasting, based on the belief that any time series of data can be broken down into core components such as season, trend and errors. Once the parameters are estimated, it can be used to extrapolate historical sales behavior over subsequent time periods.

A sample 3-period moving average forecast of demand:

Suppose a company wants to use 3-period moving average to predict VCR demands. As the excel implementation indicates, forecasts for period 29 and up substitute the previous forecasts for missing data because the forecast can not use data we haven’t seen yet.

Figure 1: An excel implementation of 3-period moving average forecast of demand

We can change the window size and see how it affects forecasts.

Figure 2: Moving average result using different windows

As the graph shows, longer periods use more of the data. The forecast is not affected by a single outlier data point, however, longer periods also mean that old data might not reflect the present.

We can also try a recursive way to modeling, which means our next prediction is a function of the previous prediction. Here is a sample of exponential smoothing.

Alpha is a parameter bounded between 0 and 1, chose by minimizing RMSE of the model.

Figure 3: Using Solver to build exponential smoothing model

After some configuration of the Excel plugin “Solver”, we can get the forecasting using exponential smoothing model. Different from a moving average, the data is never thrown away completely.

A question for readers: What does the value of alpha indicates? Under what circumstances should we use small alpha, median alpha or large alpha?

Reference:

http://www.marketscienceconsulting.com/services/forecasting-and-simulation/