A sales forecast tells you how much revenue you expect to close in a given period, and whether you're going to hit your number. Done right, it's built from real pipeline data, weighted by stage probability, and adjusted for rep capacity. This guide shows you how to build one in Excel.
Two Approaches: Top-Down vs. Bottom-Up
A top-down forecast starts from a revenue target and works backward. A bottom-up forecast starts from actual pipeline and works forward. Bottom-up is more accurate and more useful for day-to-day sales management. This guide covers bottom-up.
Your Pipeline as the Foundation
Export your current open pipeline from your CRM: deal name, rep, stage, deal value, expected close date, and probability. If your CRM doesn't assign probabilities by stage automatically, add a stage-to-probability lookup table:
- Prospecting: 10%
- Discovery: 20%
- Demo: 35%
- Proposal: 50%
- Negotiation: 75%
- Verbal Commit: 90%
Use VLOOKUP to apply the right probability to each deal based on its stage.
Weighted Pipeline by Month
Calculate weighted value for each deal:
=D2*E2
Where D2 is deal value and E2 is probability. Then use SUMIFS to aggregate weighted pipeline by close month:
=SUMIFS(F:F, G:G, ">="&DATE(2026,5,1), G:G, "<="&DATE(2026,5,31))
Where F is weighted value and G is expected close date. Do this for each month you're forecasting.
Adding Historical Win Rate Adjustment
If your historical win rate from a given stage is different from the CRM probability, apply a correction factor. If deals in Proposal close at 40% historically but CRM shows 50%, multiply weighted pipeline by 0.8 (40/50) to adjust.
Track win rate by stage quarterly by dividing closed-won deals by total deals that reached that stage. Update your probability table when win rates shift.
Rep Capacity Check
A forecast isn't credible if it assumes reps can close more deals than they have time to work. Add a capacity column: expected deals to close this month per rep based on their current pipeline and average sales cycle. Flag any rep whose forecast exceeds realistic capacity.
Scenario Planning
Build three versions: Conservative (weight all probabilities down by 20%), Base (use standard probabilities), and Upside (include 10% of early-stage deals as a stretch). Present all three to leadership with a recommended number.
The Easy Way: Using SheetXAI in Excel
Example 1: You have your pipeline export already in the spreadsheet.
"I have a pipeline export on Sheet 1 with deal name, rep, stage, value, and close date. Build a sales forecast showing weighted pipeline by month for the next 3 months, with a probability table by stage and a summary by rep."
SheetXAI reads your pipeline, applies the probability weights, aggregates by month and rep, and builds the forecast.
Example 2: Your pipeline lives in your CRM.
"Pull open opportunities from Salesforce and build a 3-month sales forecast showing weighted pipeline by stage and rep, with conservative, base, and upside scenarios."
SheetXAI connects to Salesforce, pulls the pipeline, and builds the full forecast with scenarios.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Sales Pipeline Tracker in Google Sheets, How to Track Sales Commissions in Google Sheets, and Google Sheets AI Guide.
