Sales Forecast Spreadsheet Template: Track Pipeline, Conversion Rates, and Monthly Revenue
A practical sales forecast spreadsheet turns opportunity data into a repeatable monthly revenue estimate. This guide shows how to structure an Excel or Google Sheets workbook, calculate weighted pipeline value, compare forecast methods, handle delayed deals, and improve accuracy by reviewing forecasted revenue against closed results.
Overview
A sales forecast spreadsheet is a decision-support tool, not a promise of future revenue. It organizes open opportunities by value, sales stage, expected close date, owner, and probability of closing. The workbook then uses those inputs to estimate how much revenue may close in each month.
A useful template normally contains five tabs:
- Assumptions: sales stages, probability ranges, reporting period, currency, and forecast rules.
- Pipeline: one row per lead or opportunity, with the fields needed for calculations.
- Monthly forecast: forecasted revenue grouped by month and sales representative.
- Targets: monthly or quarterly goals by team, rep, product, or region.
- Actuals and review: closed-won revenue, closed-lost revenue, forecast variance, and accuracy notes.
Keep the pipeline tab focused on inputs and calculations. A separate reporting tab makes it easier to filter the forecast without accidentally changing the underlying opportunity data. If your sales process depends on delivery capacity or project timing, connect the forecast review to a project management spreadsheet so expected wins can be checked against operational capacity.
How to estimate
The core calculation is weighted pipeline value:
Weighted value = opportunity value × probability of closing
For example, a $20,000 opportunity with a 50% probability contributes $10,000 to the weighted forecast. This does not mean the business will receive exactly $10,000. It is a consistent way to compare several opportunities when each has a different level of confidence.
In Excel or Google Sheets, assume the pipeline columns are:
- Column B: opportunity value
- Column E: probability
- Column F: expected close date
- Column G: status
A row-level weighted value can be calculated with =B2*E2, provided the probability is stored as a percentage such as 50%. To calculate the weighted forecast for a selected month, use SUMIFS:
=SUMIFS($H$2:$H$100,$F$2:$F$100,">="&J$1,$F$2:$F$100,"<"&EDATE(J$1,1),$G$2:$G$100,"<>Closed Lost")
Here, column H contains weighted value and cell J1 contains the first day of the reporting month. The formula adds opportunities whose expected close date falls within that month while excluding closed-lost deals. You can add a sales-rep criterion to create a rep-level view or use a pivot table for flexible summaries.
Do not rely on one forecast number alone. Show at least three views:
| View | Calculation | Best use |
|---|---|---|
| Committed | Include only deals the team considers highly likely to close | Near-term planning and cautious cash decisions |
| Best case | Include committed deals plus credible upside opportunities | Capacity, hiring, and inventory discussions |
| Weighted pipeline | Opportunity value multiplied by assigned probability | Consistent comparison across the full pipeline |
The rules for committed and best-case forecasts should be written in the assumptions tab. Otherwise, each salesperson may interpret the categories differently.
Inputs and assumptions
A forecast is only as useful as its inputs. Require a minimum set of fields for every active opportunity:
- Opportunity or account name
- Sales representative or owner
- Product, service, or deal type
- Expected contract value
- Current sales stage
- Expected close date
- Probability of closing
- Next action and next action date
- Forecast category, such as committed or best case
Use data validation lists for stage, status, owner, and forecast category. This prevents variations such as “Closed Won,” “Won,” and “closed-won” from splitting your summaries. Use a date format consistently and flag blank or overdue close dates with conditional formatting.
Stage probabilities can begin as simple planning assumptions. For example, you might assign lower probabilities to early discovery and higher probabilities to a signed proposal. The exact values should reflect your own sales history rather than a generic benchmark. If reliable historical data becomes available, calculate stage conversion rates from prior opportunities and review whether the assigned probabilities still represent actual outcomes.
Separate opportunity value from expected collected cash. A signed deal may be recognized as revenue in one period but collected later. For cash timing, connect the sales forecast to a cash flow forecast spreadsheet and add expected invoice and payment dates.
Worked examples
Assume a salesperson has four open opportunities:
| Opportunity | Value | Stage probability | Expected close | Weighted value |
|---|---|---|---|---|
| A | $12,000 | 25% | April | $3,000 |
| B | $18,000 | 50% | April | $9,000 |
| C | $30,000 | 75% | May | $22,500 |
| D | $10,000 | 50% | May | $5,000 |
The April weighted forecast is $12,000, calculated from opportunities A and B. May has a weighted forecast of $27,500 from opportunities C and D. The unweighted pipeline is $30,000 in April and $40,000 in May, so the weighted view provides a more cautious planning figure.
Suppose opportunity B moves from April to May during the weekly review. April then falls to $3,000, while May rises to $36,500. The annual pipeline value has not changed, but the timing has. This is why close-date discipline matters: a forecast can appear stable in total while becoming materially different for monthly targets, staffing, and cash planning.
At month-end, copy or record the forecast before replacing it with actual results. Compare the saved forecast with closed-won revenue and classify the variance. Common explanations include delayed deals, changed deal value, lost opportunities, duplicate records, or probability assumptions that were too optimistic. For broader downside and upside planning, pair the workbook with a scenario analysis spreadsheet.
When to recalculate
Update the active pipeline at least weekly when the forecast informs sales meetings or operational decisions. Each review should update stage, value, close date, owner, next action, and forecast category. Do not silently carry an old close date forward; require the owner to confirm it or move the opportunity to a later period.
Recalculate the monthly forecast when:
- A deal changes stage, value, owner, or expected close date
- An opportunity is won, lost, paused, or disqualified
- Pricing, discounts, contract terms, or product mix changes
- A sales representative joins or leaves the team
- Actual conversion results show that stage probabilities need review
- Monthly or quarterly targets are revised
At the end of each month, compare forecast versus actual by total team, representative, and month. At the end of each quarter, review the assumptions rather than only editing the results. Check whether opportunities are being entered consistently, whether close dates are routinely pushed, and whether probabilities match observed outcomes. Use a formula and link review checklist to catch broken ranges or inconsistent statuses; the spreadsheet audit checklist can support that process.
Finally, protect formula columns, label manual inputs clearly, and save a dated snapshot before each reporting cycle. A straightforward sales forecast spreadsheet becomes more valuable over time when its assumptions are visible, its results are tested against actual revenue, and its pipeline is refreshed before decisions are made.