Sales Forecast Spreadsheet Template: Track Pipeline, Conversion Rates, and Monthly Revenue
sales forecastingsales pipelinerevenue planningExcelGoogle Sheetsbusiness templates

Sales Forecast Spreadsheet Template: Track Pipeline, Conversion Rates, and Monthly Revenue

SSpreadsheet.top Editorial Team
2026-11-16
5 min read

Build a practical Excel or Google Sheets sales forecast with weighted pipeline formulas, monthly views, assumptions, and forecast-versus-actual reviews.

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:

ViewCalculationBest use
CommittedInclude only deals the team considers highly likely to closeNear-term planning and cautious cash decisions
Best caseInclude committed deals plus credible upside opportunitiesCapacity, hiring, and inventory discussions
Weighted pipelineOpportunity value multiplied by assigned probabilityConsistent 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:

OpportunityValueStage probabilityExpected closeWeighted value
A$12,00025%April$3,000
B$18,00050%April$9,000
C$30,00075%May$22,500
D$10,00050%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.

Related Topics

#sales forecasting#sales pipeline#revenue planning#Excel#Google Sheets#business templates
S

Spreadsheet.top Editorial Team

Editorial Team

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.