Check Sales Commissions Against Billing Before They Are Paid

1. 1

   Freeze the inputs on a fixed day each month (for example the third business day): export closed-won deals for the commission period (deal id, owner, split owners and percentages, amount, close date, contract start, term, products) and the invoices, payments, refunds, and credit notes from Stripe or your invoicing system, and save both as dated tabs so the review runs on a snapshot, not live data

2. 2

   Write the comp plan as a rules tab before automating anything: eligible products, rate by role and attainment tier, allowed splits, whether commission is earned on booking, invoice, or cash collected, the clawback window and triggers (for example refund, cancellation, or nonpayment within the plan's window), and caps; finance owns this tab and the signed plan document wins any conflict

3. 3

   Match each deal to billing on a stored id first (the Stripe customer or subscription id, or the invoice number, saved on the CRM deal), then fall back to customer domain plus amount and date, and mark every deal matched, partial, or unmatched

4. 4

   Run the checks as formulas or code, not AI judgment: close date outside the period or later than the first invoice, deal amount different from the invoiced amount by more than a tolerance you set, split percentages that do not sum to 100 or credit someone not on the plan, ineligible products, a commission base on the wrong basis (booking vs cash), and clawback candidates from refunds, credit notes, cancellations, or unpaid invoices inside the window

5. 5

   Write every flag to a review tab with deal id, rep, check failed, CRM value, billing value, difference, calculated commission, and the plan rule cited; an LLM step may draft a one-line plain-English explanation per flag, but it never changes an amount or clears a flag

6. 6

   Finance reviews and approves: the controller or FP&A owner, not the person who ran the calculation, marks each flag approved as is, corrected (with the corrected value and reason), or held, asks sales ops to fix the CRM record at the source, and signs off the total; post a Slack summary to the finance channel and send each rep only their own lines

7. 7

   Pay only from the approved tab: payroll or the commission tool imports approved amounts only, held items roll to next month with a note, and the snapshot, flags, approvals, and corrections are kept as the audit trail for the period; if you capitalize commissions under ASC 340-40, feed the approved amounts, not the calculated ones, into the amortization schedule

Tool cost here is your automation plan and LLM usage, which depend on your vendors and volume, so it is an input. Every number below is an assumption; replace each with your own figure.

Inputs (all assumptions)

Replace each value with your own figure.

• DClosed-won deals per month60AssumptionCount last quarter's closed-won deals and divide by 3.
• TMinutes to check one deal by hand against the invoice and the plan10AssumptionTime ten deals during the next manual review.
• SShare of deals flagged for review after the automated checks15%AssumptionUse the flag count from your first automated month.
• VMinutes to review and approve one flagged deal15AssumptionTime the first month of reviews.
• RLoaded hourly cost of the finance reviewer$75AssumptionSalary plus benefits, divided by working hours.
• KMonthly tool cost for automation runs and LLM usage$50AssumptionYour Zapier or n8n plan share plus API usage, from the vendors' current pricing and your invoices.

Formulas

Manual review hours per monthD x T / 60

60 x 10 / 60 = 10 hours

Review hours per month after automationD x S x V / 60

60 x 0.15 x 15 / 60 = 2.25 hours

Time saved per monthD x T / 60 - D x S x V / 60

10 - 2.25 = 7.75 hours, about 1.8 hours per week (x 12 / 52), shown rounded as 2 hours per week on this page

Value of time saved per month(D x T / 60 - D x S x V / 60) x R

7.75 x $75 = $581.25

Break-even deals per monthK / ((T - S x V) / 60 x R)

$50 / ((10 - 2.25) / 60 x $75) = 5.2, so the checks cover their tool cost from about 6 closed-won deals a month

• The math counts reviewer time only. It leaves out commission errors caught, because their size depends on your plan and history; if you track them, add the monthly amount recovered or avoided as its own input.
• Approval time is part of V, so the human sign-off is never treated as a cost to remove.
• Rerun the math after the first month with your real flag rate S and review minutes V.

Template outline

The structure to recreate in your own workspace. Field names and thresholds are starting points.

Workbook: Commission check YYYY-MM (one per period, read-only after sign-off)

Tab deals_snapshot (CRM export, dated):
  deal_id, account, owner, split_owner_2, split_pct_1, split_pct_2, amount, close_date,
  contract_start, term_months, products, billing_customer_id, invoice_number

Tab billing_snapshot (Stripe or invoicing export, dated):
  customer_id, invoice_number, invoice_date, invoice_amount, amount_paid, paid_date,
  refunds, credit_notes, subscription_status

Tab plan_rules (owned by finance, matches the signed plan):
  role, tier_floor, tier_ceiling, rate, eligible_products, basis (booking | invoice | cash),
  split_rule, clawback_window_days, clawback_triggers, cap

Tab matches:
  deal_id, match_method (stored id | domain + amount + date), match_status (matched | partial | unmatched)

Tab flags (one row per failed check):
  deal_id, rep, check, crm_value, billing_value, difference, calculated_commission, rule, explanation
Checks:
  1. period: close_date outside the period, or close_date > first invoice_date
  2. amount: ABS(amount - invoice_amount) > tolerance (set by finance, in dollars or %)
  3. splits: split_pct_1 + split_pct_2 <> 100, or a split owner not in plan_rules
  4. products: product not in eligible_products
  5. basis: commission base uses booking when the plan says cash (or the reverse)
  6. clawback: refund, credit note, cancellation, or unpaid invoice inside clawback_window_days
  7. unmatched: no billing record found

Tab approvals:
  deal_id, decision (approved | corrected | held), corrected_value, reason, reviewer, reviewed_at
  Reviewer is never the person who ran the calculation

Slack to #finance-close:
"Commission check YYYY-MM: {{deals_checked}} deals, {{flag_count}} flags
 ({{period_flags}} period, {{amount_flags}} amount, {{split_flags}} split, {{clawback_flags}} clawback).
 Review tab: {{sheet_url}}. Sign-off due {{due_date}}."

Rep statement (one per rep, own lines only):
  deal, amount, rate, commission, status (approved | corrected | held), reason

Guardrails:
- Nothing is paid unless its row is approved or corrected in the approvals tab
- The LLM writes explanations only; amounts come from formulas and plan_rules
- Snapshots are never edited after export; fixes go to the CRM and next month's snapshot