Expert guide

Sales Forecasting in Excel: FORECAST.ETS vs Linear vs AI

A seasonal forecast you can explain to your bank, with accuracy you can measure

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: aggregate sales into clean monthly or weekly periods with at least two years of history, then use =FORECAST.ETS(target_date, values, dates) for a seasonal forecast and FORECAST.ETS.CONFINT for its range, or Data → Forecast Sheet to build both with a chart. Hold back the last six months to measure accuracy before trusting it. Use AI or Python only when you have drivers such as price, promotions or pipeline. For a maintained forecast model, see Excel dashboards and BI reporting.

Most small business forecasts are one of two things: last year plus a percentage, or a straight trend line. Both ignore the shape of the year. A landscaping company, a retailer and a tax practice all have strong seasonal patterns, and a forecast that misses them gets cash flow, staffing and stock wrong at exactly the wrong time. Excel has had a proper seasonal forecasting engine since 2016. Few people use it.

Step 1: Prepare the History Properly

Forecast functions need a clean timeline: one row per period, evenly spaced, no duplicates. Raw invoice lists do not qualify.

  • Aggregate to periods

    Use a PivotTable grouped by month, or =SUMIFS(Sales[Amount], Sales[Date], ">="&A2, Sales[Date], "<="&EOMONTH(A2,0)) with first-of-month dates in column A.

  • Fill gaps

    A month with no sales should be 0, not missing, unless the business was genuinely closed.

  • Handle one-offs

    A single huge order or a COVID-era shutdown distorts the pattern. Keep an Adjusted column where you replace those values with a typical figure, and note why.

  • Use at least 24 periods

    Two full seasonal cycles is the minimum for ETS to detect seasonality reliably. Three or more is better.

Step 2: The Formulas

Dates in A2:A37 (first of each month), sales in B2:B37.
Future month in A38.

Forecast:
=FORECAST.ETS(A38, $B$2:$B$37, $A$2:$A$37, 1, 1)

95% confidence half-width:
=FORECAST.ETS.CONFINT(A38, $B$2:$B$37, $A$2:$A$37, 0.95, 1, 1)

Low / high range:
=C38 - D38      and      =C38 + D38

Detected season length (should return 12 for monthly data):
=FORECAST.ETS.SEASONALITY($B$2:$B$37, $A$2:$A$37)

The fourth argument (1) tells Excel to detect seasonality automatically; the fifth (1) fills small gaps by interpolation. If SEASONALITY returns something other than 12 on monthly data, look for a data problem before overriding it.

The fast route: Forecast Sheet

Select the dates and values, then Data → Forecast Sheet. Excel builds a new sheet with the forecast, upper and lower bounds and a chart. Under Options you can set the confidence level and seasonality. It produces exactly the formulas above, so you can inspect and extend them.

Step 3: Measure Accuracy Before You Trust It

A forecast without an accuracy figure is an opinion. Backtest it: pretend it is six months ago, forecast the last six months from the earlier data, and compare to what actually happened.

History A2:B31 (30 months), actuals for months 31–36 in B32:B37.
Backtest forecast in C32:
=FORECAST.ETS(A32, $B$2:$B$31, $A$2:$A$31, 1, 1)

Absolute % error in D32:  =ABS(B32 - C32) / B32
MAPE over the six months: =AVERAGE(D32:D37)

Compare with a naive "same month last year" forecast in E32:
=B20        (12 rows earlier)
and its MAPE.

If ETS does not beat "same month last year", the history probably does not have a stable pattern, and the forecast needs drivers or judgement rather than a smarter algorithm.

FORECAST.ETS vs FORECAST.LINEAR vs Others

MethodHandles seasonalityBest forWeakness
Last year + %PartlyVery stable businessesCarries last year's one-offs forward
FORECAST.LINEAR / TRENDNoNon-seasonal steady growthMisses peaks and troughs
FORECAST.ETSYesMost SMB monthly or weekly salesNeeds 2+ years; ignores external drivers
Python in Excel (statsmodels)YesAnalysts wanting model controlRequires code review skills
Driver-based / ML modelYesPromotions, pricing, pipeline effectsNeeds more data and maintenance

The Python route, including a Holt-Winters example, is in Python in Excel for business users.

Where AI Actually Helps With Forecasting

  • Explaining the forecast

    AI can turn the numbers into a short narrative for managers or lenders: what is driving the change, which months carry most risk.

  • Spotting anomalies in history

    Ask it to flag months that look unusual so you can decide whether to adjust them.

  • Building driver models

    When you have marketing spend, price changes or pipeline data, AI tools can help structure a regression or machine learning model.

  • Not for: producing the number from a chat

    Asking a chatbot to 'forecast next year' from a pasted table produces plausible numbers with no method behind them.

For the narrative step, see automating report commentary in Excel with AI.

From Forecast to Plan

A forecast becomes useful when it feeds decisions. Common next steps:

  • Convert revenue forecast to a cash-flow forecast using your typical collection days.

  • Translate demand into stock reorder points per product; see our reorder point guide.

  • Translate volume into staffing hours per week for scheduling.

  • Compare forecast against budget monthly, and update the forecast with each month's actuals.

For inventory, our reorder point formula guide picks up where this one ends. When the forecast should refresh automatically from your sales system each month, with backtesting and a management dashboard, that is a fixed-scope Excel consulting project.

FAQ

Excel sales forecasting FAQ

FORECAST.ETS, history length, accuracy and AI

FORECAST.ETS predicts a future value using exponential triple smoothing (the AAA version of ETS), which accounts for level, trend and seasonality. Give it the target date, your historical values and their dates, and it returns the forecast. FORECAST.ETS.CONFINT returns the confidence interval around it.

For seasonal data, at least two full seasonal cycles: 24 months of monthly data or 104 weeks of weekly data. With less, Excel cannot separate seasonality from noise, and the forecast becomes a trend line with random wiggles.

For data with a seasonal pattern, yes. FORECAST.LINEAR fits a straight line and ignores seasonality entirely, so it under-forecasts peaks and over-forecasts troughs. FORECAST.LINEAR is fine for steady, non-seasonal series or for a quick sanity check.

Sometimes, when you have drivers beyond history, such as price changes, promotions, marketing spend, weather or pipeline data, and enough data to learn from them. For most small and mid-sized businesses with two to five years of monthly sales, FORECAST.ETS is competitive and far easier to explain. AI-written narratives are useful for explaining forecasts, not for producing them.

Usually because the timeline is not evenly spaced, contains duplicate dates, or the seasonality argument is larger than the data allows. Aggregate to clean monthly or weekly periods, remove duplicate dates, and let seasonality auto-detect (argument 1) unless you have a reason to set it.