FORECAST.ETS in Excel

The FORECAST function in Excel was extended from Excel 2016 to include the FORECAST.ETS function and this uses a form of triple exponential smoothing.  FORECAST.ETS in Excel makes use of one particular member of the exponential smoothing family of forecasting models, the AAA method.  It is a somewhat complicated model that gives period by period adjustment of the level of demand, the trend and the additive seasonal factors.

 Some care is needed because FORECAST.ETS uses just one of the fifteen or so methods that exist in the exponential smoothing family and there are times when a simpler model 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 in Excel can also be directly set up within any new or existing worksheet to provide a forecast 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.