Notes on the Excel Forecast.ETS function

The official Microsoft documentation for the Excel Forecast.ETS function is pretty weak [1]. Below I’ve written a few notes on how the function works, and the underlying formulas.

Source: Microsoft office in Seattle, @Coolcaesar, https://en.wikipedia.org/wiki/File:Building92microsoft.jpg

​How do I use the formula in Excel?

The use case is when we have a time series, and we wish to forecast the values of the times series into the future.

​For example, suppose we have the following index, which we wish to project into the future.

​Then we can either call the Forecast.ETS function directly, by typing =Forecast.ETS into a cell, or by using the ‘Forecast Sheet’ button in the ‘Data’ ribbon. The Forecast Sheet produces the following output:

Note that this chart provides us with a central estimate, plus a two-sided confidence interval.

​What formula is the Excel function using?

The Microsoft documentation gives us the following fairly terse description:

“… using the AAA version of the Exponential Smoothing (ETS) algorithm. “
​
A bit of detective work reveals that ‘AAA’ in this context means the additive version of Holt’s Winter Seasonal method. From Rob Hyndman’s excellent open-source online textbook [2], the formula are as per the middle cell of the table below (the table is for the Additive version, and we then need A from Trend, and A from Seasonal, hence the three A’s = AAA)

Recreate calcs explicitly in a Spreadsheet

The following spreadsheet recreates the results of the forecast function.

The values for alpha, beta, gamma, etc. are taken from the Excel Forecast sheet, but can also be output directly using FORECAST.ETS.STATS. Note that Hyndman’s formulas use a different parameterisation of beta.

You can download the Spreadsheet with the reconciliation from Github:
github.com/Lewis-Walsh/Excel_Forecast.ETS/blob/main/Forecast_ETS.xlsx

Source links:
 
[1] https://support.microsoft.com/en-us/office/forecast-ets-function-15389b8b-677e-4fbd-bd95-21d464333f41
[2] https://otexts.com/fpp2/holt-winters.html