Skip to content
Workflow · Reconcile · Opening soon

Insurance commission reconciliation

Export your Excel book of business and carrier commission statements, then load them into the workflow. It builds matching keys, calculates expected commission from premium and rate, and compares that to paid commission.

Early access opens shortly. Check back soon.

Someone typed

“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”

The agent read it as

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.

  1. File
    Book of business
    CSV export · 30 rows
  2. File
    Carrier commission statements
    CSV export · 30 rows
  3. Step
    Make book matching keys
    Step 1
  4. Step
    Make statement matching keys
    Step 2
  5. Join
    Match carrier statements
    30 of 30 paired
  6. Step
    Compare expected and paid
    Step 4
  7. Result
    Commission Match
    30 rows · 3 flagged
  8. Schedule
    Runs when you say
    Proposed

Checked on the sample files: every step ran, 30 rows came out, and the join paired 30 of 30 rows.

What you get back

The result of that run, the rows to look at first.

Commission MatchSample data30 rows
27 agrees3 short paid
Policy numberStatusExpected commissionCommission paidDifferenceCarrierPremiumCommission rateStatement premium
PL-1010Short paid99090090HarborPoint Insurance9,90010%9,900
PL-1029Short paid2,0521,710342Northstar Mutual17,10012%17,100
PL-1030Short paid77570075HarborPoint Insurance7,75010%7,750
PL-1001Agrees1,4941,4940Northstar Mutual12,45012%12,450
PL-1002Agrees8608600HarborPoint Insurance8,60010%8,600
PL-1003Agrees1,7841,7840Summit Casualty22,3008%22,300
PL-1004Agrees6906900Northstar Mutual5,75012%5,750
PL-1005Agrees1,7461,7460Pinnacle Assurance19,4009%19,400
PL-1006Agrees7307300HarborPoint Insurance7,30010%7,300
PL-1007Agrees1,2641,2640Summit Casualty15,8008%15,800
PL-1008Agrees5765760Pinnacle Assurance6,4009%6,400
PL-1009Agrees1,5841,5840Northstar Mutual13,20012%13,200
30 rows in the result · 12 shown, problems first. Sample data: illustrative figures, not business results.

How it works

How to use the Commission Match workflow

  1. 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.
  2. 2Swap in your exportsUpload your own files as they come out of your tools. The agent checks the workflow on your rows.
  3. 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?
Export your Excel book of business and carrier commission statements, then load them into the workflow. It builds matching keys, calculates expected commission from premium and rate, and compares that to paid commission. Each policy is marked Agrees, Short paid, or Missing. You can use the results to find payment differences and review the policies that do not match.
What export should you use?
Use the exported Excel files from your book of business and from the carrier commission statements. The workflow reads the rows in those files and compares them by matching fields.
What happens to rows that do not match?
Rows that do not find a match are shown as Missing. Rows that match but have a lower paid amount than expected are shown as Short paid. Rows that match and agree are shown as Agrees.
How do you change the rules?
You change the matching and comparison rules in words, such as which fields to use for matching and how to calculate expected commission. Then the workflow runs again with the updated instructions.

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.

Early access opens shortly. Check back soon.