7. FORECAST.ETS and Seasonality

Sales of ice cream, tours, and space heaters repeat the same pattern every year. This is seasonality. A straight trend line (FORECAST.LINEAR) doesn't see it: at the peak it forecasts too little, and in the slump too much.

Excel 2016 and later has FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]). It builds an exponential smoothing model (the AAA version of the ETS algorithm): recent points weigh more than old ones, and the level, trend, and seasonal wave are estimated separately.

• seasonality: 1 or omitted lets Excel detect the cycle length itself, 0 means no seasonality, 4 is a quarterly cycle, 12 is a monthly one;
• the timeline must have a constant step (every month, every quarter); Excel can fill small gaps;
• FORECAST.ETS.CONFINT returns the width of the forecast's confidence interval, and FORECAST.ETS.SEASONALITY returns the season length it detected;
• Data › Forecast Sheet does it all at once: select the dates and values, pick the forecast end date, and Excel creates a new sheet with a table, ETS formulas, interval bounds, and a chart.

Note: the simulator doesn't calculate FORECAST.ETS. The algorithm fits its smoothing parameters numerically, and we won't reproduce it here. Instead, you'll build the classic multiplicative model, which you can check by hand and explain to your manager:

1. The seasonal index of a quarter = the average sales of that quarter / the average of all sales. An index of 1.4 means the quarter is 40% above normal;
2. The deseasonalized series = sales / the index of their quarter. The seasonal wave is removed and only the trend is left;
3. The trend of the deseasonalized series comes from FORECAST.LINEAR or SLOPE/INTERCEPT;
4. The forecast = the trend value x the seasonal index of the target quarter.

A more accurate variant computes the indexes from a moving average, but the idea is the same. If ETS and the manual model give similar numbers, you can trust the forecast more.