Commission summary by carrier
Open a month of commission lines with the carrier on every row, such as a commission log or the filled-in commission tracking template. The lines are added up by carrier, then by line of business within each carrier, and each carrier's lines are compared with the statement total and the payment received that you type for it.
The file is read in your browser and stays on this device. Nothing is uploaded.
The file is read in this browser and is not uploaded.
These are matched from the header words. Check them and change any that are wrong. Only the commission amount is required; the carrier column groups the lines, and the line of business column breaks each carrier down.
For a file from one carrier with no carrier column, type the carrier's name in the last box. It is used for every row that names no carrier.
One row for each carrier on the lines. Type the total printed on that carrier's statement and the payment received. Each amount entered is compared with that carrier's lines.
| Carrier | Lines | Statement total | Payment received |
|---|
Summary
Commission by carrier
Each carrier against its statement total
Each carrier against the payment received
By carrier and line of business
By line of business, all carriers
What gets checked
- Each carrier's lines add up to the statement total entered for it, and to the payment received entered for it. A carrier that differs is named with both amounts.
- Every carrier and line-of-business subtotal matches a second pass over the lines, added a different way.
- Each carrier total is the sum of its line-of-business subtotals, and each line-of-business total is the sum of that line of business across the carriers.
- Carrier totals add up to all commission on the lines, and line-of-business totals add up to the same amount.
- The exported file is read back and its commission column adds up to the commission total.
- Every row with data was read. A row that could not be read is listed with the reason and the result is marked not verified.
- A warning, not a failure, for a carrier with nothing entered to compare it with, and for lines that name no carrier.
How it works
- Open the month's lines. Drop a spreadsheet (.csv or .xlsx) with one row per commission line. It is read in the browser. When the commission tracking template is opened, its Commission log sheet is read, and the statement totals on its Carriers sheet are filled in.
- Check the column mapping. The carrier, line of business, policy number, premium, and commission amount columns are matched from the header words and can be changed. For a file from a single carrier with no carrier column, type the carrier's name once.
- Type the statement totals. Each carrier found on the lines gets a row. Type the total printed on its statement, the payment received, or both. Each amount entered is compared with that carrier's lines.
- Review and export. The page shows commission by carrier, each carrier's lines beside its statement total and its payment with the difference (commission on the lines less the amount entered), the breakdown by line of business, and the certificate. Download Excel or CSV, or print.
Worked example
A made-up commission log for February with nine lines from three carriers. The statement totals typed are $689.00 for Carrier A, $625.00 for Carrier B, and $495.00 for Carrier C.
| Carrier | Lines | Premium | Commission on the lines | Statement total | Difference |
|---|---|---|---|---|---|
| Carrier A | 3 | $5,810.00 | $689.00 | $689.00 | $0.00 |
| Carrier B | 3 | $6,333.32 | $625.00 | $625.00 | $0.00 |
| Carrier C | 3 | $4,200.00 | $495.00 | $495.00 | $0.00 |
Carrier A's three lines are 600.00 + 125.00 − 36.00 = $689.00, Carrier B's are 250.00 + 125.00 + 250.00 = $625.00, and Carrier C's are 180.00 + 225.00 + 90.00 = $495.00, so the commission total is $1,809.00 on $16,343.32 of premium. By line of business across all three carriers: commercial package $850.00, general liability $125.00, homeowners $189.00, personal auto $395.00, and workers compensation $250.00, which also add up to $1,809.00. One of Carrier C's rows is typed as "carrier c" and is counted with Carrier C. If Carrier B's statement total were typed as $650.00, its row would show a difference of -$25.00, the statement check would name Carrier B, and the result would be marked not verified.
The example uses made-up names and numbers.
Questions
What file does it take?
Any .csv or .xlsx with a header row and one row per commission line. The commission amount column is required. A carrier column groups the lines, and a line of business column breaks each carrier down. The Commission log sheet of the commission tracking template on this site has all of these.
How are carrier names matched?
Two rows belong to the same carrier only when the names are the same after trimming spaces, collapsing repeated spaces, and ignoring upper and lower case. Names that differ in any other way, such as an abbreviation, are listed as separate carriers so that a person decides.
What if I have one file per carrier?
Open one file at a time and type the carrier's name in the box for rows that name none. The file is summarized by line of business and compared with that carrier's statement total. To see every carrier together, the lines go in one sheet with a carrier column.
What happens when a carrier's lines do not add up to its statement total?
The carrier's row shows the difference, the statement check fails and names the carrier with both amounts, and the result is marked not verified. An export is then labelled UNVERIFIED.
Does the summary say which amount is right?
No. It shows the arithmetic difference between the lines in the file and the totals typed. It makes no statement about what any carrier contract or commission schedule requires.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.