From Spreadsheets to a Unified Distribution Platform

Where the spreadsheet still runs the business

Almost every insurance distribution business has a core system of some kind. Almost every one of them also has a spreadsheet layer sitting on top of it, doing the work the core system was never configured to do. The submission passes through a triage sheet before it reaches a rating engine. Commission statements are matched to carrier remittances by hand, row by row, every month. Bordereaux are compiled, reformatted, and re-sent.

Six points in the distribution chain account for most of it:

• Commission calculation and remittance reconciliation across multiple carrier agreements.
• Bordereaux compilation and premium-flow tracking for delegated business.
• Counterparty registers — TOBA and DUA terms, authority limits, remuneration bases.
• Client money calculations, ageing, and breach tracking.
• Producer performance, pipeline, and target tracking.
• Consumer Duty and board management information.

This persists for an honest reason. Spreadsheets are the fastest way to model a financial arrangement that the core system does not support, and insurance distribution generates those arrangements constantly — every new carrier agreement, every override, every profit-commission basis, every scheme with its own remuneration ladder. A rating and administration system that cannot adapt to varying carrier agreements pushes the commission logic back out into a spreadsheet, layered on top of the system of record. That is the shadow-IT pattern, and it is structural rather than lazy.

What the research actually says about spreadsheet error

The field research on spreadsheet error is unusually consistent, and it is old enough that nobody can claim the problem is new. Raymond Panko’s reviews of audited real-world spreadsheets, published across the decade to 2004, found errors in 91% of the field audits reviewed, a cell error rate of 5.2% across 43 spreadsheets with detailed data, and a spreadsheet error rate of 94% — the share containing at least one error — across 88 spreadsheets studied.

The second finding is the one that changes how you should read your own controls. Developers consistently and dramatically underestimate their own error rates. People asked to estimate the probability that their spreadsheet contained an error gave numbers an order of magnitude below the rate subsequent audits found. Reviewers described themselves as impressed with their ability to find errors while having no idea how many they had missed.

This is not carelessness. It is the well-documented human error rate on repetitive, detail-heavy, low-cognitive-load tasks — exactly the profile of monthly commission matching and bordereaux compilation. The rate does not fall with seniority or care. It falls when the task stops being manual.

Why insurance distribution is a harder case than most

Generic spreadsheet-risk research understates the exposure in distribution, for four reasons.

Multi-party, multi-instrument structures

A single policy can involve a retail broker, a wholesale broker, an MGA, a carrier, and — on delegated business — a profit-commission arrangement settled months later. Each relationship has different remuneration terms, different accounting treatment, and different reporting formats. The spreadsheet has to model all of them.

Regulated records with a supervisory audience

The FCA does not ask for a summary; it asks for the record. A firm whose calculation lives in a workbook has to reconstruct evidence at the point of a request rather than produce it as a by-product. That reconstruction is itself a repetitive, detail-heavy task — the class of work the error research says goes wrong most often.

Errors that recur rather than occur

An error in a producer’s split percentage, introduced once and never corrected, does not cost the firm once. It costs the firm every month the affected policies remain on the books, and it costs the producer the same amount in underpayment — with the corresponding damage to trust when it is finally found. One mispriced schedule looks like a rounding difference in any single period and is invisible until an audit.

The audit population is not a control group

Fewer than 1% of UK insurance intermediary CASS audits produce a clean opinion. The recurring findings behind that number — TOBA ambiguity, credit write-back errors, reconciliation cut-off failures — are precisely the class of error the spreadsheet literature predicts. That is not a coincidence, and it is the closest thing the sector has to independent verification of the risk.

Commission and remittance reconciliation

Manual commission processing at MGAs runs at error rates in the 5–8% range, falling to under 1% with automated reconciliation. Three mechanisms account for most of it:

• Direct transcription. Carrier statements re-keyed or copied into the firm’s own record, with no independent check beyond a second look by another reviewer.
• Reconciliation cut-off. Mid-term adjustments, cancellations, and timing differences between when the carrier recognises a change and when the firm does.
• Rate and hierarchy drift. Overrides, tiered ladders, and split percentages maintained in one place and applied in another.

Automated reconciliation does not simply do the same work faster. It changes what is possible: line-level matching against the agreement rather than statement-level totals, exceptions raised as owned items with ageing rather than as a list somebody triages, and a full record of who accepted what and when. The saving in analyst hours is real but secondary. The change that matters is that the difference between what you should have been paid and what you were paid becomes a monitored number rather than an annual discovery.

The full calculation, including a worked leakage example, is in The Hidden Cost of Commission Leakage.

Bordereaux and premium flow

Bordereaux compilation is close to universally spreadsheet-dependent. The reason is the same one that keeps commission logic in a workbook: every carrier wants a different template, on a different cycle, with different fields, and the core system produces one format.

Reconciling a single carrier’s bordereaux back to the underlying premium and cash movement typically takes a finance analyst three to five business days. An MGA running a handful of programmes therefore spends most of the month in compilation and reconciliation, which is why the work is almost never done more often than the reporting cycle requires — and therefore why the detection interval for a break is a month rather than a day.

The regulatory dimension raises the stakes. Where the premium accounting behind a submitted bordereau is maintained manually, the cost of a single adverse examination finding — legal fees, remediation, potential penalties — typically exceeds the full annual cost of the platform that would have prevented it.

Lloyd’s has reached the same conclusion at market level. Blueprint Two is, among other things, a multi-year investment in standardised bordereaux processing and market settlement infrastructure, with the explicit goal of replacing manual, spreadsheet-based exchange with structured data. A firm whose process starts from a spreadsheet faces a re-templating exercise every time the standard moves. A firm holding structured data behind the scenes reformats and re-sends.

TOBA, DUA, and counterparty sprawl

The counterparty register is where spreadsheet dependency is most consequential and least visible. It holds the answers to the questions a regulator, a carrier, or an insolvency practitioner asks first: who is this counterparty, what authority do they hold, and what are the remuneration terms?

Maintained as a workbook, that register records what a person remembered to update, at the point they remembered to update it. Authority limits change, agreements are renewed on different cycles, sub-delegation chains extend, and a binder that lapsed three weeks ago still reads as current until someone opens the file. The exposure is written before the discrepancy is found — and the periodic audit finds it at the only point the process allows.

Held properly, the same information is a live repository: agreement of record, authority terms with effective dates, remuneration basis, sub-delegation chain, and the evidence trail behind each. Pre-bind validation then becomes possible, because the system can check the authority before the risk is written rather than after.

Client money: the CASS-adjacent risk

Client money is where the spreadsheet layer stops being an efficiency question. Fewer than 1% of UK insurance intermediary CASS audits produce a clean opinion, and the recurring findings — TOBA ambiguity, credit write-back errors, reconciliation cut-off failures, claims-money delays — are the spreadsheet failure modes under a regulatory heading.

One further point deserves attention now. Since 7 May 2026, payment service providers and e-money institutions have operated under the CASS 15 safeguarding regime created by FCA Policy Statement PS25/12 — daily reconciliation, monthly returns, an annual safeguarding audit, and 48-hour resolution pack readiness. CASS 15 does not apply to insurance intermediaries, who remain under CASS 5. It applies to a growing share of the infrastructure they settle through.

The practical consequence is a data opportunity with a short window. The PSPs in your premium chain are now producing richer, more frequent, more structured reconciliation data than they were. A firm whose own reconciliation runs on a spreadsheet cannot consume it — it receives a better feed and turns it back into a monthly manual exercise. The full treatment of both regimes is in CASS 15 and Insurance Intermediaries and Client Money in a Digital-First World.

Consumer Duty MI and the static documentation problem

The FCA’s review of Year 2 Consumer Duty board reports named the weakness directly: firms producing static, defensive documentation assembled for the board meeting, rather than live evidence of outcomes. Spreadsheet-compiled management information is that weakness by construction.

The good-practice examples in the same review are instructive. The firms the FCA singled out could link a complaints trend to a specific technical incident, or a call-abandonment spike to a specific resourcing change. What separated them was not better analysis — it was that their underlying data supported that level of granularity as a matter of course, rather than requiring a monthly spreadsheet summary to be built first.

The fair-value connection makes this concrete. A firm whose pricing, commission, and remuneration data live in separate workbooks, reconciled against each other only when a fair-value review falls due, is manufacturing exactly the retrospective construction the review flagged. The Duty asks you to evidence fair value continuously. That is a data-architecture requirement wearing a conduct label.

Four reasons this is urgent rather than chronic

1. Scale has outpaced tooling. US MGA direct premium nearly doubled from $47bn in 2020 to $97bn in 2024, around 14% a year. An operating model that was merely inefficient at $20m of premium is structurally unsafe well before $200m, because the detection interval stays fixed while the transaction count rises.
2. The talent constraint bites. The supply of senior commercial underwriters is tight, and 35–45% of underwriter time goes on rekeying and document review at firms without automated intake. Spreadsheet dependency is not only an error-rate risk; it is a cap on growth that tightens as the talent market does.
3. The payments perimeter has moved. PSPs and e-money institutions now under CASS 15 are producing data your reconciliation could use — if it can consume it.
4. AI has a data precondition. Every measured AI result in distribution assumes structured, normalised, reliable input data. Procure the model before fixing the substrate and you pay the same integration cost per capability, repeatedly, while the underlying data problem becomes harder to diagnose because the AI layer obscures where in the pipeline the error started. See AI in Insurance Distribution.

What a unified platform actually looks like

Four layers, one system of record. Read this as a specification to check any vendor against — including us — rather than as a feature list.

The governance thread runs through all four. An audit trail assembled from three of the layers is not an audit trail; it is a reconciliation exercise with a new name. The evidence layer is the one most often still spreadsheet-tracked after the other three have moved to a proper system — which is why a firm can complete a platform programme and still fail a CASS audit.

Three scenarios, and the sequence that works

There are three realistic paths, and only one of them is a decision rather than a default.

• Scenario A — continue as you are. Fragmented spreadsheets alongside the current core system. This is not a neutral baseline: the compounding cost accumulates fastest here, because each new carrier relationship and each new regulatory expectation adds another manual layer.
• Scenario B — consolidate point tools. An AMS plus a commission-automation tool plus a separate counterparty or compliance tracker. This reduces the commission error rate and leaves a new integration burden, because each tool consumes and re-emits the others’ data. Several point tools tend to rebuild the same fragmentation one layer up.
• Scenario C — one platform across all four layers. New carrier relationships and new channels scale with configuration effort rather than headcount, and CASS, CMAR, and Consumer Duty evidence becomes a by-product of operation rather than a monthly reconstruction.

The sequencing rule that matters most

Start with the layer where the data quality is already best, not the layer where the pain is loudest. That is usually finance rather than distribution: commission and premium data is structured enough to migrate cleanly, and a clean first migration buys the credibility the later stages need. Migrating the messiest layer first is how programmes stall — you spend the political capital on data cleansing and have nothing working to show for it.

Two further rules, both learned expensively:

1. Treat governance as part of the migration, not a follow-on project. A platform will produce good data across all four layers and still leave nobody specifically accountable for CASS sign-off if the accountability was scoped as phase two.
2. Budget the integration tax. A £500k licence producing £1.2m of first-year integration cost is a reasonable planning assumption, not a worst case. Firms that budget for it choose better vendors and sequence better, because the question stops being what the licence costs and becomes what the whole change costs.

What to do next

1. Map the spreadsheet layer honestly. List every workbook that carries a number someone else relies on, and name its owner. The list is usually longer than the operations lead expects.
2. Run the three-question counterparty test across your top twenty counterparties and time how long the answers take.
3. Measure your detection interval, not your error rate. How long would a wrong split percentage sit before anyone noticed?
4. Pick the first layer by data quality, not by pain. Usually finance. Migrate it fully, including the evidence it has to produce.
5. Scope governance and the integration tax into the business case before the vendor conversation, so the comparison is between whole programmes rather than between licence fees.