Bottom-Up Quota Build Worksheet: Four-Input Stress Test
A structured worksheet that runs the build-up arithmetic on four non-negotiable inputs before quotas publish, so revenue leaders can compare what the territory can actually produce against what finance is asking for. Produces a plain-English verdict: within ceiling, stretch, or fiction.
How to use it
Work through each of the four input tables in order, using the last four completed quarters as your data source. Fill in the threshold flags as you go, then carry the outputs into the final comparison table. The whole exercise should take under two hours; the completed sheet is designed to be shared directly with finance or a board.
What's inside
- Input 1 table: segment-specific win rate by deal type with a blended-rate warning flag
- Input 2 table: trended average deal size (ADS) with a recency-weighting instruction and spike/compression flags
- Input 3 table: sales cycle length with a quarterly lag calculator and pipeline timing adjustment
- Input 4 table: pipeline generation capacity per rep with a coverage ratio threshold check
- Worked example with real numbers running through all four inputs end to end
- Named failure modes for each input (what goes wrong when the input is wrong)
- Threshold flag system that marks any input as unreliable before it contaminates the model
- Final comparison table converting build-up output to an annualised figure and setting it against the top-down requirement
- Gap calculation with a plain-English verdict: within ceiling, stretch, or fiction
- When not to use this worksheet note covering edge cases where the model breaks
Who this is for: CRO, VP Sales, RevOps lead, or any revenue leader who owns quota-setting. When to use it: Before quotas are communicated to reps, not after. Data you need: Last four completed quarters of closed-won/lost data, pipeline reports by rep, and ACV by segment.
How the model works
Top-down quota starts with a revenue target and divides it. Bottom-up quota starts with what the territory can actually produce and checks whether the number is achievable. This worksheet runs the build-up in four steps, flags unreliable inputs before they corrupt the output, and produces a single comparison against the top-down requirement.
Complete the inputs in order. Each section has a fill-in table, a worked example, a named failure mode, and a threshold flag.
Input 1: Segment-Specific Win Rate
Why segment-specific matters: A blended win rate across SMB, mid-market, and enterprise will always flatter the enterprise number. If 80% of your deals are SMB at a 35% win rate and 20% are enterprise at a 12% win rate, a blended 30% rate overstates enterprise capacity and understates the SMB ceiling.
Fill-in table
| Segment | Deals Entered (last 4Q) | Deals Won (last 4Q) | Win Rate % | Q weighting (optional) |
|---|---|---|---|---|
| SMB | ||||
| Mid-Market | ||||
| Enterprise | ||||
| Blended |
Worked example
| Segment | Deals Entered | Deals Won | Win Rate % |
|---|---|---|---|
| SMB | 240 | 84 | 35% |
| Mid-Market | 80 | 22 | 28% |
| Enterprise | 25 | 3 | 12% |
| Blended | 345 | 109 | 32% |
Named failure mode: blended masking Using 32% as the single win rate for a rep who carries an enterprise-heavy territory produces a quota that assumes 32% of enterprise deals close. The real rate is 12%. The rep is unachievable before the year starts.
Threshold flag Mark this input as UNRELIABLE if:
- Enterprise win rate is below 10% and enterprise ACV is above 40% of total quota
- Win rate in any segment has moved more than 8 percentage points across the last four quarters (trend is not stable enough to plan from)
Input 2: Trended Average Deal Size (ADS)
Why trended, not averaged: A single-period ADS is distorted by outlier deals. Use four quarters and weight the two most recent quarters at 60% combined.
Fill-in table
| Segment | Q-4 ADS (£) | Q-3 ADS (£) | Q-2 ADS (£) | Q-1 ADS (£) | Weighted ADS (£) |
|---|---|---|---|---|---|
| SMB | |||||
| Mid-Market | |||||
| Enterprise |
Weighting formula: Weighted ADS = (Q-4 × 0.15) + (Q-3 × 0.25) + (Q-2 × 0.30) + (Q-1 × 0.30)
Worked example
| Segment | Q-4 | Q-3 | Q-2 | Q-1 | Weighted ADS |
|---|---|---|---|---|---|
| SMB | £8,200 | £8,500 | £8,100 | £8,400 | £8,310 |
| Mid-Market | £31,000 | £29,500 | £33,000 | £34,500 | £32,300 |
| Enterprise | £112,000 | £98,000 | £125,000 | £140,000 | £122,750 |
Named failure mode: one landmark deal inflating enterprise ADS If a single £400k deal closed in Q-1 and typical enterprise deals run £90-120k, the weighted ADS for enterprise will be materially wrong. Strip outliers (deals more than 2x the median for their segment) before running the weighting.
Threshold flag Mark this input as UNRELIABLE if:
- Any single deal represents more than 25% of total closed-won ACV in its segment across the four quarters
- ADS is trending downward for two or more consecutive quarters (compression signal: quota built on higher ADS will miss)
Input 3: Sales Cycle Length and Quarterly Lag
Why lag matters: A deal that enters pipeline in Q1 under a 90-day average cycle closes in Q2. A quota that expects 12 months of pipeline-to-close in a calendar year is actually drawing on pipeline that must be present in Q4 of the prior year for Q1 to close.
Fill-in table
| Segment | Avg Cycle (days) | Closes in which Q if entered Q1? | Pipeline needed by 1 Jan to hit Q1 quota |
|---|---|---|---|
| SMB | |||
| Mid-Market | |||
| Enterprise |
Lag adjustment rule: If average cycle for a segment exceeds 75 days, reduce the number of closeable cycles in a 12-month year by one. A 120-day enterprise cycle = 3 closeable cycles per year, not 4.
Worked example
| Segment | Avg Cycle | Closeable cycles/year | Quota contribution per cycle |
|---|---|---|---|
| SMB | 28 days | 4 | Full year distributes evenly |
| Mid-Market | 65 days | 4 (just inside threshold) | Q1 needs pipeline from prior Q4 |
| Enterprise | 118 days | 3 | First close lands Q2 at earliest |
Named failure mode: assuming four enterprise cycles in year one A new rep ramping into an enterprise territory cannot close in Q1. The earliest realistic first close is Q2. A quota that loads Q1 and Q2 equally is a fiction for any rep starting fresh.
Threshold flag Mark this input as UNRELIABLE if:
- Cycle data comes from fewer than 15 closed deals per segment
- Standard deviation on cycle length exceeds 50% of the mean (the data is too noisy to plan from)
Input 4: Pipeline Generation Capacity per Rep
Why per rep, not total: A team total obscures the fact that two reps are generating 70% of new pipeline. Quota must be buildable by the median rep, not the top performer.
Fill-in table
| Rep | Avg new pipeline created per quarter (£) | Win rate (their segment) | Closeable value per quarter (£) | Annualised closeable (£) |
|---|---|---|---|---|
| Rep 1 | ||||
| Rep 2 | ||||
| Rep n | ||||
| Median rep |
Coverage ratio check: Divide each rep's quarterly pipeline creation by their quarterly quota. Below 3.0x is a warning. Below 2.5x is a hard stop: the rep cannot hit quota without an unrealistic win rate.
Worked example (five-rep team)
| Rep | Avg pipeline/Q | Win rate | Closeable/Q | Annualised |
|---|---|---|---|---|
| A | £280,000 | 28% | £78,400 | £313,600 |
| B | £195,000 | 28% | £54,600 | £218,400 |
| C | £310,000 | 28% | £86,800 | £347,200 |
| D | £160,000 | 28% | £44,800 | £179,200 |
| E | £220,000 | 28% | £61,600 | £246,400 |
| Median (B+E avg) | £207,500 | £58,100 | £232,400 |
Named failure mode: planning to the top performer If Rep C's £347,200 output becomes the template for individual quota, three of five reps are immediately unachievable. The model must be built from the median.
Threshold flag Mark this input as UNRELIABLE if:
- Top two reps generate more than 60% of total team pipeline
- Any rep has fewer than two full quarters of data (new hire: use segment benchmark, not their personal history)
Final Comparison Table: Build-Up vs Top-Down
Fill this in once all four inputs are complete. Use only RELIABLE inputs. If any input is flagged UNRELIABLE, note it and document the assumption you are substituting.
| Input | Value used | Status |
|---|---|---|
| Segment-specific win rate (by segment) | RELIABLE / UNRELIABLE | |
| Weighted ADS (by segment) | RELIABLE / UNRELIABLE | |
| Closeable cycles per year (by segment) | RELIABLE / UNRELIABLE | |
| Median pipeline capacity per rep/year | RELIABLE / UNRELIABLE |
Build-up formula (per rep):
Build-up quota = Median pipeline per year × Win rate (their primary segment) × no adjustment needed if cycle allows 4 full cycles; multiply by 0.75 if cycle allows only 3
Worked example output
Median pipeline/year: £830,000 Win rate (mid-market primary): 28% Cycle: 4 closeable cycles Build-up output per rep: £232,400
Team build-up (5 reps): £1,162,000
| Amount | |
|---|---|
| Build-up output (team) | £1,162,000 |
| Top-down requirement (from finance) | £1,400,000 |
| Gap | £238,000 (17%) |
Verdict table
| Gap % | Verdict | What it means |
|---|---|---|
| 0-10% | Within ceiling | Achievable with normal upside; publish with confidence |
| 11-20% | Stretch | Achievable if pipeline coverage improves or ADS moves up; document the assumption and track it monthly |
| 21-35% | Fiction-adjacent | Requires a named change: new headcount, a new segment, or a different product mix. Do not publish without surfacing this to finance. |
| 36%+ | Fiction | The top-down number has no grounding in current capacity. Presenting it as a quota will damage retention and forecast accuracy. |
This example verdict: Stretch. The £238k gap is closeable if pipeline coverage moves from 3.5x to 4.0x across the team, or if one enterprise deal closes per half. Document that assumption. If neither materialises by Q2, the quota needs a mid-year review.
When NOT to use this worksheet
- New product line, no history: You have fewer than two quarters of closed-won data. Use a market-sizing model instead, with explicit assumption ranges.
- Post-acquisition integration: Win rates and ADS from the acquired entity will not transfer directly. Run separate models and merge at the total output level only.
- Radical GTM change in the plan year: If you are moving from field sales to product-led growth, or splitting an SMB team from a combined one, prior pipeline and win rate data will mislabel the new motion. The inputs are not portable.
- Team smaller than four reps: The median rep calculation loses meaning. Use rep-by-rep builds and sum them.
The thinking behind this
Most Companies Set Quota From the Top Down and Then Wonder Why 60% of Reps Miss It is the argument this resource puts to work.