Time Series Forecast with Excel

In the year 2006, I had moved to the MIS team. We were the data-keepers of the organization. Even Excel (version 2003) was new and fascinating for me. Then we got introduced to forecast techniques. A team of Six Sigma professionals was adept in generating yearly, monthly & weekly forecasts. They used Minitab for generating forecasts using time series techniques as Excel had limited capabilities. I could hardly understand the time series then. For me, it was quite a fascinating subject: predicting the future using historical data!

With time, both Excel and I developed analytical capabilities. And prediction modeling has become a regular feature for both of us. This article explains the steps involved in generating forecasts using the Forecast sheet.

Introduced with Excel 2016 (for windows only), Forecast Sheet made forecast generation a two-step process.

You can find the feature under the Data ribbon:

The Underlying Function: FORECAST.ETS

The Forecast Sheet predicts using the Exponential Smoothing seasonal algorithm (ETS AAA) using an inbuilt function FORECAST.ETS. It models the time series using an equation that accounts for additive error, additive trend, and additive seasonality. This algorithm is also popularly known as the Holt-Winters algorithm. It is widely used in predicting and planning demand in businesses.

In other words, the Forecast sheet is useful for prediction when the time series data has a trend and seasonality present.

Generating Forecast with Forecast Sheet

Primarily it requires two columns:

1. The value to be forecasted (forecast variable)

2. A column with a continuous time-period (it can be the 1st of every month, a yearly timeline, or a serial number assign to each period)

For a time series, a consistent period is a must. It can be monthly intervals with values on the 1st of every month, yearly intervals, or numerical intervals.

The data set may have blank records for values, but these records constitute 30% or less of the total records. The Forecast sheet also handles multiple records for the same timestamp or period. However, summarizing the dataset is recommend (to ensure unique records for the same timestamp)

Select the two columns > Data > Forecast Sheet

Select Values > Data > Forecast Sheet

It opens a Forecast Worksheet window:

Components of Forecast Worksheet

· Forecast End: Defines the number of forecast periods (denoted by the orange color in the graph). Excel creates a table with historical data along with the forecast values.

· Forecast Start: Defines the start point for the forecast. You can pick a period before the end of the historical data. When you do that, the Forecast worksheet ignores all values beyond that period. In this example, if we change the Forecast Start from 16 to 11, Forecast Worksheet consider points 1 to 10 for forecast generation.

Starting your forecast before the last historical point gives you a sense of the prediction accuracy as you can compare the forecasted series to the actual data.

However, if you start the forecast too early, the estimates generated will not necessarily represent the forecast it generates using all the historical data.

Using all your historical data gives you a more accurate prediction.

· Confidence Interval: Check or uncheck Confidence Interval to show or hide it. A confidence interval is a range around the predicted value. A confidence interval of 95% signifies that 95% of the predicted value expected to fall within this range.

· Seasonality: It is the number of time steps that it takes for a full data cycle, after which the cycle repeats throughout the time series. By default, it autodetects the seasonality of the data set. You could use the formula FORECAST.ETS.SEASONALITY to calculate the seasonality in the dataset.

· Fill Missing Points Using: Useful when you have missing data points in your dataset for values.

  • If selected Zero, then it treats missing values as 0 and calculates forecast (not recommended, requires business judgment)

  • If selected Interpolation, it uses the weighted average of neighboring data points around the missing values. It works when missing values are less than 30% of the total records.

· Aggregate Duplicates Using: If the dataset has multiple records for the same period, by default, Excel uses the average of those values for forecasting. For example, for the period 10, if we have two records with values 5 & 6, then Forecast sheet uses 5.5 (average of 5 & 6) as value for period 10.

We can change the default calculation depending on the data and expected forecast values:

· Include Forecast Statistics: Check this option to get the following statistical information:

The output of the Forecast Sheet

The values in Column C are the forecast for future periods. Column D & E represent the lower and upper bound of the estimates, respectively. So, for the period 17, the mean forecast is 7.3, and it may vary between 6.9 to 7.8 (with 95% confidence).

Drop Me a Line, Let Me Know What You Think



+91 9871-641-146