FORECAST.ETS - the FORECAST function in Excel

FORECAST.ETS was included from Excel 2016 in an extended FORECAST function and it uses a form of triple exponential smoothing.  This is one particular member of the exponential smoothing family of forecasting models, the AAA method, that adapts the level of demand, trend and seasonal factors.

 Some care is needed because FORECAST.ETS is just one of the fifteen or so methods that exist in the exponential smoothing family.  It is fairly complicated and there are times when a simpler model, for example one that does not include trend, would be a better choice.

The new Forecast Sheet tool provides a graphical preview of the result of using FORECAST.ETS on a selected data set, with the option to create a new worksheet.  FORECAST.ETS can also be set up directly within a worksheet to provide a forecasting solution for single or multiple products.

The earlier FORECAST function in Excel provided a simple straight line fit to the historical data and this method is still available as FORECAST.LINEAR.

Advantages of FORECAST.ETS

1.  For the first time a complete forecast can be produced using a single Excel function, incorporating trend and seasonality.

2.  An attempt is made to optimise the smoothing parameters.

3.  The automatic option will test for the presence of seasonality.

4.  Abnormal data can be blanked out in the sales history.

Disadvantages of FORECAST.ETS

1.  The automatic method sometimes suggests an inappropriate seasonal periodicity e.g 6 months or 11 months (can be overridden by the user).

2.  There is no way to intervene in the statistical significance level used in the seasonality test.

3.  Only the AAA method is used, so a trend is always added even though a simpler non-trend method might be a better solution.

4.  The seasonal smoothing method is usually only good if there are a large number of years of consistent data.

5.  The additive seasonality that is used can sometimes lead to the creation of negative forecasts in declining sales situations.

6.  The method is not at all suitable for intermittent sales patterns - a trend will always be added, even though a non-trend solution is usually better for this type of data.

Training on Forecasting in Excel

At Forecast Solutions we offer a training course for those wishing to Forecast in Excel.  This covers the creation from scratch of a number of the most common and popular forecasting methods.  It also covers the new Forecast Sheet facility and the use of the FORECAST.ETS function, including advantages and disadvantages. 

Forecasting Software in Excel

We may be able to provide a forecasting system based on standard Excel or with a user-friendly front end using VBA.  For further information  please click here.