Why spreadsheet commission tracking breaks: 5 common causes
The five errors businesses hit most when they calculate commission in Excel: double counting, hand-applied rules, periods that never lock. How to avoid them.
A spreadsheet is the easiest place to start calculating commission. The trouble is that as the business grows, the same file is expected to behave like a calculation engine. With several branches, hundreds of transaction rows and an incentive program that changes from period to period, the file starts giving wrong answers quietly. These are the five causes seen most often.
A record is counted twice, or not at all
Many workbooks concatenate rows into text and count them with wildcard COUNTIFs. If one product name appears inside another (for example "Service Pack" and "Service Pack Plus"), both fall into the same bucket. The reverse happens too: a spelling difference puts a row in no bucket at all. Neither raises an error on screen; the gap appears when the payment arrives.
Quick check
Add up the branch totals and compare them with the raw row count. If they do not match, there is double counting or loss somewhere.
The rules live in people, not in cells
Price bands, multipliers, prerequisites and target-achievement scores are often applied by hand. When the person who wrote the file leaves or goes on holiday, which rule was applied in which cell is lost. The result looks right, but nobody can explain why it is right.
The period never locks
A closed month’s file is opened next month, a formula is fixed, and last month’s figure changes too. Because it is not recorded which figure changed when, the closing figure can no longer be trusted. Really closing a period means freezing its records and rules and writing a correction as a separate entry.
Incoming and outgoing commission get mixed
A business runs two flows at once: commission earned from the party that pays it, such as an operator, an insurer or a brand (income) and commission paid to staff, managers and coordinators (expense). Kept on the same sheet, their periods, due dates and clawback rules blur, and it becomes unclear what has been collected and what has been paid.
The payer’s figure is never compared with yours
When the payer’s statement arrives, most businesses look at the total and say "about right". Without a line-by-line comparison, cancellations and clawbacks beyond tolerance go unnoticed. Regular reconciliation is the only way to find which transaction a difference comes from.
What to do
The five problems share a root: the calculation and the rules are buried in the file. The fix is to keep rules as data, make every calculation traceable, lock closed periods and compare the statement line by line. Commission calculation software does exactly that; how rules are defined is covered on the incentive program management page.