Templates 6 min read

Sales Forecast Template for Excel: Free Download

Free sales forecast template for Excel with deal list, stage-weighted pipeline, monthly rollup, and quota comparison. Download and customize in minutes.

Siddharth Gangal

Most sales forecast spreadsheets are either too simple — a flat list of deals with a gut-feel close date — or too complex to maintain past the first week. This template lands in the middle: four connected tabs, a stage-weighted pipeline calculation, monthly and quarterly rollups, and a direct comparison to quota. It runs in Excel or Google Sheets with no macros and no add-ins required.

Below you'll find the full template structure, the exact formulas to build each section, typical stage probabilities drawn from aggregate B2B conversion data, and the interpretation mistakes that make weighted forecasts misleading even when the math is right.

Why Most Sales Forecasts Break Down

The research is stark: only 7% of sales teams achieve forecast accuracy above 90%, and roughly 4 in 5 sales and finance leaders missed a quarterly number at least once in the past year. The failure mode is almost never arithmetic — it's data staleness. A deal sitting at "Proposal — 60%" for six weeks carries the same weighted value as one where the proposal was sent yesterday, and the spreadsheet can't tell the difference.

A well-structured template fixes the format problem. It won't fix stale CRM data, but it creates the right structure so that when data quality is high, the output is actually usable. Teams that review pipeline weekly achieve roughly 87% forecast accuracy versus 52% for teams that track irregularly — the template is the scaffold for that discipline.

Template Overview: Four Tabs

The template is organized across four worksheets that feed each other through cell references. Build them in order.

Tab 1 — Stage Probability Table

This is a two-column reference table that every other tab draws from. Column A lists your pipeline stages. Column B lists the win probability for each stage. Name this range StageProb so you can reference it with VLOOKUP without absolute cell addresses.

Sample probabilities based on aggregate B2B conversion data (adjust these to your own historical close rates after 4–6 quarters of data):

  • Prospecting / MQL — 5%
  • Discovery / Qualified — 15%
  • Demo Completed — 30%
  • Proposal Sent — 50%
  • Negotiation / Legal — 75%
  • Verbal Commit / Contract Out — 90%
  • Closed Won — 100%
  • Closed Lost — 0%

The right way to calibrate these: for each stage, divide the number of deals that reached that stage and ultimately closed won by the total number of deals that ever reached that stage. Do this over your last four to six quarters. Your Discovery probability is probably lower than 15% if you're in a competitive market; your Verbal Commit rate may be closer to 85%. Use your own data.

Tab 2 — Deal List (Pipeline Register)

This is the core data entry tab. One row per active opportunity. Columns:

  • A — Opportunity Name: Company + deal descriptor (e.g., "Acme Corp — Annual Expansion")
  • B — Account Owner: Sales rep name
  • C — Deal Value: Full ACV or contract value, not discounted
  • D — Stage: Must match exactly the stages in Tab 1
  • E — Close Date: Formatted as a date, not free text
  • F — Probability %: Auto-populated from Stage Prob table: =VLOOKUP(D2, StageProb, 2, FALSE)
  • G — Weighted Value: =C2*F2
  • H — Close Month: =TEXT(E2,"YYYY-MM") — used for rollup grouping
  • I — Close Quarter: =CHOOSE(MONTH(E2),1,1,1,2,2,2,3,3,3,4,4,4) — returns 1, 2, 3, or 4
  • J — Last Activity Date: Manual entry — the date someone last advanced or touched this deal
  • K — Days Since Activity: =TODAY()-J2 — conditionally format red above 14 days for high-probability deals
  • L — Rep Commit: Yes / No — whether the rep is committing this deal to the current period
  • M — Notes: Free text — next step, key risk, stakeholder status

Add a conditional formatting rule: highlight rows red where F2 > 0.5 AND K2 > 14. This surfaces high-probability deals with no recent activity — the biggest source of forecast miss.

Tab 3 — Monthly and Quarterly Rollup

This tab summarizes the deal list by time period. Use SUMIFS formulas to pull from the Deal List tab.

Monthly rollup structure (repeat for each month in your fiscal year):

  • Row label: Month (e.g., "2026-06")
  • Total Pipeline: =SUMIFS(DealList!C:C, DealList!H:H, A2)
  • Weighted Forecast: =SUMIFS(DealList!G:G, DealList!H:H, A2)
  • Commit Deals Only: =SUMIFS(DealList!C:C, DealList!H:H, A2, DealList!L:L, "Yes")
  • Deal Count: =COUNTIFS(DealList!H:H, A2)

Quarterly rollup uses the same pattern but references the Close Quarter column instead of Close Month. Add a separate row for each of Q1–Q4.

At the bottom of the rollup, add a "Best Case" row: full value of all deals at Negotiation stage or above, regardless of probability. Best Case represents the ceiling if everything late-stage closes. The gap between Best Case and Weighted Forecast is your at-risk exposure.

Tab 4 — Quota Comparison

This is where the forecast becomes actionable. The structure is a simple table:

  • Column A: Month / Quarter
  • Column B: Quota (manual entry)
  • Column C: Weighted Forecast (linked from Tab 3)
  • Column D: Commit (linked from Tab 3)
  • Column E: Coverage Ratio — =C2/B2 — weighted pipeline as a multiple of quota
  • Column F: Gap to Quota — =B2-C2 — positive means shortfall, negative means projected overperformance
  • Column G: Commit Coverage — =D2/B2

A coverage ratio below 3x on weighted pipeline is a warning signal for most B2B sales motions — you need roughly three dollars of weighted pipeline for every one dollar of quota to account for slippage, delayed close dates, and forecast error. Above 4x, you may be carrying too much stale pipeline that's inflating the number.

The Weighted Pipeline Formula, Explained

The core calculation is straightforward:

Weighted Value = Deal Value × Stage Probability
Total Weighted Forecast = SUM(Weighted Value) for all deals in the period

In practice: a $120,000 deal at Proposal Sent (50%) contributes $60,000 to your weighted forecast. A $40,000 deal at Verbal Commit (90%) contributes $36,000. Add them all up across the month and you have your weighted number.

The formula is only as accurate as the probability table feeding it. The biggest mistake is setting probabilities based on intuition ("Proposal should be 60%") rather than historical conversion data. If your company converts 30% of proposals to closed won, the proposal stage probability should be 30% — regardless of how optimistic the spreadsheet felt when you built it.

A secondary mistake: treating weighted pipeline as a single-number forecast. It's a probability-weighted expected value, not a prediction. You should read it as "if this cohort of deals behaves like our historical average, we expect to close approximately $X." That distinction matters when communicating to a board or a CFO.

Using the Commit Column Correctly

The Rep Commit column (Column L in the Deal List) captures the sales rep's subjective judgment — a signal that's separate from the mathematical weighting. A rep committing a deal at 50% probability is saying "I believe this closes this period regardless of what the math says." Tracking this separately lets you compare rep commit accuracy over time.

Run a simple accuracy analysis after each quarter: for each rep, compare their commit total entering the final month of the quarter to their actual closed won. Reps who consistently commit 20% more than they close need their commit filtered down. Reps who are consistently conservative need their commit taken more seriously than the stage probability suggests. This calibration is what separates a useful forecast from a number that management adjusts by instinct.

Integrating the Spreadsheet with Your Actual Revenue Data

A spreadsheet forecast has one persistent weakness: it's disconnected from what's actually happening in your business. You can build a perfect weighted pipeline model and still miss quota because the forecast doesn't account for expansion revenue from existing accounts, slippage from the prior quarter, or the relationship between new logo performance and retention patterns downstream.

Teams that use Fairview connect their CRM pipeline data to the broader revenue picture — so weighted forecast is shown alongside current-period bookings, expansion pipeline, and retention risk in one view, rather than in a spreadsheet that has to be manually refreshed every week. The forecast becomes an input to operating decisions rather than a report that gets emailed on Monday mornings.

For teams that aren't yet at that level of integration, the spreadsheet template here is the right starting point. The discipline of building and maintaining it — especially the Stage Probability Table calibrated on real data and the last-activity tracking — is valuable even before you automate anything.

Common Errors That Invalidate the Forecast

Close dates as decoration. If reps enter a close date and never update it, your monthly rollup becomes meaningless. Deals that slipped from March will still appear in the March tab in June. Enforce a rule: any deal with a close date more than 30 days in the past must be updated or removed from active pipeline.

Probability overrides without discipline. Some teams add a manual probability column that overrides the stage lookup. This is useful for exceptions — a deal where the champion left, or where a competitor was just selected — but if every rep is overriding probabilities upward, you've built a spreadsheet that tells you what you want to hear.

Combining pipeline and bookings in the same column. Keep closed-won deals in the deal list (at 100% probability) until the close month passes, then archive them to a separate tab. If you delete them or mark them as zero, your coverage ratios will look artificially low and your rollup won't reconcile to actual bookings.

No historical version control. Take a snapshot of the full deal list on the last business day of each month. This lets you run a retrospective: which deals were in the weighted forecast at the start of the month and didn't close? What stage were they? This data is what you use to recalibrate stage probabilities over time.

Building Toward a Repeatable Process

The template is a tool. The process is what makes it work. Teams with the highest forecast accuracy share a few common habits: they review the pipeline weekly (not monthly), they have a single owner responsible for data quality, and they distinguish between "weighted forecast," "commit," and "best case" as three separate numbers rather than collapsing them into one figure.

As your team grows and pipeline complexity increases, the manual work of maintaining this spreadsheet becomes the bottleneck. That's the point at which operating intelligence platforms like Fairview start delivering outsize value — not by replacing the logic in your template, but by automating the data flow so you're always looking at a current number rather than last week's export.

Frequently asked questions

What is a good pipeline coverage ratio for sales forecasting?

Most B2B sales teams target 3x to 4x weighted pipeline coverage relative to quota. This means if your quota for the quarter is $500,000, you want $1.5M to $2M in weighted pipeline value. Below 3x, you're likely underpowered and need to either accelerate existing deals or add pipeline. Above 4x, the number may be inflated by stale or misqualified opportunities that are never going to close. The right ratio depends on your average sales cycle length and historical close rate — teams with longer cycles or lower stage conversion rates need more coverage buffer.

How do I calculate weighted pipeline value in Excel?

Multiply each deal's value by its stage probability to get a weighted value per deal, then sum across all deals in the forecast period. In Excel: in column G, enter =C2*F2 where C2 is deal value and F2 is the probability percentage (as a decimal, e.g., 0.5 for 50%). For the total, use =SUM(G2:G100) or a SUMIFS formula if you want to filter by close month or rep. The stage probabilities in column F should pull from a lookup table using =VLOOKUP(D2, StageProb, 2, FALSE) rather than being typed manually.

Should sales reps or sales managers own the forecast spreadsheet?

The deal-level data should be owned by reps — they're closest to the deals and have the best information on close date accuracy and deal risk. The forecast rollup and quota comparison should be owned by the sales manager or RevOps function, who can apply judgment across the full pipeline and identify patterns (e.g., a rep who consistently over-commits, deals that cluster at the same stage every month). The spreadsheet template here supports this split: reps update the Deal List tab, managers review the Quota Comparison tab. Data quality gates — like the conditional formatting rule on stale high-probability deals — should be enforced by whoever owns the pipeline review process.

How often should I update the sales forecast spreadsheet?

Weekly is the minimum cadence for teams with active pipelines. The last-activity tracking in the template (Column K) loses its value if you're only refreshing the file monthly — a deal that hasn't moved in 21 days looks the same as one stuck for 60 days if you only check once a month. The best practice is a weekly pipeline review meeting where each rep walks through deals that have moved (stage change, close date update, activity logged) and flags any stuck deals for manager attention. Research consistently shows that weekly pipeline reviews correlate with significantly higher forecast accuracy than monthly reviews.

What's the difference between weighted forecast, commit, and best case?

These are three distinct numbers that should never be collapsed into one. Weighted forecast is the mathematical expected value: the sum of (deal value × stage probability) across all active opportunities in the period. Commit is the rep or manager's judgment-based prediction of what will actually close — it reflects information that the probability table can't capture, like a champion relationship, a known competitor risk, or a buyer's budget cycle. Best case is the ceiling: the full value of all deals at advanced stages (typically Negotiation and above) if every one of them closes. A useful forecast review covers all three: "Our weighted number is $420K, our commit is $380K, and best case is $510K against a $400K quota." The relationship between these three numbers tells you more than any single figure.