Every comp plan looks reasonable on paper until you run the numbers. A plan that seems fiscally responsible at 100% attainment might blow your budget at 130%. A set of accelerators that appears generous might actually be impossible to trigger given your average deal size. The only way to know is to model it. This guide walks you through building a compensation modeling spreadsheet that will become the most important tool in your RevOps toolkit.
Spreadsheet Architecture¶
Your modeling workbook should have five tabs. Each serves a distinct purpose, and together they give you a complete picture of your compensation economics.
Tab 1: Plan Parameters This is your control panel. Every variable that defines your comp plan lives here:
| Parameter | Example Value |
|---|---|
| Role | Account Executive |
| OTE | $200,000 |
| Base Salary | $100,000 |
| Variable Pay Target | $100,000 |
| Annual Quota | $900,000 |
| Quota-to-OTE Ratio | 4.5x |
| Accelerator Tier 1 (101-130%) | 1.5x |
| Accelerator Tier 2 (131%+) | 2.0x |
| Decelerator Floor | 50% attainment |
| Decelerator Rate | 0.5x |
Build this as a reference table with named ranges. Every formula in the other tabs should pull from this single source of truth - never hard-code rates into formulas.
Tab 2: Attainment Curve This is the heart of your model. Build a table that calculates total variable payout at every attainment level from 0% to 160% in 5% increments.
| Attainment | Revenue Booked | Variable Payout | Total Comp (Base + Variable) | Cost of Sale |
|---|---|---|---|---|
| 0% | $0 | $0 | $100,000 | N/A |
| 50% | $450,000 | $25,000 | $125,000 | 27.8% |
| 75% | $675,000 | $75,000 | $175,000 | 25.9% |
| 100% | $900,000 | $100,000 | $200,000 | 22.2% |
| 120% | $1,080,000 | $130,000 | $230,000 | 21.3% |
| 140% | $1,260,000 | $170,000 | $270,000 | 21.4% |
| 160% | $1,440,000 | $210,000 | $310,000 | 21.5% |
What to look for: Cost of sale should decrease (or stay flat) as attainment increases above 100%. If cost of sale rises sharply above 130%, your accelerators are too aggressive.
Tab 3: Scenario Analysis Model five scenarios using different assumptions about team-wide attainment distribution:
- Bear case: Average attainment 40%, bell curve shifted left. Only 15% of reps hit quota.
- Below target: Average attainment 70%. About 35% of reps hit quota.
- Target case: Average attainment 100%. 60-65% of reps hit quota.
- Above target: Average attainment 120%. 80% of reps hit quota.
- Bull case: Average attainment 140%. 90%+ of reps hit quota.
For each scenario, calculate:
- Total commission expense across the full team
- Commission expense as a percentage of total bookings
- Highest individual payout (your top performer at 160%+ attainment)
- Lowest individual payout (your bottom performer at 20-30% attainment)
Tab 4: Team Cost Projection List every plan participant with their individual parameters:
| Rep Name | Role | Base | Variable Target | Quota | Projected Attainment | Projected Variable Payout | Total Comp |
|---|---|---|---|---|---|---|---|
| Rep A | AE | $100K | $100K | $900K | 110% | $115K | $215K |
| Rep B | AE | $100K | $100K | $900K | 85% | $85K | $185K |
| Rep C | SDR | $55K | $30K | 14 mtgs/mo | 100% | $30K | $85K |
Sum the columns to get your total compensation budget and compare it to your revenue plan. For most SaaS companies, total sales comp (base + variable) should fall between 18-25% of revenue at target attainment.
Tab 5: What-If Simulator Build a simple interface where you can change one variable and see the ripple effect. Key toggles include:
- What if we increase quota by 10% but keep OTE flat? Shows impact on attainment rates and cost of sale.
- What if we add a second accelerator tier at 1.75x above 120%? Shows incremental cost for top performers.
- What if we raise the decelerator floor from 50% to 60%? Shows impact on bottom-performer payouts.
- What if we hire 5 more AEs mid-year with ramped quotas? Shows impact on total comp expense with partial-year contributors.
Use data validation dropdowns and conditional formatting to make this tab usable by non-spreadsheet-experts on your leadership team.
Modeling Best Practices¶
Use last year’s actual attainment distribution as your starting point for projections. Do not assume everyone will hit quota - they will not.
Always model the top earner. Your CFO will ask what the highest-paid rep could earn under the new plan. Have the answer ready. If a rep at 160% attainment earns more than the VP of Sales, you may want to add a soft cap or declining accelerator above 150%.
Refresh quarterly. Update Tab 4 with actual year-to-date attainment each quarter. Compare projected full-year comp expense to budget and flag variances above 5%.
Version control your model. Save a new copy each time you make structural changes. Label versions clearly (e.g., “FY26 Comp Model v3 - Post-Board Feedback”). You will need the audit trail.
Key Takeaways¶
- Build five tabs: plan parameters, attainment curve, scenario analysis, team cost projection, and a what-if simulator
- Model at least five attainment scenarios from bear case (40%) to bull case (140%) before approving any plan
- Validate that cost of sale stays flat or decreases as attainment rises above 100%
- Refresh with actuals quarterly and maintain version history for audit purposes