By Siddharth GangalThis sales forecast template turns opportunity rows into monthly and quarterly closed, commit, best-case, pipeline, and weighted views. It includes the fields, formulas, category gates, eight-deal example, movement register, reconciliation controls, accuracy metrics, and printable sign-off needed to audit the number later.
Direct answer. Create one immutable weekly snapshot. Assign every open opportunity to one owner, period, currency, and evidence-based category. Calculate cumulative category rollups and a separately calibrated weighted view. Reconcile row counts and amounts to CRM, reconcile actuals to finance, lock overrides with reasons, and grade the frozen forecast after the period closes.
This is an operating artifact, not a universal forecast model. Finance and sales leadership must approve revenue versus bookings, currency, timing, category, probability, actual, and restatement rules. Illustrative probabilities below demonstrate formulas; replace them with historical rates from comparable deals.
How to use this sales forecast template
Use three tabs or tables. Deals is the row-level source. Rollup aggregates each month and quarter from the frozen snapshot. Accuracy joins a past snapshot to finance-approved actuals. Add a fourth Movements table if the CRM export does not preserve changes.
Choose a snapshot timestamp, timezone, fiscal calendar, reporting currency, and forecast measure before importing. A bookings forecast should not silently mix recognized revenue, annual contract value, total contract value, services, renewals, and usage. Document exactly which amount is forecast and when it becomes actual.
The sales forecasting guide explains methods and cadence. The forecasting tools guide owns software selection. This page is the copyable sheet and its control logic.
Copy the deal-level forecast fields
Copy this header into a spreadsheet or CSV. Keep stable identifiers and raw fields separate from calculated columns.
snapshot_at,fiscal_period,close_month,opportunity_id,account,owner,
forecast_amount,currency,reporting_amount,stage,forecast_category,
close_date,probability_source,probability,weighted_amount,next_step,
next_step_date,category_evidence,primary_risk,prior_close_date,
prior_amount,prior_category,manager_override,override_reason,last_activity_atUse opportunity_id as the unique key, not account name. Preserve source currency, exchange rate, rate date, and reporting amount when multiple currencies exist. Calculate weighted_amount = reporting_amount × probability. Never type the weighted result manually.
Add product, segment, region, motion, new/renewal, competitor, source, and manager where they materially affect conversion or accountability. Keep personally sensitive or irrelevant fields out. The template should support decisions, not become a second CRM.
Define forecast categories before entry
Salesforce documents standard Pipeline, Best Case, Commit, Omitted, and Closed categories, with optional Most Likely. Labels are not definitions. Write observable entry and exit rules tied to your process.
| Category | Template rule | Required evidence |
|---|---|---|
| Closed | Eligible actual completed inside the period | Finance-approved closed event and amount |
| Commit | Manager and rep expect close in period; every hard gate has an owner and date | Buyer-confirmed next step, decision path, commercial scope, close plan |
| Best Case | Plausible in period, but at least one material gate is not controlled | Qualified deal plus explicit missing gate and recovery action |
| Pipeline | Open and eligible, but insufficient evidence for Best Case | Valid opportunity, amount, period hypothesis, next action |
| Omitted | Excluded from forecast by approved rule | Reason code such as duplicate, internal, test, invalid, or outside model |
Category is not stage probability. A late-stage deal can remain Best Case when procurement is uncontrolled; an early-stage fast-cycle deal may be Commit when every gate is verified. Store stage and category separately. Manager overrides require amount, category or probability changed, reason, author, and timestamp.
Build the monthly forecast view
Assign each deal to exactly one close month using the current approved close date. For month m, use these noncumulative bases:
Closed_m = SUMIFS(amount, close_month, m, category, "Closed")
OpenCommit_m = SUMIFS(amount, close_month, m, category, "Commit")
OpenBest_m = SUMIFS(amount, close_month, m, category, "Best Case")
OpenPipeline_m = SUMIFS(amount, close_month, m, category, "Pipeline")
Weighted_m = SUMIFS(weighted_amount, close_month, m)Then calculate cumulative views explicitly: CommitForecast = Closed + OpenCommit; BestCaseForecast = Closed + OpenCommit + OpenBest; TotalPipeline = Closed + OpenCommit + OpenBest + OpenPipeline. Salesforce’s documentation describes comparable cumulative category behavior. Name columns clearly so an open-category subtotal is not mistaken for a cumulative forecast.
Show beginning snapshot, current snapshot, new, moved in, moved out, increased, decreased, won, and lost. Movement columns explain why the number changed; overwriting last week destroys that evidence.
Roll monthly values into the quarter
The quarter is the sum of its fiscal months from the same snapshot and measure:
QuarterClosed = Month1Closed + Month2Closed + Month3Closed
QuarterCommit = Month1CommitForecast + Month2CommitForecast + Month3CommitForecast
QuarterBestCase = Month1BestCaseForecast + Month2BestCaseForecast + Month3BestCaseForecast
QuarterWeighted = Month1Weighted + Month2Weighted + Month3WeightedDeduplicate by opportunity ID before summing. A deal with split products needs an approved line-item model or one opportunity total—not both. A deal moved from month one to month two remains one current opportunity; its movement register shows the shift. It must not appear in both current-month bases.
Keep the fixed quarterly commit alongside the rolling forecast when planning beyond the quarter. A rolling 12-month view extends the horizon; it does not change row identity or permit two forecasts for one close event.
Worked forecast rollup with eight deals
Assume eight opportunities in one quarter. These illustrative probabilities—Closed 100%, Commit 80%, Best Case 45%, Pipeline 15%—are demonstration inputs, not recommended defaults.
| ID | Month | Category | Amount | Probability | Weighted |
|---|---|---|---|---|---|
| A | Jan | Closed | $60,000 | 100% | $60,000 |
| B | Jan | Closed | $40,000 | 100% | $40,000 |
| C | Jan | Commit | $100,000 | 80% | $80,000 |
| D | Feb | Commit | $80,000 | 80% | $64,000 |
| E | Feb | Best Case | $120,000 | 45% | $54,000 |
| F | Mar | Best Case | $70,000 | 45% | $31,500 |
| G | Mar | Pipeline | $200,000 | 15% | $30,000 |
| H | Mar | Pipeline | $150,000 | 15% | $22,500 |
| Total | $820,000 | — | $382,000 | ||
The noncumulative bases are Closed $100,000, Open Commit $180,000, Open Best Case $190,000, and Open Pipeline $350,000. Therefore cumulative Commit Forecast is $280,000; cumulative Best Case is $470,000; Total Pipeline is $820,000; weighted forecast is $382,000. Recalculate independently: $100,000 + ($180,000 × 0.80) + ($190,000 × 0.45) + ($350,000 × 0.15) = $382,000.
If the monthly weighted values are January $180,000, February $118,000, and March $84,000, their sum reconciles to the $382,000 quarterly weighted total. The close-month allocation is useful even when the executive number is only quarterly.
Reconcile the template to CRM and finance
Before sign-off, run structural checks: unique opportunity IDs; one current row per modeled close event; allowed categories; valid currency; exchange-rate lineage; amount not blank; close date inside assigned period; probability between zero and one; weighted amount recomputed; required evidence for Commit; and override reason where override exists.
Run aggregate checks: deal row count equals the eligible CRM export; source-currency totals equal CRM by currency; reporting totals equal converted values; category bases sum to eligible total; cumulative formulas rebuild from bases; monthly values sum to quarter; weighted rows sum to weighted rollup; and omitted rows reconcile separately by reason.
Then reconcile Closed to finance. List timing, currency, amendment, cancellation, refund, partial booking, services, and restatement differences. Sales cannot grade accuracy against one actual while finance reports another. Freeze a reconciliation owner and resolution date. Use CRM data quality controls before blaming the model.
Measure forecast accuracy and bias
Store immutable snapshots by timestamp and horizon. Grade the forecast that existed at the chosen cutoff—not the version edited after outcomes were known. Record both absolute error and signed bias.
Error = Forecast − Actual
Absolute Percentage Error = ABS(Forecast − Actual) / Actual
WAPE = SUM(ABS(Forecast_t − Actual_t)) / SUM(Actual_t)
Bias = SUM(Forecast_t − Actual_t) / SUM(Actual_t)
Accuracy convention = 1 − error metricExample: the weighted quarter forecast was $382,000 and actual was $360,000. Error is +$22,000, absolute percentage error is 6.11%, and signed bias is +6.11%, meaning over-forecast. Under this stated convention, accuracy is 93.89%.
For monthly forecasts of $150,000, $120,000, and $112,000 against actuals of $140,000, $130,000, and $90,000, absolute errors are $10,000, $10,000, and $22,000. WAPE is $42,000 ÷ $360,000 = 11.67%; accuracy is 88.33%. Bias remains +$22,000 ÷ $360,000 = +6.11%. Publish formulas with every accuracy claim because metric choices can rank forecasts differently.
Print and sign off the forecast
Sales forecast sign-off
Snapshot: ______ Timezone: ______ Fiscal period: ______ Measure: ______ Reporting currency: ______
Closed: ______ Open Commit: ______ Cumulative Commit: ______ Open Best Case: ______ Cumulative Best Case: ______ Open Pipeline: ______ Total Pipeline: ______ Weighted: ______ Manager call: ______
New: ______ Moved in: ______ Moved out: ______ Increased: ______ Decreased: ______ Won: ______ Lost: ______
Top three risks and owners:
1. ____________________
2. ____________________
3. ____________________
☐ IDs unique ☐ CRM count and amount reconciled ☐ currencies reconciled ☐ monthly equals quarterly ☐ overrides explained ☐ Closed reconciled to finance ☐ snapshot locked
Rep/manager owner: ______ RevOps: ______ Finance: ______ Signed at: ______
Print this sign-off page after the formulas pass. Keep the deal table and reconciliation exceptions attached. A signed number without its snapshot, row set, category rules, and actual definition cannot be audited.
Gangly can support cleaner call evidence, next steps, notes, and CRM suggestions. It does not set forecast policy, probabilities, finance actuals, or executive overrides. For meeting governance, use the forecast review meeting guide.
Review the official forecast category documentation, cumulative rollup rules, and M4 competition paper before changing the template’s definitions or evaluation metric.
Final rule: one opportunity, one period, one governed category, one immutable snapshot, and one reconciled actual.