Templates

Sales Compensation Workbook

Pay mix by role, quota capacity with ramped new hires and over-assignment, a four-tier payout curve calculator with accelerators, the crediting and commission of a single deal, a distribution of reps with low and high scenarios and a flat-rate comparison, account manager, SDR and sales engineer plans, the cost of the sales team, the commission accrual and the overtime adjustment: twelve tabs, one fictional company, no macros.

Enter your own target incentive, quota and tiers. The workbook pays $100,000 at quota and $200,000 at 150% on a $1,000,000 quota, prices one deal credited at $240,000 at $45,000 or $16,000 depending on where the rep stands, shows a team at 100.6% of quota earning 106.0% of target, runs scenarios at 9.48% and 11.43% effective rates, sets an accrual rate of 10.24% and adds $26.67 of overtime to a 45-hour week. A Checks tab ties 61 figures to the book.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 149 free models.

Sales_Compensation_Workbook.xlsx (27 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind the workbook: The Sales Compensation Handbook, PDF and paperback, with the full case, the controls and the checklists.

What is in the workbook

Inputs
every assumption of the case in one place: headcount, OTE and pay mix by role, the account executive quota, the four tiers of the payout curve and their rate multiples, the ramp schedule, the company plan, the distribution of attainment of the fully ramped reps, the new hires' bookings, the crediting rates for devices, the account manager, SDR, sales engineer and manager inputs, the SPIF, clawbacks, the accrual budget, the SDR overtime example and the scenario factors.
Pay Mix
base salary and target incentive by role, total base and total target incentive, the quota-to-OTE ratio, the base commission rate and total cash at 150% of quota.
Quotas
quota capacity from fully ramped reps and ramped new hires, over-assignment against the company plan, and team attainment if bookings equal the plan.
Curve
a payout calculator: enter any attainment and the tab returns new ACV, commission, the multiple of target incentive and the effective rate, tier by tier, with the payout table of Chapter 5.
Deal
the crediting of a contract with subscription and devices, and the commission on that credit for a rep at any year-to-date position, split across the tiers it crosses.
Distribution
commission for each group of fully ramped reps, team totals, the share of reps at or above quota, the top and bottom of the distribution, and the comparison with a flat-rate plan.
Scenarios
the same distribution with every rep's attainment scaled up or down, with commission and effective rate for each scenario.
Other Roles
the account manager expansion and retention calculation, SDR pay per meeting and team payout, and the sales engineer team payout on the same curve.
Cost
variable pay by role against target, total cash compensation, and the cost ratios of Chapter 11.
Accruals
the expected annual accrual rate, the first-quarter accrual and liability, the year-end true-up and the monthly amortization of a capitalized commission.
Overtime
the allocation of a monthly incentive to a workweek and the extra overtime due for a nonexempt employee.
Checks
figures recalculated by the workbook and compared with the figures printed in the book.

Every assumption is shaded in a light color on the Inputs tab; every other cell is a formula, with no macros, no links to other files and no circular references. Every ratio is guarded against an empty divisor. Replace the inputs with your own company's figures; the Checks tab will then show differences from the book by design.

From The Sales Compensation Handbook. To read: commission with accelerators, calculated · setting sales quotas from capacity · overtime on commissions, calculated.