Sales Commission Data Reconciliation Test

A sales commission data reconciliation test traces every eligible deal line from CRM and finance events through the approved plan, credit allocation, rate, timing, rep total, approval, and payroll handoff. It reports exact mismatches and hard-gate failures; it does not declare a universal accuracy benchmark.

This is not a commission software buyer guide, compensation plan design, or rate-structure tutorial. Qualified finance, accounting, legal, tax, payroll and compensation owners must approve the actual policy. This article is not accounting, legal, tax, payroll, or compensation advice.

Define source contracts and authority

Write a field-level source contract before testing. For bookings, name the CRM/order/contract authority and event: signed date, booked date, activation, product line, amount, term, currency and status. For invoices, collections, credits, cancellations, refunds and clawbacks, name billing, payments, ERP or finance ledger authority, stable transaction ID, effective/accounting date and reversal linkage.

For territory, owner, overlay and split, define CRM or approved compensation-assignment authority, effective dates and history. For plan version, eligibility, quota, rates, accelerators, caps, floors, thresholds, draws, guarantees and clawback rules, identify the signed/approved plan source. For FX, identify rate source, rate date, currency pair, direction and rounding. Payroll owns paid amount/date and adjustment reference, not the underlying eligibility decision unless policy says otherwise.

Salesforce documents opportunity and product splits; Salesforce Spiff documents calculations using synced opportunity splits. These are bounded examples, not proof that CRM credit equals approved commission credit.

Freeze the period and plan version

Freeze the population, source snapshots, and exact plan version. Record test period, timezone, cut-off, included entities/reps/plans/currencies/products, event dates, source extract times, schemas, row/file counts, hashes, plan approvals and all amendments. Preserve immutable originals and a normalized test copy.

Define late-arriving policy: which events enter current close, next period, or a controlled reopen. Separate transaction date, effective date, accounting date, CRM close date, invoice date, collection date, refund date, commission earned date, payroll period and payment date. “March commission” is ambiguous until these are explicit.

Freeze quota attainment before rate evaluation if the plan uses accelerators. State whether attainment is based on bookings, ARR, revenue, collections or another measure; whether credits are gross/net; and how cancellations/refunds retroactively affect attainment and prior payments. Do not rewrite the plan after seeing mismatches.

Build a deal-line truth set

Build truth at the lowest payable deal-line and participant grain. Stratify ordinary lines plus new hires/terminations, territories, overlays, split deals, multi-product, multi-year, ramps, thresholds, currencies, discounts, cancellations, refunds, negative adjustments, caps/floors and prior disputes. Include every high-value/high-risk line and a random sample of ordinary lines.

For each line capture deal/order/product, participant, role/split type, source event IDs, eligible basis, credit percentage/amount, quota-credit amount, attainment band, rate, accelerator, cap/floor/draw, FX rate/source/date, rounding rule, commission amount, earned period, pay period, clawback linkage, plan/rule version and reviewer evidence.

Recalculate independently from source contracts and plan text. Do not copy the commission platform’s derived amount into the “truth” column. Use two calibrated reviewers for ambiguous plan interpretation and route disagreement to the authorized plan owner before comparing the system.

Measure eligibility, credit, rate, amount, and timing

Score each decision layer separately. Eligibility precision = system-eligible lines that truth says eligible / all system-eligible lines. Eligibility recall = truth-eligible lines found / all truth-eligible lines. Credit precision compares participant, split type and credited basis exactly. Rate precision compares plan/rule, attainment band and rate. Amount precision compares currency-rounded line amount. Timing precision compares earned and pay period. Rep-total variance = system total − truth total.

Publish counts, denominators, absolute currency variance and signed variance by rep, plan, product, currency and error type. A correct rep total can hide offsetting overpayment and underpayment; require line-level correctness. Define tolerance only for approved rounding and FX rules, not as a blanket percentage.

Trace every variance to the first failed layer. An amount mismatch caused by wrong eligibility is not merely “calculation variance.” Root-cause categories should support remediation: source missing/duplicate, mapping, eligibility, credit, attainment, rate, FX, rounding, timing, clawback, manual override or payroll transfer.

Inject lifecycle and calculation edge cases

Seed deterministic edge cases before close. Test duplicate deal and replayed event; missing product line or invoice; late collection/refund; owner change before/after effective date; territory transfer; split that totals below/at/above policy; overlay plus revenue credit; merged opportunity; reopened/canceled deal; retroactive plan amendment; new-hire ramp; termination; threshold crossing; accelerator rollback; cap/floor; draw recovery; negative commission; multi-currency line; missing FX rate; inverse pair; half-cent rounding; and clawback of a prior-period payout.

Stripe’s official refund documentation supplies one bounded example of a reversal linked to a payment. It does not determine commission treatment. Test full, partial, multiple, failed and late refunds using the organization’s billing source and approved plan.

Retry ambiguous imports and payroll exports. The same stable event must not create two credits, payments or clawbacks. Change an owner after the frozen effective date and verify history—not current state—drives the result. Apply the CRM data-quality controls before blaming the commission engine for missing or stale inputs.

Reconcile rep totals and payroll handoff

Reconcile bottom-up and top-down. Bottom-up sums approved deal-line/participant amounts to rep, team, plan, entity, currency and payroll earning code. Top-down maps every commission-system payment line and payroll earning/adjustment back to those same truth lines. Each expected line has one outcome; every paid line has one approved source.

Reconcile opening balance/draw, current earnings, prior adjustments/clawbacks, recoveries, taxes/deductions outside commission scope, payment and closing balance. Finance defines any ledger tie-out. Payroll confirms file/control totals, accepted/rejected lines, payment period and final paid amounts. A controlled commission tracking template can hold the evidence map; never email uncontrolled spreadsheets containing unnecessary personal/pay data.

Set separate states: calculated, reviewed, approved, exported, accepted by payroll, paid, disputed, adjusted and reopened. A “paid” flag in commission software is not evidence the payroll system accepted or disbursed the amount.

Approve, audit, dispute, close, and reopen

Require evidence and segregation at every close gate. Preparer attaches frozen sources, plan version, reconciliation, variances and exceptions. Reviewer independently checks sample/critical lines and totals. Authorized compensation/finance owner approves interpretations and adjustments. Payroll acknowledges accepted control totals. Preserve who, what, when, prior/new value, reason, evidence and approval.

The SEC’s internal-control rule release describes controls over initiating, recording, processing and reconciling transactions, plus evidential matter and operating-effectiveness testing. Apply the control principle in proportion to your organization; it does not establish this test as an accounting requirement for every company.

Create a dispute packet: rep/manager, line IDs, plan/rule, source evidence, requested correction, owner, status, decision, approver, resulting adjustment and pay period. Lock the closed period. Reopen only through an authorized ticket with scope, reason, impacted reps/totals, source correction, retest, approvals, new version and notification. Preserve the original close.

Apply hard gates and the worked example

Hard gates override an aggregate score: unapproved plan/amendment; missing authoritative source; wrong rep/entity/currency; unauthorized override; duplicate payment; omitted eligible high-risk line; failed suppression of terminated/ineligible participant; unexplained variance above approved materiality/tolerance; payroll control-total mismatch; missing audit/approval; or inability to reproduce a line.

Synthetic example—not a platform result: truth has 200 eligible deal-participant lines. The system marks 202 eligible: 198 are true, four are false positives, and two truth lines are missing. Eligibility precision = 198/202 = 98.0%; recall = 198/200 = 99.0%. These are illustrative, not acceptable benchmarks.

One line has a $40,000 eligible basis, 50% credit, and 8% rate: $40,000 × 0.50 × 0.08 = $1,600 before plan-defined adjustments. The system pays $1,760 because it used a 10% accelerator then applied a $240 clawback: $2,000 − $240. Truth uses no accelerator and a $160 proportional clawback: $1,600 − $160 = $1,440. Variance is +$320. Resolve attainment/rate and clawback policy separately; do not patch the total.

Rep truth totals $12,450 while the system totals $12,770, a +$320 variance. Payroll received $12,770. The same mismatch across all three systems does not make it correct; it proves consistent propagation of the upstream calculation error.

Print the reconciliation checklist

A defensible commission close is reproducible at the deal-line level, tied across source authorities, approved with evidence, and reversible through controlled adjustment—not merely a spreadsheet whose grand total looks plausible.