Skip to content

Broker Commission Reconciliation

Example prompt: "Every month, read the insurer commission statements in our Drive folder, match them line by line against our policy register, and flag anything that's missing, short-paid, or sitting on our books unpaid. Draft the chase notes to each insurer's accounts team and post the unmatched cases in #accounts on Slack."

The Problem

A broker writes a hundred policies a month across a dozen insurers, and each insurer pays commission on a different cadence — some monthly bordereaux, some quarterly statements, some still by paper remittance. The accounts manager spends two days every month opening PDFs, cross-referencing each line against the broker's own policy register, working out which commissions are missing, which are short-paid because the policy was endorsed mid-term, and which policies the broker has on the books that the insurer has not paid yet. The cases that hurt are the ones nobody chases because the reconciliation never reached the bottom of the stack — six months later, the policy has lapsed, the insurer's accounts team has rotated, and the commission is written off.

How GloriaMundo Solves It

We build a workflow that runs on the first working day of each month. An integration step lists the new statement files in the Drive folder for the previous month. A code step reads each statement (PDF or CSV) and extracts the line items — policy number, broker reference, inception or transaction date, gross premium, commission rate, commission paid. A code step matches each line against the broker's Policy Register on policy_number and broker_reference, and writes the matched, unmatched, and short-paid lines into three tabs of the Commission Reconciliation sheet. An LLM step drafts a chase note to each insurer's accounts team listing the policies the broker has on the books that are not on this month's statement, and a separate chase for any short-paid lines. An integration step saves each chase as a Gmail draft for the accounts manager and posts the unmatched and short-paid counts in #accounts on Slack. Glass Box preview shows the matched, unmatched, and short-paid lines before any chase email is drafted.

Example Workflow Steps

  1. Trigger (scheduled): First working day of the month at 8am.
  2. Step 1 (integration): List every file added to the 'Commission Statements' Drive folder since the start of the previous month, grouped by insurer.
  3. Step 2 (code): For each statement, extract the line items — policy_number, broker_reference, transaction_date, gross_premium, commission_rate, commission_paid_gross, commission_paid_net. Normalise insurer-specific column names against a small mapping table maintained on a 'Statement Schemas' sheet.
  4. Step 3 (integration): Read the broker's 'Policy Register' tab — every active policy with insurer, policy_number, broker_reference, inception_date, gross_premium_written, commission_rate_expected.
  5. Step 4 (code): Match each statement line against the Policy Register on (insurer, policy_number, broker_reference). Classify each line as Matched (commission_paid within 1% of expected), Short_Paid (paid is below expected by more than 1%), or Unrecognised (no register match). Classify each Register row not appearing on the statement as Unpaid_Pending if inception_date is within the insurer's typical payment lag, or Unpaid_Overdue if past the lag.
  6. Step 5 (integration): Write the four lists to the 'Commission Reconciliation' sheet — Matched, Short_Paid, Unrecognised, Unpaid_Overdue — with the month tag in the row key for idempotency.
  7. Step 6 (llm): For each insurer with at least one Short_Paid or Unpaid_Overdue line, compose a chase email to the insurer's accounts team — names the broker, names the broker reference scheme, lists the affected policies with the expected versus paid figures, and asks the accounts team to remediate on the next statement.
  8. Step 7 (integration): Save each chase email as a Gmail draft to the insurer's accounts contact on the Insurers sheet. Persist the gmail_draft_link to the Commission Reconciliation row.
  9. Step 8 (integration): Post a one-line summary in #accounts on Slack — month, matched_count, short_paid_count, unrecognised_count, unpaid_overdue_count, total_value_at_risk — so the accounts manager can see at a glance whether the month was clean.

Integrations Used

  • Google Drive — the monthly insurer commission statement files
  • Google Sheets — the Policy Register, the Insurers sheet, and the Commission Reconciliation tabs
  • Gmail — the chase drafts to each insurer's accounts team
  • Slack — the monthly summary in #accounts

Who This Is For

Commercial brokers and MGAs writing 50 to 500 policies a month across a panel of insurers, where commission is paid via a mix of monthly bordereaux and quarterly statements, where the accounts manager is the only person on the desk who knows where the gaps are, and where six-figure commission leakage builds up over a year of unchecked statements.

Time & Cost Saved

An accounts manager doing this by hand typically spends two full days a month reading statements, cross-referencing the policy register, and writing the chase emails — and the deeper reconciliation against unpaid policies on the books often gets pushed to next month. This workflow turns it into a half-day review of the four reconciliation tabs and a pass through the pre-drafted chase emails, and the unpaid-overdue list comes off the bottom of the stack and onto the accounts manager's desk where it can be acted on while the trail is fresh.