The first week of every month
Your brokerage writes business under a binding authority or runs a scheme with delegated underwriting. Each month the insurer or managing agent wants a premium bordereau, and often a claims bordereau, in its own template. Someone exports the month's transactions from the broking system, pastes them into the template, fills in columns the export does not provide, fixes the dates that came out as text, and adds the risk details the template asks for that live in another report.
The file goes off. A week later it comes back with queries: missing postcodes, a sum insured outside the authority, a policy that appears twice, a cancellation with no return premium. Someone fixes it and sends it again.
Why bordereaux never get easier
Every capacity provider has its own template, and templates change. Some follow market standards, some do not. The broking system's exports were not designed around any of them. So the monthly job is a manual translation, done by the one or two people who know how.
The checks the insurer runs, such as limits within authority, territorial scope, complete mandatory fields, are the same checks that could be run before sending, but nobody has time to build them into a spreadsheet properly.
The cost of a manual bordereau
- Days of skilled time each month on copying and fixing.
- Rejected or queried files, which strain the relationship with capacity providers.
- Risks written outside authority limits found late, after the insurer spots them.
- Key-person dependency: if the one person who knows the file is away, it is late.
- No clean history of what was reported for each month.
A bordereau pipeline, checked before it leaves
- The month's transactions, risk details and claims are pulled from your broking system and any scheme platform, through APIs or scheduled exports.
- Data is held in one structured store with a record per risk and per transaction.
- Each capacity provider's template is set up as a mapping, including their codes, date formats and required fields, and can be updated without rewriting anything.
- Checks run before the file is produced: required fields present, limits and sums insured within the authority, territorial and trade restrictions, duplicates and cancellations without return premiums.
- Anything that fails appears in an exceptions list with the reason, for a person to correct at source or explain.
- The final file is produced in the insurer's format, versioned and stored, and can be sent or uploaded as the insurer requires.
- Month-on-month summaries are produced for your own management information.
Where a risk appears to sit outside authority, the system flags it. What happens next is a matter for your agreement with the insurer and your own compliance process.
Where the checks sit now
| Check | Manual file | Pipeline |
|---|---|---|
| Mandatory fields | Found by insurer | Checked before sending |
| Limits within authority | Found by insurer, or not | Checked every risk |
| Duplicates | Found by insurer | Checked before sending |
| Template changes | Rework by hand | Mapping updated once |
| History | Emails with attachments | Every version stored |
A calmer first week
The file is produced from the data rather than assembled by hand, and the person who used to build it now works through a short exceptions list. Insurer queries become rarer and easier to answer, because every version and every correction is recorded. New schemes or new capacity providers mean a new mapping, not a new monthly routine.
Is this your month end?
- Bordereaux are built in Excel from several exports.
- Insurers regularly come back with queries on your files.
- Only one or two people can produce the bordereau.
- Each capacity provider needs its own manual routine.
- Out-of-authority risks have been found by the insurer, not by you.