Sales Forecast Spreadsheet Template: Project Pipeline Revenue and Conversion Rates

Overview

A sales forecast is an estimate of the revenue likely to close during a defined period. It is not the same as a list of every open opportunity. A useful forecast applies consistent stage definitions, realistic probabilities, and a clear time period so that sales, finance, and operations can work from the same view.

For many small businesses and sales teams, a sales forecast spreadsheet is flexible enough to support weekly reviews without requiring a complex system. The basic model records one row per opportunity and calculates expected revenue as:

Expected revenue = deal value × probability of closing

For example, a $10,000 opportunity with a 40% probability contributes $4,000 to the weighted forecast. The result is not a promise that exactly $4,000 will be collected. It is a planning estimate that becomes more useful when applied consistently across many opportunities and compared with actual outcomes.

A practical pipeline forecast template should include at least two views:

• Pipeline detail: individual opportunities, owners, stages, values, expected close dates, and next actions.
• Forecast summary: revenue by month, sales owner, stage, product, or forecast category.

Keep the forecast separate from cash planning when payment timing differs from the expected close date. A sales forecast can feed a cash flow forecast spreadsheet, but the cash model should account for invoicing terms, collection timing, taxes, and other receipts and payments.

How to estimate

Start by creating a pipeline table with one row per active opportunity. Recommended columns include:

• Opportunity or customer name
• Sales owner
• Product, service, or segment
• Deal value
• Pipeline stage
• Probability
• Expected close date
• Next action and next action date
• Forecast category, if your team uses one
• Actual outcome and closed date

Then add a calculation column for weighted revenue. In an Excel or Google Sheets table, a simple row formula is:

=DealValue*Probability

If the deal value is in column D and probability is in column F, the equivalent cell formula might be =D2*F2. Format probability as a percentage and deal value as currency. Avoid typing the percent sign inconsistently; a value stored as 40% is different from a value stored as 40.

To summarize a month, add the weighted values for opportunities whose expected close dates fall within that month. A date-based formula can use SUMIFS, for example:

=SUMIFS(WeightedRevenueRange,CloseDateRange,">="&StartDate,CloseDateRange,"<="&EndDate)

Use a separate closed-won total so that completed revenue is not accidentally counted as open pipeline. A simple forecast summary can show:

Do not combine every estimate into one number without labeling the method. A leadership review may use a conservative commit forecast, while a planning view may use the full weighted pipeline.

Inputs and assumptions

The quality of a revenue projection spreadsheet depends more on definitions than on formatting. Write down the assumptions in a dedicated Assumptions sheet or at the top of the forecast.

Define each pipeline stage

Stages should describe observable buying progress rather than a salesperson’s general confidence. For example, a stage might require a qualified need, an agreed scope, a proposal delivered, or a confirmed purchasing process. The exact names are less important than using the same criteria for every opportunity.

Assign probabilities deliberately

Probability can be based on historical conversion rates by stage, but the calculation should use comparable opportunities and a clearly defined outcome. If the available history is limited, use provisional probabilities and label them as assumptions rather than presenting them as precise measurements.

Historical conversion should also account for the time period being forecast. A deal expected to close this month may have a different risk profile from one expected to close next quarter. Consider maintaining separate probabilities by stage and sales cycle when enough reliable history exists.

Control the data

• Use dropdown lists for stages, owners, products, and forecast categories.
• Require an expected close date for every open opportunity.
• Flag opportunities with no next action or an overdue next action.
• Use one currency and document whether values include or exclude tax, discounts, and recurring periods.
• Remove duplicates and mark lost, paused, or disqualified opportunities clearly.

These controls make the spreadsheet easier to audit and reduce the risk of a forecast changing because someone used a different stage or value convention. For broader reporting, a KPI dashboard spreadsheet can display forecast coverage, win rate, average deal size, and forecast accuracy alongside other business measures.

Worked examples

Assume an illustrative pipeline contains the following open opportunities:

The open pipeline is $40,000, while the weighted open pipeline is $21,600. If $5,000 has already been closed won during the forecast period, the simple weighted forecast total is $26,600. This example is useful because it keeps the two questions separate: how much business is open, and how much of that business appears likely to close based on the assumptions?

For a target of $30,000, the weighted view shows a $3,400 gap. That gap does not automatically mean the team needs another $3,400 in pipeline. It may indicate a need to improve late-stage execution, confirm close dates, pursue additional opportunities, or review whether the assigned probabilities are too conservative or too optimistic.

You can add scenario views without changing the underlying pipeline. A conservative view might include closed won plus only opportunities in a late stage. A base view might use weighted open pipeline. An upside view might show closed won plus the value of all qualified opportunities. For a more structured comparison, use a scenario analysis spreadsheet and document what qualifies an opportunity for each scenario.

When to recalculate

Refresh the sales forecast at least weekly when the pipeline is active, and update it immediately after major changes such as a signed contract, lost deal, revised scope, changed price, or new expected close date. The spreadsheet should reflect the current pipeline, not the pipeline as it looked at the start of the quarter.

During each review, complete this short process:

1. Remove duplicates and update every opportunity’s stage, value, owner, and close date.
2. Confirm that each open opportunity has a dated next action.
3. Separate closed won, closed lost, paused, and still-open records.
4. Compare the previous forecast with actual closed results.
5. Check whether each stage probability is producing reasonable results over time.
6. Review target gaps and decide which opportunities deserve attention first.
7. Save a dated snapshot so forecast changes can be explained later.

Revisit the assumptions when pricing inputs change, sales-cycle length shifts, product mix changes, or new conversion history becomes available. If forecast accuracy matters for staffing or purchasing, connect the revenue view to a broader strategic planning spreadsheet and review the implications for goals and initiatives.

The best sales forecasting Excel template or Google Sheets template is not the one with the most formulas. It is the one the team can update consistently, understand quickly, and challenge constructively. Start with clean opportunity data, make probabilities visible, compare estimates with outcomes, and revise the model whenever the underlying pipeline changes.