Insurance brokerage statement to reconciliation: tracing commissions across Applied Epic, Excel and the ERP

Short answer

A referral partner can describe an insurance brokerage's statement-to-reconciliation history from metadata alone: which system records the placement and expected commission, how insurer statements arrive and where they are reconciled, whether a spreadsheet sits between the agency system and the ledger, how far back each goes, which identifier links a statement line to a policy, and who owns each step. Nobody opens a statement. The case to test is a cancellation that shows up on a later statement than the original commission.

Visual briefFull-size diagram
A month-by-month commission ledger: a $5,400 commission is paid and reconciled on the February statement, the policy cancels on April 30, and a $3,825 return commission appears on the June statement with no link back to February, leaving $1,575 net.
Illustrative. One placement, two statements four months apart, and the reconciliation that closed before the cancellation arrived.

The business task: agreeing what the insurer owes

A brokerage places a policy and expects a commission. The insurer reports what it actually pays on a statement, often weeks later and sometimes with differences: a changed premium, a mid-term endorsement, a cancellation. Someone in accounting compares expected with paid, chases the differences and books an adjustment. Each accepted adjustment is a recorded judgment about which side was right and why.

In a typical mid-size agency the steps are split three ways. The agency management system, such as Applied Epic, holds the policy, the expected commission and the direct bill statements. In Epic, a direct bill commission statement moves through Suspended, Finalized and Approved statuses, and a suspended or finalized statement can be associated with an existing receipt, disbursement or journal entry. An Excel workbook often tracks the month's open differences. The ERP or general ledger holds the consolidated entries, especially after an acquisition brings several agencies together.

What metadata can a referral partner collect about brokerage-statement-to-reconciliation without reading private content?

Use five dimensions: what, how much, how long, which system, who owns it. An insurance accountant or fractional CFO can fill these in from the reconciliation routine they already know. The values below are Illustrative, for a fictional 220-person commercial lines brokerage.

DimensionApplied EpicExcel reconciliation workbookERP
WhatPolicies, direct bill statements, cancellations, endorsementsMonthly tracker of open differences by carrierCommission journals and month-end entries
How muchAbout 9,000 active commercial policies; roughly 4,000 statement lines a month at peakOne workbook a month, about 30 carrier tabsOne consolidated ledger for three legacy agencies
How longSince 2014On SharePoint since 2019, a file server before thatSince 2020, after a merger
Link to the next stepPolicy number on statement lines: most carriers, not allStatement number typed per rowJournal reference to the Epic statement
Who owns itAccounting managerAccounting managerController or fractional CFO

Add two flags beside the table. First, does every carrier put a policy number on its statement lines, or do some only give an insured name? Second, do last year's monthly workbooks still exist, and where? Those two answers decide most of the joined history's usable range. Microsoft's documentation notes that version history in Microsoft 365 works only for files stored in OneDrive or SharePoint, so a workbook kept on a file server before 2019 may survive only as its last saved state.

Write counts as the client gives them and mark guesses as guesses. Amounts never go in the packet. For the system itself, see Applied Epic agency histories and who owns agency management system data.

When a cancellation lands on a later statement

This is the case the packet should be able to describe. It is Illustrative and matches the graphic above.

A commercial package policy pays a $5,400 commission on the February insurer statement. The accounting team matches it, finalizes the statement and closes the month. The policy cancels on April 30. The insurer takes back $3,825 of unearned commission on the June statement, as a single debit line. Nothing on that line points back to February, so the brokerage kept $1,575 net, and the only way to see that is to join two statements four months apart through the policy number.

If the policy number is on both lines, the join is easy. If one carrier reports the debit under the insured's name only, someone has to match it by hand, and whether they recorded that match is a fact worth one line in the packet: "late cancellations matched manually in the workbook, notes kept since 2021" or "not tracked". The partner records the habit, not the amounts.

Fractional CFOs often see this pattern first, which is why the fractional CFO for insurance agencies guide is a useful companion.

Who can authorize a licensing discussion, and what stays unknown?

Commission records reveal carrier relationships and client premiums, so the decision sits with the CEO, usually the agency principal in an owner-run agency; the CFO, or in a private-equity-backed platform the platform CFO, often sponsors the introduction. An accounting manager can describe the records but cannot authorize licensing them.

Before introducing, confirm:

  • 50+ full-time employees at peak, contractors excluded, and a documented operating history.
  • Rights to license: carrier agency agreements and client confidentiality terms may limit reuse; note them for SourceX's review rather than judging them.
  • A named decision-maker who will take a first call.
  • 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.

Partners never ask for or handle statements, workbooks or ledger exports. What stays unknown until SourceX reviews it: whether client and carrier details can be de-identified to a buyer's standard, which years a buyer would want, what carrier contracts permit, and what the agency would be paid. The insurance operations screening worksheet covers wider fit, selling an insurance agency covers timing around a sale, and the systems hub lists other record locations.

Go deeper on brokerage-statement-to-reconciliation records

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 the agency can say since when its statement lines carry policy numbers and its monthly workbooks survive, register as a partner, check it against who qualifies, and introduce the principal under the program terms.

  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

Does this apply to agency bill as well as direct bill?

The example uses direct bill, where the insurer pays commission on a statement. Agency bill has a different cash path, but the same questions apply: which identifier links the policy, the statement line and the ledger entry, and how late adjustments are handled.

Do I need to see commission statements to describe the chain?

No. Which carriers send statements, in what format, since when, how many lines a month, where the reconciliation lives and who owns it are enough. Statements, client names and amounts stay with the agency; the figures on this page are fictional.

Does SourceX connect to Applied Epic or an ERP?

This page names those tools only to show where commission records commonly sit. It does not describe a SourceX connector. If the agency proceeds, it decides what to share under a signed agreement.

Sources

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

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