Twenty free monthly sales forecast templates for Microsoft Excel. A forecast is only useful if it is a genuine estimate of what will happen — which sounds obvious until you notice how many forecasts are really targets with a different label.
Three numbers that get used interchangeably and should not be. Keeping them separate is most of what makes forecasting work.
Number
What it is
Forecast
What you honestly believe will happen, given what you know today. It should move as evidence changes, and being under it is information rather than failure.
Target
What you want to happen. Set to motivate, often deliberately ambitious. A target is a decision; a forecast is an estimate.
Budget
What the business has committed to — the figure costs and hiring are planned against. Usually fixed for the year.
When a forecast is quietly set equal to the target, it stops predicting anything, and the business plans its spending against a wish. Keep them in separate columns and let them differ. The gap between forecast and target is one of the more useful numbers you can look at.
The templates
Twenty sheets, from simple monthly revenue grids to layouts broken down by product, price and quantity, including some covering 24 months for a rolling view.
Most forecasts use one of these, and larger businesses run two and compare.
Run rate from history. Take recent actuals and project forward, adjusting for growth and seasonality. Quick, and reasonable for a stable business with a track record. Useless for anything new.
Weighted pipeline. For businesses selling deal by deal. Multiply each open opportunity’s value by the probability of its stage, then total the lot. A 20,000 deal at a stage you close 25 per cent of the time contributes 5,000 to the forecast. It sounds crude and is substantially better than asking people how confident they feel.
Bottom-up by product. Units multiplied by price, line by line, summed. Most useful where you sell a defined catalogue, and the structure most of these templates follow.
Whichever you use, write the assumptions on the sheet. Conversion rate, average order value, expected units — a forecast whose derivation is invisible cannot be argued with or improved, which means nobody learns anything when it turns out wrong.
Handle seasonality deliberately
Dividing an annual figure by twelve is the commonest forecasting error. Almost no business sells evenly across the year — retail concentrates in the final quarter, many B2B sectors stall in summer, and anything weather-dependent swings hard.
If you have two or three years of history, work out what share of the year each month actually represented, and apply those shares rather than an even split. If you do not have history yet, say so in your assumptions and revisit after a year. Our moving average forecast templates are built for smoothing this kind of variation.
Check your own accuracy
This is the step that turns forecasting from paperwork into a skill, and almost nobody does it.
Add two columns beside each month: forecast and actual. Fill in the actual once the month closes and leave the original forecast untouched. After six months you will see your own bias, and it is almost always consistent in one direction.
Consistently over? You are forecasting optimistically, or counting deals that are not really close. Tighten your stage probabilities.
Consistently under? Either deliberate sandbagging, or your pipeline is better than you credit. Either way the business is planning on a number lower than reality, which has costs of its own.
Wildly variable? The method is not suited to the business. Try a different one of the three above.
A forecast that is reliably ten per cent high is more useful than one that is randomly right, because you can correct for a known bias.
Working in Excel
Keep assumptions in their own cells
Put growth rate, conversion rate and average order value in labelled cells at the top, then reference them in your formulas. Changing one assumption then updates the whole sheet, which is the entire point of doing this in a spreadsheet.
Never type a number you could calculate
Units times price should be a formula, not a typed total. Hard-coded figures are where forecasts quietly stop adding up after a few edits.
Chart it
Select your months and values, then Insert, Charts, Line. Seasonality, implausible jumps and hockey sticks are obvious in a chart and invisible in a column of numbers.
Keep each version
Save a dated copy each time you reforecast rather than overwriting. Comparing what you thought in January with what happened is where the learning is.
General information, not financial advice. Forecasting methods vary by industry and business model, and no template predicts the future. If a forecast is going to drive borrowing, hiring or investment decisions, have an accountant or finance adviser review the assumptions behind it.
Common questions
How far ahead should I forecast?
Twelve months rolling suits most businesses, with the next quarter in detail and later months coarser. Longer horizons are estimates and should be labelled as such.
How often should I update it?
Monthly for most businesses — when the month closes, add the actual and re-forecast the remainder. A forecast touched once a year is a budget.
What if I have no sales history?
Build bottom-up from activity you can estimate: leads, conversion rate, average value. State the assumptions explicitly, expect the first few months to be wrong, and revise as real numbers arrive.
Should the forecast include tax?
Forecast net of sales tax or VAT, since that money is not yours. Keep it consistent with how your accounts report revenue, and note on the sheet which basis you used.
Do I need Microsoft Excel?
No. Upload the file to Google Sheets or open it in LibreOffice Calc. Formulas and charts carry over in both.
Can I use these commercially?
Yes, including with clients. You may not resell or redistribute the template files themselves. See our Terms of Use.
Pingback: 19 Free Moving Average Forecast Templates for Excel
Pingback: Business Plan Templates for Startups, Loans & Investors
Pingback: Annual Budget Sheet Templates for Companies and Households
Pingback: Free Budget Worksheet Templates - Office Templates
Pingback: Amortization Schedule Templates: See Where Every Payment Goes
Pingback: Price List Templates and the Margin Arithmetic Behind Them