Guide

Bordereaux automation without inventing totals

Premium, claims, and commission are three jobs. A covering-email total is not a grand total. Wording first, cells second, exceptions third, book last.

A bordereau is a schedule of rows a cedent owes a reinsurer, or a reinsurer posts from a cedent. It is not a narrative. It is not a covering-email total. Premium, claims, and commission are three jobs. Mixing them on one tab because the subject line said "bordereaux" is how class, attachment, and commission basis disappear into a number nobody can unwind.

This guide is operator copy. It covers the three file types, why reconciliation fails, the fields you actually need, the ingest-then-validate loop versus the treaty-then-exceptions loop, provenance on totals, and the cedent-out versus reinsurer-in mirror. It is not a payback calculator. There are no accuracy percentages.

The short definition is What are bordereaux in reinsurance?. The glossary term is bordereaux. Premium versus claims, in one page, is the difference between premium and claims bordereaux. The treaty operating system around these files is treaty reinsurance. Cedents assembling outbound files start on the cedents hub. Reinsurers posting inbound files start on solutions for reinsurers. The named worker is the Bordereaux Agent. Field contract: source-grounded extraction. No span, no value.

The fictional placing sample — not a live bordereau — is the pack inspector. Do not upload a live cedent file to this website. Production ingest is a read-only mailbox in the tenant.

Premium versus claims versus commission

Do not flatten three jobs into one dump. The email subject is not a schema. If the treaty asks for three accounts, send three files or three clearly bounded sheets. A combined workbook is fine only if each sheet has a name, a period, a basis, and a total that foots to its own rows.

Premium bordereaux

Premium bordereaux list what was written or ceded in the period. The completeness test is whether the reinsurer can see that the cession matches the treaty definition of subject business.

A row with no class, when the treaty excludes a class, is a query. A premium total that does not foot to the lines is a query. Gross and ceded mixed in one amount is a query. Period that overlaps two treaty years with no split is a query.

Typical columns, in language a technician can map:

• Treaty name or reference, and year of account.
• Period of the file, and the as-at date on the face of the sheet.
• Policy, certificate, or risk reference the cedent actually uses.
• Named insured. If a facultative leftover appears, treat it as an exception, not as colour.
• Inception and expiry, or the written period the treaty named.
• Class or occupancy, in the vocabulary of the wording, not a branch code that happened to sit in last quarter's "class" column.
• Territory or location at the grain the treaty can test.
• Original limit or sum insured, where surplus or per-risk XL will need it.
• Gross written, ceded premium, and currency. One currency per column, or a cited FX as-at.
• Share or cession percentage, if the treaty is proportional.
• A total row that is a sum of the lines, not a typed figure.

If ACME Construction Ltd, acme.example, sits on a facultative certificate, that premium belongs on the file that matches that contract. It does not belong on a treaty bordereau for a different class because the spreadsheet tab was handy.

Claims bordereaux

Claims bordereaux list losses. Paid, outstanding, recoveries, status. The completeness test is whether each row can be tested against attachment, hours clause, occurrence versus aggregate, excluded causes, and period basis.

A paid figure with no loss date is not a claims row. It is a number. Event date versus report date versus paid date are not one column. A reserve in a comment in the margin is not a reserve.

Typical columns:

• The same treaty, year, period, and as-at as the premium file, named on the cover.
• Claim or event reference, and the original policy reference so the row can join.
• Named insured, where the treaty needs it to test class or occupancy.
• Loss date, notice or report date, and the date of the movement on this file.
• Cause or peril, in language you can test against exclusions.
• Paid to date, outstanding, incurred, recoveries, and currency.
• Event identifier if the wording uses hours or catastrophe event definition.
• Status: open, closed, reopened. A closed claim that still has outstanding is a conflict.

A catastrophe that sits in an unsent claims file does not stop the clock on reinstatement. Sending a catch-up file without event dates is how both sides invent a timeline. See what is a reinstatement clause in reinsurance? when the argument is capacity coming back, and what is an attachment point in reinsurance? when the argument is where cover starts.

Commission bordereaux

Commission files, when separate, are how ceding commission, sliding scale, and profit commission get argued. Flattening commission into the premium sheet without keeping the basis is how a sliding scale gets applied to the wrong denominator.

Name the basis on the cover. Is the commission on gross written, on ceded, on earned? Is profit commission on a loss ratio that uses the same earned premium the wording named, or last year's spreadsheet header? If you cannot cite the clause, you do not have a commission row. You have a hope.

Keep commission movements visible: provisional, adjustment, clawback. A single net premium that already "includes commission" cannot be reconciled to a wording that states a rate and a sliding scale. The reinsurer will reverse-engineer the rate from the net and then fight you about the denominator.

Why reconciliation fails

Reconciliation fails because people treat it as format conversion. It is not. It is a join between a wording and a sheet, plus a join between this period and the last one, plus a join between premium rows and claims rows. Format is the first break. It is not the only break.

Column names drift. Last quarter "Pol Ref" was column B. This quarter it is "Certificate Number" in column D, and column B is a branch code. A template that follows last quarter's map silently posts the branch as the policy reference. Empty is better than that. Silent wrong is how you book a total you cannot unwind.

Covering emails invent totals. The analyst totals a filtered view, pastes the figure into the email, and the sheet's grand total is different. The inbound team books the email. The outbound team swears by the sheet. Both have a number. Neither has provenance.

Years mix. Premium written in December and reported in January is this treaty or next, according to the wording, not according to when the file was emailed. A sheet that mixes years without a column you can cite cannot be split by instinct.

Class and territory fail the wording test. The treaty excludes a flood zone, or a construction occupancy, or a named territory. The row is still in the file because the admin extract does not know the treaty. Booking the total "because it is close to last quarter" hides the two rows that will become a loss dispute.

Currency is mixed without a rate as-at. Surplus and per-risk XL need a cited sum insured. Quota share needs a cited share. If the implied line or share disagrees with the wording, that is a conflict, not a rounding item.

Restatements arrive as quiet overwrites. Last quarter's file is replaced. There is no bridge. Follow-the-settlements evidence dies. Six months later the query asks what you sent, not what you now wish you had sent. Label restatements. Prior as-at, new as-at, what changed, which rows.

Policy references do not join. Claims rows have a claim number and no original policy reference. Premium rows use a different identifier than the claims extract. You cannot test whether a loss sits on ceded business.

The hours clause and the attachment live in the wording, not in the bordereau header. A claims file with no event dates cannot apply a 72-hour clause. A paid row below attachment is not a recovery. If the extract never captured those fields, "AI mapping" will not invent a lawful recovery. It will invent a booking.

People also fail the human sequence. They map columns, like the total, and book. They open the wording only when a query arrives. That is backwards. Wording spans first. File cells second. Exceptions third. Booking last.

Field list

This is a working list, not a market standard. Cedents will add columns. Reinsurers will need more. If a field is required by the wording and absent from the file, it is a gap. If two sources disagree, it is a conflict. Do not invent a third number.

Identity and period, on every file:

1. Treaty reference and year of account, cited from the wording cover and repeated on the sheet cover.
2. File type: premium, claims, or commission. One type per sheet unless the bounds are obvious.
3. Period of account and as-at date, on the face of the file, not only in the email.
4. Currency, and FX as-at if more than one currency appears.
5. Cedent name as the contract names it, not a trading style that drifted.

Premium rows:

1. Policy or certificate reference.
2. Named insured.
3. Inception and expiry, or written period.
4. Class or occupancy, testable against the wording.
5. Territory or location grain the treaty can exclude.
6. Original limit or sum insured, where the structure needs it.
7. Gross and ceded premium, and the share or line if proportional.
8. Commission on that row only if the premium file is also the commission file, with the basis named.

Claims rows:

1. Claim or event reference, plus original policy reference.
2. Loss date, report date, movement date.
3. Cause or peril.
4. Paid, outstanding, incurred, recoveries.
5. Event id if hours or catastrophe definition applies.
6. Status.

Commission rows or adjustments:

1. Basis: written, ceded, earned, as the wording states.
2. Rate or sliding-scale band, with a span into the clause.
3. Provisional versus adjustment.
4. Profit-commission inputs: the loss ratio basis, not a remembered percentage.

Provenance metadata, on every emitted total and every exception:

• Document id, sheet name, cell or range.
• Status: traced, gap, conflict, unverifiable.
• For exceptions: the wording span that failed, and the row cell that failed it.

A covering email is a document. If it is the only place a total lives, cite it and treat disagreement with the sheet as a conflict. Usually the chase is: put the total on the sheet as a sum.

Ingest then validate

Inbound operations often start with the file, because the file arrived. That is allowed as a queue. It is not allowed as a booking path.

Ingest means: the mailbox or drop zone received a workbook or a PDF of a workbook. Classify it. Premium, claims, commission, or unknown. Unknown is a chase, not a default to premium. Extract the cover: treaty, period, as-at, currency. Map columns to the field list above. Mapping is a hypothesis. It is not a posting.

Validate means: every mapped column is checked as data, then as treaty.

Data checks are boring and load-bearing:

• Required cells empty.
• Duplicated policy references on a premium file that should be unique.
• Dates that cannot be dates.
• Amounts that do not parse.
• Total row that does not equal the sum of lines.
• Sheet that is an image with no recoverable cells: unverifiable, chase a workbook.

Treaty checks need the wording, not last quarter's memory:

• Class, territory, occupancy against cited exclusions and subject business.
• Period against year of account and the basis: losses occurring, clean-cut, claims made, as written.
• Share, line, or attachment against the cited structure.
• Claims below attachment flagged, not recovered.
• Hours clause applicable only if event dates exist; otherwise a gap, not a guessed event.

The output of ingest-then-validate is not a booked total. It is a pack: mapped rows with cell spans, a list of data gaps, a list of treaty exceptions, and totals that cite cells. Operators accept, reject, or chase. Software that posts because the map "looked like last time" is a re-keying engine with extra steps.

PDF bordereaux are pictures of schedules until cells recover. If you cannot total the sheet, you do not have a bordereau. You have a scan. Chase the workbook. Do not type the email total into the admin system and call it extraction.

Treaty then exceptions

The other loop starts from the contract. It is the loop this site prefers, even when the file arrived first.

Extract the wording into fields with spans. Share or surplus line. Class. Territory. Attachment. Limit. Hours clause. Reinstatement formula. Commission basis. Period and basis of attachment. Inception. The treaty wording extraction guide is the sibling. Quota share versus surplus is the proportional split. Excess of loss still needs attachment and hours before any claims row is posted.

Then the inbound file is tested against those fields. Rows that pass are candidates to book. Rows that fail are exceptions. Exceptions are first-class. They are not a footnote after the total is posted.

An exception is a row plus a wording span. Examples:

• Construction occupancy on a treaty that excludes it. ACME Construction Ltd on the row, exclusion on wording page 4. The row does not vanish. It sits on the exception list until someone produces a special acceptance or takes it off the file.
• Sum insured above surplus capacity. Facultative leftover, error, or special acceptance. Not a rounding item.
• Loss date outside the period on the named basis.
• Paid plus outstanding on an event above the cited limit, which is an exhaustion and reinstatement question, not a silent extra recovery.
• Two policies in an excluded flood zone. Same shape as the occupancy miss.

Special acceptances are documents. A mailbox yes without a file is how you discover the extra occupancy at the loss. Join the acceptance to the policy reference. Put a period on it.

Year of account stays a wording question. If the file cannot show year, the whole file is an exception, not a pile of rows you split by the email timestamp.

Treaty-then-exceptions is slower to demo than "we ingested the Excel." It is the only order that survives a query. Guessed totals do not close the quarter. They delay the dispute.

Provenance on totals

A total without a cell is a rumour. The covering email is not the grand total. The subtotal of a filtered view is not the grand total. A figure typed on the tab named "Summary" is not the grand total unless it is a formula that sums the lines and you can cite the range.

Source-grounded extraction is the rule for bordereaux totals the same way it is the rule for slip limits. Field, document, sheet, cell. No span, no value.

What to store for every total you emit:

• The value as typed data.
• Sheet and cell, or the range that sums.
• Status: traced if the cell is a sum of in-scope rows, conflict if another document prints a different figure, gap if the template required a total and none exists, unverifiable if the page is an image.

If the covering email says one number and the grand total cell says another, keep both spans. Do not average them. Do not pick the email "because that is what the account manager sent." Do not pick the sheet "because spreadsheets are source" when the sheet total includes out-of-scope rows. The operator needs the conflict in order to ask: which rows are in, and will the file be amended.

Subtotals lie in ordinary ways. A cedent totals only the home-office sheet and emails that figure. The workbook still has a branch sheet. The inbound map reads the first tab. The outbound team thinks they sent the book. Provenance on the emailed figure would show it was a subtotal. Provenance on a true grand total would show a range that includes both tabs, or would show a gap if the branch tab cannot be parsed.

Do not use covering-email totals as ground truth. That sentence is also in the document ops index. It belongs here because this is where people actually book from the email.

Restated totals need two provenances: the prior file's total cell and the new file's total cell, plus a bridge of rows. A silent overwrite has no bridge. It will not survive a query.

Cedent-out versus reinsurer-in

The object is the same. The direction is not.

Cedent-out is reporting. Premium, claims, and commission leave the building on a calendar the treaty already named. Late is not a vibe. Late changes who owns IBNR and whether a reinstatement is on this year or the next. The job is a file the counterparty can post, with a list of rows you still need to chase internally. You are not trying to impress the reinsurer with a narrative. You are trying to make their technician not open a dispute.

The outbound loop: extract source rows from policy admin and claims admin, validate them against the wording, pack a bordereau, chase internal gaps, send on time. A gap list on time beats a complete-looking file that moved numbers to close the quarter. Reinsurers can work a gap. They cannot work a surprise restatement that pretends it was always that way.

Print the treaty year, the basis, and the as-at on every file cover before anyone opens sheet 2. Freeze an internal cut-off earlier than the contractual due date. Do not wait for a perfect extract.

Reinsurer-in is posting. Inbox or drop zone. Classify, map, validate against the signed wording, exception, then book. The counterparty's covering email is not authority. Their total is a span to check, not a number to type. Their class column is a hypothesis. Your wording is the system of record.

The inbound loop fails when you treat every cedent as a template you already know. Layouts drift. The map must be re-justified. Exceptions must still cite the wording, even for a cedent you have seen for ten years. Familiarity is not a span.

Mirror tests, so both sides can use the same pack shape:

• Can a stranger foot the total to the lines?
• Can they test class and territory against a cited clause?
• Can they join claims to premium by a reference that exists on both files?
• Can they see year of account without opening the email?
• Can they see which rows are exceptions, with wording spans, instead of a blended total?

If you are the cedent and you send a fight, you will get a query. If you are the reinsurer and you book a fight, you will own the unwind. Software that invents a mapping to look complete is on the other side of the fight, against you, whether you bought it or they did.

Fictional discipline: ACME Construction Ltd, acme.example, is a facultative named insured on this site. If that name appears on a treaty bordereau, ask whether the row is subject business or a leftover. The slip versus SOV fight is a placing problem. It does not license mixing that risk into a quota-share sheet.

What software must not do

Do not fill a hours clause from market practice because the claims file has no event dates. Leave the field empty. Chase event dates or chase the clause.

Do not complete a grand total from the covering email when the sheet does not foot. Store a conflict or a gap.

Do not follow last quarter's column map when "Pol Ref" moved. Fail the document or list unmapped columns. Silent remap is how you post branch codes as policies.

Do not book rows that fail class, territory, or period and then hide them in a blended total. Exceptions stay visible.

Do not average two totals. Do not pick a winner to make month-end pretty.

Do not call a PDF picture an SOV-style schedule you can total. Chase the workbook.

Do not upload live bordereaux to a marketing site. The pack inspector is a fictional placing file so you can see conflict versus gap. Bordereaux belong in the tenant.

Do not buy a chatbot that summarises the sheet and invents a total. Generic LLM versus IDP versus operations software is the comparison. Fluency without a span is the failure mode.

The test is operational. Give the same messy file to a vendor. A quota-share wording that excludes construction. A premium sheet with an ACME Construction Ltd row, a covering email total that does not match the grand total cell, and a "Pol Ref" column that moved. The product you want emits a class exception with a wording span, a TIV-or-premium total conflict with two cells, and unmapped columns as gaps. A product that returns one booked number has failed the test, regardless of the demo narrative.

If you are building the outbound file, use the field list, keep three jobs separate, put provenance on the total, and send the exception list as the cover note. If you are posting inbound, wording first, cells second, exceptions third, book last. Anything that completes the total for you has failed the same test the submission pack uses on hours clause and TIV: empty when missing, two numbers when they disagree.