Start with the principle: reconcile expected against actual, not paid against paid The agencies that keep this under control without an accounts department all do the same basic thing. They treat every policy written as creating an
expected commission receivable at the point of inception, then match insurer statements against that receivable. The spreadsheet approach usually fails because it only records what has come in, so nothing flags what should have come in but hasn't, or what came in at the wrong rate.
Once you have an expected figure per policy, the reconciliation becomes an exceptions exercise. Matched lines need no attention at all. Only the gaps, short payments and rate discrepancies need a human to look at them, and that is a very different workload from checking every line.
The system, in practice - Get the expected commission out of your broker management system. Acturis, Open GI, SSP and Applied Epic will all produce a report of policies incepted or renewed in a period with the agreed commission rate and amount. If you are on something smaller, or pricing outside the system, that report is the first thing to fix. Without it you are guessing.
- Load insurer statements into the same place. Most UK insurers and MGAs will send a monthly statement or bordereau as a CSV or Excel file if you ask. PDF statements are the enemy here. Push the insurer for a spreadsheet format through your account handler; they usually oblige because it saves their own team queries.
- Match on policy reference, then on client name and premium as a fallback. A simple lookup in Excel or Google Sheets does this fine for a few hundred lines a month. Anything much bigger and you want either the reconciliation module in your broker system or a dedicated tool that ingests statements automatically.
- Produce three lists every month: paid but not expected (usually a policy not set up properly on your side), expected but not paid (the aged debt you chase), and paid at a different amount (rate disputes, clawbacks, mid term adjustments).
Keep the cadence fixed
Monthly, same week each month, without fail. The failure mode for small agencies is letting it slide for a quarter, at which point the exceptions pile up and nobody wants to touch it. Two to four hours a month done properly is far less painful than a two day clean up twice a year.
Anything unpaid beyond 90 days from the expected date should trigger a query to the insurer. Insurers do lose policies in their systems, particularly on schemes and facilities, and money left unclaimed for long enough becomes very hard to recover.
Who actually does the work
You do not need a full time accountant, but you do need someone who owns it. The realistic options for a UK agency of this size:
- An account handler or admin who is given the job explicitly, with a checklist, and whose time is protected for it each month. This works well if the matching is largely automated and they are only working the exceptions.
- A part time or outsourced bookkeeper who understands insurance intermediary accounting. General bookkeepers often struggle with the difference between client money and office money, so ask specifically about broker experience and CASS 5 before engaging one. There are firms that specialise in this for brokers and typically charge a fixed monthly fee.
- The principal doing it themselves, which is fine at a small book but does not scale and tends to be the first thing dropped when the diary is busy.
Whoever does it, the reconciliation output should feed your general ledger in Xero, QuickBooks or Sage as a summary journal, not line by line. Keep the detail in the broker system or the reconciliation workbook and post the totals.
Two things that get overlooked
Clawbacks and cancellations. Build these into the expected ledger as negative entries when the cancellation is processed, otherwise your outstanding figure is permanently overstated and you waste time chasing money that was never due.
Client money implications. If you hold client money under CASS 5, insurer commission you have earned but not yet drawn down sits in a specific place in your client money calculation. Getting the commission reconciliation wrong can quietly break your client money reconciliation too, and that is an FCA problem rather than just an untidy ledger. Worth making sure whoever signs off the client money calculation is seeing the commission exceptions report as well.
If the spreadsheet is creaking, the honest answer is that the fix is rarely a better spreadsheet. It is getting the expected commission data out of the system you already pay for and letting the matching do the heavy lif ting, so the person owning it only ever looks at exceptions.
If you do move to a dedicated tool
Broker systems vary a lot in how good their built in reconciliation actually is, so before buying anything separate, ask your current provider for a demo of what you already have. Plenty of agencies pay for a module they have never switched on.
If you do go external, the questions worth asking a vendor are:
- Can it ingest statements in the formats your main insurers actually send, or will you be reformatting files every month?
- Does it handle clawbacks and mid term adjustments as negative entries, or does it just show them as unmatched?
- What does the exceptions report look like, and can a non accountant work from it?
- Can it post a summary journal to Xero, Sage or QuickBooks rather than dumping every line into the ledger?
Whatever you land on, the measure of success is simple: at month end, can someone say with confidence how much commission is outstanding from each insurer and why? If the answer is yes in under half a day of effort, the system is working. If it takes longer than that or the answer is a shrug, the process needs fixing before the book grows any further.
