The spreadsheet that one person owns and nobody can audit

‍

Commission calculation is one of the most consequential processes in sales operations and finance. It determines what salespeople get paid. It flows into compensation expense on the P&L. It affects sales behavior, retention, and trust between the field and the back office.

At most mid-market companies, commission calculation lives in a spreadsheet. One person built it. That person understands the formulas, the exceptions, the lookup tables, the manual overrides. They pull the sales data, apply the commission plan rules, calculate the payout for each rep, reconcile against prior payments, and produce the final numbers. The process runs monthly or quarterly. It takes days. And it is almost entirely dependent on that one person.

Commission calculation verification, the step where someone independently checks whether the calculated payouts match the plan terms and the underlying sales data, rarely happens at all. The person who built the spreadsheet is also the person who runs it and the person who would audit it. The controller sees the total commission expense. The sales reps see their individual statements. Nobody systematically verifies that the calculation between the plan document and the payout is correct.

The consequences of errors run in both directions. Overpayments erode margin and are painful to claw back. Underpayments damage trust and drive turnover. Both are difficult to detect until someone complains or an auditor samples transactions months later.

Why commission calculations break

‍

Commission plans are designed to motivate specific sales behaviors. The resulting calculation logic is complex by design, and that complexity is where errors hide.

Plans have more rules than the spreadsheet can cleanly express

A typical commission plan includes a base rate, tiered accelerators above quota, SPIFs for strategic products, split credit rules for deals involving multiple reps, clawback provisions for churned customers, and exceptions for deals that were negotiated outside standard pricing. Each rule adds conditional logic to the spreadsheet: if the rep exceeded 100% of quota, apply the accelerator; if the deal included Product X, add the SPIF; if the deal was split, allocate 60/40 based on the split agreement; if the customer cancels within 90 days, reverse the commission.

These rules interact. A deal that triggers both the accelerator and the SPIF, involves a split, and later gets partially cancelled requires five separate calculations that depend on each other. The spreadsheet handles this through nested formulas, helper columns, and manual overrides. Each layer adds fragility.

Sales data arrives in inconsistent formats

Commission calculations require sales data: closed deals, booking amounts, product mix, customer information, close dates. This data comes from the CRM, but it rarely arrives in a format that maps directly to the commission plan.

The CRM records the total contract value. The commission plan pays on annual recurring revenue. The CRM records the close date. The commission plan credits the deal to the quarter in which the signed contract was received. The CRM records one account owner. The deal involved two reps with a split agreement that exists in an email, not in the CRM.

Translating CRM data into commissionable amounts requires a set of transformations that are often performed manually: filtering out non-commissionable revenue, applying revenue recognition rules, adjusting for multi-year deals, and mapping deals to the correct rep and territory. Each transformation is a potential error point.

Mid-period plan changes and exceptions

Commission plans change. A new product launches mid-quarter with a temporary SPIF. A territory is realigned. A rep is promoted and moves to a new plan mid-period. A deal gets a special commission rate approved by the VP of Sales in an email that was forwarded to the commission analyst.

Each exception requires a manual adjustment to the spreadsheet. These adjustments are often not documented beyond the email thread that authorized them. When the commission analyst processes the next cycle, they need to remember which exceptions are still active, which have expired, and which deals were already adjusted in the prior period.

Quota attainment calculation is its own problem

Before commissions can be calculated, quota attainment must be determined. What counts toward quota? New business only, or renewals too? Gross bookings or net of cancellations? Does a multi-year deal count at full contract value or annualized? Is quota prorated for a rep who started mid-quarter?

These rules are defined in the plan document but applied in the spreadsheet through formulas that may not precisely match the plan language. A plan that says "net new ARR" and a spreadsheet that sums the "Amount" field from the CRM are only equivalent if the CRM's Amount field actually represents net new ARR, which it often does not without adjustment.

‍

What commission verification needs to check

‍

A systematic commission verification compares three sources: the commission plan document (what should be paid), the sales data (what was sold), and the calculated payout (what was actually computed). Each comparison catches a different category of error.

1. Rate and tier application

Every deal's commission rate should match the plan's rate schedule for that rep, that product, and that attainment level. A rep at 105% of quota should be paid at the accelerator rate, not the base rate. A deal that includes a SPIF-eligible product should include the SPIF. A deal below the minimum threshold should not generate a commission.

Rate verification catches the most common commission errors: deals paid at the wrong tier because the attainment calculation was slightly off, SPIFs missed because the product categorization was wrong, or accelerators not applied because the spreadsheet referenced the wrong quota figure.

2. Commissionable amount accuracy

The amount on which the commission is calculated should match the revenue recognition rules defined in the plan. A $120,000 three-year deal might be commissionable at $120,000 (total contract value), $40,000 (annual value), or some other basis depending on the plan. The commissionable amount in the spreadsheet should match the plan's definition, not the CRM's default amount field.

This check catches systematic errors that affect every deal: if the spreadsheet uses the wrong revenue basis, every commission calculation in the period is wrong by a consistent percentage.

3. Split credit and territory assignment

Deals involving multiple reps should be credited according to the split agreement. A 60/40 split should produce commissions that sum to the same total as a single-rep deal at the same rate. Territory assignments should match the current territory map, which may differ from the CRM's account assignment if territories were recently realigned.

Split credit errors are common because splits are often managed outside the CRM. The split agreement exists in an email or a side document. The commission analyst applies it manually. A 60/40 split entered as 50/50 changes two reps' paychecks.

4. Clawback and adjustment accuracy

Commissions paid on deals that subsequently cancelled, downsized, or were credited should be reversed according to the plan's clawback provisions. The clawback should be applied in the correct period, at the correct rate, and only if the cancellation occurred within the clawback window defined in the plan.

Clawback errors can go in either direction: a clawback applied to a deal that cancelled outside the window (rep underpaid), or a clawback not applied to a deal that cancelled within the window (company overpaid).

5. Prior period reconciliation

The current period's calculation should account for adjustments from prior periods: late-booked deals credited retroactively, corrections to prior calculations, and clawbacks applied to previously paid commissions. The running balance for each rep should reconcile with what has been paid to date.

Prior period errors compound. An uncorrected error in Q1 carries through Q2, Q3, and Q4, and may not surface until the annual true-up or an audit.

‍

From building the calculation to verifying it

‍

The time-consuming portion of commission processing is the calculation itself: pulling sales data, applying plan rules, handling exceptions, and producing payout statements. The critical portion that gets skipped is the verification: independently checking that the calculation matches the plan.

The Agent handles the verification. Upload the commission plan document (PDF or spreadsheet), the sales data extract (CRM export, booking report), the calculated commission statements, and optionally the prior period payment history. Describe what the verification should check:

"Compare each rep's commission calculation against the plan terms and the sales data. Verify that quota attainment is calculated correctly. Check that the correct rate tier was applied to each deal. Confirm commissionable amounts match the plan's revenue definition. Verify split credits match the split agreements. Check that clawbacks were applied correctly and within the plan's clawback window. Flag discrepancies between the calculated payout and what the plan and sales data indicate the payout should be. Produce an exception report with the specific variance per rep and per deal."

The output is a verification report: each rep, each deal, the plan rate that should apply, the rate that was applied, the commissionable amount per the plan definition versus the amount used, the split allocation versus what was credited, and any clawback discrepancies. Every variance is quantified with the dollar impact and the specific plan clause that governs it.

The finance team reviews the exception report, confirms or overrides each finding, and adjusts the commission statements before payout. The verification that was previously skipped now runs every cycle, against every deal, for every rep.

The Agent works with the files the team already has: the plan document, the CRM export, the commission spreadsheet. No CRM integration or commission software implementation required.

‍

What the numbers look like

‍

A mid-market SaaS company with 35 sales reps, tiered commission plans, and quarterly payouts averaging $420,000 in total commission expense.

Before: The commission analyst spends four to five days each quarter building the calculation. The spreadsheet has 12 tabs, 40+ formulas, and a manual override log. The controller reviews the total expense but does not verify individual calculations. Sales reps submit disputes after receiving their statements; roughly 8-10 disputes per quarter, each requiring two to three hours to investigate and resolve. An internal audit last year found that 6% of deal-level calculations contained errors, split roughly evenly between overpayments and underpayments. The net dollar impact was small because errors partially offset, but the gross error rate was $51,000 on $1.68 million in annual commissions, and two reps cited commission accuracy as a factor in their decision to leave.

After: All 35 rep calculations verified against the plan document and sales data. The verification report flags 14 discrepancies: 3 deals where the accelerator tier was applied at 100% attainment instead of requiring 101%, 4 deals where the commissionable amount used total contract value instead of annual value, 2 split credit errors (one 60/40 entered as 50/50, one deal credited to a rep who transferred territories), 3 clawbacks applied outside the plan's 90-day window, and 2 SPIF-eligible deals where the SPIF was not included. Total gross error: $18,400 this quarter. The finance team reviews and corrects in three hours. Rep disputes drop from 8-10 to 1-2 per quarter.

The specifics shift by industry:

• In manufacturing, commission plans for distribution sales reps often include volume-based bonuses tied to specific product lines, rebate pass-throughs, and territory-based overrides for house accounts. A rep who covers both direct and distributor sales may have different rates for each channel, and the booking data that determines which channel a deal belongs to may not match the commission plan's channel definitions.
• In CPG, broker commissions are calculated as a percentage of net sales through each retail account, with adjustments for promotional allowances, returns, and chargebacks. The broker's commission statement should reconcile with the company's sales data net of trade deductions, but the two data sources (internal sales reports and retailer remittance advice) rarely agree without adjustment. Verification requires comparing the broker's claimed commissionable sales against the company's net sales per account.
• In professional services, commission plans often include project-based bonuses, utilization multipliers, and margin-based accelerators. A deal that generates revenue over 18 months may trigger commission at booking, at project milestones, or at cash collection, depending on the plan. Tracking which commission trigger applies to which deal across multiple active projects requires matching the plan's payment timing rules against the project accounting data.

Every rate, amount, split, and clawback is verified against the plan document and the sales data. When a rep disputes their statement, the verification report provides the specific calculation, the plan clause, and the underlying data for every line.

‍

Trust is built on accuracy, not speed

‍

Commission accuracy is not just an accounting problem. It is a retention problem, a morale problem, and a compliance problem. Reps who do not trust their commission statements spend time auditing their own pay instead of selling. Managers who cannot explain the calculation to their team lose credibility. Finance teams that cannot defend the numbers to auditors create risk.

The constraint has been that verification requires independently recalculating every deal against the plan, which takes as long as the original calculation. When the verification runs systematically against every deal, every rep, every cycle, the commission process gains the audit trail it has always lacked. The spreadsheet may still be how commissions are calculated. But it is no longer the only record of whether the calculation is correct.

‍