Sales Forecast: Beat Last Month’s Number First

A free forecasting textbook gives one test for any method, from a spreadsheet trend line to a machine-learning model: it has to beat a few deliberately simple methods, or it is not worth considering. The book is Forecasting: Principles and Practice, and the simple methods it names are not clever. This playbook is that test, set up for an owner with a spreadsheet and a couple of years of monthly sales.

Move 1: Write down the easy forecasts first

The textbook’s simple methods come down to three you can build in one column each. The naive forecast says every future month equals the most recent one. The seasonal naive forecast says each month equals the same month a year earlier. The drift forecast takes the last value and adds the average change per period, which is the same as extending a straight line from your first month to your last (source).

Then hide some history. The same book says accuracy should be judged on data the method never saw, with a test set typically about 20 percent of the sample and ideally at least as long as the furthest-out forecast you need (source). With 24 months of sales, that is a bit under five months, so hold back the last six and keep the earlier 18 for fitting. Put the three easy forecasts next to the six hidden months.

Move 2: Split sales into rows that match your books

A forecast of one total hides why you missed. Tim Berry’s SBA guide says to split sales into units and price per unit, so a miss shows up as a price problem, a volume problem, or both, and to use natural divisions such as sales channel or major product category rather than 75 menu items (SBA, August 2016). It also says to line your forecast rows up with the rows on your profit and loss statement, exactly so if you have twenty or fewer.

For most owners that means three to six rows. A landscaper might use design jobs, maintenance contracts and one-off installs. Pull each row’s monthly totals from the same bookkeeping export you already use for taxes.

Move 3: Find the number that moves before sales do

The same SBA guide tells owners to look for their specific sales drivers, such as foot traffic, web traffic, or the lead-to-close pipeline, and to measure them (SBA). If you sell by quote, that driver is open quotes.

HubSpot’s forecasting glossary describes the pipeline version: give each deal stage a probability, then sort deals into commit, best case and pipeline groups (HubSpot). The page does not tell you what probabilities to use, so use your own. Our suggestion: for each stage, divide the quotes that reached it and were later won by all quotes that reached it, using your last 6 to 12 months of closed quotes. Multiply each open quote’s value by its stage’s number and add them up. That total is a pipeline forecast built from your history, not a vendor’s default.

Move 4: Let a spreadsheet draw the line

Two common options, with the price of the plan that carries them:

• Excel Forecast Sheet. On the Data tab, in the Forecast group, choose Forecast Sheet. Microsoft says the timeline needs consistent intervals, seasonality is detected automatically, you should have at least two full cycles of history if you set it by hand, and the default confidence level is 95 percent (Microsoft support). Microsoft’s developer reference says the method is the AAA version of exponential smoothing. Microsoft 365 Business Standard lists at $23.50 per user per month paid yearly and includes desktop, web and mobile Excel; Business Basic at $7.00 lists web and mobile versions only (Microsoft pricing), so confirm the feature is in your version before you buy for it.
• Google Sheets FORECAST. Google’s help page says the function calculates the expected value from a linear regression of your data, written as FORECAST(x, data_y, data_x) (Google help). A straight line cannot see a summer peak, so for a seasonal business treat it as a drift-style benchmark rather than the answer. Google Workspace Business Starter lists at $7 per user per month (Google Workspace pricing).

The third option is the free one from Move 1. Keep it in the sheet.

Move 5: Score every method on the hidden months

Add up each method’s misses in dollars, ignore whether it was too high or too low, and divide by the number of months. The textbook calls this mean absolute error and notes it is easy to understand and compute. It warns that percentage errors become undefined when an actual value is zero and extreme when values sit close to zero (source), which is a real risk for a business with a dead month, so score in dollars.

Here is a hypothetical, with invented numbers, for a countertop installer. Sales in thousands of dollars for the six hidden months, April through September, were 41, 44, 52, 47, 43 and 40, and March was 38. The same six months last year were 40, 45, 50, 48, 44 and 41.

• Naive (next month equals last month): forecasts of 38, 41, 44, 52, 47 and 43 miss by 3, 3, 8, 5, 4 and 3. That adds to 26, so the average miss is about $4,333.
• Seasonal naive (same month last year): misses by 1, 1, 2, 1, 1 and 1. That adds to 7, so the average miss is about $1,167.

Now anything fancier has a number to beat. If the Forecast Sheet line misses by more than $1,167 on the same six months, it lost, and the one-cell guess stays. That is the textbook’s rule, not ours. Notice what it also does: it converts “the model feels right” into a dollar figure you can argue with.

Move 6: Review it monthly, with the forecast written down in advance

The SBA guide says to review the forecast at least once a month, compare it with actual results, and investigate both the good and the bad surprises. Add one habit: each month, paste the forecast into a column labeled with the month it was made, before the month begins. You cannot score a forecast you rewrote after seeing the result.

A sales forecast is also not a cash forecast. Sales booked in one month often arrive in the next, and our receivables playbook shows how much of the wait is set by your own payment terms. Our weekly cash flow playbook turns the sales number into the Friday check that tells you whether payroll clears.

Where an AI prompt fits

BusinessPrompter’s Sales Forecasting Model prompt is listed under Marketing and Sales with the summary “Build sales forecasting models for accurate revenue predictions.” It is a Pro prompt behind an upgrade, so we could not read its steps and we do not describe what it outputs. The page’s own description text talks about a strategic transformation plan for market shifts rather than a sales forecast, so check the preview before you pay. You can browse the rest of the library at BusinessPrompter.com.

Two uses of any chat assistant that we would suggest, and that are our suggestions rather than anything the prompt page promises: ask it to explain, in plain words, a column in the Forecast Sheet statistics table you do not understand, and ask it to draft the questions for your monthly review. Paste monthly totals only, not customer names or invoices, and make the call yourself. The assistant drafts; you decide.

Limits

A Harvard Business Review piece by Lou Shipley (August 28, 2019) argues that alignment between sales and marketing is a prerequisite for forecast accuracy; we could read only its public summary, so we cannot tell you what the rest of the article recommends (HBR). A business under two years old does not have enough history for the seasonal method, and with no history the pipeline method in Move 3 is the one to use. Any method misses a flood, a lost anchor client or a competitor’s price cut.

Our opinion: most of the value here is the comparison, not the model. A written-down guess scored every month teaches you more about your business than a clever tool you never check.

Open your monthly sales next to last year’s. If you had forecast this year’s months as “the same as last year’s,” or “the same as last month’s,” which would be closer so far, and by how many dollars?