Sales Commission Payout Calculator

--- name: sales-commission-payout-calculator description: Calculates sales commission payouts for a period from your comp plan and a deals CSV - quota credit by deal type, split deals, tiered rates with accelerators (marginal or flat), credit and payout caps, SPIFFs, clawbacks for early cancellations, pay on booking or collection, and draws. Holds deals with bad splits, unknown reps or unapproved discounts and writes rep statements. Includes an offline stdlib Python calculator. --- # Sales Commission Payout Calculator You help sales ops, finance and founders pay commissions that are right the first time. Commission runs go wrong in predictable ways: a split deal adds up to 90%, an accelerator is applied to the whole quarter instead of the credit above the threshold, a renewal is credited at full value, a deal with an unapproved 30% discount slips through, a customer cancelled after 55 days and nobody clawed the commission back, or the same deal is in the export twice. You turn the plan and the deal export into a payout per rep that anyone can recompute, with every held deal and judgement call listed. You apply the plan the user gives you. You do not decide what the plan should say, whether a clawback or a negative balance can legally be deducted from pay, or how commission is taxed; say so and point to the signed comp plan, HR/payroll and local employment law. ## Files in this skill - `scripts/commission_calc.py` - reads a plan JSON (period, tiers as fractions of quota, marginal or flat method, credit cap, max payout, credit per deal type, discount limit, SPIFFs, clawback window, pay on booking or collection, reps with quota and optional draw) and a deals CSV (deal id, close date, account, owner split, type, amount, discount, billing, status, cancel date, commission already paid, approval, collection); computes credit, tier slices per deal in close order, caps, SPIFFs, clawbacks, deferrals and draw top-up or recovery with Decimal arithmetic; reports findings CM01-CM11, a payout table, per-rep deal lines and held/excluded deals (Python 3.8+ standard library only) - `references/plan-mechanics.md` - tiers, marginal vs flat, caps, credit rules, splits, SPIFFs, clawbacks, draws, timing, with worked numbers - `references/checks-and-codes.md` - every finding code, its severity and what to do about it, and the input fields - `templates/commission-statement.md` - the statement each rep receives and signs off - `examples/comp-plan.json` - an AE plan for October 2026: 4 tiers (5/8/12/15%), credit cap 200%, max payout 8,000, renewals at 50% credit, 20% discount limit, annual-prepay SPIFF, 90-day clawback, three reps, one with a recoverable draw - `examples/deals.csv` - 15 rows: normal deals, a 60/40 split, a renewal, an approved and an unapproved big discount, a split that adds up to 90%, a deal for an unknown rep with a thousands separator, two cancellations (inside and outside the clawback window), a September deal and a duplicate row - `examples/payout-report.md` - the real calculator output on the example ## Workflow ### 1. Collect the plan and the deals Ask for the comp plan document (or the relevant page) and turn it into a plan JSON like `examples/comp-plan.json`. Confirm the details that change the money: are tiers marginal (each slice of credit at its own rate) or flat (the reached rate applies to all credit)? Is attainment measured on bookings, ACV or margin? How are renewals and expansions credited? Is there a credit cap or a payout cap? When is commission earned: at booking or when the customer pays? How long is the clawback window and does it cover the full commission? Then ask for the CRM export of closed-won deals for the period plus cancellations of earlier deals, with the commission already paid on those. ### 2. Run the calculator ``` python3 scripts/commission_calc.py examples/comp-plan.json examples/deals.csv python3 scripts/commission_calc.py plan.json deals.csv --rep ana python3 scripts/commission_calc.py plan.json deals.csv --json python3 scripts/commission_calc.py --self-test ``` Exit 0 = no high findings, 1 = high findings (deals held that need a decision), 2 = bad input. Without a shell, follow `references/plan-mechanics.md`: sort each rep's deals by close date, add up credit, split it at each tier threshold and multiply each slice by its rate. ### 3. Read the result - High first: CM01 (unknown rep, bad amount or date, unknown deal type) and CM02 (split not 100%). These deals are held - get the answer from sales ops before payroll. - Medium: CM05 (discount over the limit without approval - held), CM04 (duplicate row), CM08 (clawback applied - check the amount paid), CM11 (clawbacks bigger than earnings - negative balance). - Low/info: deals from other periods (CM03), cancellations outside the window, caps (CM06), accelerators (CM07), deferrals (CM09), draws (CM10). - Check one rep by hand against the per-deal rate slices before sending anything. ### 4. Resolve and re-run For every held deal record the decision (correct split, approval found, rep assignment) in the CSV and re-run. Never change the plan to make a number look right; if the plan is ambiguous, write the question down and show both results (for example with `tier_method` flat vs marginal). ### 5. Deliver Return the payout table for approval, a statement per rep from `templates/commission-statement.md` with every deal, rate slice, cap, clawback and draw line, the list of held deals with the open question, and notes for payroll (negative balances, draw balances, deferred amounts). ## Example result On the example October payouts total 9,500.00 EUR, with 3 deals held. Ana Ruiz reaches 158.7% of her 60,000 quota (renewal Delta Foods credited at 50%), earns 8,280.00 on marginal tiers plus 400.00 in annual-prepay SPIFFs and is capped at the 8,000.00 maximum. Ben Okafor earns 690.00 but the Ironclad deal cancelled 55 days after closing claws back 960.00, so he is paid 0 and carries -270.00 forward. Chloe Martin earns 1,050.00, below her 1,500.00 recoverable draw, so the draw tops up 450.00 and her balance rises to 1,050.00. Held: Granite Works (split 50/40 = 90%), Nimbus Air (rep "dave" not on the plan) and Fjord Outdoor (30% discount, limit 20%, no approval). ## Rules - Follow the signed plan; when the plan and the data disagree, hold the deal and ask - never guess a split, a rep or an approval. - Show the arithmetic: every payout must be traceable to deal credit, tier slices and adjustments. - Apply tiers in close-date order and say whether the plan is marginal or flat. - Treat clawbacks and negative balances carefully: state what the plan allows and point to HR/payroll and local law before deducting from pay. - Commission data is confidential: share each rep only their own statement. - This is a calculation aid, not legal, tax or payroll advice.