THE REINSURANCE ACTUARY
  • Blog
  • Project Euler
  • Category Theory
  • Disclaimer

Notes on the Excel Forecast.ETS function

1/4/2022

 


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.
Picture
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.
Picture
Picture
​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:
Picture
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)

Picture

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

Your comment will be posted after it is approved.


Leave a Reply.

    Author

    ​​I work as an actuary and underwriter at a global reinsurer in London.

    I mainly write about Maths, Finance, and Technology.
    ​
    If you would like to get in touch, then feel free to send me an email at:

    ​LewisWalshActuary@gmail.com

      Sign up to get updates when new posts are added​

    Subscribe

    RSS Feed

    Categories

    All
    Actuarial Careers/Exams
    Actuarial Modelling
    Bitcoin/Blockchain
    Book Reviews
    Economics
    Finance
    Forecasting
    Insurance
    Law
    Machine Learning
    Maths
    Misc
    Physics/Chemistry
    Poker
    Puzzles/Problems
    Statistics
    VBA

    Archives

    March 2023
    February 2023
    October 2022
    July 2022
    June 2022
    May 2022
    April 2022
    March 2022
    October 2021
    September 2021
    August 2021
    July 2021
    April 2021
    March 2021
    February 2021
    January 2021
    December 2020
    November 2020
    October 2020
    September 2020
    August 2020
    May 2020
    March 2020
    February 2020
    January 2020
    December 2019
    November 2019
    October 2019
    September 2019
    April 2019
    March 2019
    August 2018
    July 2018
    June 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    April 2016
    January 2016

  • Blog
  • Project Euler
  • Category Theory
  • Disclaimer