=FORECAST.ETS(...) looks like magic: give it three years of monthly sales and it returns a December that is higher than any other month, a January that falls back, and a confidence range around both. Underneath is exponential smoothing with a trend and a repeating season, a method from the late 1950s that fits in about forty lines. The Forecast Sheet in the osFoundry desktop app's Spreadsheet Editor writes that function into a new sheet, and this post rebuilds the same idea in Python, runs it on the same 36 months, and checks where it agrees with the sheet and where a simpler method fails. The data is example data: a small shop's sales with a steady climb, one fixed yearly shape and about 4 percent noise, generated for the walkthrough.
What the sheet produces
On 36 months (Jan 2023 to Dec 2025), Forecast Sheet with seasonality on auto and a 95 percent confidence level writes three columns, Forecast, Lower and Upper Confidence Bound, and a chart. Cell C49, December 2026, holds =FORECAST.ETS(A49,$B$2:$B$37,$A$2:$A$37,1,1) and shows $20,654. The two bounds next to it are $19,008 and $22,300.
Those are the numbers to reproduce. Everything below runs with uvx --with numpy --with scipy python -I forecast.py on Python 3.12, numpy 2.5.4 and scipy 1.18.1.
Find the season first
Seasonality auto has to guess how long one cycle is. A cheap version of that guess is the autocorrelation of the month-to-month change. A trend adds the same amount to every month and cancels out in the differences; a yearly shape does not.
d = np.diff(y); d = d - d.mean()
ac = [np.sum(d[:-k] * d[k:]) / np.sum(d * d) for k in range(1, 19)]
On these 36 months, lag 12 comes out at 0.53, lag 6 at 0.06 and lag 13 at -0.01, so the season length is 12. That is my own rule of thumb, not a description of how the editor picks its season length; what can be compared is the output, below.
Holt-Winters, additive
Three numbers are kept up to date as each month arrives: a level (where sales are now, with the season taken out), a trend (how much it grows per month) and twelve season values (how far above or below the level each calendar month sits). Each is a weighted mix of the new observation and the old estimate, with weights alpha, beta and gamma.
def run(y, a, b, g):
l, t, s = init(y); s = list(s); err = []
for i, v in enumerate(y):
err.append(v - (l + t + s[i % M])) # one-step-ahead error
ln = a * (v - s[i % M]) + (1 - a) * (l + t) # level
t = b * (ln - l) + (1 - b) * t # trend
s[i % M] = g * (v - ln) + (1 - g) * s[i % M] # season
l = ln
return np.array(err), l, t, s
The starting values come from the first two years: the first year's mean as the level, the difference between the first two yearly means divided by twelve as the trend, and each month's deviation from the first year's mean as its season. The three weights are found by minimising the squared one-step errors with scipy.optimize.minimize, from three starting points so that a bad start doesn't pick the answer. Forecasting h months ahead is level + k * trend + season[month].
For an interval, I used a rule of thumb: 1.96 times the residual standard deviation times the square root of the horizon. The sheet's own interval formula was not inspected, so only its numbers are compared.
Against the sheet
On the full 36 months the fit gives alpha 0.37, beta 0.01 and gamma 0.42, and a residual standard deviation of $567. Against the Forecast column of the sheet:
| Month | This script | Forecast Sheet |
|---|---|---|
| Jan 2026 | $12,136 | $12,260 |
| Dec 2026 | $20,698 | $20,654 |
| Mean absolute difference, 12 months | $297 (2.0%) | |
| Largest difference (Jun 2026) | $587 |
Twelve months agree to within 2 percent on average, which is what one would expect from two implementations of the same family of method with different starting values and different fitting. It is not proof that the editor does exactly this, and it is one data set with a clean shape. Real data with a missing month or a change of level will move the two further apart.
The intervals do not agree: December comes out at $16,849 to $24,548 here, against $19,008 to $22,300 in the sheet. Mine is wider, and its width grows faster with the horizon: $2,223 in January to $7,700 in December, against $1,621 and $3,292 in the sheet. The square-root rule is a crude one, and nothing here says which band is better calibrated.
What the season buys
Running the same data without a season, with only a level and a trend (Holt's linear method), shows what the pattern is worth:
no season: Jan 17872 Dec 23159 residual sd 2185
Without the season the January forecast is $17,872, where the sheet gives $12,260 and both earlier Januaries fell by about 40 percent from December. The residual standard deviation is 2,185 against 567 with the season. The December forecast is wrong in the other direction, $23,159 against $20,654, because a trend line has no way of knowing that December is a peak and January a trough.
A one-year test
Fit on Jan 2023 to Dec 2024 only, forecast 2025, and compare with what happened. Mean absolute percentage error (MAPE) over the twelve months:
| Method | MAPE | Dec 2025 forecast |
|---|---|---|
| Overall mean | 19.4% | $10,586 |
| Last month repeated | 37.3% | $17,000 |
| Same month last year | 12.0% | $17,000 |
| Same month last year, times growth | 4.1% | $20,025 |
| Holt-Winters (this script) | 3.4% | $18,511 |
December 2025 actually sold $19,480. Two things come out of it. A plain seasonal copy of last year (12.0 percent) misses the growth, and adding the observed yearly growth of 17.8 percent (4.1 percent) is nearly as good as Holt-Winters (3.4 percent), so with two years of data the exponential smoothing is not a large step up from a careful spreadsheet formula. And the 95 percent band covered all twelve actuals, which with twelve points says only that it is not too narrow.
This is one split of one example series. It says nothing about how the methods rank on other data.
A pitfall: asking a statistics library to estimate everything
The obvious shortcut is statsmodels. With default fitting on the same 36 months:
ETSModel(series, error='add', trend='add', seasonal='add', seasonal_periods=12).fit()
statsmodels 0.15.0 gives December 2026 as $22,738 with a range of $17,688 to $27,787, 10 percent above the sheet. In the one-year test the same model had a MAPE of 33.2 percent, worse than repeating the overall mean. Probably, a model that estimates twelve starting seasonal values plus the smoothing weights from 24 or 36 points has too many free numbers, and the fit settles on something odd. The script above fixes the starting values from the first years instead, which is one of the differences between the two; it was not tested on its own. It is worth checking any library forecast against "same month last year, times growth" before trusting it.
Using it
The script (forecast.py) is the entire experiment. For actual sales numbers, the quickest route is the Forecast Sheet in the app, which is free from the osFoundry download page and works on the file on the computer. Writing the method out is useful for knowing what the range means, and for noticing when a forecast ought to be thrown out.
Top comments (0)