Templates 6 min read

Sales Forecasting Template: Free Excel and Google Sheets

Free sales forecasting template for Excel and Google Sheets: weighted pipeline, scenario analysis, FORECAST.ETS formulas, and a four-tab structure explained.

Siddharth Gangal

A sales forecast is only as good as the structure behind it. Most teams start with a blank spreadsheet, add some deal amounts and close dates, and call it a forecast. The result is a number that looks precise but isn't — because it treats every deal in every stage the same, ignores historical win rates, and gives you no way to stress-test assumptions before presenting to the board.

This guide walks through a four-tab sales forecasting template built for Excel and Google Sheets, covering the exact structure, formulas, and logic that turn raw pipeline data into a defensible revenue projection.

Why Spreadsheets Still Matter for Sales Forecasting

CRMs generate a lot of data. They rarely generate a clean, board-ready forecast on their own. Most CRM forecast modules show you a single number — usually the sum of all open opportunity amounts — without applying probability weighting, separating committed deals from pipeline, or modeling scenarios.

Spreadsheets fill that gap. They let you apply your own probability logic, run scenario analysis in seconds, and share a snapshot with finance or leadership without requiring everyone to log into your CRM. They are also the fastest way to build a forecasting habit before you have the budget or headcount to invest in dedicated forecasting software.

The template structure below is designed to be audit-friendly: every number traces back to a source cell, every assumption is labeled, and the scenario toggles are transparent rather than buried in hidden tabs.

The Four-Tab Template Structure

Tab 1: Inputs and Assumptions

This tab is the control panel for your entire model. Nothing here uses formulas that pull from other tabs — it is the raw input layer. Every assumption your forecast depends on lives here so that when something changes, you update one cell and the entire model recalculates.

Key fields to include on the Inputs tab:

  • Stage probability table. List every pipeline stage (Prospecting, Discovery, Demo, Proposal, Negotiation, Verbal) with a corresponding close probability. Use historical win rates from your CRM — not gut feel. If your Proposal stage historically closes at 38%, use 38%, not 50%.
  • Average deal size by segment. Break this out by product line, customer segment, or sales motion (inbound vs. outbound). Average deal size can vary by 3–5x across segments, and blending them into a single number distorts your weighted pipeline.
  • Average sales cycle length. Used to project when pipeline created today will close. If your average cycle is 47 days, a deal created on May 1 should map to a June close, not a May close.
  • Seasonality adjustments. If your business has a Q4 spike or a Q3 summer slowdown, encode that here as a multiplier. A 1.2 multiplier in December and a 0.85 in August will materially improve your model's accuracy.
  • Forecast period. Define whether you are forecasting by week, month, or quarter, and set the start and end dates. Everything downstream references these two cells.

Tab 2: Pipeline Data

This tab holds every open opportunity, pulled from a CRM export or entered manually. Keep one row per deal. The columns should be:

  • Opportunity Name
  • Account Name
  • Sales Rep
  • Pipeline Stage
  • Deal Amount (raw, unadjusted)
  • Expected Close Date
  • Days Since Last Activity
  • Close Probability (auto-populated via VLOOKUP from the Inputs stage probability table)
  • Weighted Forecast Amount (Amount × Probability)
  • Forecast Month (derived from Close Date)

The Weighted Forecast Amount column is the single most important column in the model. If a deal is worth $80,000 but sits in Discovery with a 22% historical close rate, its weighted value is $17,600 — not $80,000. Summing weighted values across your pipeline gives you a far more honest revenue projection than summing raw amounts.

Add one conditional formatting rule that flags any deal where probability exceeds 50% and Days Since Last Activity is greater than 14. This single rule will surface more forecast risk than almost any formula refinement.

Tab 3: Weighted Forecast Summary

This tab aggregates the pipeline data into a monthly (or quarterly) forecast view. Use SUMIFS to roll up weighted amounts by month and by rep:

=SUMIFS(PipelineData[Weighted Amount], PipelineData[Forecast Month], B2, PipelineData[Rep], $A3)

For teams that want to layer in historical trend data, Excel's FORECAST.ETS function can project future periods based on your actuals. The syntax is:

=FORECAST.ETS(target_date, historical_values, timeline, [seasonality], [data_completion])

FORECAST.ETS uses triple exponential smoothing — applying overall smoothing, trend smoothing, and seasonal smoothing — to project values that follow a repeating pattern. For a business with monthly seasonality, set the seasonality argument to 12. For quarterly patterns, use 4. When Excel cannot detect a pattern, the function falls back to a linear regression, so it is safe to use even on short data sets.

A practical use: populate one column with your last 18 months of closed-won revenue, then use FORECAST.ETS to generate a trailing baseline. Compare that baseline to your weighted pipeline total. If they are within 10–15%, your pipeline is healthy. If the weighted pipeline is materially below the FORECAST.ETS projection, you have a coverage problem.

Also include a Pipeline Coverage Ratio cell:

=Total Weighted Pipeline / Monthly Revenue Target

Best-in-class B2B sales teams maintain 3x to 4x weighted pipeline coverage for their next-quarter target. If coverage drops below 2.5x, that is an early warning that needs to surface immediately — not at the end of the quarter.

Tab 4: Scenario Analysis

This is the tab finance and leadership actually examine. It translates your weighted forecast into three scenarios — Conservative, Base, and Aggressive — by applying different probability multipliers to your pipeline.

Structure it as a simple input matrix:

ScenarioStage Probability MultiplierDeal Volume AssumptionProjected Revenue
Conservative0.75x–10% new pipeline=SUMPRODUCT formula
Base1.00xCurrent pipeline=SUMPRODUCT formula
Aggressive1.20x+15% new pipeline=SUMPRODUCT formula

The multiplier approach is more honest than manually overriding probabilities by deal. It acknowledges that your stage-level assumptions are estimates, not certainties, and gives leadership a range rather than a false point estimate.

Add a second section to the Scenario tab for new business versus expansion revenue. Expansion (upsell, cross-sell, renewal uplift) has a fundamentally different win rate and cycle time than new logo pipeline. Blending them inflates your apparent coverage and masks risk in your new business number.

Common Formula Reference

Weighted Pipeline Total

=SUMPRODUCT(PipelineData[Amount], PipelineData[Probability])

Stage-Level Rollup

=SUMIFS(PipelineData[Amount], PipelineData[Stage], "Proposal")

Win Rate by Rep (for Inputs tab)

=COUNTIFS(ClosedData[Rep], A2, ClosedData[Outcome], "Won") / COUNTIFS(ClosedData[Rep], A2, ClosedData[Outcome], "<>")

Time-Series Projection (FORECAST.ETS)

=FORECAST.ETS(DATE(2026,7,1), B2:B19, A2:A19, 12)

Pipeline Coverage Ratio

=SUM(WeightedForecast[Q3 Weighted]) / Inputs!B12

Using Your Template Alongside an Intelligence Layer

A well-built spreadsheet can get you to 80% accuracy with disciplined data hygiene and weekly pipeline reviews. The remaining 20% — understanding which deals are at risk before they slip, identifying the rep behaviors that predict wins, or seeing how a slow-moving pipeline cohort is affecting your 90-day cash position — requires connecting your forecast to the rest of your operating data.

Teams that outgrow the spreadsheet often move to a platform like Fairview, which connects pipeline data to actuals, margin, and expense trends in a single operating view. The template structure in this guide translates directly: Fairview's pipeline module uses the same weighted forecast logic, and the scenario analysis maps to its what-if analysis feature. Starting in a spreadsheet means you arrive at a more sophisticated tool with your assumptions already stress-tested.

Keeping the Template Accurate Over Time

A forecast template that isn't maintained is worse than no template — it creates false confidence. Three disciplines keep a spreadsheet model reliable:

Weekly pipeline reviews. Every Friday, export fresh pipeline data from your CRM and paste it into the Pipeline tab. Teams that do this weekly improve forecast accuracy by 15–20 percentage points versus teams that update monthly, simply because stale data in the Probability column is the primary source of forecast error.

Monthly actuals reconciliation. When a month closes, record the actual closed-won revenue on the Inputs tab and calculate your forecast accuracy as: (Actual / Forecast − 1). Track this metric over time. If you are consistently forecasting 20% high, your stage probabilities are too aggressive and need to be revised downward. Target a Mean Absolute Percentage Error (MAPE) below 10% for a mature forecasting process.

Quarterly assumptions review. Average deal sizes shift. Sales cycles lengthen during economic contractions. New products change your win rates. Revisit every cell on the Inputs tab at the start of each quarter and update based on trailing 90-day actuals. This is the highest-leverage maintenance task in the model.

When a Spreadsheet Is Not Enough

Spreadsheets have real ceilings. Manual data entry creates lag. Copy-paste errors corrupt formulas. Version control across a distributed revenue team is nearly impossible. And spreadsheets cannot proactively surface anomalies — they only show you what you ask them to calculate.

When your pipeline exceeds 200 active deals, when you have more than four reps, or when your board requires a rolling 12-month forecast with actuals variance, a tool like Fairview handles the data plumbing automatically — pulling live pipeline from your CRM, applying your stage probabilities, and surfacing coverage gaps without requiring manual exports. The template in this guide remains useful as a model-design reference and a validation layer even after you adopt dedicated forecasting infrastructure.

Frequently asked questions

What is a weighted pipeline forecast and how is it different from a raw pipeline total?

A raw pipeline total sums every open deal at face value, regardless of where each deal sits in your sales process. A weighted pipeline forecast multiplies each deal's amount by its stage-specific close probability before summing. A $100,000 deal in early-stage Discovery with a 20% historical close rate contributes $20,000 to the weighted total, not $100,000. Weighted forecasts are consistently more accurate than raw totals because they account for the reality that most pipeline never closes — they just close at different rates depending on stage.

How do I set probability percentages for each pipeline stage?

Use historical data from your CRM, not intuition. Pull the last 12–18 months of closed opportunities and calculate: won deals from each stage / all deals that entered each stage. If 38 out of 100 deals that reached the Proposal stage eventually closed as won, your Proposal probability is 38%. Avoid round numbers like 25%, 50%, 75% — they signal that no one measured anything. Update these percentages quarterly as your win rates evolve.

What does the FORECAST.ETS function do differently than a simple linear trendline?

FORECAST.ETS uses the AAA variant of triple exponential smoothing, which separately smooths the overall level, trend direction, and seasonal pattern in your data. A linear trendline assumes revenue grows at a constant rate in a straight line. FORECAST.ETS detects and projects cyclical patterns — for example, if your business consistently peaks in Q4 and dips in Q2, FORECAST.ETS incorporates that rhythm into future projections. For businesses with clear seasonality, FORECAST.ETS typically outperforms linear regression by 10–25% on MAPE.

How many scenarios should a sales forecast include?

Three scenarios — Conservative, Base, and Aggressive — covers most planning needs. Conservative applies a downward probability multiplier (typically 0.70x–0.80x) and assumes below-average new pipeline creation. Base uses your current weighted pipeline at face value. Aggressive applies an upside multiplier (1.15x–1.25x) and models upside deals closing earlier or additional pipeline materializing. Present all three to leadership and finance so decisions are made against a range, not a single number that implies false precision.

How often should I update my sales forecasting spreadsheet?

Pipeline data should be refreshed weekly — ideally by exporting from your CRM every Friday and replacing the Pipeline tab contents. Probability assumptions on the Inputs tab should be reviewed monthly against closing actuals and formally updated quarterly. The scenario multipliers should be adjusted whenever macro conditions change materially (for example, at the start of a slowdown or a market expansion). Teams that update pipeline weekly achieve 85%+ forecast accuracy on average; teams that update monthly rarely exceed 70%.