Commission Reconciliation
The StatementYour rate schedule against what the carrier actually paid.
The statement arrives, it looks about right, and it gets filed. Checking it properly means reading every line against a rate schedule that lives in somebody's head or in a folder of contracts.
A carrier sends a statement, and the agency's own book says what each policy should have paid at the rate agreed for that carrier and that product. This puts the two side by side, line by line, and names every line that came in short rather than summing them away.
About this tool
Commission Reconciliation: what it solves
Commission reconciliation is a rate card applied to a list, with one extra column: what somebody actually paid. The Commission Reconciliation sheet holds the agency's own schedule — carrier and product together, at the rate in that contract — and works out what each policy on a statement should have paid on its premium. It then shows the difference per line, and it lists every short line separately rather than only reporting a total, because a statement that nets to zero can still hide an underpayment canceled out by an overpayment. A policy whose carrier and product are not on the schedule is reported and left unpriced. It suggests no rate, knows nothing about what other agencies are paid, and takes no view on what to do about a difference — that part is a phone call.
A statement that nets to zero can still be wrong
This is the whole reason to check line by line rather than checking a total.
A carrier statement arrives. You compare the bottom of it against what you expected, the two are close enough, and the statement goes in the drawer. Inside it, one policy paid at a rate from a contract that ended in March and another paid at a rate that was never yours. One is short, one is over, and they very nearly cancel.
The total told you nothing. It could not have told you anything — that is what a total is for.
This sheet applies your own schedule to every line, shows the difference on each, and lists every short line separately. A statement that comes out even can still hand you a page of lines that do not agree.
The schedule is yours, and it is more specific than a rate
A rate belongs to a carrier and a product together. Not to a carrier. The same company pays differently on a new personal auto policy than on a renewal, and differently again on a commercial line. A schedule with one number per carrier is a schedule that will be wrong most of the time.
New and renewal are different numbers. Sometimes very different. A schedule that does not carry both is one somebody is going to have to remember, which means somebody is going to forget.
What the rate runs against. Written premium, earned premium, premium net of fees and taxes. A percentage is meaningless until that is settled, and it is settled in the contract rather than by convention.
Those live in agreements you signed, in a folder, and there is no service anywhere that can read them for you. That is why this is a sheet you fill in once rather than something somebody sells by the month.
The line that does not match anything
A policy arrives on a statement carrying a carrier and a product that are not on your schedule.
This does not guess. It does not fall back to the nearest rate, does not use the carrier’s other product, and does not quietly price it at zero and let it disappear into a total. It reports the line, says it could not be priced, and leaves it unpriced.
That refusal is the point. A tool that guessed would produce a confident number, and a confident wrong number on a commission statement is worse than no number, because somebody will act on it.
What it replaces
The spreadsheet with the rates typed into it, which was right when it was built and has since had a contract change nobody propagated. Or the check that does not happen at all, because the statement is forty pages and there is no version of doing this by hand that fits into a week.
You enter your schedule once — carrier, product, basis, rate. The sheet keeps it. Every statement after that is a paste and a read.
Who it is for
Insurance agencies and brokers — anywhere commission arrives on somebody else’s statement, computed by somebody else’s system, against contracts only you hold.
What it will not do
It will not suggest a rate. It does not know what your carriers pay, what other agencies are paid, or what a rate ought to be. It applies the schedule you entered, from the contracts you signed.
It takes no view on what to do about a difference. A short line is a phone call, and whether that call is worth making is a judgment about a relationship. This sheet finds the line. It does not tell you what the line means.
It will not net a shortfall away. Overpayments and underpayments are shown separately and never allowed to cancel, because the canceling is the failure that made this necessary.
Nothing you paste is sent anywhere. The arithmetic runs in your browser. A commission statement carries policy numbers and client names, and none of it leaves the machine you are sitting at. Close the tab and it is gone.
Yours would be built the same way.
This one runs on made-up data. The version built for your business runs on yours, and you own it outright. Builds start at $1,300. That is a floor, not a quote. What yours costs is agreed before any work begins. The first conversation is free.