Reconciliation exception report
Purpose
Most reconciliations are presented as a tidy summary with one residual line
labelled "difference to investigate", and that line is where the loss lives. This
skill inverts the output: the deliverable is the exception list, item by item,
each break named, classified by cause, aged, owned and actioned. A reconciliation
with a clean summary and an unexplained residual has not been done, it has been
formatted.
Data classification
Confidential. Reconciliation extracts carry counterparty names, bank details,
transaction-level client activity and unpublished balances.
STOP CONDITIONS:
| Trigger |
Action |
| Full or partial account numbers, IBANs, sort codes, card PANs, or payment credentials appear in the extract |
Stop. Name the category detected without repeating the value. Ask for the file re-supplied with those columns removed or hashed. |
| Client- or counterparty-identifiable balances, positions or transactions are pasted |
Stop. Ask for a de-identified extract keyed on a reference, not on a name or account. |
| Payroll detail at a grain where an individual's pay is derivable |
Stop. Ask for it aggregated. |
| The request is to net offsetting breaks, write a difference to suspense or P&L, or adjust one side so the two agree |
Stop. That is a plug and it conceals a misstatement. Report the break gross, both legs, and escalate to the financial controller. |
| The request is to post, approve or release a correcting journal |
Stop. This skill prepares evidence; posting is a segregated human control. |
NON-FABRICATION RULE. Never invent, interpolate or round a figure that was
not supplied. Never assume an unmatched item is timing because the amounts are
similar — timing requires the settlement date on both sides. Never classify a
break as "immaterial" without the stated materiality threshold. If either source
extract, the opening reconciled position, or the cut-off dates are missing, output
exactly which is missing and stop. A break given a plausible cause it does not
have is worse than an unexplained break, because it stops being investigated.
Prerequisites
- Inputs (mandatory): both sides, at transaction level, for the same period —
not two summary totals. A pair of totals cannot be reconciled, only differenced.
- Inputs (mandatory): the period start and end, and the cut-off convention for
each side (trade date, value date, posting date, settlement date).
- Inputs (mandatory): the prior period's closing reconciled position and its
then-open items, so brought-forward breaks are not re-reported as new.
- Inputs (mandatory): the matching keys available on each side (reference,
invoice number, amount, date) and which are reliable.
- Inputs (if multi-currency): the currency of each leg and the rates and rate
convention used on each side.
- Inputs (for the ageing and escalation columns): materiality thresholds and
the ageing buckets in use. If absent, ask. Do not invent a threshold.
- Runtime: none for the analysis. If a workbook or file output is requested,
code execution and a spreadsheet library are required — confirm availability
before promising a file.
If a mandatory input is missing, name it and stop.
Procedure
1. Establish the difference before matching anything
State both closing balances, the gross difference, and the brought-forward
difference. If the two sides are not on the same basis — different cut-off, one
gross and one net of a fee, one including a suspense account — normalise first and
show the normalisation as its own reconciling line. An unnormalised comparison
produces a break list that is entirely artefact.
2. Match in tiers, and record which tier matched each item
| Tier |
Rule |
Confidence |
| 1 |
Unique reference matches, amount matches, date within cut-off |
Matched |
| 2 |
Unique reference matches, amount differs |
Matched with a value break — report the delta |
| 3 |
Amount and date match, no shared reference |
Probable — flag for human confirmation, never silent |
| 4 |
Many-to-one or one-to-many (part payments, batched settlements) |
Group, then match the group; show the constituents |
| 5 |
No match on any key |
Exception — goes to step 3 |
Never allow one item on one side to satisfy two items on the other. Record the
match rate: matched value and count as a percentage of each side. A falling match
rate is a process failure signal independent of the residual.
3. Classify every exception by cause
Cause drives the owner and the action. An unclassified break has no route to
resolution, which is why they age.
| Cause |
Recognised by |
Typical owner |
Action |
| Timing |
Present both sides, settles in the next period; the later date is evidenced |
Preparer |
Carry forward with expected clearing date; confirm it clears |
| Cut-off |
Item belongs to the period on one basis and the next on another |
Preparer / controller |
Restate to a single basis; fix the convention, not the item |
| Missing entry — our side |
Evidenced externally, absent from the ledger |
Ledger owner |
Prepare a journal with support; posting is separate |
| Missing entry — their side |
Posted by us, absent from counterparty or system |
Relationship or system owner |
Raise with counterparty; keep the query reference |
| Duplicate |
Same reference or same amount, date and counterparty twice on one side |
Ledger owner |
Confirm before reversing; a duplicate and a genuine repeat look identical |
| FX |
Legs agree in transaction currency, differ in reporting currency |
Preparer |
Recompute at the stated rate; split rate difference from true break |
| Fees and charges |
Difference equals a known fee, spread or commission |
Preparer |
Map to the fee schedule; if it does not tie to the schedule, it is not this |
| Rejected, returned or reversed |
Original plus a reversal on one side only |
Operations |
Match the pair; report the net exposure |
| Classification |
Correct amount, wrong account or entity |
Ledger owner |
Reclassify; check for a systematic mapping fault |
| Genuine error |
None of the above fits |
Controller |
Investigate; quantify the P&L effect explicitly |
| Unexplained |
Cause not yet established |
Controller |
Report as unexplained. Do not guess a cause to empty this row. |
The unexplained bucket is the most important line in the report. It must be
reported with a value and a count even when it is embarrassing.
4. Age every exception and apply the escalation gate
Age from the item's own date, not from the date the reconciliation was prepared —
a break rediscovered each month is not new. Bucket to the agreed ageing profile.
Aged breaks escalate on age as well as value: a small break open for six months is
a control failure and should be reported as one.
5. Assign a named owner and a dated action to each exception
An exception with no name is a to-do list nobody owns. If the owner cannot be
determined from the supplied data, write OWNER NOT DETERMINED — assign rather
than inferring one from the account name.
6. Assemble the report in this order
- Both balances, the gross difference, and the difference net of explained items.
- The exception list — every break: reference, both legs, value, currency,
age, cause, owner, action, expected clearing date.
- Summary by cause: value and count, this period against last.
- Match rate and any tier-3 probable matches awaiting confirmation.
- Unexplained residual, stated in value and as a share of the balance, against
materiality.
- Control observations: repeated causes, systematic mapping faults, breaks that
recur every period.
7. Self-check before issue
Does the sum of classified exceptions plus matched items reconcile exactly to the
gross difference? If not, the report is not internally consistent and must not be
issued. Is every brought-forward break either cleared or re-reported with its
original date? Is anything in the unexplained bucket presented as if explained?
Boundaries
- Not for explaining period-on-period movement in a reconciled figure — that
is
finance-month-end-variance-analysis, which assumes this work is already
done and consumes the exception list as its reconciliation block.
- Not for individual expense claim decisions —
finance-expense-policy-triage.
- Not for budget or forecast narrative —
finance-budget-vs-actual-commentary.
- Not for determining accounting treatment of a correction, or whether an
error is material enough to restate. That needs the reporting standard and a
qualified accountant.
- Not for deciding whether a break is a reportable incident to a regulator or
a client-money breach. Escalate; do not conclude here.
Hand-offs
- Receives from: the close checklist, the ledger and sub-ledger extracts, and
the prior period's open-item list.
- Routes to:
finance-month-end-variance-analysis — the classified exception
list is that skill's reconciliation input; variance commentary written before it
exists is unreliable by construction.
- Routes to:
it-service-desk-triage when a break's cause is a system or
interface fault rather than an accounting one.
- Routes to:
operations-process-mapping when the same cause recurs every
period; a recurring break is a broken process, not a finance task.
- Escalates to: the financial controller for any unexplained residual above
materiality, any aged break past the escalation gate, and every plug request.
1---2name: finance-reconciliation-exception-report3description: Reconciles two supplied sources — ledger to sub-ledger, bank to cash book, intercompany pair, processor settlement to billing — at item level, then produces the exception report: every break classified by cause (timing, missing entry, duplicate, FX, cut-off, genuine error), aged, quantified, and given a named owner and an action. Re-derives the match from the supplied data rather than accepting an asserted total, and never books, nets or plugs a difference. Use when two sources of the same balance disagree, when a reconciliation needs preparing or reviewing, when a control account will not tie, or when someone asks what the unreconciled difference consists of. Trigger on 'reconcile these', 'the balance does not tie', 'unreconciled difference', 'break analysis', 'exception report', 'bank rec', 'intercompany mismatch', 'suspense account'. Not for explaining why a reconciled P&L line moved period on period (use finance-month-end-variance-analysis) and not for deciding whether a single expense claim is payable (u4---56# Reconciliation exception report78## Purpose910Most reconciliations are presented as a tidy summary with one residual line11labelled "difference to investigate", and that line is where the loss lives. This12skill inverts the output: the deliverable is the exception list, item by item,13each break named, classified by cause, aged, owned and actioned. A reconciliation14with a clean summary and an unexplained residual has not been done, it has been15formatted.1617## Data classification1819**Confidential.** Reconciliation extracts carry counterparty names, bank details,20transaction-level client activity and unpublished balances.2122**STOP CONDITIONS:**2324| Trigger | Action |25| --- | --- |26| Full or partial account numbers, IBANs, sort codes, card PANs, or payment credentials appear in the extract | Stop. Name the category detected without repeating the value. Ask for the file re-supplied with those columns removed or hashed. |27| Client- or counterparty-identifiable balances, positions or transactions are pasted | Stop. Ask for a de-identified extract keyed on a reference, not on a name or account. |28| Payroll detail at a grain where an individual's pay is derivable | Stop. Ask for it aggregated. |29| The request is to net offsetting breaks, write a difference to suspense or P&L, or adjust one side so the two agree | Stop. That is a plug and it conceals a misstatement. Report the break gross, both legs, and escalate to the financial controller. |30| The request is to post, approve or release a correcting journal | Stop. This skill prepares evidence; posting is a segregated human control. |3132**NON-FABRICATION RULE.** Never invent, interpolate or round a figure that was33not supplied. Never assume an unmatched item is timing because the amounts are34similar — timing requires the settlement date on both sides. Never classify a35break as "immaterial" without the stated materiality threshold. If either source36extract, the opening reconciled position, or the cut-off dates are missing, output37exactly which is missing and stop. A break given a plausible cause it does not38have is worse than an unexplained break, because it stops being investigated.3940## Prerequisites4142- **Inputs (mandatory):** both sides, at transaction level, for the same period —43 not two summary totals. A pair of totals cannot be reconciled, only differenced.44- **Inputs (mandatory):** the period start and end, and the cut-off convention for45 each side (trade date, value date, posting date, settlement date).46- **Inputs (mandatory):** the prior period's closing reconciled position and its47 then-open items, so brought-forward breaks are not re-reported as new.48- **Inputs (mandatory):** the matching keys available on each side (reference,49 invoice number, amount, date) and which are reliable.50- **Inputs (if multi-currency):** the currency of each leg and the rates and rate51 convention used on each side.52- **Inputs (for the ageing and escalation columns):** materiality thresholds and53 the ageing buckets in use. If absent, ask. Do not invent a threshold.54- **Runtime:** none for the analysis. If a workbook or file output is requested,55 code execution and a spreadsheet library are required — confirm availability56 before promising a file.5758If a mandatory input is missing, name it and stop.5960## Procedure6162### 1. Establish the difference before matching anything6364State both closing balances, the gross difference, and the brought-forward65difference. If the two sides are not on the same basis — different cut-off, one66gross and one net of a fee, one including a suspense account — normalise first and67show the normalisation as its own reconciling line. An unnormalised comparison68produces a break list that is entirely artefact.6970### 2. Match in tiers, and record which tier matched each item7172| Tier | Rule | Confidence |73| --- | --- | --- |74| 1 | Unique reference matches, amount matches, date within cut-off | Matched |75| 2 | Unique reference matches, amount differs | Matched with a value break — report the delta |76| 3 | Amount and date match, no shared reference | Probable — flag for human confirmation, never silent |77| 4 | Many-to-one or one-to-many (part payments, batched settlements) | Group, then match the group; show the constituents |78| 5 | No match on any key | Exception — goes to step 3 |7980Never allow one item on one side to satisfy two items on the other. Record the81match rate: matched value and count as a percentage of each side. A falling match82rate is a process failure signal independent of the residual.8384### 3. Classify every exception by cause8586Cause drives the owner and the action. An unclassified break has no route to87resolution, which is why they age.8889| Cause | Recognised by | Typical owner | Action |90| --- | --- | --- | --- |91| Timing | Present both sides, settles in the next period; the later date is evidenced | Preparer | Carry forward with expected clearing date; confirm it clears |92| Cut-off | Item belongs to the period on one basis and the next on another | Preparer / controller | Restate to a single basis; fix the convention, not the item |93| Missing entry — our side | Evidenced externally, absent from the ledger | Ledger owner | Prepare a journal with support; posting is separate |94| Missing entry — their side | Posted by us, absent from counterparty or system | Relationship or system owner | Raise with counterparty; keep the query reference |95| Duplicate | Same reference or same amount, date and counterparty twice on one side | Ledger owner | Confirm before reversing; a duplicate and a genuine repeat look identical |96| FX | Legs agree in transaction currency, differ in reporting currency | Preparer | Recompute at the stated rate; split rate difference from true break |97| Fees and charges | Difference equals a known fee, spread or commission | Preparer | Map to the fee schedule; if it does not tie to the schedule, it is not this |98| Rejected, returned or reversed | Original plus a reversal on one side only | Operations | Match the pair; report the net exposure |99| Classification | Correct amount, wrong account or entity | Ledger owner | Reclassify; check for a systematic mapping fault |100| Genuine error | None of the above fits | Controller | Investigate; quantify the P&L effect explicitly |101| Unexplained | Cause not yet established | Controller | Report as unexplained. **Do not guess a cause to empty this row.** |102103The unexplained bucket is the most important line in the report. It must be104reported with a value and a count even when it is embarrassing.105106### 4. Age every exception and apply the escalation gate107108Age from the item's own date, not from the date the reconciliation was prepared —109a break rediscovered each month is not new. Bucket to the agreed ageing profile.110Aged breaks escalate on age as well as value: a small break open for six months is111a control failure and should be reported as one.112113### 5. Assign a named owner and a dated action to each exception114115An exception with no name is a to-do list nobody owns. If the owner cannot be116determined from the supplied data, write `OWNER NOT DETERMINED — assign` rather117than inferring one from the account name.118119### 6. Assemble the report in this order1201211. Both balances, the gross difference, and the difference net of explained items.1222. **The exception list** — every break: reference, both legs, value, currency,123 age, cause, owner, action, expected clearing date.1243. Summary by cause: value and count, this period against last.1254. Match rate and any tier-3 probable matches awaiting confirmation.1265. Unexplained residual, stated in value and as a share of the balance, against127 materiality.1286. Control observations: repeated causes, systematic mapping faults, breaks that129 recur every period.130131### 7. Self-check before issue132133Does the sum of classified exceptions plus matched items reconcile exactly to the134gross difference? If not, the report is not internally consistent and must not be135issued. Is every brought-forward break either cleared or re-reported with its136original date? Is anything in the unexplained bucket presented as if explained?137138## Boundaries139140- **Not for** explaining period-on-period movement in a reconciled figure — that141 is `finance-month-end-variance-analysis`, which assumes this work is already142 done and consumes the exception list as its reconciliation block.143- **Not for** individual expense claim decisions — `finance-expense-policy-triage`.144- **Not for** budget or forecast narrative — `finance-budget-vs-actual-commentary`.145- **Not for** determining accounting treatment of a correction, or whether an146 error is material enough to restate. That needs the reporting standard and a147 qualified accountant.148- **Not for** deciding whether a break is a reportable incident to a regulator or149 a client-money breach. Escalate; do not conclude here.150151## Hand-offs152153- **Receives from:** the close checklist, the ledger and sub-ledger extracts, and154 the prior period's open-item list.155- **Routes to:** `finance-month-end-variance-analysis` — the classified exception156 list is that skill's reconciliation input; variance commentary written before it157 exists is unreliable by construction.158- **Routes to:** `it-service-desk-triage` when a break's cause is a system or159 interface fault rather than an accounting one.160- **Routes to:** `operations-process-mapping` when the same cause recurs every161 period; a recurring break is a broken process, not a finance task.162- **Escalates to:** the financial controller for any unexplained residual above163 materiality, any aged break past the escalation gate, and every plug request.
alihusains