Build a Sales Forecast with Exponential Smoothing

Most sales forecasts in spreadsheets are a trend line, last year plus a percentage, or someone's judgement typed into next year's columns. Exponential smoothing is a step up that stays understandable: the forecast is built from a smoothed level, a smoothed trend and a repeating seasonal pattern, each updated month by month with more weight on recent months than old ones. The Holt-Winters version, which handles trend and seasonality together, is what Excel's FORECAST.ETS uses under the hood, and in Python the statsmodels library provides it with more control and better diagnostics. This page reads three years of monthly sales from Excel, fits a Holt-Winters model, tests it honestly on six months it was not allowed to see, compares it with a simple seasonal benchmark, produces a twelve-month forecast with a plausible range, and writes the result into a workbook with a chart. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl statsmodels

The input is thirty-six months of sales, October 2023 to September 2026, with steady growth, a summer peak and a December spike — the shape of a typical seasonal business. Holt-Winters needs at least two full seasons of history to estimate a monthly pattern; three is better.

Step 1 — Load and look at the history

import pandas as pd

hist = pd.read_excel("monthly_sales.xlsx", index_col="Month", parse_dates=True)["Sales"].asfreq("MS")
by_year = hist.groupby(hist.index.month).mean().div(hist.mean()).round(2)
print(hist.tail(6).to_string())
print("seasonal index by month:", by_year.to_dict())
print(f"growth, last 12 months vs previous 12: {hist[-12:].sum() / hist[-24:-12].sum() - 1:.1%}")

asfreq("MS") gives the series a monthly frequency, which the model needs, and would expose any missing month as a gap rather than letting it slip by. A quick seasonal index — each month's average relative to the overall average — confirms what the model is about to learn: June to August about 18% above average, October, November and January about 20% below, and December lifted above its neighbours by the holidays. Year-on-year growth of almost 16% says the series has a trend as well. Both matter for choosing the model.

Step 2 — Fit on most of the data, test on the rest

A forecast is only worth trusting if it has been tested on data it did not see. Hold back the last six months, fit on the rest, and compare:

import numpy as np
from statsmodels.tsa.holtwinters import ExponentialSmoothing

train, test = hist[:-6], hist[-6:]
model = ExponentialSmoothing(train, trend="add", damped_trend=True, seasonal="mul", seasonal_periods=12).fit()
hw = model.forecast(6)
naive = train[-12:-6].set_axis(test.index)                      # same months last year: the seasonal naive benchmark

def mape(actual, forecast):
    return float(np.mean(np.abs(forecast - actual) / actual))

print(pd.DataFrame({"Actual": test, "Holt-Winters": hw.round(-2), "Last year": naive}).to_string())
print(f"MAPE Holt-Winters {mape(test, hw):.1%} | seasonal naive {mape(test, naive):.1%}")
print({k: round(v, 3) for k, v in model.params.items() if k in ("smoothing_level", "smoothing_trend", "smoothing_seasonal", "damping_trend")})

The model has an additive, damped trend — growth continues but flattens gradually rather than compounding for ever, which is usually the safer assumption — and multiplicative seasonality, because the summer peak grows in proportion as sales grow. The benchmark is the simplest sensible forecast: the same month last year. The mean absolute percentage error (MAPE) puts both on one scale. Holt-Winters beats the benchmark comfortably — 4.1% against 14.2% — mainly because it accounts for growth that "same as last year" ignores; it ran a little high in every one of the six months, which is worth watching but well inside what the range in Step 3 allows. If a model cannot beat that benchmark, it is not worth its complexity. The fitted smoothing parameters show how quickly each component adapts: a level parameter of 0.33 means recent months carry real weight, and a seasonal parameter of zero means the seasonal pattern was judged stable from year to year, so the model does not chase noise in individual months.

Step 3 — Forecast twelve months, with a range

Refit on all thirty-six months and forecast a year ahead. A single line looks more certain than any forecast is, so simulate the model forward many times to get a range:

final = ExponentialSmoothing(hist, trend="add", damped_trend=True, seasonal="mul", seasonal_periods=12).fit()
forecast = final.forecast(12)
sims = final.simulate(12, repetitions=2000, error="mul", rng=np.random.default_rng(7))
low, high = sims.quantile(0.1, axis=1), sims.quantile(0.9, axis=1)
fc = pd.DataFrame({"Forecast": forecast, "Low (P10)": low, "High (P90)": high}).round(-2)
print(fc.to_string())
print(f"next 12 months: {forecast.sum():,.0f} vs last 12 months: {hist[-12:].sum():,.0f} ({forecast.sum() / hist[-12:].sum() - 1:+.1%})")

simulate runs the fitted model forward 2,000 times with random errors of the size it saw in the history, and the 10th and 90th percentiles of those paths give a range that should contain the outcome in about eight months out of ten. The range widens month by month, as it should: next month is more predictable than next September. For next June the central forecast is about 103,000, but the range runs from roughly 86,000 to 120,000. The twelve-month total of about 1.04 million is 16% above the last twelve months, in line with recent growth. Reporting the range alongside the central figure changes the conversation from "will we hit 103,000 in June?" to "June will probably land between 86,000 and 120,000" — which is what planning stock and staff actually needs.

Step 4 — Write the forecast workbook with a chart

from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

out = pd.concat([hist.rename("Actual").to_frame(), fc], axis=1)
out.index = out.index.strftime("%b %Y")
path = "sales_forecast.xlsx"
with pd.ExcelWriter(path, engine="openpyxl") as xw:
    out.to_excel(xw, sheet_name="Forecast", index_label="Month")
    pd.DataFrame({"Model": ["Holt-Winters (damped additive trend, multiplicative seasonality)", "Seasonal naive"],
                  "Holdout MAPE": [mape(test, hw), mape(test, naive)]}).to_excel(xw, sheet_name="Model check", index=False)
wb = load_workbook(path)
ws = wb["Forecast"]
chart = LineChart()
chart.title, chart.y_axis.title = "Monthly sales: actual and forecast", "Sales"
chart.add_data(Reference(ws, min_col=2, max_col=5, min_row=1, max_row=ws.max_row), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=ws.max_row))
for s in chart.series:
    s.smooth = False
chart.series[2].graphicalProperties.line.dashStyle = "dash"
chart.series[3].graphicalProperties.line.dashStyle = "dash"
chart.x_axis.delete = chart.y_axis.delete = False
chart.width, chart.height = 24, 10
ws.add_chart(chart, "G2")
for row in ws.iter_rows(min_row=2, min_col=2):
    for c in row:
        c.number_format = "#,##0"
wb.save(path)
print(wb.sheetnames, "rows:", ws.max_row - 1)

The Forecast sheet holds actuals and forecast in one table, so the forecast continues straight on from the history, with the low and high columns either side; the chart draws actuals and forecast as lines and the range as dashed lines. The Model check sheet records how the model did on the held-back months, against the benchmark — the first thing a sceptical reader should be shown. Turning a forecast into a budget comparison later is covered in Build a Budget vs Actual Variance Report.

What exponential smoothing cannot know

The model extrapolates the patterns in the history, which is exactly its strength and its limit. It does not know that a price rise is planned for January, that a large customer has just been won or lost, or that last year's March was inflated by a one-off order. Those are the business's knowledge and belong in the forecast as explicit adjustments, recorded next to the statistical forecast rather than typed over it, so the next comparison of forecast and actual can tell which part was wrong. Unusual months in the history deserve the same care: replace a known one-off with a typical value before fitting, or the model will expect it to repeat. And refit monthly as new actuals arrive; the holdout check is cheap enough to run every time, and a model whose error starts to climb is the earliest warning that the business has changed.

Common pitfalls

• No holdout test. Measure accuracy on months the model has not seen.
• No benchmark. Compare with "same month last year"; beat it or keep it simple.
• Undamped trends. Growth compounding for ever is rarely realistic; damp it.
• A single line. Show a range; forecasts are uncertain.
• Fitting through one-offs. Adjust exceptional months before fitting.

Conclusion

To forecast monthly sales from Excel, load the history with a monthly frequency, fit ExponentialSmoothing with a damped additive trend and multiplicative seasonality, and test it on the last six months against a same-month-last-year benchmark. Refit on all the data, forecast twelve months with a P10–P90 range from simulation, and write actuals, forecast, range and the accuracy check to a workbook with a chart — with business adjustments recorded separately.

Frequently asked questions

How do I forecast sales with exponential smoothing in Python? Use statsmodels' ExponentialSmoothing with trend and seasonal settings, call fit(), then forecast(n) for the next n periods.

How do I know if my forecast is any good? Hold back the last few months, fit on the rest, and compare the forecast with the actuals using a measure such as MAPE, alongside a simple benchmark like the same month last year.

Should seasonality be additive or multiplicative? Multiplicative when the seasonal swing grows with the level of sales, additive when it stays roughly constant in absolute terms.

How do I get a forecast range instead of a single number? Simulate the fitted model forward many times with simulate() and take percentiles such as the 10th and 90th of the simulated paths.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Weekly Sales Report in Excel with Python — the actuals side.
• FORECAST, TREND, SLOPE and RSQ Equivalents in Python — linear forecasts.
• Build a Rolling 12-Month Chart on an Excel Dashboard — presenting the trend.