Brokerage statement to reconciliation completeness checklist: score an agency's commission trail

Short answer

A statement-to-reconciliation trail in an insurance agency breaks mostly at late adjustments. Commission is paid on one carrier statement and taken back on another, sometimes months later, after the first reconciliation is closed. Statement lines without policy numbers, trackers saved over each month and ledger entries posted as lump sums finish the job. This scorecard tests those weak points with five checks an accounting manager can answer from memory, each scored pass, partial or fail, without anyone opening a statement.

Visual briefFull-size diagram
Five checks for coverage, linkage, timestamps, outcomes and late cancellations tick one by one while a semicircular gauge sweeps to 6 of 10; the late-cancellation check shows as partial.
Illustrative score for a fictional agency. Pass scores 2, partial 1, fail 0.

It is part of insurance brokerage statement to reconciliation records. Use it after you know the agency's systems and before you decide whether to introduce its principal.

Scoring rules

  • Score each check 2 for pass, 1 for partial and 0 for fail, out of 10.
  • Record the accounting manager's own words beside each score.
  • Where carriers or legacy agencies differ, score the weakest large part of the book and note the spread.
  • Mark "don't know" as unknown and leave it out of the total. An unknown is a question for SourceX, not a fail.

The score describes the trail. It does not decide demand, rights or any offer, and partners never ask for statements, trackers or ledger extracts to fill it in.

Five checks for a commission trail

CheckHow to check it at metadata levelPass (2)Partial (1)Fail (0)
CoverageAsk since when the agency management system, the tracker and the ledger each hold commission historyAll three cover the same three or more yearsOne system starts later, after a merger or migrationOnly the agency system has history
LinkageAsk which carriers send statements without policy numbers, and whether statements were associated with journal entriesPolicy number on nearly every line; statements tied to ledger entries in the agency systemMost carriers print it; association used sometimesName-only statements common; no association
TimestampsAsk whether the tracker keeps the statement date or the day a row was pastedStatement, cancellation and posting dates all keptSome dates are entry datesRows dated only by entry
OutcomesAsk whether net commission per policy can be read from the ledger or the trackerNet per policy derivableDerivable only through the agency systemLedger lump sums only, no tracker detail
Late-cancellation gapsAsk what happens to a return commission when the original item is already closedReopened or referenced, with a noteParked on an unmatched tab, cleared laterPosted to suspense with no trace

Where the agency runs Applied Epic, two documented features decide how the linkage and late-cancellation checks come out:

  • Statement status and association. A direct bill commission statement passes through Suspended, Finalized and Approved, and before approval it can be tied to a receipt, disbursement or journal entry already in the system. An agency that used that tie scores higher on linkage, because the ledger side is reachable without the spreadsheet.
  • Final payment. Marking an item as finally paid removes it from later reconciliations, so nothing more can be applied to it. Any commission clawed back after that point must be parked somewhere, and the last check asks where.

Version: the check hidden inside timestamps

Add one follow-up under timestamps: could the agency open the tracker exactly as it stood at last March's close? Microsoft 365 keeps earlier versions only for files held in OneDrive or SharePoint, so a workbook overwritten monthly on a local or network drive holds nothing but its current state. An answer like "on a file server until 2019" scores partial; write down the year the workbooks moved.

A scored agency (Illustrative)

A fictional 230-person agency group, formed when two regional agencies merged, with one agency management system since 2014, trackers on SharePoint since 2019 and a new ERP since 2021.

CheckAccounting manager's answerScore
CoverageAgency system since 2014; trackers since 2019; ERP since 2021Partial (1)
LinkageTwo regional carriers send name-only statements; others print policy numbersPartial (1)
TimestampsStatement date column kept; earlier tracker versions on SharePointPass (2)
OutcomesNet per policy readable from the agency system and the tracker, not the ledgerPartial (1)
Late-cancellation gapsReturn commissions parked on an unmatched tab, cleared monthly with a notePartial (1)
TotalSum of the five checks6 of 10

Illustrative. All answers are invented.

Stress test: a name-only clawback months after the original payment

Walk the accounting manager through this case and ask which of the five checks it would trip. The figures are fictional, and this case is different from the one on the record chain page: the carrier statement carries no policy number, and the insured holds two policies with the same carrier.

WhenRecordWhat happensChecks it tests
MarchCommercial auto policy, effective March 1Expected commission $7,200 set up in the agency systemCoverage
AprilRegional carrier statement$7,200 paid; the line shows the insured's name only; matched by handLinkage
AprilERPOne commission entry for the whole carrier statementOutcomes
SeptemberCancellation effective September 307 of 12 months earnedTimestamps
NovemberSame carrier's statementReturn commission of $3,000, again name onlyLinkage, late-cancellation gaps
NovemberTrackerMatched by name to the insured's other policy, a general liability renewalLate-cancellation gaps
NovemberERPA negative amount inside one carrier totalOutcomes

The agency keeps $4,200 on the auto policy, and the totals still balance, so nothing looks wrong. The error is a wrong match, not a missing one: the general liability policy now shows a clawback it never had. An agency whose answer is "for name-only carriers we confirm the policy with the carrier before matching, and note it on the row" turns this from a hidden error into a labeled exception, the kind of record described under reconciliation exception data. How each system reads a trail on its own is in what an Epic-only export leaves out, and the field-by-field keys are in the record chain.

After the score: sponsor, rights and unknowns

The accounting manager answers the scorecard; the CEO, usually the agency principal in an owner-run agency, or the platform CEO in a private-equity-backed group, authorizes a licensing discussion. Confirm 50+ full-time employees at peak (contractors excluded), documented operating history, rights to license and an authorized contact. Companies in the U.S., Canada and the EU qualify, as do companies in the UK, Australia, New Zealand, India, Singapore, South Africa, Latin America, the UAE and Saudi Arabia when at least 70% of the data is in English.

Unknown until SourceX reviews it: carrier agreement limits, de-identification of client and premium details, export formats and any offer. Fractional CFOs often run this kind of check already; see the fractional CFO for insurance agencies guide and the insurance agency M&A advisor page. More record locations are in the systems hub.

Next step

Refer a company. No cap on rewards. Default reward on a company's first qualifying transaction: the greater of $50,000 plus 5% and 15% of Actual Supplier Payout, with no cap; further qualifying transactions first agreed within 12 months of the first Closing pay 15%. Eligibility and payment conditions apply, including the USD 100,000 Minimum Transaction, and a signed Schedule A or addendum can differ. A lead, meeting or signature alone does not trigger payment.

If an agency can answer all five checks and explain where its late return commissions go, register as a partner, check it against who qualifies, and read the program terms before introducing its principal.

  1. Step 1Share your linkSend your personal link to a company you know.
  2. Step 2Company appliesThe company applies itself at /apply.
  3. Step 3Company signs and is paidThe company signs, delivers its data and is paid by SourceX under its agreement.
  4. Step 4You get your rewardthe greater of $50,000 plus 5% and 15% of the actual supplier payout, with no cap. A reward becomes payable after SourceX has paid the referred company under its signed agreement and the payout conditions in the program terms are met, including finance approval and your tax and payment details.

Common questions

Who in the agency should answer the scorecard?

The accounting manager or whoever runs the monthly commission reconciliation. A fractional CFO often knows the answers too. Neither can authorize licensing; that sits with the CEO, usually the agency principal in an owner-run agency.

Does agency bill business score differently?

The checks are the same. Agency bill has a different cash path, but the questions about identifiers, dates, preserved versions and late adjustments still apply.

Sources

  1. help.appliedsystems.com/Help/Epic/2023.2en-US/General_Ledger/Reconci…
  2. appliedsystems.com/DOC_CENTRAL/TR-TRAINING/RECONCILINGOFAGENCYO…
  3. support.microsoft.com/en-us/office/collab-files/view-previous-vers…
  4. refer.sourcex.si/terms

Free resources

Know a company with valuable proprietary data?

Become a referral partner, get your link and introduce the company’s CEO (in an owner-run company, usually the owner). Referral partners can join from most countries. We can't work with partners in countries or regions under U.S. sanctions or on U.S. restricted-party lists. The companies you introduce should be based in the U.S., Canada and the EU, or in the UK, Australia, New Zealand, India, Singapore, South Africa, Latin America, the UAE and Saudi Arabia with at least 70% of their data in English.

Refer a company →

I own a business

Explore licensing your company's data directly to SourceX for AI training and evaluation. Start a short assessment; no uploads needed.

Start an assessment