Sales Forecast Template: A Free Google Sheet With Win Rates From Real Pipeline Data
Publish date: Nov 9, 2026
A sales forecast template turns two inputs into one number for next quarter: today's open deals and the deals you closed in recent months. This free Google Sheet forecasts the quarter three ways on the same pipeline: stage-weighted, history-based and run rate. The gap to target sits beside each. Make a copy of the Sheet, type both, and the Forecast and Summary tabs fill in.
The Win rates tab comes loaded with Spona's own history: 344 closed opportunities over four recent months, split into won, lost and no decision. Those counts show why the same pipeline can forecast two numbers 25% apart, depending on how you count a win. The sheet is in English and recalculates in Google Sheets, Excel and LibreOffice.
Get thousands of leads in 4 minutes.
Stop stitching tools together.
Start with verified sales data, delivered in minutes.
No contracts · No setup · No onboarding
Sales forecast template: three methods side by side
Every forecast is a multiplication. The method is what you multiply, and by what. The sheet runs three methods on the same open deals, plus two totals the rep calls by hand. A disagreement shows up before the quarter does.
| Method | What it multiplies | Where it lives in the sheet | When it misleads |
| Stage-weighted | Each open deal's Amount by the Probability of its Stage | Forecast, column Stage-weighted | Probabilities are borrowed guesses until your own closed deals replace them |
| History-based | Open value closing in the quarter by your win rate | Forecast, two History-based columns | This quarter's pipeline looks nothing like the months you measured |
| Run rate | Won value per month by three | Forecast, column Run rate | A peak month becomes the whole year |
| Commit | Nothing: the deals the rep promises, added up | Forecast, column Commit | Reps promise what they hope |
| Commit + Best case | Nothing: Commit plus the deals that could still land | Forecast, column Commit + Best case | A moved close date hides inside Best case |
Stage-weighted is HubSpot's default. HubSpot defines the weighted amount as the total amount in each stage multiplied by the stage probability. Its default pipeline runs from 20% at Appointment scheduled to 90% at Contract sent.
HubSpot's forecast tool uses the weighted amount by default. The sheet starts with those five defaults and swaps in your own rate per stage once enough deals have closed.
Commit and Best case are the rep's judgment, not arithmetic. Microsoft's Dynamics 365 defines Committed as a verbal or contractual commitment with high confidence. Best case is medium confidence with no commitment yet, and Omitted leaves every total.
HubSpot's default categories are Commit, Best case, Most likely and Pipeline. The sheet uses Commit, Best case, Pipeline and Omitted, and only Omitted drops a deal from the totals.
Run rate is the simplest method and the easiest to fool. AccountingTools defines it as the extrapolation of financial results into future periods. Its example turns a $5,000,000 quarter into a $20,000,000 annual run rate. The actual year came in at $15,000,000.
The sheet computes run rate in deals from your closed counts, and in revenue once you add Won value per month.
What is the best forecasting method for sales?
The best method is the one your own closed deals can check. Start with history-based: open value closing this quarter times the share of closed deals you won. Nothing in it is a guess except the close dates.
Then read stage-weighted beside it. If the two sit within 20% of each other, your stage probabilities are roughly honest. The Forecast tab prints that comparison on row 10 and names the fix when the gap is wider.
Run rate is the sanity check, not the forecast. If history-based runs well above run rate, your pipeline is fuller than usual, or your close dates are optimistic. Commit is the number you report upward once the other three agree. Treat Best case as the list of deals to work, not as revenue.
Sales forecast spreadsheet: five tabs, five steps
To create a sales forecast you need three things. The first is every open deal with an amount, a stage and an expected close date. The second is your closed deals per month for recent months, and the third is a target. The sheet has five tabs, one per step. Make a copy of the Sheet and work through them in order.
- Instructions: set six settings. Quarter start, Quarter target and As-of date matter most. Three more set the rules. An open deal counts as stale after 7 days without activity. The forecast reads six months of history, and a stage needs 10 closed deals before your own rate replaces the default. The dropdown lists for Outcome, Forecast category, Owner and Code outcome live here too, so you edit them in one place.
- Pipeline: one row per deal. You type eleven columns: Deal, Owner, Amount, Stage, Expected close, Forecast category, Next step, Last activity, Close date moves (n), Outcome and Reason code. Six grey columns compute themselves: Stage no., Probability, Weighted amount, Close month, In forecast quarter and Check. Stage and Reason code are dropdowns from a range that reject anything off the list; Owner only warns. If your deals sit in our CRM template, copy Amount and Deal stage across, with Decision date as Expected close. Then clear the 15 example rows.
- Win rates: your history, your codes, your stages. Type closed deals per month as Won (n), Lost (n) and No decision (n), oldest first, one month per row. Closed (n), Won / won + lost and Won / all closed compute per month and for the months used. Below the table, the sheet turns that history into a quarter in deals: closed per month, closed in a quarter, and wins under each rate. Lower down sit the ten reason codes and the stage table with HubSpot's default probabilities. Replace Spona's four example months with yours; the formulas do not change.
- Forecast: three months, six methods. Each row is a month of the quarter. For every open deal whose Close month falls in it, SUMIFS adds the Amount and the Weighted amount, and COUNTIFS counts the deals. The two History-based columns multiply the open value by each win rate. Commit and Commit + Best case add the amounts by Forecast category. The Quarter row sums the three months, and row 10 compares stage-weighted with history-based.
- Summary: the page you share. The methods table shows each Quarter forecast, its Gap to target and Share of target. Coverage tells you how much pipeline you hold against the target and how much you need at your win rate. Deal checks counts every flag on the Pipeline tab. The Weekly forecast log on the right holds the numbers you paste each Monday, and an error line once the quarter's actual is in.
Formulas use English function names. Google Sheets keeps them when you tick Always use English function names; the locale setting changes currency, date and number formats. On the Win rates tab, SUMPRODUCT does the month weighting, and on Pipeline, INDEX and MATCH look up each deal's stage probability. No QUERY, no scripts, no add-ons, so the file opens in Excel through File, Download.
Worked example: next quarter from Spona's closed deals
Spona's four example months are real. Each opportunity counts once, in the month it closed, and open opportunities stay out. Jesse Chan at Spona confirmed which reason codes count as won, lost and no decision.
| Month | Closed | Won | Lost | No decision | Won / won + lost | Won / all closed |
| Month 1 | 73 | 48 | 11 | 14 | 81.4% | 65.8% |
| Month 2 | 91 | 62 | 12 | 17 | 83.8% | 68.1% |
| Month 3 | 82 | 54 | 10 | 18 | 84.4% | 65.9% |
| Month 4 | 98 | 63 | 15 | 20 | 80.8% | 64.3% |
| All four | 344 | 227 | 48 | 69 | 82.5% | 66.0% |
Two win rates come out of the same counts. Won / won + lost is 82.5%, because it drops the 69 no-decision deals from the denominator. Won / all closed is 66.0%. Neither rate is a benchmark for your team; the gap between them is the point.
Computed from those counts, Spona closed an average of 86 opportunities a month, or 258 a quarter. At 66.0%, the history forecasts 170 wins. At 82.5%, it forecasts 213.
That is 43 more wins, or 25.1%, from the same deals and the same pipeline. The only change is whether a deal that ended with no decision counts as closed.
No decision is not a rounding error. It was the outcome of 69 of the 344 closed opportunities. Of the 117 deals Spona did not win, 59.0% ended without a decision rather than with a loss.
Dixon and McKenna analyzed 2.5 million recorded sales conversations for their JOLT research. They found that 40 to 60% of lost deals ended in no decision. A Won-and-Lost history hides every one of them.
The Pipeline tab's example rows are made up. Nine open deals close in the quarter, with 198,000 in open value against a Quarter target of 150,000. The amounts carry no currency, so type yours in whatever you sell in.
Stage-weighted, on HubSpot's defaults, forecasts 125,400. History-based at Spona's won / all closed rate forecasts 130,657. The two sit 4.0% apart, so the Forecast tab prints Methods agree within 20%.
Switch to won / won + lost and the forecast jumps to 163,440, the only method that beats the target. Commit totals 57,000 and Commit + Best case 129,000, both short.
Volume moves more than the rate. Spona's closed count ranged from 73 to 98 a month. That is a 34% swing. The won share moved only between 64.3% and 68.1%.
So the forecast's risk sits in how many deals close, not in the rate you multiply by. Watch the Open deals closing (n) column on the Forecast tab as closely as the value.
Keeping the sales forecast honest: stage rules, slips, weekly updates
A forecast sheet is only as honest as the three fields reps type into it: Stage, Expected close and Forecast category. Three rules keep them honest, and the Check column enforces most of them.
Stage rules that hold up
Write an exit rule for every stage, one the buyer has to do, not the rep. Presentation scheduled means a date in the buyer's calendar. Decision maker bought-in means the economic buyer said yes on a call.
Our guide to the B2B sales pipeline shows how to write them. Without exit rules, a 60% probability means the rep feels good.
Commit needs a named buyer. In Spona's data, No active buying owner was the primary reason on 28 of the 69 no-decision deals. A deal with nobody who can sign is Best case at most, whatever its stage. The MEDDIC economic buyer question belongs before the category changes to Commit.
Let the defaults go. The stage table on the Win rates tab counts the closed deals that reached each stage and the wins among them. Once a stage has 10 closed deals, Probability used switches from HubSpot's default to your own rate, and the Based on column says so. With the example rows, every stage still shows Default: fewer than 10 closed deals.
Slip rules for moved close dates
A moved close date is one of the strongest warning signs a sheet can give. Ebsta's benchmark of $57bn in analyzed revenue found that win rates fall by 83% when deals slip. A slip of one to four weeks alone cuts the win rate by 34%.
Ebsta also reports that the share of deals slipping past their close date dropped from 44% to 36% in a year. Ebsta reads the drop as a sign of tighter deal control.
The sheet has two slip rules. Add 1 to Close date moves (n) each time a date moves. On a Commit deal's second move, the Check column prints Moved twice: not Commit, and the deal needs a new category.
When Expected close is earlier than the As-of date, it prints Close date passed. A deal cannot sit in the forecast with a close date in the past.
The third rule is about closing deals, not moving them. Project deferred was the code on 41 of Spona's 69 no-decision deals. When a buyer defers, close the deal as No decision with its reason code instead of moving the date a third time.
Our win loss analysis post shows how to code every closed deal with one primary reason. The Check column flags a code that does not fit the outcome.
A weekly update routine
Xactly surveyed 405 North American sales and finance leaders about their forecasts. Weekly updates were the first choice for 41% of them, monthly for 26%. Daily came third at 22%.
HubSpot's forecast tool can flag reps who have not updated their forecast, and that indicator defaults to eight days. The sheet's stale rule is 7 days for the same reason: a deal untouched for a week is a deal nobody is working.
The Monday routine takes five entries per rep. Type the day's date into As-of date on Instructions. Check Stage, Expected close and Last activity on every open deal. Add 1 to Close date moves (n) where a date moved.
Set Outcome and Reason code on every deal that closed. Then paste the Quarter row from Forecast into the Weekly forecast log on Summary.
The log is what makes the sheet improve. When the quarter ends, type the result into Actual won this quarter. The error line then shows how far each method was off in the first logged week. After two quarters you know which method to trust for your team.
How much pipeline the quarter needs
Coverage is open value closing in the quarter divided by the target. The coverage you need is one divided by your win rate.
The 3x rule of thumb assumes a 33% win rate. On Salesforce's blog, Jason Jordan says it works only when your win rate is exactly 33% and your sales cycle is exactly one year. A 10% win rate needs 10x the target in pipeline. A 50% rate needs 2x.
His example starts from a $100,000 target and a 25% win rate. Dividing by the number of 60-day sales cycles in a year gives $65,757 of pipeline.
At Spona's won / all closed rate of 66.0%, the quarter needs 1.52x the target in open value. At the won / won + lost rate of 82.5%, it needs only 1.21x. The lower rate is the honest one, because a deferred project is still a deal you did not win.
The Summary tab does the arithmetic. The example pipeline holds 1.32x the target and needs 1.52x. The Status line reads Short of pipeline: add 29,313.
Before you buy that pipeline, check what a lead is worth to you with our lead value calculator. At the example's average deal of 22,000, that gap is two more opportunities, not twenty.
Where Spona fits when pipeline runs short
Spona is not a forecasting tool. It does not read your pipeline, set your win rates or roll forecasts up a team. If you run HubSpot or Dynamics 365, their forecast tools are stronger than any sheet for roll-ups and forecast submissions. HubSpot's even starts from the same weighted amount this template uses.
Where Spona fits is the Status line. When Summary says Short of pipeline, the gap is new opportunities, and that is what Spona builds.
You describe your ideal customer in a chat, and Spona returns a verified, scored lead list. You pay per lead, from $0.008 to $2.00, depending on the search type and plan. Every search starts with a free sample of leads. A HubSpot integration is available for teams whose deals live in HubSpot.
The limits are plain. Spona delivers the top of the pipeline, not the stages after it. The leads still need a first call, an exit rule per stage and a Monday update like every other row. And the lead price buys contacts, not a win rate: your history, not Spona's, sets the Probability used.
Get thousands of leads in 4 minutes.
Stop stitching tools together.
Start with verified sales data, delivered in minutes.
No contracts · No setup · No onboarding
Sales forecast template FAQ
How do you do a sales forecast in Excel?
Download this sales forecast template as an .xlsx file through File, Download, Microsoft Excel. Its formulas use only standard functions such as SUMIFS, COUNTIFS and SUMPRODUCT. Excel recalculates them without edits.
For a trend forecast of monthly revenue, Excel also has a Forecast Sheet on the Data tab. It uses the AAA version of exponential smoothing with a 95% confidence interval by default. It needs consistent intervals between data points and tolerates up to 30% missing points.
That projects a line from past totals; it cannot see your open deals. Google Sheets' FORECAST function does the same with linear regression. Use either for a monthly trend, and this template for the quarter in front of you.
What is the formula for a sales forecast?
Three formulas cover most teams. Stage-weighted: the sum of each open deal's amount times its stage probability. History-based: open value closing in the period times your win rate, with won divided by all closed deals as the rate. Run rate: won value per month times the months in the period.
The sheet runs them as a SUMIFS on Weighted amount, a SUMIFS on Amount times the win rate, and Won value per month times three.
How accurate are sales forecasts?
Less accurate than most teams admit. In Xactly's survey, 4 in 5 leaders missed a quarterly forecast in the past year, and over half missed twice or more. 52% of sales leaders said their forecast was off by 10% or more.
Gartner found that only 45% of sales leaders and sellers have high confidence in their organization's forecast accuracy. The Weekly forecast log in this sheet records each week's number, so your own error rate stops being a guess.
How much history does the template need?
The Months of history to use setting starts at 6, and the Used in forecast column marks the latest months up to that number. Spona's four example months are enough to show the method; your own history should cover at least a quarter.
A team that closes a handful of deals a month should type a year. Then set Months of history to use to 12. Stage probabilities switch to your own rate only after 10 closed deals have reached a stage. Small teams keep the defaults longer.
Can you use the template without a CRM?
Yes. The Pipeline tab is the CRM for the forecast: one row per deal with the eleven typed columns. If you have no deal list at all, start from our CRM template and keep the two Stage lists identical. Expected close matters most, because the Forecast tab reads deals by Close month.
Share this article
GET FREE LEADS
