Sales Forecast Template: The Columns Your Spreadsheet Needs

Most sales forecast templates fail for a boring reason. They record what the pipeline looks like today and keep no memory of what it looked like last week. The number gets overwritten every Monday, and by week eight of the quarter nobody can reconstruct which deals moved, which ones slipped, and which ones quietly grew a new close date. This guide covers the fields a template needs and the structure that keeps it honest.

What should a sales forecast template contain?

Three tabs: deals, rates, and summary. The deal tab carries one row per open opportunity. The rates tab holds the conversion assumptions that turn those rows into revenue. The summary tab aggregates the result by period, segment, and owner.

Splitting these apart matters more than it sounds. When win rates live inside deal rows as hardcoded percentages, updating an assumption means touching hundreds of cells. When they live on a rates tab and get pulled in with a lookup, one edit updates the whole model. Separation also makes the assumptions visible to anyone reviewing the file, which is the first thing a CFO asks about.

Keep raw CRM exports on a fourth tab if you need them, and never build formulas that reference the export directly. Exports change column order without warning.

What columns should the deal tab have?

Twelve columns cover a B2B SaaS forecast. Everything else is decoration. Two of these do heavy lifting and get skipped in most templates. Original close date lets you count how many times a deal has moved. A deal on its third close date is a different risk than a deal on its first, and no CRM report shows you this by default. Last meaningful change date tracks activity that matters, which means a stage change, an amount change, or a close date change. Logged emails and calendar invites do not count.

How do you turn deal rows into a forecast number?

Multiply each deal by a rate you calculated from your own closed-won history, then subtract a slippage allowance.

Stage probability from your CRM's default settings is not an input, it is a placeholder someone set during implementation. Calculate real rates by pulling twelve months of closed opportunities and computing, for each stage, the share that reached closed-won. Do this by segment. Enterprise deals in late stage convert at rates that have nothing to do with SMB deals in late stage.

Then apply the correction most templates omit. Deals close for less than their pipeline value. A pipeline carrying an $80,000 average deal size against $40,000 in average closed-won value is a model that overstates every period by half, and the gap is invisible if you only look at coverage ratios. Compute your own ratio of closed-won amount to the amount recorded at the same stage a quarter earlier, and apply it as a value haircut.

The math for each row: amount, times stage conversion rate for that segment, times the value realization ratio. Sum by close-date period. Compare against your sales forecasting categories to see where the weighted math and the rep judgment disagree.

What belongs on the summary tab?

The period number, the same number broken three ways, and the change since last week.

Break the total by segment, by owner, and by forecast category. Each cut answers a different question. Segment tells you whether the quarter depends on a deal type you close reliably. Owner tells you whether one rep carries the number. Category tells you how much of the total rests on judgment rather than history.

Add a concentration line: the share of the forecast coming from the top three deals. Watch that share across quarters. Once the top three deals decide the period, the correct executive conversation is about those three deals rather than about coverage.

The change-since-last-week column is the most valuable cell in the file. It shows net movement, and the components of that movement matter more than the net. A flat total that hides $400,000 of slippage offset by $400,000 of pull-forward is a quarter in trouble.

How do you stop the template from going stale?

Snapshot before you refresh, always to a new tab, never over the old one.

Each Monday, copy the deal tab to a dated archive tab, then pull the new export. The archive is what makes deal slippage measurable. Without it, a deal that moved from March to June looks identical to a deal that was always a June deal.

Set a staleness filter on last meaningful change date. Any open deal untouched for 90 days should be flagged, and any deal untouched for twelve months should be excluded from the forecast entirely, whatever its stage says. Across our customer base, more than 10 percent of open pipeline has not been touched in twelve months. That share sits in the coverage ratio and inflates the sense of safety.

Also flag deals whose close date has been pushed twice. A rep changing a close date is the single best slippage signal available.

When does a spreadsheet template stop working?

When maintaining it costs more hours than the insight it produces.

Three signals mark the line. The deal tab passes a few hundred open opportunities and formula recalculation becomes a coffee break. More than three people edit the file and version conflicts start eating Monday mornings. Someone asks why a number changed and the answer takes two days to reconstruct.

A template is the right tool for one team forecasting one product in one currency. It stops being the right tool when the forecast has to explain itself to a board, because a spreadsheet cannot show why it produced a number. For the full sequence of building the underlying process rather than the file, see how to create a sales forecast.

The template is a starting structure. Its job is to make your assumptions explicit and your weekly movement visible. Once both are true, whether the file lives in Excel or somewhere else is a tooling decision, not a forecasting one.