Built for sales leaders working through the sales forecast formula math
The sales forecast formula explained: the math that converts pipeline into a defensible number, with derivations and the operating assumptions baked in.
A sales forecast formula is not one formula but a composition: stage weighting, risk adjustment, pacing math, multi-year splitting, and multi-dimensional rollup. Each layer has its own math and its own operating assumptions. This page walks through each formula with the math, the derivation, and the operating guidance for picking the right parameters.
Teams looking up the sales forecast formula typically have a unresolved math question: the stage weights are producing a number that does not match intuition, the risk adjustment feels arbitrary, the pacing math breaks at year boundaries. The six pains below are the formula pains that commonly show up.
The stage weights are set by convention, not data
Commit at 100, best at 50, upside at 25 because somebody said so.
Common stage weights (commit 100 percent, best 50 to 70 percent, upside 20 to 30 percent) are set by convention or industry rule-of-thumb. The weights rarely match the firm's historical win rate per stage. The forecast number consistently over-forecasts or under-forecasts because the weights are not calibrated. Strkr's risk adjustment layer tunes the weights continuously from live historical win rate.
The pacing formula breaks on year boundaries
The TODAY() formula wrapped around and the pacing bar went red.
Pacing formulas commonly use TODAY() and quarter start / end dates. Year boundaries, leap years, holiday schedule changes commonly break the formula in subtle ways. The pacing bar reads correctly until the quarter boundary, then shows a number that does not match reality. Reps and the exec team lose confidence. Strkr's pacing uses encoded business-day math with holidays which handles year boundaries natively.
Multi-year math requires per-deal calculation
A 3-year 540K contract needs ARR, TCV, ACV worked by hand.
Multi-year contract math splits into three views: ARR (first-year ratable), TCV (full contract value), ACV (average annual value). The three views require specific formulas per contract. For a 540K 3-year contract with straight ramp: ARR = 540K / 3 = 180K; TCV = 540K; ACV = 540K / 3 = 180K. For a ramped contract (100K year 1, 180K year 2, 260K year 3): ARR = 100K first-year; TCV = 540K; ACV = 180K average. Teams running the math by hand accumulate errors. Strkr renders all three views automatically.
Pipeline coverage ratio is informally applied
Coverage of 3x is good, but relative to what?
The pipeline coverage ratio formula is pipeline divided by target. The informal rule is 3x coverage is healthy. The rule varies by sales cycle length, close rate, and segment. A 240-day enterprise cycle with 20 percent close rate needs 5x coverage; a 45-day SMB cycle with 40 percent close rate needs 2.5x. Strkr computes the coverage ratio with the cycle length and close rate factored in, surfacing the segment-specific coverage health.
The attainment math drifts for new hires
The new rep's attainment reads 180 percent against their ramp quota.
Attainment math commonly reads closed won divided by quota. For new hires on ramp quotas (first quarter at 25 percent of target, second at 50 percent, third at 100 percent), the attainment math must read the ramp quota as of the period, not the senior-rep target. Teams running the math against the senior target under-report new hire attainment; teams running against the ramp quota correctly see 100 percent against the ramp. Strkr's attainment math reads the dated ramp quota per rep per period.
The forecast variance calculation is undefined
We say forecast accuracy is 85 percent but we cannot show the math.
Forecast accuracy is a derived metric with specific definitions: (a) absolute percentage error = abs(forecast - actual) / actual, (b) mean absolute percentage error across quarters, (c) coverage of forecast range (did actual fall within forecast commit to commit plus upside). The accuracy number differs under each definition. Teams citing an accuracy number without specifying the formula produce comparisons that do not reconcile. Strkr's accuracy report defines the formula and renders the number consistently.
The core sales forecast formulas
The math each layer of a working forecast requires.
A full sales forecast formula is a composition of layer-specific formulas. The cards below are the math each layer requires, with the formulas, the derivations, and the operating assumptions.
Stage-weighted pipeline
Weighted total = sum of (amount times stage weight).
For each deal in the pipeline, multiply the amount by the stage weight (commit 1.0, best case 0.6, upside 0.25, omit 0.0). Sum across all deals to produce the weighted pipeline total. Formula: SUMPRODUCT(amount_column, weight_column). The weighted total represents the probability-adjusted expected revenue if the stage weights accurately reflect close probability by stage.
Risk-adjusted rollup
Risk-adjusted = weighted total times historical win rate.
Multiply the stage-weighted total by the historical win rate per stage. If commit stage historically closes at 72 percent, the risk-adjusted commit = weighted commit times 0.72. Formula: SUMPRODUCT((category="commit")*amount*win_rate_commit). The risk-adjusted rollup corrects for the gap between the methodology's theoretical weight and the actual observed win rate.
Pacing fraction
Pacing = business days elapsed / total business days.
Business days elapsed divided by total business days in the period produces the pacing fraction. Formula: NETWORKDAYS(period_start, TODAY(), holidays) / NETWORKDAYS(period_start, period_end, holidays). The pacing fraction represents the expected proportion of quota that should be closed at the current point in the period, assuming linear quota distribution.
Pacing target
Pacing target = period quota times pacing fraction.
The expected closed-won amount at the current point in the period equals the period quota times the pacing fraction. Formula: quota * pacing_fraction. The pacing target is the quantitative threshold for pace status: actual closed won above the target is ahead of pace, below the target is behind.
Pacing delta percentage
Delta percentage = (actual - target) / target.
The pacing delta percentage expresses pace status as a percentage: positive values ahead of pace, negative values behind. Formula: (actual_closed_won - pacing_target) / pacing_target. Status thresholds commonly set at: Green when delta percentage is zero or positive, Yellow when between negative 5 and zero, Red when below negative 5.
Pipeline coverage ratio
Coverage = (open pipeline + closed won) / target.
The pipeline coverage ratio measures total pipeline exposure against the target. Formula: (sum of open pipeline amount + closed won amount) / period quota. A coverage ratio of 3x means pipeline exposure is three times the target. The healthy coverage threshold varies by close rate: coverage threshold = 1 / historical_close_rate times a safety multiplier (typically 1.2 to 1.5).
Attainment ratio
Attainment = closed won / quota (period-dated).
Attainment reads closed won divided by quota for the period. For new hires on ramp quotas, use the dated ramp quota as of the period, not the senior-rep target. Formula: closed_won / IF(ramp_quota_as_of_period, ramp_quota_as_of_period, senior_quota). The dated ramp quota ensures new hires are measured against their appropriate target.
Multi-year ARR
ARR = first-year ratable amount from contract.
For multi-year contracts the ARR formula reads the first-year ratable amount from the contract ramp structure. For a straight 3-year 540K contract: ARR = 540K / 3 = 180K. For a ramped contract (100K year 1, 180K year 2, 260K year 3): ARR = 100K. ARR represents the recurring revenue the contract generates in the first year, used for valuation and investor reporting.
Multi-year TCV
TCV = total contract value across all years.
The TCV formula reads the sum of all year amounts in the contract ramp structure. For a straight 3-year 540K contract: TCV = 540K. For a ramped contract (100K year 1, 180K year 2, 260K year 3): TCV = 100 + 180 + 260 = 540K. TCV attributes the full contract value to the close period for commission and quarter-end reporting.
Multi-year ACV
ACV = TCV / contract length in years.
The ACV formula reads TCV divided by contract length. For a straight 3-year 540K contract: ACV = 540K / 3 = 180K. For a ramped contract: ACV = 540K / 3 = 180K (average of 100 + 180 + 260 divided by 3). ACV supports capacity planning and services operations by representing the average year revenue.
Forecast accuracy
Accuracy = 1 - abs(forecast - actual) / actual.
Forecast accuracy at quarter close reads 1 minus the absolute percentage error. For a Week 10 forecast of 2.1M against a quarter-close actual of 2.08M: accuracy = 1 - abs(2.1 - 2.08) / 2.08 = 99.0 percent. The formula computes per quarter; the mean absolute percentage error (MAPE) averages across quarters for trend analysis.
Named formulas by methodology
How specific methodologies compute the forecast number.
Different named methodologies apply different formulas. The cards below show the formulas each methodology uses, so teams picking a methodology know what math they are adopting.
The lean Commit-Best-Upside formula: sum of commit deal amounts plus (sum of best case amounts times 0.5 to 0.7 weight) plus (sum of upside amounts times 0.2 to 0.3 weight). The formula gives the probability-adjusted total. Formula: SUMIF(category="commit") + SUMIF(category="best")*0.6 + SUMIF(category="upside")*0.25.
MEDDPICC gate formula
Category = count of populated criteria.
MEDDPICC assigns category based on criteria populated: 8 criteria populated maps to commit, 6 of 8 maps to best case, 4 of 8 maps to upside, fewer than 4 maps to omit. Formula: category = IF(COUNTA(criteria_columns)>=8, "commit", IF(COUNTA(criteria_columns)>=6, "best", IF(COUNTA(criteria_columns)>=4, "upside", "omit"))). The gate converts qualification depth into forecast category.
BANT scoring formula
Score = Budget + Authority + Need + Timeline (each 0 or 1).
BANT scores each criterion as 0 (not met) or 1 (met). Total score 0 to 4. Category thresholds: score 4 maps to commit, 3 maps to best case, 2 maps to upside, 0 or 1 maps to omit. Formula: score = budget_met + authority_met + need_met + timeline_met; category = lookup(score). The score-based gate is lighter weight than MEDDPICC and fits mid-market motions.
Probability-calibrated formula
Forecast = sum of (amount times per-deal probability).
The probability-calibrated formula multiplies each deal's amount by its specific probability percentage. The probability comes from either a manual rep assignment or an auto-computed value from stage, cycle length, and specific signals. Formula: SUMPRODUCT(amount, probability_percentage / 100). The forecast reflects per-deal nuance that stage-weighted methods aggregate away.
Deterministic rule-based formula
Forecast = algorithm encoded in admin rules.
The deterministic rule-based formula applies explicit rules per deal: if stage is verbal and close date within 30 days and amount under 100K then category commit; if stage is proposal and close date within 60 days and amount above 100K then category best case. The algorithm is encoded in admin rules without machine learning. The strength is explainability; every category assignment traces to a rule.
Predictive model formula
Probability = f(features) from trained model.
The predictive model formula computes per-deal close probability from a machine-learning model trained on historical deal patterns. The features include stage, cycle length, amount, stakeholder count, specific engagement signals, days since last activity. Formula: probability = model.predict(features). Vendors like Clari, Aviso, and People.ai specialize at predictive forecasting; Strkr integrates with each while running a deterministic base.
Operating assumptions baked into the formulas
The choices that shape the forecast number.
Every sales forecast formula bakes in operating assumptions that may or may not match the team's motion. The cards below name the assumptions and the checks for each.
Linear quota distribution assumption
Pacing math assumes quota distributes evenly across the period.
The pacing formula assumes the period quota distributes linearly across business days. In reality some quarters have back-end-weighted close patterns (enterprise motions commonly close 60 to 70 percent of quota in weeks 10 to 13). Teams with back-end-weighted patterns should either adjust the pacing fraction with a weighting curve or interpret the pacing bar with the back-end expectation in mind. Strkr supports configurable quota distribution curves for non-linear patterns.
Stage-to-category mapping stability assumption
The formula assumes stages map consistently to categories.
The weighted pipeline formula assumes the stage-to-category mapping stays stable across the period. If the mapping changes mid-quarter (new stage definition, methodology update), the formula produces inconsistent numbers. Teams should freeze the mapping at the quarter start and change at the quarter boundary. Strkr's versioned admin configuration preserves the mapping across the quarter.
Historical win rate representativeness
The risk adjustment assumes historical win rate predicts future.
The risk-adjusted rollup formula assumes historical win rate per stage predicts future win rate. This assumption breaks if the motion changes materially (new product line, new segment, major sales reorg, economic shift). Teams in transition commonly see risk adjustment under-fit or over-fit the current reality. Strkr's risk adjustment uses a trailing-window historical rate that re-weights toward recent data to adapt faster.
Multi-year ramp interpretation
The ARR formula assumes the first-year amount is the ratable base.
The ARR formula for multi-year contracts assumes the first-year amount represents the ratable base for recurring revenue. For contracts with discounted first years (promotional pricing to win the deal), the ARR understates true recurring revenue. Teams in this situation should either adjust the first-year amount upward with a "fair value" calculation or report ARR with the discount footnote. Strkr's contract structure supports multiple interpretations with explicit configuration.
Attainment period alignment
The attainment formula assumes quota aligns to forecast period.
The attainment formula assumes rep quota aligns to the forecast period (quarterly quota, quarterly forecast). For teams with annual quota and quarterly forecasts, the attainment formula should either annualize the actual (quarterly closed won times 4 as a run-rate) or compare against a quarterly plan slice. Teams running mismatched periods commonly see confusing attainment numbers. Strkr aligns quota and forecast periods explicitly in admin.
Accuracy measurement timing
The accuracy formula assumes a measurement point.
The forecast accuracy formula produces different results depending on when the forecast is measured: Week 1 forecast against quarter actual, Week 10 forecast against quarter actual, Week 13 (last Friday) forecast against actual. Teams should specify the measurement point consistently. Common choices are Week 10 forecast (3 weeks out from quarter close) and Week 13 forecast (quarter close). Strkr's accuracy report lets teams pick the measurement point explicitly.
Head-to-head
Running the formulas in Excel vs on Strkr native.
The sales forecast formula math runs in both Excel and CRM-native tools. The comparison below is the head-to-head on each specific formula layer.
Feature
Strkr
Formula math in Excel
Stage-weighted pipeline
Live rollup, no formula maintenance
SUMPRODUCT formulas; break on sort
Risk adjustment from historical win rate
Live historical calculation per stage
Static formula with manual update
Pacing with business days and holidays
Encoded with configurable holidays
NETWORKDAYS with holiday list argument
Multi-year ARR / TCV / ACV
Native formulas from contract record
Hand-maintained per deal
Attainment with dated ramp quotas
Reads dated ramp quota automatically
IF chains per rep per period
Forecast accuracy measurement
Snapshot-based with configurable measurement point
Not measurable without snapshots
Pipeline coverage with cycle-adjusted threshold
Dynamic threshold per segment
Static 3x rule-of-thumb
Formula drift risk
Encoded math, no drift
Sort operations re-point formulas
Versioned methodology
Admin config history preserved
Current formulas only
Multi-dimensional rollup
Segment, product, region, manager
Rebuild formulas per dimension
How teams use Strkr
How teams encoded the formulas on CRM-native infrastructure.
The pattern across teams: define the formula layers explicitly, encode them in admin, validate against prior quarter data, tune the operating assumptions quarterly. The playbooks below are specific motions.
B2B SaaS, 36 reps, head of RevOps
Encoded the Commit-Best-Upside formulas and tuned risk adjustment with historical data.
A 36-rep B2B SaaS head of RevOps had been running the Commit-Best-Upside methodology with static weights (commit 1.0, best 0.6, upside 0.25). The lead encoded the formulas in Strkr admin and enabled risk adjustment with live historical win rate. After 2 quarters of accumulated data: commit historically closed at 71 percent, best case at 42 percent, upside at 11 percent. The lead tuned the weights to reflect reality. The forecast accuracy improved from 78 percent to 91 percent in the following 2 quarters.
Mid-market services, 48 reps, VP of sales
Switched from BANT to MEDDPICC and re-encoded the gate formulas.
A 48-rep mid-market services VP of sales had been running BANT with 4-criteria gating. The motion complexity grew with new enterprise segment entry, which required the richer MEDDPICC qualification. The VP switched methodologies at the quarter boundary. The gate formulas re-encoded in Strkr admin: 8 criteria populated maps to commit, 6 maps to best case, 4 maps to upside. The versioned admin configuration preserved the BANT era for historical accuracy comparison. Enterprise segment forecast accuracy improved from 68 percent to 82 percent in the first 2 quarters under MEDDPICC.
Enterprise B2B, 120 reps, head of GTM operations
Encoded regional variance in the risk-adjustment formula.
A 120-rep enterprise B2B team ran the standard methodology with a single global risk-adjustment factor. The actual historical win rates differed significantly by region: NA at 71 percent, EMEA at 68 percent, APAC at 62 percent. The head of GTM operations encoded regional variance in the risk-adjustment formula so the risk-adjusted rollup reflected the regional reality. The forecast number became more accurate at the regional level and the global rollup also tightened because the components aggregated correctly.
B2B hardware, 24 reps, CFO
Encoded the multi-year ARR / TCV / ACV formulas and ended quarter-end disputes.
A B2B hardware CFO had chronic quarter-end reconciliation issues: sales reported TCV, finance reported ARR, operations needed ACV. The three numbers disputed because the formulas were hand-calculated per deal. The CFO encoded the three multi-year formulas in Strkr contract structure so all three views rendered automatically from the same contract record. The quarter-end disputes stopped the following period.
Startup, 11 reps, first-time VP of sales
Started with lean formulas and added complexity as the team grew.
A Series A startup first-time VP of sales started with the lean Commit-Best-Upside formulas on day 2 of CRM deployment. The forecast ran clean through 4 quarters at 11 to 18 reps. When the team added an enterprise segment at 20 reps, the VP added per-segment stage-to-category mapping. When the team passed 50 reps the VP added MEDDPICC qualification criteria. The formula complexity grew incrementally as the motion matured. The VP cited the incremental formula scaling as a key operating advantage.
The sales forecast formulas running native on live CRM data.
Start a 14-day Strkr trial with the native forecast: stage-weighted pipeline, risk adjustment from historical win rate, pacing with business days and holidays, multi-year ARR / TCV / ACV, attainment with dated ramp quotas, and accuracy measurement with snapshot history ready to configure.
What is the basic sales forecast formula every team should know?
The basic sales forecast formula is the stage-weighted pipeline rollup: for each deal, multiply the amount by the stage weight (commit 1.0, best case 0.6, upside 0.25, omit 0.0); sum across all deals in the period to produce the weighted total. The formula gives the probability-adjusted expected revenue if the stage weights accurately reflect close probability by stage. For a working forecast, add the risk-adjustment layer: multiply each stage total by the historical win rate for that stage (commit stage historically closes at 72 percent so risk-adjusted commit = weighted commit times 0.72). The two-layer formula (stage-weighted times risk-adjusted) produces a defensible forecast number that reconciles against historical accuracy. Add pacing math (business days elapsed / total business days times quota) and pipeline coverage (pipeline / target) and the team has the core formulas for a working forecast.
How do I compute the risk adjustment factor for each stage?
The risk adjustment factor per stage equals the historical win rate for deals that entered that stage. Compute it as: (deals that entered the stage and later closed won) / (deals that entered the stage). The computation requires preserved historical pipeline data so the lead can see which deals entered each stage over the trailing 12 to 24 months and the eventual outcome. For teams running the forecast in a spreadsheet with weekly overwrites, the historical data does not exist and the risk factor stays guesswork. For teams running CRM-native with snapshot preservation, the risk factor computes automatically and updates as new deals close. The factor should recompute at least quarterly to track changes in motion dynamics. Strkr computes the risk factor per stage per segment and surfaces it inline in the admin configuration.
What formula handles multi-year contracts for forecasting?
Multi-year contracts require three formulas rendering three views from the same contract record. ARR equals the first-year ratable amount from the contract ramp structure. For a straight 3-year 540K contract: ARR = 540K / 3 = 180K. For a ramped contract (100K year 1, 180K year 2, 260K year 3): ARR = 100K first-year amount. TCV equals the sum of all year amounts in the contract. For the straight 3-year 540K: TCV = 540K. For the ramped example: TCV = 100 + 180 + 260 = 540K. ACV equals TCV divided by contract length. For both examples ACV = 540K / 3 = 180K. The three views support different stakeholders: ARR for valuation and investor reporting, TCV for commission and quarter-end reporting, ACV for capacity planning. Teams running the formulas by hand accumulate errors. CRM-native tools render the three views automatically from the same contract record.
How does the pacing formula handle holidays and business days?
The pacing formula uses NETWORKDAYS as the foundation. NETWORKDAYS(period_start, TODAY(), holiday_list) returns business days elapsed from the period start to today, excluding weekends and the holidays listed. NETWORKDAYS(period_start, period_end, holiday_list) returns total business days in the period. Pacing fraction equals the ratio of the two. Pacing target amount equals period quota times pacing fraction. For holiday-heavy quarters (Q4 with Thanksgiving, Christmas, New Year) the formula correctly reflects fewer selling days. For leap years and quarters that span year boundaries the formula handles the day count correctly as long as the holiday list stays accurate. Teams running pacing in Excel need to maintain the holiday list manually; CRM-native tools maintain the holiday list in admin configuration with regional variants (NA holidays, EMEA holidays, APAC holidays) available.
What is the pipeline coverage ratio formula and when should it apply?
The pipeline coverage ratio formula equals total pipeline exposure divided by the period target. Formula: (open pipeline amount + closed won amount) / period quota. A coverage ratio of 3x means total pipeline exposure is three times the target. The healthy coverage threshold varies by close rate: for a 25 percent close rate, 4x coverage is needed; for a 40 percent close rate, 2.5x coverage is sufficient. The threshold calculation: healthy_coverage = 1 / historical_close_rate times a safety multiplier (typically 1.2 to 1.5 depending on cycle variance). Teams citing a static 3x coverage rule-of-thumb without the close-rate adjustment commonly under-cover for low-close-rate motions and over-cover for high-close-rate motions. Strkr computes the segment-specific coverage threshold from the historical close rate and surfaces coverage health per segment.
How does forecast accuracy get measured as a formula?
Forecast accuracy at quarter close reads 1 minus the absolute percentage error. Formula: accuracy = 1 - abs(forecast - actual) / actual. For a Week 10 forecast of 2.1M against a quarter-close actual of 2.08M: accuracy = 1 - abs(2.1 - 2.08) / 2.08 = 0.990 = 99.0 percent. The formula computes per quarter; the mean absolute percentage error (MAPE) averages accuracy across quarters for trend analysis. Teams should specify the measurement point explicitly: Week 1 forecast against actual (early-period accuracy), Week 10 forecast against actual (late-period accuracy), or Week 13 forecast against actual (quarter-close accuracy, which should approach 100 percent on a well-run forecast). Alternative measures include coverage of forecast range (did actual fall within the commit to commit-plus-upside band). Teams running accuracy on an unspecified formula produce comparisons that do not reconcile. Strkr's accuracy report defines the measurement point explicitly and renders the number consistently across quarters.
No sales call, no migration consultant, no four-month implementation. Enter your card, get 14 days of the full Pro tier, cancel any time before day 14 with zero charge. Spin up a workspace, import your CSV, and have something useful before lunch.
We use cookies.
Essential cookies keep the site working. If you accept, we also enable Google Analytics
so we can see which pages help and which don't. Reject to opt out entirely. Details in our
Privacy Policy.
