Sales Capacity Planning Model Excel Template

What a Sales Capacity Planning Model Should Calculate

A sales capacity planning model Excel template should calculate how much revenue the current and planned sales team can produce in each month or quarter. Revenue capacity equals the sum of productive-rep capacity in the period, adjusted for ramp and expected quota attainment.

Core formula: Period revenue capacity = Σ (each rep’s quota × ramp percentage × expected attainment).

• Quota is the rep’s fully productive target.
• Ramp reflects time in role and productivity progress.
• Expected attainment applies a realistic planning assumption rather than assuming every rep achieves 100% of quota.

For example, one rep with a $50,000 monthly quota, 75% ramp, and 85% expected attainment contributes $31,875 in monthly capacity ($50,000 × 75% × 85%).

A sales capacity model separates what the team could produce from what the pipeline is likely to produce. Use it for annual plans, quarterly sales hiring plan decisions, and forecast reviews, then validate sales ramp assumptions against segmented B2B benchmarks.

Define the Inputs in Your Sales Headcount Planning Spreadsheet

Build the sales headcount planning spreadsheet from explicit, monthly inputs. Count starting reps as fully employed sellers on the first day of each month, and separate roles or segments whenever quota, ramp, or productivity differs.

• Hires: record planned start month, role, and start-date confidence.
• Attrition: model expected voluntary and involuntary exits by month, not as one annual deduction.
• Quota and productivity: enter monthly or annual quota, ramp curve, expected attainment, and when each hire becomes productive.
• Pipeline coverage: calculate qualified pipeline divided by the revenue target or forecast. It is a forecast input, not proof that capacity will convert.

For quota capacity planning, use realistic expected attainment rather than assuming every rep reaches 100%. Validate ramp and attainment assumptions against relevant segmented B2B benchmarks where possible.

Build the Excel Sales Capacity Model Step by Step

Build this sales capacity planning model Excel template in a blank workbook using monthly columns and an assumptions tab.

1. Create columns for each month in the planning year. Put quota, attainment, ramp, hiring, attrition, and pipeline assumptions on a separate Inputs tab.
2. Add a Beginning Headcount row. Link each month to the previous month’s ending headcount.
3. Add Hires and Attrition rows for your sales hiring plan. Calculate Ending Headcount = Beginning Headcount + Hires - Attrition.
4. Create one ramp cohort row for each hiring month. Apply the relevant ramp percentage as each cohort progresses through tenure months.
5. Calculate Productive Rep Equivalents by multiplying each cohort’s headcount by its monthly ramp percentage, then sum the cohorts.
6. Calculate Productive Capacity = Productive Rep Equivalents x Monthly Quota x Expected Attainment.
7. Add pipeline coverage and forecast rows. Compare available pipeline, forecasted bookings, and capacity to identify whether quota capacity planning is limited by sellers, pipeline, or both.

Validate ramp and attainment assumptions against segmented B2B benchmarks, then test hiring timing and pipeline coverage scenarios before committing to the plan.

Set Sales Ramp Assumptions by Cohort

Model sales ramp assumptions by hire cohort, not as one blended annual average. Two reps hired in April produce differently from reps hired in July, so each cohort needs its own monthly capacity row. Ramp percentage means productive contribution relative to a fully ramped rep, not simply months employed.

Replace this illustrative curve with historical performance data. Adjust sales ramp assumptions for role complexity, sales cycle length, territory readiness, enablement quality, and manager capacity to coach new hires.

Worked Example: Forecast Capacity From Headcount to Revenue

All figures below are illustrative, derived example data, not RevXForge benchmarks. In this sales capacity planning model Excel template, start with eight fully ramped reps, two April hires, and one expected July departure. Use a monthly quota of $50,000 per fully ramped rep, expected attainment of 85%, and an April cohort ramp curve of 25%, 50%, 75%, then 100%.

• April incumbent capacity: 8 × $50,000 × 85% = $340,000.
• April new-hire capacity: 2 × $50,000 × 85% × 25% = $21,250.
• April total capacity: $340,000 + $21,250 = $361,250.

By July, the April cohort reaches 100% ramp, but one incumbent departure leaves seven established reps. July capacity becomes 7 × $50,000 × 85% plus 2 × $50,000 × 85%, or $382,500. This is why a sales hiring plan needs attrition timed by month, not just an annual headcount total.

Add illustrative pipeline coverage of 3.0x: April would require $1,083,750 in qualified pipeline. Validate that coverage against conversion rates, sales cycle length, and pipeline quality, using segmented B2B benchmarks where relevant.

Use the Model to Test Hiring, Pipeline, and Forecast Scenarios

Run scenarios in your sales capacity planning model Excel template before treating the hiring plan as fixed. The objective is to expose the revenue gap early and test the operational response.

Compare each result with the target before choosing to add reps, improve conversion, or generate more pipeline. Pipeline coverage helps distinguish the constraint: low coverage points to demand generation, while adequate pipeline with insufficient rep capacity points to a selling-capacity issue.

Update actual start dates, departures, attainment, pipeline, and forecast results monthly. Validate assumptions with segmented B2B benchmarks when deal size, sales motion, or industry materially changes ramp or quota capacity. For broader constraint analysis, use the Revenue Engine Diagnostic.

Conclusion

A sales capacity planning model Excel template is most useful when it turns hiring assumptions, ramp time, quota attainment, and productivity into a clear view of future revenue capacity. RevXForge’s Revenue Engine Diagnostic can connect that capacity view with pipeline, conversion, and economics to identify the constraint that needs attention before you invest in a fix.