Sales forecasting for small businesses: a practical guide

The forecast that lives in someone's head

Ask the owner of a small business what they expect to sell next quarter and you will usually get a confident answer. Ask where that number comes from and the answer gets vaguer. It lives in their head, or in a tab of a spreadsheet that only one person opens.

That is not a criticism. Most SMEs I work with in Leeds and across Yorkshire run perfectly well on instinct for years. The trouble starts when the business grows past the point where one person can hold it all: stock gets ordered too early, a seasonal dip catches everyone out, or the bank asks for a cash flow forecast and nobody trusts the sales line underneath it.

This guide is the method I use with clients. It needs no special software, works in Excel or Google Sheets, and can be built in an afternoon from data you already have.

What a sales forecast is actually for

A forecast is not a target and it is not a promise. It is your best estimate of what will happen if nothing unusual changes, so that you can make decisions before the numbers arrive. In practice that means three things: how much stock or staff time to plan for, how much cash will come in and when, and whether you are on track early enough to do something about it.

If your forecast does not feed at least one of those decisions, it is a report, not a forecast. This is the same principle as in The 5 Metrics That Actually Matter: a number earns its place only if it changes what you do on Monday.

Step 1: Get two years of clean monthly sales

Start with history. Export your sales from your accounts package, till system or ecommerce platform, and get it into one simple table: month, product or category, units, revenue. Two years is the useful minimum, because you need to see each month at least twice to tell seasonality from luck.

Expect this to be the slowest step. Duplicate customers, refunds booked as sales and categories that changed name halfway through are normal. If your data is spread across several spreadsheets and email attachments, the 30-day data starter kit walks through getting it into shape first.

Keep the level of detail sensible. Forecast your top ten products or your main categories individually and group the long tail together. A forecast for 400 individual lines is mostly noise.

Step 2: Build a baseline before anything clever

The most useful forecast you can build is also the simplest. A seasonal naive forecast says next October will look like last October. That is it.

It sounds too basic to be worth writing down, but it matters for two reasons. First, it captures seasonality, which is the biggest pattern in most small business sales. Second, it gives you a benchmark. The forecasting textbook by Rob Hyndman and George Athanasopoulos recommends these simple methods as the yardstick any more sophisticated approach has to beat. If your elaborate model cannot beat "same as last year", it is not worth the effort.

A slightly better version adjusts for growth. Take the same month last year and multiply it by your recent year-on-year trend, for example the last three months compared with the same three months a year earlier. In a spreadsheet that is one formula per row.

Step 3: Layer in what you know that the data does not

History cannot see the future, but you can. This is where the owner's instinct becomes useful, as long as it is written down as an explicit adjustment rather than mixed silently into the numbers.

• Price changes. A rise in January will move both revenue and, often, volume.
• Known events. A new contract signed, a large customer lost, a trade show, a product launch.
• Capacity. If you can only produce or deliver so much, the forecast cannot exceed it, whatever demand looks like.
• Calendar effects. Easter moves between March and April, and some months have an extra trading week.

Keep each adjustment as its own line next to the baseline, with a short note explaining it. When the forecast turns out wrong, you will want to know whether the baseline or the assumption was to blame. Business Wales makes the same point about balancing what history says with what you know is coming, especially seasonality.

Step 4: Give it a range, not a single number

A single number invites false confidence. Instead, produce three: a likely case, a cautious case and a strong case. For most SMEs a simple approach works well enough. Look at how far your past forecasts or past months have typically strayed from the baseline, and use that spread to set the range.

If you work in Excel, the built-in Forecast Sheet feature will produce a seasonal forecast with confidence bounds in a few clicks. It is a decent second opinion against your own baseline.

The cautious case is the one to plan cash against. The strong case is the one to check stock and staffing against, so you are not caught short if things go well.

Step 5: Measure how wrong you were, every month

This is the step almost everyone skips, and it is the one that makes a forecast improve over time. At the end of each month, put the actual figure next to the forecast and record the error as a percentage.

Track the average absolute error over the last six months. A useful introduction to forecast accuracy measures covers the options, but for a small business the mean absolute percentage error is easy to explain to anyone in the room. Then ask one question: is your forecast consistently beating the simple seasonal baseline? If not, simplify.

1. Record the forecast before the month starts. Never adjust it after the fact.
2. Compare against actuals within a week of month end. Memory fades quickly.
3. Note the reason for any big miss. Over a year these notes become your best adjustment guide.

Where sales forecasts usually go wrong

• Forecasting the target. If the forecast always equals the budget, it is not a forecast.
• Too much detail. Forecasting every product line separately creates noise and work without accuracy.
• No owner. If nobody is responsible for updating it monthly, it will be out of date by spring.
• Ignoring the cost side. Sales forecasts are most valuable when they drive purchasing and staffing. If you are under pressure to cut costs, a good forecast is often worth more than a restructure, as I argued in The Smarter Way to Cut Costs.

When a spreadsheet stops being enough

For most small businesses, a well-built spreadsheet is the right tool for years. It stops being enough when you have hundreds of products with short shelf life, several sales channels with different patterns, or a forecast that someone spends half a day updating every week.

At that point a small, purpose-built forecasting tool that reads straight from your accounts or sales system can pay for itself quickly. This is exactly the kind of process I described in Buy the standard parts, build the part that is yours: keep the bought software, and build a thin layer for the logic that is specific to you. If you are new to the idea of using data this way, Data Analytics for SMEs is a good place to start.

Frequently asked questions

How far ahead should a small business forecast sales?

Twelve months, broken down by month, is right for most SMEs. Review the next three months in detail every month and treat anything beyond six months as a rough guide.

What if I do not have two years of sales history?

Use what you have and lean more on explicit assumptions: enquiries, conversion rates, average order value and capacity. Replace assumptions with real data as each month comes in.

Do I need special forecasting software?

No. Excel or Google Sheets is enough for most businesses with a few dozen products. Software becomes worth it when updating the forecast takes hours each week or accuracy is costing you real money in stock or missed sales.

How accurate should a sales forecast be?

There is no universal number. A good test is whether your forecast beats the simple "same month last year, adjusted for trend" baseline. If it does, and the error is shrinking over time, it is working.

Start with this month

You do not need to build all five steps at once. Export your last two years of sales this week, build the seasonal baseline, and write down your forecast for next month before it starts. Compare it with what actually happens. That single habit puts you ahead of most businesses your size.

If you would rather have someone build it with you, or you want a forecast that updates itself from your systems, have a look at my data analytics services for SMEs. I am based in Leeds and happy to look at your numbers and tell you honestly whether a spreadsheet will do.