Taking commissions out of the spreadsheet
- Organization
- Life insurance organization
- Built with
- Zoho CRM, Deluge, Custom widget
The problem
Commissions lived in a spreadsheet that a small number of people understood. Every carrier statement meant working out, by hand, who got what: the writing agent, the consultant, case managers, strategists, referral partners. The rules changed by policy year, some roles were paid only in year one, and some payouts were capped. When a producer asked "why was I paid this?", the answer was buried in a workbook.
What I built
- Split records on every policy, entered through a custom widget that checks the math live as you type, so splits that don't add up never get saved.
- Team pay rules built in. Year-one team pay, role buckets shared across multiple people, and top-ups that stop or continue after year one based on a single checkbox.
- Policy year calculated from dates, not typed in. When the dates don't agree with what the statement says, the case is flagged for review instead of guessed.
- Caps that flag instead of block. Payouts over the applicable cap are flagged and need a note before they're finalized, so edge cases get a human look without stopping the whole run.
- A payout button on each carrier statement that creates one payout record per payee, deducts flat amounts in the right order, and turns chargebacks into negative payouts automatically.
- Duplicate protection, so running a statement twice can't pay anyone twice.
What changed
The first live payout run reconciled to the penny against the manual calculation. Producers' questions are answered from the record, the rules are written down in the system instead of in someone's head, and paying people no longer depends on one person being in the office.