Commission reconciliation
Commission reconciliation is the check that what a company actually paid in commission matches what its plan rules say it owed. It compares the deals in the source system against the deals the calculation priced, and the calculated amounts against what payroll released, then explains every difference before the period is closed.
Reconciliation is widely treated as a sanity check on a total, which is the version of the job that catches almost nothing. A total can be within a percent of expectation while two reps are wrong in opposite directions, while five deals never entered the calculation at all, and while a manual adjustment keyed twice quietly offsets a rate applied to the wrong basis. Errors in commission are strongly inclined to cancel each other out in aggregate, so the aggregate is the last place they are visible.
A real reconciliation is three separate tie-outs, each done on counts before amounts. First, coverage: every deal the source system says closed in the period should appear in the calculation, and the count should match before anyone looks at a dollar figure. Second, calculation: the amount produced for each deal should be reproducible from the rule that produced it, at the rate and basis the plan specifies for that rep on that date. Third, release: the total per rep leaving the calculation should equal the total per rep arriving in payroll, including adjustments, which is the step most often skipped because it crosses a system boundary and usually an ownership boundary too.
Do the matching on stable identifiers. Deal id to deal id, employee id to employee id. Names break, and they break silently: a rep who exists in the CRM as an email address and in payroll as an employee number will simply vanish from a lookup keyed on either, taking their whole book with them and leaving a total that still looks plausible. Freeze the source extract with a timestamp and reconcile against that frozen copy, because a source system queried twice an hour apart returns two different answers and neither of them is the one you paid against.
Every variance then gets a category and a name: timing, where a deal landed either side of a period boundary; data, where the source record changed after the run; rule, where the calculation applied something the plan does not say; manual, where a human added or removed an amount; and genuine error. The period closes when each variance has one of those labels and a decision attached, either accepted or scheduled as a true-up in a named future run. A reconciliation that ends with an unexplained residual has been abandoned rather than completed.
A March reconciliation that fails
Twenty-two reps, one monthly run. The finance lead compares the CRM extract taken on 1 April against the commission run and against what payroll released.
- Closed-won deals in the CRM for March
- 341 deals, $2,140,000
- Deals the commission run priced
- 336 deals, $2,085,000
- Never reached the run, owed to two reps on the 6% band
- 5 deals, $55,000, worth $3,300
- Commission the run produced
- $182,400
- Commission paid on the March payroll
- $186,950
- Two variances to explain
- $4,550 paid above the rules, $3,300 never calculated
The overpayment is 2.5% of the run, comfortably inside the margin a total-level review waves through. It turned out to be one adjustment keyed twice. The five missing deals closed in the final two days of March and were picked up by the April run, so they were timing rather than loss, which is something you can only say once you have found them.
What goes wrong when reconciliation lives in a spreadsheet
Four habits account for most of it. Reconciling totals instead of rows, which hides offsetting errors by design. Matching on rep name or customer name, where one renamed account or one rep with two identities drops rows from a lookup without raising anything. Working from an export that was silently truncated, since many systems cap a download at a fixed number of rows and return it as a complete file. And re-pulling the source after the pay run, so the thing you are reconciling against has already moved. All four produce a workbook that balances. None of them produce a reconciliation you could defend to a rep holding a different number.
- How do you reconcile commission payments?
- Freeze a timestamped extract of closed deals for the period. Match it deal by deal against the deals the commission calculation priced, comparing counts first, then amounts. Recalculate a sample of lines by hand against the plan rules. Compare the per-rep totals leaving the calculation with the per-rep totals payroll released. Categorise every difference as timing, data, rule, manual or error, and record a decision for each before closing the period.
- How often should commission be reconciled?
- Every pay run, before the payment leaves, with a fuller review at quarter end covering clawback balances and outstanding true-ups. Reconciling after payment converts an internal correction into a conversation with a salesperson about money already in their account, which is harder to have and much harder to reverse. Annual reconciliation finds errors far too late to correct cleanly.
Nearby terms
Knowing the word is
the easy half
Commish pays commission reconciliation the way your plan describes it, shows the arithmetic on every line, and traces each payment back to the deal that earned it.