Insurance commission reconciliation
“match the book of business to the carrier commission statements, calculate expected commission from premium × rate and compare to paid commission, and show each policy as Agrees, Short paid, or Missing”
Match the Book of business to the Carrier commission statements, calculate expected commission from premium × rate and compare to paid commission, and show each policy as Agrees, Short paid, or Missing
What the agent built
4 steps, each one an ordinary Mammoth step you can open and change.
- FileBook of businessCSV export · 30 rows
- FileCarrier commission statementsCSV export · 30 rows
- StepMake book matching keysStep 1
- StepMake statement matching keysStep 2
- JoinMatch carrier statements30 of 30 paired
- StepCompare expected and paidStep 4
- ResultCommission Match30 rows · 3 flagged
- ScheduleRuns when you sayProposed
Checked on the sample files: every step ran, 30 rows came out, and the join paired 30 of 30 rows.
- Download Book of business sample (30 rows)Excel: open the workbook tab 'Book of business' and copy the used range
- Download Carrier commission statements sample (30 rows)Excel: open the workbook tab 'Carrier statements' and copy the used range
What you get back
The result of that run, the rows to look at first.
| Policy number | Status | Expected commission | Commission paid | Difference | Carrier | Premium | Commission rate | Statement premium |
|---|---|---|---|---|---|---|---|---|
| PL-1010 | Short paid | 990 | 900 | 90 | HarborPoint Insurance | 9,900 | 10% | 9,900 |
| PL-1029 | Short paid | 2,052 | 1,710 | 342 | Northstar Mutual | 17,100 | 12% | 17,100 |
| PL-1030 | Short paid | 775 | 700 | 75 | HarborPoint Insurance | 7,750 | 10% | 7,750 |
| PL-1001 | Agrees | 1,494 | 1,494 | 0 | Northstar Mutual | 12,450 | 12% | 12,450 |
| PL-1002 | Agrees | 860 | 860 | 0 | HarborPoint Insurance | 8,600 | 10% | 8,600 |
| PL-1003 | Agrees | 1,784 | 1,784 | 0 | Summit Casualty | 22,300 | 8% | 22,300 |
| PL-1004 | Agrees | 690 | 690 | 0 | Northstar Mutual | 5,750 | 12% | 5,750 |
| PL-1005 | Agrees | 1,746 | 1,746 | 0 | Pinnacle Assurance | 19,400 | 9% | 19,400 |
| PL-1006 | Agrees | 730 | 730 | 0 | HarborPoint Insurance | 7,300 | 10% | 7,300 |
| PL-1007 | Agrees | 1,264 | 1,264 | 0 | Summit Casualty | 15,800 | 8% | 15,800 |
| PL-1008 | Agrees | 576 | 576 | 0 | Pinnacle Assurance | 6,400 | 9% | 6,400 |
| PL-1009 | Agrees | 1,584 | 1,584 | 0 | Northstar Mutual | 13,200 | 12% | 13,200 |
How it works
How to use the Commission Match workflow
- 1Get early accessWe invite people in small batches, job by job. You'll land on this workflow, already built and checked on the sample files.
- 2Swap in your exportsUpload your own files as they come out of your tools. The agent checks the workflow on your rows.
- 3Change it in words, then turn it onSay what to change and see the new result before anything saves. Then choose when it runs.
Commission Match: questions
How do you track insurance commission?
What export should you use?
What happens to rows that do not match?
How do you change the rules?
Get early access to this workflow
It will open already built and checked on the sample files. Swap in your own exports and see your result.
Workflows like this
Match two sets of records line by line, and see what agrees, what differs and what is missing on either side.
- Bank reconciliationEvery line matched, flagged for a date check, or missing from one sideSee the workflow Template
- Payment reconciliationEach payout matched to its bank deposit, flagged for a date check, or missingSee the workflow Template
- Cash reconciliationEach day's takings matched to a bank deposit, or flagged as not bankedSee the workflow Template
- Payment processing fees by methodTrack payment processing fees by method each month, total sales and fees, and flag fee rates that are too high.See the workflow Built by the agent
- Three-way matchEach PO line: matched, partly received, billed more than received, or price differsSee the workflow Template
- Invoice reconciliationEach invoice paid in full, part-paid, overpaid or not paid, and payments with no invoiceSee the workflow Template