Take at least two years of monthly sales by channel, build a baseline (last year's same quarter scaled by your recent growth rate, or Excel's FORECAST.ETS), and test it by forecasting last year's quarter as if you did not know it. Then adjust for changes the history cannot know and present a low, base and high figure.
AI earns its place in the testing and challenging, not the guessing. A chat assistant asked "what will my sales be next quarter?" produces a confident number with nothing behind it. Asked to fit three simple models to your file, backtest each one honestly and show the error, it does in ten minutes what used to need an analyst. The judgement part (a lost customer, a price rise, a stockout last year) still comes from you, and it often moves the forecast more than the model choice does.
The history you need, in the shape a model can use
Export monthly sales for the last 24 to 36 months from your till, online shop and accounting software, and put them in one table with one row per month per channel. Monthly is right for a quarterly forecast; weekly helps only if you also plan staffing or stock week by week.
| Month | Channel | Net sales | Orders | Notes |
|---|---|---|---|---|
| 2025-10 | Shop | 18,900 | 1,210 | |
| 2025-10 | Online | 11,600 | 402 | Autumn case offer, 10 days |
| 2025-10 | Trade | 7,300 | 38 | |
| 2025-10 | Events | 3,200 | 6 | Two tastings |
Four cleaning jobs make the biggest difference, and an assistant can help with each once you tell it what to look for:
- Use net sales (after refunds and discounts), consistently. Mixing gross online sales with net shop sales creates a fake jump the month you changed reports.
- Mark one-offs in the Notes column: a closing-down purchase by a customer, a film crew buying 40 cases, a week closed for refitting. Do not delete them; flag them so the model can be run with and without.
- Mark stockouts. If you ran out of your best-selling sparkling wine on 12 December last year, December's history understates demand. Note the date and estimate what you would have sold.
- Keep channels separate. Trade accounts behave differently from the shop, and losing one restaurant can move a quarter more than any seasonal pattern.
Baseline one: last year's quarter, scaled by recent growth
The simplest forecast is often the hardest to beat for a seasonal small business. Take the same quarter last year and scale it by how much you have been growing recently. Take an illustrative independent wine merchant with a shop, an online store, restaurant trade accounts and paid tastings. Its last two festive quarters:
| October | November | December | Quarter | |
|---|---|---|---|---|
| 2024 | $38,000 | $52,000 | $96,000 | $186,000 |
| 2025 | $41,000 | $57,500 | $101,500 | $200,000 |
April to September 2026 ran 5 per cent ahead of the same months in 2025. So the baseline for October to December 2026 is $200,000 × 1.05 = $210,000: October $43,050, November $60,375, December $106,575. Use the growth rate from the most recent six months rather than the full year; it reflects where the business is now.
Baseline two: FORECAST.ETS in Excel
Excel has a built-in exponential smoothing forecast that finds the seasonal pattern itself. With months in column A and sales in column B, the forecast for a future month in A40 is:
=FORECAST.ETS(A40, $B$2:$B$37, $A$2:$A$37, 1)
Check which season length Excel detected (it should say 12 for monthly data):
=FORECAST.ETS.SEASONALITY($B$2:$B$37, $A$2:$A$37)The final argument of 1 tells Excel to detect seasonality automatically. According to Microsoft's documentation, the timeline needs evenly spaced dates, missing points are filled with the average of their neighbours by default, and the function is not available in Excel for the web, iOS or Android, so use the desktop app. The Forecast Sheet button on the Data tab builds the same kind of forecast with a chart and confidence band. Give it at least two full years; with less, it cannot tell a seasonal peak from a one-off.
Baseline three: ask an AI assistant to fit and compare models
ChatGPT's data analysis and Claude's analysis tool can both run real code on an uploaded file, which is what you want: calculated forecasts, not estimated ones. The prompt matters because the most common error in this step is the model quietly peeking at the answer.
Monthly totals by channel are not personal data, but a trade-sales export often carries restaurant names and contact details. Delete those columns before uploading, or replace names with codes such as TRADE-01, and use a plan that does not train on your content. If your file has more than one row per month per channel (one row per order, say), ask the assistant to total it by month first and show you the monthly table before it models anything; mistakes in that step spoil everything after it.
Attached: monthly_sales.csv (Month, Channel, NetSales, Notes), Jan 2023 to Sep 2026.
Total the channels by month first.
Build three forecasts of monthly total sales:
A) Same month last year x growth of the previous 6 months vs a year earlier
B) Exponential smoothing with 12-month seasonality
C) 3-month moving average
Backtest each on the last 4 complete quarters. For each backtest quarter, fit using
ONLY data before that quarter starts, and print the last training month so I can
check. Report the error for each quarter and the mean absolute percentage error.
Then forecast Oct-Dec 2026 with the best method. Show your code.An illustrative first reply:
Mean absolute % error across 4 backtest quarters:
A) Seasonal x growth 1.1%
B) Exponential smoothing 0.9%
C) Moving average 21.8%
Best: B. Forecast Oct-Dec 2026: 208,900Errors under 1.5 per cent for a small shop's quarterly sales are too good to be true, and the printout shows why: the last training month for every backtest was September 2026. The assistant fitted on all the data, including the quarters it was testing. After pointing that out, the corrected run said:
Last training month: Sep 2025 (for Q4 2025), Jun 2025 (for Q3 2025), etc.
A) Seasonal x growth 3.9% (range -5.0% to +3.1%)
B) Exponential smoothing 4.6% (range -6.2% to +4.4%)
C) Moving average 21.8% (misses every seasonal peak)
Best: A, narrowly. Forecast Oct-Dec 2026: A 210,000; B 207,800That is a believable result. The simple method narrowly beat the more elaborate one, the moving average was useless for a business with a December peak, and the two sensible methods agree to within about 1 per cent. When two reasonable methods agree, you can stop fiddling with models and spend your time on adjustments.
Backtesting in a spreadsheet, if you'd rather see every number
You can run the same test by hand in 15 minutes. Pretend it is 30 September 2025: forecast October to December 2025 using only 2024's quarter and the growth rate from April to September 2025 (which was 9 per cent).
| Month | Forecast (2024 × 1.09) | Actual 2025 | Error |
|---|---|---|---|
| October | $41,420 | $41,000 | +1.0% |
| November | $56,680 | $57,500 | −1.4% |
| December | $104,640 | $101,500 | +3.1% |
| Quarter | $202,740 | $200,000 | +1.4% |
Do this for three or four past quarters and you have your own error range, which is far better than any rule of thumb. It also shows where the method struggles: here, December ran a little below forecast, which the owner traced to the sparkling wine stockout.
Adjusting for what the history cannot know
The baseline assumes next quarter behaves like last year's plus growth. Keep a short adjustments log so every change to the number is written down with its reason, and so you can check later whether your judgement helped or hurt. The wine merchant's log for the coming quarter:
| Adjustment | Amount | Evidence | In which case |
|---|---|---|---|
| Restaurant trade account lost (closed in August) | −$6,300 | Averaged $2,100 a month | All |
| 4% price rise on the house range from August | +$1,500 | Only two months of it are in the growth rate already | All |
| No sparkling wine stockout this year | +$4,000 | Last year's run rate before 12 Dec; stock now secured | Base and high |
| Corporate gift orders confirmed | +$6,500 | Three signed orders | All |
| Corporate gift pipeline | +$5,000 | Half of $10,000 in quotes out | High only |
Two things to avoid. Double-counting: the price rise started in August, so the recent growth rate already includes part of it; adding the full 4 per cent on top would overstate it. And adjusting for things that are only hopes. "We'll do more social media" is not an adjustment until it has moved a number.
AI is a useful sceptic here. Paste the log and ask: "For each adjustment, tell me whether it might already be reflected in the baseline, and what evidence would make it stronger or weaker." It regularly catches the double-counting problem, and it does not mind telling you your pipeline is optimistic.
The wine merchant's forecast, start to finish
Putting the baseline, the backtested error range and the adjustments together:
| Low | Base | High | |
|---|---|---|---|
| Baseline ($210,000) with backtest error | $199,500 (−5%) | $210,000 | $216,500 (+3.1%) |
| Lost trade account | −$6,300 | −$6,300 | −$6,300 |
| Price rise, remaining effect | +$1,500 | +$1,500 | +$1,500 |
| No stockout | $0 | +$4,000 | +$4,000 |
| Confirmed gift orders | +$6,500 | +$6,500 | +$6,500 |
| Gift pipeline | $0 | $0 | +$5,000 |
| Quarter forecast | $201,200 | $215,700 | $227,200 |
The low case uses the worst backtest error and assumes the stockout happens again; the high case uses the best error and half the pipeline. Split the base by month using last year's pattern (October 20.5 per cent of the quarter, November 28.75 per cent, December 50.75 per cent, then adjusted for the calendar as below), and by channel the same way, so the shop, online and trade teams each have their own number.
What the owner did with it: ordered stock to the base case, with a confirmed top-up option from two suppliers to reach the high case; booked seasonal staff for the base case plus one on call; and set the cash plan to survive the low case. A range is useful because each decision needs a different point on it.
Calendar effects that move months but not the quarter
Last year's monthly pattern assumes the calendar repeats, and it does not. October 2026 has five Saturdays against four in October 2025; November 2026 has four against five. The quarter has 13 Saturdays both years, so the quarterly total is unaffected, but the monthly split is not. If the shop takes about $1,900 on an average autumn Saturday and $450 on a weekday (illustrative figures from its till reports), trading one weekday for a Saturday is worth roughly $1,450. So the owner moved about $1,450 of the shop forecast from November into October.
Christmas Eve falls on a Thursday in 2026, against a Wednesday in 2025. For a wine merchant, the last full shopping weekend and which weekday the final collection day lands on matter more than the date itself. None of this needs a model; a calendar and your till's day-of-week report are enough. An assistant can do the counting reliably if you ask it to list the Saturdays in each month for both years, and it is worth checking its list against a real calendar, because date arithmetic is a known weak spot.
From one number to a number per channel
A single quarterly figure is fine for the bank; the people running each channel need their own. Splitting the base case by last year's channel mix, then applying each channel's own adjustments, gives the wine merchant this (rounded):
| Channel | Last year's share | Share of $210,000 baseline | Channel adjustments | Base forecast |
|---|---|---|---|---|
| Shop | 46% | $96,600 | +$2,900 (price rise share, no stockout) | $99,500 |
| Online | 29% | $60,900 | +$2,600 (price rise share, no stockout) | $63,500 |
| Trade | 18% | $37,800 | −$6,300 (lost account) | $31,500 |
| Events and gifts | 7% | $14,700 | +$6,500 (confirmed gift orders) | $21,200 |
| Total | 100% | $210,000 | +$5,700 | $215,700 |
Trade deserves one more step. With only a dozen or so accounts, forecast it account by account (last year's quarter for each, minus the closed restaurant, plus anything agreed) rather than as a percentage. One account doubling its order or leaving moves the channel far more than any trend.
How to tell whether the forecast is working
- Compare each month as it closes. If October lands within about 5 per cent of forecast, leave November and December alone.
- Re-forecast when a month misses badly. If October is 10 per cent under, ask why before changing anything. A late delivery of the autumn case offer is timing; a quiet shop across every week is a trend, so scale November and December down.
- Score your adjustments afterwards. In January, compare each log line with what happened. Owners are usually right about lost customers and too optimistic about new initiatives. Knowing your own bias improves the next forecast more than a better model.
- Keep the forecast and actual in one sheet. Three columns per month (forecast, actual, error) becomes, after a year, the best evidence you have about how far to trust your own numbers.
Where other businesses need a different starting point
- A specialty coffee roaster with subscriptions should forecast subscribers rather than sales: current subscribers, minus expected cancellations (from your monthly churn rate), plus expected sign-ups, times average order value. History of total sales hides the churn that decides next quarter.
- An online clothing shop should forecast gross orders and the return rate separately. A good season with a worse return rate can produce flat net sales, and the fix is different.
- A farm shop has produce seasons that shift by a few weeks each year with the weather. Forecast the quarter, not individual weeks, and treat the timing of the first strawberries or pumpkins as an adjustment rather than something the model knows.
- A business under two years old does not have enough history for seasonality. Use the months you have, borrow the seasonal shape from a similar business if you know one, and widen the range.
If you are not sure your file is ready for any of this, what to upload and check before AI analyses a sales spreadsheet covers the preparation. The ways forecasts fail are much the same for stock as for sales, and why AI stock forecasts go wrong lists eight of them. Once the quarter is running, explaining budget vs actual variances gives you the monthly routine, and a 13-week cash flow forecast turns the sales number into the question that matters most in a peak quarter: whether you can pay for the stock before customers pay you. For a wider view of how stock forecasting tools work, see AI inventory forecasting for small businesses.
Further reads
- What Is Cash Flow Forecasting? A Plain-English Guide With AI Examples — Turn the sales forecast into a cash forecast you can plan around.
- How to Use AI to Set Reorder Points and Prevent Stockouts — Use the forecast to set stock levels for the busy months.
- Capacity Planning With AI: Know When Your Team Is Full — Check whether your team can handle the forecast peak.
- Why Customers Cancel: How to Run a Churn Analysis With AI — For subscription sales, churn drives the forecast more than history.
- How Small Shops Can Use AI to Forecast Stock and Reorder — A shop-floor view of forecasting stock and reordering.
- How to Build a KPI Dashboard With AI When You Have No Data Team — Track actual against forecast every week on one screen.
- What Business Data Should You Start Collecting Now for AI? — Seven datasets worth capturing from today (enquiries, quotes, job actuals, questions, complaints, prices, feedback), with the fields that make them usable.
- How to Use AI for Scenario Planning: Best, Worst and Likely Cases — Build best, worst and likely cases from your own numbers, use AI to challenge the assumptions, and turn each case into triggers and pre-agreed actions.
- How to Build an Investor Pitch Deck With AI, and What to Check — Which parts of an investor deck to hand to AI and which to keep, slide by slide, plus the checks that catch invented market figures and mismatched numbers.
- Do You Need a Lot of Data to Use AI in Your Business? — How much data each common AI task needs, from three example emails to two years of sales history, and why scattered data is the bigger problem.
- Generative AI vs Traditional AI: Which Does Each Task Need? — How generative and traditional AI differ in what they need, cost and get wrong, four questions that sort any task, and a print shop's six tasks sorted.
- AI Menu Engineering: Which Dishes to Promote, Reprice or Drop — Run the classic menu engineering matrix with AI on your own till data, with a worked ten-dish example and a price-test calculation you can copy.
- How to Use Booking Data and AI to Fix Your Class Timetable — Five numbers per class slot, a prompt that finds the patterns, and a six-week test so timetable changes don't cost you regulars.
- How Florists Can Prepare for Valentine's and Mother's Day With AI — An eight-week countdown for florists: forecast, pre-order menu, stem maths, cut-off messages, card-message rules and delivery routes.
- How Clothing Boutiques Use AI to Spot Trends Before Buying Stock — Combine free trend tools with AI analysis of your own sell-through, then buy trend pieces at test depth. Includes prompts, sample outputs and a buy plan.
- Can AI Help a Small Shop Set Prices and Promotions? — How a small shop can use AI to test promotions against its own margins before running them, with the break-even maths and a pharmacy example worked through.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Microsoft Support pages for FORECAST.ETS and FORECAST.ETS.SEASONALITY (syntax, seasonality detection, missing-data handling, availability).