This is a sales forecast template for Excel or Google Sheets that sets each deal's forecast category from the evidence the buyer has given. Copy the columns and formulas below into a blank sheet. It is built to support the weekly forecast call and sits under our deal risk guide.
The template
Sheet 1: Deals (one row per open deal, row 1 is the header)
| Col | Header | What goes in it |
|---|---|---|
| A | Rep | Deal owner |
| B | Deal | Account and opportunity name |
| C | Amount | Deal value |
| D | Close date | Expected close date |
| E | Buyer's reason for the date | The event on the buyer's side driving the date; blank if none |
| F | Signer heard the case | Y or N |
| G | Cost in the buyer's words | What a buyer said the problem costs; blank if no buyer has said |
| H | Approval steps mapped | Y if legal, security and procurement steps are known with names |
| I | Buyer-owned next step | What the buyer will do, who, by when |
| J | Rep's call | Commit, Best case or Pipeline, as the rep sees it |
| K | Evidence category | Formula (below) |
| L | Missing | Formula (below) |
| M | Next buyer conversation | Date of the conversation that fills the first gap |
Formulas (row 2, fill down)
K2 =IF(AND(F2="Y",G2<>"",H2="Y",E2<>""),"Commit",IF(AND(F2="Y",G2<>""),"Best case","Pipeline"))
L2 =TEXTJOIN(", ",TRUE,IF(F2<>"Y","signer",""),IF(G2="","cost",""),IF(H2<>"Y","approval steps",""),IF(E2="","buyer date",""))
TEXTJOIN works in Google Sheets and in Excel 2019 or later.
Sheet 2: Roll-up
Evidence commit =SUMIF(Deals!K:K,"Commit",Deals!C:C)
Evidence best case =SUMIF(Deals!K:K,"Best case",Deals!C:C)
Rep-called commit =SUMIF(Deals!J:J,"Commit",Deals!C:C)
Belief gap =Rep-called commit - Evidence commit
Snapshot week (type the week, for example Week 4)
Snapshot commit (paste the Evidence commit value at the snapshot; do not link it)
Closed won (period) (enter at period end)
Accuracy =MAX(0,1-ABS(Closed won - Snapshot commit)/Closed won)
Commit-to-close =committed deals closed in period / committed deals at snapshot
The accuracy line follows the formula HubSpot uses in its forecast tool. The category rules are our operating rules; if you run MEDDPICC or another method, swap columns E to H for its fields.
A filled-in example
This example is illustrative.
| Rep | Deal | Amount | Buyer's date reason | Signer heard | Cost stated | Steps mapped | Rep's call | Evidence category | Missing |
|---|---|---|---|---|---|---|---|---|---|
| Ana | Brenlow renewal | $80,000 | Contract ends 30 Nov | Y | "$200K a year in rework" | Y | Commit | Commit | |
| Ana | Harborview Supply | $55,000 | Y | "Two FTEs on manual entry" | N | Commit | Best case | approval steps, buyer date | |
| Ben | Mossbank Health | $120,000 | Budget closes 15 Dec | N | "Audit findings in Q2" | N | Commit | Pipeline | signer, approval steps |
| Ben | Kelvara | $30,000 | Go-live in January | Y | "Losing two deals a month" | Y | Best case | Commit | |
| Cara | Cragmoor Logistics | $65,000 | N | N | Commit | Pipeline | signer, cost, approval steps, buyer date | ||
| Cara | Alderwick Labs | $40,000 | Board meets 10 Dec | Y | "Churn at 9%" | N | Best case | Best case | approval steps |
Rep-called commit is $320,000. Evidence commit is $110,000. The belief gap is $210,000, and most of it sits in two deals: Mossbank Health, where the signer has not heard the case, and Cragmoor Logistics, where every evidence column is blank. Those two get coaching calls this week. Kelvara is the reverse case: the rep is under-calling a deal the evidence supports, which is worth a conversation too.
Setting it up in ten steps
The sheet takes about as long to build as one forecast call, and most of that is the drop-downs.
- Create a blank workbook with two tabs, named Deals and Roll-up, so the formulas above work as written.
- Type the headers from the table into row 1 of Deals, columns A to M.
- Add a drop-down to columns F and H with two values, Y and N. In Google Sheets this is Data, then Data validation; in Excel it is Data, then Data Validation, then List.
- Add a drop-down to column J with Commit, Best case and Pipeline, so every rep spells the categories the same way.
- Paste the K2 and L2 formulas and fill them down past your longest deal list.
- Protect columns K and L so only the sheet owner can edit them.
- Add a highlight rule that colors the row when J and K differ. That row is a conversation to have.
- Build the Roll-up tab from the lines above.
- Load the open deals from your CRM export: rep, deal, amount and close date. Leave the evidence columns empty.
- Fill the evidence columns with each rep in their next one-on-one, from what buyers said. Expect a lot of blanks the first week. The blanks are the forecast's real state.
Adding a weighted pipeline line
Some teams also want a stage-weighted number next to the evidence commit. Add column N, "Stage win rate", and fill it from your own closed deals: of the deals that reached each stage in recent quarters, the share that closed. Then add one line to the roll-up:
Weighted pipeline =SUMPRODUCT(Deals!C2:C500,Deals!N2:N500)
Use your own history for the rates. A rate copied from someone else's team describes their buyers and their stages. Read the weighted number next to the evidence commit, not in place of it: the weighted line tells you what history says a pipeline like this usually produces, and the evidence line tells you which deals have the buyer behind them.
How to use it
Use the spreadsheet by filling the evidence columns from what buyers said, updating it weekly before the forecast call, and freezing one snapshot per period.
- Fill the evidence columns from what buyers said. Each cell in E to H should trace to a call or an email. If the rep cannot say who said it and when, leave it blank.
- Let the formula set the category. The rep still enters a call in column J. Do not overwrite column K.
- Run the forecast call from the sheet, in the order below, so the meeting spends its time on the deals that move the number.
- Freeze the snapshot. At week 4 (or your chosen week), paste the evidence commit as a value. At quarter end, enter closed won and read accuracy and commit-to-close. How to improve forecast accuracy covers what to do with the slips.
The forecast call runs in this order:
- The belief gap, by rep. One line each. Nobody defends a deal yet.
- The two or three deals that make up most of the gap. These are the largest deals where column J says Commit and column K does not. For each one, the rep says what the buyer has said, and the team agrees what is missing from column L.
- Column M for those deals. Each gets a named buyer conversation and a date before the call ends; a gap with no date is not being worked. That week the rep and manager (or the coach) take a 20-minute call on each, and the rep talks the deal through and leaves with an opening line for the buyer conversation, said out loud.
- Any deal the rep is under-calling. Like Kelvara in the example, the buyer is further along than the rep's call says, and the deal deserves attention before something changes.
- Last week's dates. Did the conversations in column M happen, and what did the buyer say?
Where this template fits among forecasting methods
Most forecast templates use one of a handful of methods. This one sets the category from buyer evidence and keeps the rep's call beside it.
| Method | How it works | What it misses | How this template uses it |
|---|---|---|---|
| Historical run-rate | Next period looks like recent periods, adjusted for growth | New products, new segments, a changed market | Not used; finance usually owns it |
| Stage-weighted pipeline | Each deal's amount times the win rate for its stage | Two deals at the same stage can be in very different shape | Optional weighted line above |
| Sales cycle length | Deals close when their age reaches the usual cycle | Deals that stall or speed up | Column D, the close date, checked against column E |
| Rep judgment | The rep calls each deal | Optimism, and pressure late in the quarter | Column J, kept and compared |
| Buyer evidence | The category follows what the buyer has said and done | Takes discipline to fill from real conversations | Columns E to I and the K formula |
Teams with years of clean CRM data sometimes add a statistical model on top. The evidence columns still help there, because they explain why a model's number moves.
Monthly, quarterly and by-rep versions
The same sheet runs on any period. Add a Period column in O (for example "2026-Q4" or "Nov") and filter the roll-up with SUMIFS. A by-rep commit for the current quarter looks like this:
Ana evidence commit =SUMIFS(Deals!C:C,Deals!K:K,"Commit",Deals!A:A,"Ana",Deals!O:O,"2026-Q4")
A by-rep belief gap is the same formula on column J minus the formula on column K. Managers with large teams often find the by-rep gap more useful than the team total, because it shows whose deals need the coaching call.
Keeping the forecast apart from the target
A sales forecast is what the team expects to close; a sales target, or quota, is what the business needs it to close. The two get mixed up when the forecast sheet is used to show the board a number that matches the plan.
Keep them apart in the sheet. Add the target as one line at the top of the Roll-up tab and never let it feed a formula. The gap between the evidence commit and the target is the quarter's real problem, and it tells leadership early whether the answer is more pipeline, faster deals or a revised plan.
A forecast that always lands on the target in week 2 and misses it in week 12 was a target all along. The evidence columns make that harder to hide, because the category moves only when a buyer does something.
Three ways teams misuse it
- Typing evidence the buyer never gave. If a rep fills column G with their own summary of the problem, the formula promotes a deal on the rep's words. Quote the buyer or leave it blank.
- Overriding the formula to make the number. Editing column K by hand in week 10 to hit a target turns the template back into a list of beliefs. Argue the rule at quarter end, and change it for everyone.
- Keeping it and coaching nothing. A sheet that shows a $210,000 gap every week with no booked conversations in column M has stopped doing its job, because the calls in column M are what close the gap. A rep can talk the deal through with Caty and prepare the conversation in column M.
FAQ
What should a sales forecast template include?
A sales forecast template should include one row per open deal with its amount, close date and forecast category, and the buyer evidence that justifies the category. The evidence columns cover who signs and whether they have heard the case, the cost a buyer stated, the approval steps and the buyer's reason for the date. A roll-up should record a fixed snapshot so accuracy can be measured.
How do I make a sales forecast in Excel?
List open deals in rows with amount, close date and category, then use SUMIF to total each category. To make the category more reliable, add buyer evidence columns and an IF formula that sets the category from them, as in the template above. Freeze a snapshot value each quarter to measure accuracy.
What is the difference between a revenue forecast and a pipeline forecast?
A pipeline forecast projects what will close from the open deals in the current period. A revenue forecast often includes recurring revenue, renewals and history-based projections across a longer horizon. Sales managers usually own the pipeline forecast; finance usually owns the revenue forecast.
What are the main sales forecasting methods?
The common ones are historical run-rate, stage-weighted pipeline, sales cycle length, rep judgment and, for teams with enough data, statistical models. This template sets each deal's category from buyer evidence and keeps the rep's call and an optional stage-weighted line beside it, so the methods can be compared week by week.
When should a team move its forecast off a spreadsheet?
Move when the sheet is being copied between people, when deal data has to be retyped from the CRM every week, or when more than one person edits the same rows. The evidence columns can move into custom CRM fields with the same rules. Keep the rules the same when you move, so accuracy stays comparable.
How often should a sales forecast spreadsheet be updated?
Update it weekly, before the forecast call, from the latest buyer conversations. Freeze one snapshot per period, such as the week-4 commit, and compare it with closed revenue at period end. Updating more often than the deals change adds noise.