The update rule
FORECAST.ETS is additive exponential smoothing: a level, a trend and, with seasonality, one seasonal value per position in the season. Excel picks the smoothing coefficients alpha, beta and gamma itself; FORECAST.ETS.STAT with statistic type 1, 2 and 3 returns them.
For each period after the first:
- Forecast = level + trend (+ the seasonal value for this position).
- Error = actual value − forecast.
- Level = level + trend + alpha × error.
- Trend = trend + beta × error. Beta multiplies the error directly, not beta × alpha as in some textbooks.
- Seasonal value = seasonal value + gamma × error.
The forecast h periods after the last one is the last level + h × the last trend (+ the seasonal value). A target date between two periods is a straight-line interpolation of the two neighbouring forecasts.
The starting state
Without seasonality, the first period starts the state: level = the first value, trend = the straight-line slope of the whole series (SLOPE against 1, 2, 3, …), and the first error is 0.
This start does not hold for every series. Very short series (4 points) and some series whose last values turn negative come out differently in Excel, so check the difference row in the workbook.
The interval
FORECAST.ETS.CONFINT returns the half-width of the interval: z × SQRT(s² × factor).
- z = NORM.S.INV((1 + confidence) / 2).
- s² = the sum of squared one-step errors / the number of periods, over the whole series.
- factor = 1 + the sum over j = 1 … h−1 of (alpha + j × beta + gamma when j is a multiple of the season length)².
One step ahead, the factor is 1 and the interval depends only on the errors.
What Excel does not explain
The workbook takes alpha and beta from Excel. How Excel chooses them is not documented, and it is not the least-squares best fit: typical choices look like 0.001, 0.25, 0.5 or 0.998. The starting seasonal values and the exact season-length detection are not documented either, so the workbook covers series without seasonality.
Does your own workbook calculate the same outside Excel?
WorkbookAPI runs an Excel model as an API, so a website or an app gets the same numbers as the workbook. The free check recalculates your workbook in your browser and shows every formula that comes out different from the values Excel saved. FORECAST.ETS cells are named as not supported rather than given a different number.
Check your workbookFree · Your file stays in your browser