Sample commission reconciliation report

Example data. The agency, carriers, insureds, policy numbers, files and amounts on this page are invented to show what Lapidar produces. No real agency’s statements were used. The layout, headings, finding names and the wording of the notes and the letter are the ones the app uses.

When you run a check, Lapidar matches every carrier statement line to a policy in your book, works out what the commission schedule says you should have been paid, and lists the differences. Below is that result for one invented month: three carriers, three statements (a PDF, a spreadsheet and a CSV) and one book export. Use the filters to look at one carrier or one kind of finding; every line points back to the page and row it came from.

How to read the findings

• Missing: the policy is in your book and owes commission this period, and no statement paid anything on it. The expected amount comes from the book premium and your schedule rate; for a policy billed in installments, only the installments due in the period count.
• Underpaid: the carrier paid, but less than book premium times schedule rate, by more than your tolerance (one dollar unless you change it).
• Paid twice: the same amount for the same policy, installment and transaction type appears more than once. The note names the line it repeats.
• Chargeback: a negative or reversal line. Lapidar pairs it with the payment it reverses, on these statements or on statements checked in an earlier run, or tells you the original isn’t there.
• Overpaid: paid more than expected. It goes to review, not to the letter, because the carrier may take it back.
• Not in book: paid on a statement with no matching policy in your book. Often a book that needs updating, sometimes a payment that isn’t yours.
• Unchecked: matched, but there is no schedule rate or premium to compute an expected amount against. It goes to review.
• OK: paid what the schedule says, within tolerance.

Only Missing and Underpaid count toward “Owed to the agency” and go on the carrier letter. Everything else is listed for your review. How these situations arise is covered in our guides on missing renewal commissions and commission chargebacks.

The carrier letter

For each carrier, Lapidar prepares a printable report you can save as PDF and send yourself. It lists what is owed with the source of each line, then the items for your review, then the statements reconciled. Lapidar never contacts carriers for you. Items you mark resolved drop off the letter. The structure follows what we suggest in our guide to writing a commission discrepancy letter.

What went in

Three kinds of file produce a result like this one:

• Your book of business: an export from your agency management system with carrier, policy number, line, new or renewal, and written premium. CSV or XLSX.
• Your commission schedule: the rate each carrier agreed to pay, by line and by new or renewal. Upload it as CSV or XLSX, or type it into a table in the app.
• Carrier statements: CSV, XLSX, or a PDF with selectable text, in the layout the carrier sent. A layout Lapidar doesn’t recognise needs its columns matched once, and that layout is then remembered.

Scanned or photographed PDFs can’t be read yet, and nothing connects to your AMS directly. The full list is on the commission reconciliation software page.

Frequently asked questions

Is this a real agency’s data?

No. The agency, carriers, insureds, policy numbers, file names and amounts are invented. The structure of the page, the finding names and the wording of the notes and the carrier letter match what the Lapidar app produces.

What counts as money owed to the agency?

Only findings marked Missing or Underpaid. Paid twice, chargebacks, overpaid and not-in-book lines are listed for your review but are not added to the total or put on the carrier letter.

Does Lapidar send the letter to the carrier?

No. Lapidar prepares a printable report per carrier that you can save as PDF and send yourself. It never contacts carriers on your behalf.

Can I try this on my own statements?

Yes. Creating an account at app.getlapidar.com is free; running checks needs the founding pilot subscription, USD 99 a month plus applicable tax, cancel anytime.