Commission week
At the end of each month someone, often the finance manager or the sales director, builds the commission spreadsheet. They export won deals from the CRM, cross-check which have been invoiced and which have been paid, apply the rates for each salesperson, handle the split deals, deduct clawbacks for customers who cancelled, and add the accelerators for anyone over target. Then they send each person their figure.
Within a day, the questions arrive. 'Where is the deal I closed on the 29th?' 'I thought the rate went up after I hit target?' 'Why has this been clawed back?' Each one means going back into the spreadsheet to explain, and sometimes finding that the salesperson is right.
Why commission is so hard to get right by hand
Commission plans grow complicated because they are trying to encourage the right behaviour. There are tiers, splits, different rates for new and existing business, payment on invoice or on cash received, clawbacks and one-off bonuses. Each rule is reasonable. Together they make a calculation that is hard to do reliably in a spreadsheet.
The data is also spread out. Deals and owners are in the CRM. Invoicing and payment status are in Xero, QuickBooks or your ERP. Targets are in another spreadsheet. The person doing the calculation is the only link between them.
| Common rule | Why it causes errors |
|---|---|
| Paid on cash received | Needs payment status from accounting, not just deal status |
| Tiered rates | Rate depends on cumulative total, which changes as deals are corrected |
| Split deals | Percentages agreed informally and forgotten |
| Clawbacks | Need the original calculation to reverse accurately |
| Plan changes mid-year | Old and new rules apply to different periods |
What the spreadsheet costs
Time from a senior person every month. Errors that are either over-payments nobody notices or under-payments that damage trust. Disputes that take up sales managers' attention. And a sales team that does not quite believe the numbers, which undermines the point of having a commission plan at all.
How we automate the calculation
- Write the plan down as rules: we go through your commission plan with you and turn it into explicit, testable rules, including the edge cases you usually handle by judgement.
- Pull the data: won deals, owners and splits from your CRM; invoice and payment status from your accounting system; targets from wherever they are kept.
- Calculate: each person's commission for the period, applying tiers, splits, clawbacks and plan versions by date.
- Produce statements: each salesperson sees a line-by-line breakdown of every deal, the rule applied and the amount, with a running total against target through the month.
- Handle queries in the system: a salesperson can flag a line they disagree with, and the manager resolves it with a note, so disputes are recorded rather than argued by email.
- Approve and export: once approved, totals go to your payroll software or bureau as an import file, and a record of the period is locked.
Before switching over, we run the new calculation alongside your spreadsheet for past periods and reconcile any differences, so everyone can see it matches the plan.
What the team sees afterwards
Salespeople can see where they stand during the month, not just after it, which is what motivates. Statements explain every line, so most questions answer themselves. The person who built the spreadsheet gets their week back. And when you change the plan, you change the rules and can model the effect on past periods before you commit.
Finance gets a cleaner audit trail. Each period's calculation is locked with the data and rules it used, so if a figure is questioned months later, or a salesperson leaves and disputes a final payment, the answer is a record rather than a reconstruction. Accruals for commission earned but not yet paid can be produced from the same data for your management accounts.
Does this sound like your commission process?
- Commission is calculated in a spreadsheet one person maintains.
- Salespeople regularly query their commission figure.
- Payment depends on invoice or cash status from another system.
- Split deals and clawbacks are handled by hand.
- Nobody can see their commission position until after month end.