You will build a spreadsheet that shows what each rep earns under different results.
A pay plan looks reasonable on paper at 100 percent of quota. That is the one scenario everyone checks. The surprises come at the edges: the month a rep sells half their quota and earns almost nothing, the quarter one rep has a huge deal and the bill arrives at finance, the point where a few hundred dollars of extra sales makes a thousand dollars of difference to someone's pay.
This project makes you find those surprises before the team does. You will build a spreadsheet that shows what each rep earns under different results, check the total cost against budget, look for cliffs, and get the plan reviewed before launch.
Use your own team or a sample team of four or five reps. The sheet should show, for each rep, pay at three levels of performance, and the total cost to the company at each level. Add a one-paragraph explanation of the plan written for the reps.
We will use Wei Ming's draft plan from lesson 7.2, The parts of a sales pay plan. All figures are examples. Base pay is S$4,000 a month. Variable pay is S$2,000 at 100 percent of a monthly quota of S$16,000, which works out to a commission of 12.5 percent of sales value up to quota. Above quota, the rate is one and a half times higher, 18.75 percent. A clawback recovers commission on any client who cancels in the first three months.
Put every input in its own cell at the top of the sheet: base, target variable, quota, commission rate, accelerator multiple, and any threshold or cap. Every calculation below should refer to these cells, so that changing one input updates the whole sheet.
Then list each rep in a row with their own quota. If a rep is still ramping, use their ramp quota for the period, as set in lesson 3.1. For simplicity, Wei Ming modelled a month after Hui Min's ramp ends, with all five reps on the full S$16,000 quota.
For each rep, calculate pay at three levels: below quota, on quota, and well above. Wei Ming used 70, 100 and 150 percent of quota.
At 70 percent, a rep sells S$11,200. Commission at 12.5 percent is S$1,400, so total pay is S$5,400.
At 100 percent, a rep sells S$16,000. Commission is S$2,000 and total pay is S$6,000, which is the OTE.
At 150 percent, a rep sells S$24,000. The first S$16,000 earns S$2,000, and the extra S$8,000 earns 18.75 percent, which is S$1,500. Variable pay is S$3,500 and total pay is S$7,500.
Build the formula once, copy it down, and check one row by hand as above. A misplaced bracket is cheaper to find now than after the first payday.
Add up the pay for the whole team at each level, and compare it with the budget your finance team has set for sales pay. Wei Ming's example budget was S$32,000 a month for the five reps.
If every rep hits 70 percent, total pay is S$27,000, which is S$5,000 under budget. At 100 percent, it is S$30,000, which is S$2,000 under. At 150 percent, it is S$37,500, which is S$5,500 over.
Being over budget when the whole team sells half as much again is not automatically a problem. At 150 percent, the team has sold S$120,000 instead of S$80,000. Pay as a share of sales falls as performance rises: about 48 percent at 70 percent of quota, 37.5 percent at quota and about 31 percent at 150 percent. Show those figures to finance. The question for them is whether the company can fund the extra pay in a strong month, not whether the plan costs more when sales are higher, which every commission plan does.
Ask HR to add employer CPF and other employment costs before the sheet goes to finance.
A cliff is a point where a small change in results makes a large change in pay. Cliffs encourage reps to hold back or pull forward deals to land on the right side of the line, and they feel unfair to anyone who lands just below.
The most common cliff is a threshold: no commission until the rep reaches some share of quota. Wei Ming tested adding a threshold at 50 percent. A rep who sells S$7,840, which is 49 percent, earns no variable pay. A rep who sells S$8,000, 50 percent, earns S$1,000. A difference of S$160 in sales becomes S$1,000 in pay. A rep just under the line at month end has every reason to force a deal through, and a rep far below it has a reason to hold deals for next month.
Wei Ming dropped the threshold. Other cliffs to check for are caps, bonuses that pay a fixed sum at a target, and rules that change the rate on all sales once a level is reached, rather than only on sales above it. Step through your plan in small increments of sales, perhaps every S$500, and look for any jump in pay that is much larger than the steps on either side.
Before the plan goes to the team, send the plan document and the spreadsheet to HR or finance, and to compliance if your industry has rules on how sales staff are paid, as lesson 7.3, Pay plans that backfire, described. Ask them to check the clawback rules in particular.
Then write the one-paragraph explanation for the team. Wei Ming's paragraph read: "Your base is S$4,000. You earn 12.5 percent on everything you sell up to your monthly quota of S$16,000, and 18.75 percent on everything above it. If a client cancels in their first three months, the commission on that deal is taken back from your next payment. At quota, you earn S$6,000 a month."
A finished version has an input block, a row per rep with three scenarios, team totals and pay as a share of sales at each level, a note of the cliffs you tested and what you changed, and the paragraph for the team. That is the module 7 capstone.
In the activity below you will build your own spreadsheet. Start with the inputs block, and do not type a number into any formula cell, so you can change the plan in seconds when the review comes back.
Build a quota and pay plan spreadsheet with three scenarios per rep and a one-paragraph explanation for the team.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).