Month-end reconciliations: turn 5,000 rows into an exception review

How do you reduce the review without losing coverage?

Use repeatable preparation and documented matching rules to separate routine matches from items that require judgment. Then reconcile the matched and exception populations back to the source. The aim is not to replace 5,000 rows with a reassuring summary; it is to give a reviewer a smaller, evidence-backed queue while preserving accountability for the whole population.

All figures in this guide are synthetic, including the 5,000 rows, five flags and four cases below. They illustrate a design, not measured results, a client deployment or a promise of performance. A simulation can test known conditions. It cannot prove that an actual month-end close is correct.

Define the balance you are explaining

Before matching, state the accounts, reporting period, cutoff, currencies and sources covered. A ledger export and a supporting schedule might use different dates or include different statuses. If one includes pending entries and the other does not, even flawless matching produces misleading exceptions. Capture the exact export criteria and version the input files.

Record row counts, debit and credit totals and the expected opening-to-closing movement. Decide whether the process is matching transaction-level items, comparing aggregated balances or both. Do not net unrelated items merely to make the totals agree. A zero difference at an aggregate level can conceal duplicate and missing transactions that happen to offset.

Use rules with an explicit order

Normalize dates, signs, account codes and currency in a working dataset while preserving original values and row identifiers. Apply the strongest reliable match first, such as a unique source transaction ID. A secondary rule might use invoice reference, supplier identifier and amount. Document tolerances and treat duplicate keys as ambiguity, not confirmation.

Power Query can merge structured tables and combine consistently shaped files. Those capabilities do not choose the accounting policy for you. A tolerance for rounding must be authorized; an out-of-period item must not be silently reassigned. Keep the rule version with the output so a later refresh can be explained and reproduced.

Five flags can represent four cases

In the synthetic 5,000-row dataset, two flagged rows share one duplicate invoice reference and belong to a single duplicate case. A third row falls outside the agreed period. A fourth has no supporting schedule counterpart. A fifth has a currency mismatch. That gives five flagged rows but four review cases. The distinction helps prevent misleading reporting about workload or resolution.

Each case needs source IDs, the triggered rule, the relevant amount, evidence and an owner. The duplicate case retains both rows rather than dropping one. The missing counterpart may be a timing item, an incomplete export or a real omission; its cause is unresolved until evidence supports an explanation. Do not label every unmatched item an error.

What should the reviewer receive?

Provide a concise control summary plus a usable exception register. Show source counts and totals, matched totals, flagged totals and any excluded population with reasons. Include case status, reviewer, evidence location and final treatment. The register should distinguish ‘under review’, ‘explained without adjustment’ and ‘correction required’, rather than offering only a completed checkbox.

A reviewer should be able to drill from a case to its original records. Record explanations in specific terms: a reference to a supporting document and an agreed cutoff is stronger than ‘timing’. If a correction is necessary, keep preparation and authorization within the business’s controls. Finding a difference does not authorize the workflow to post a journal.

Rehearse before using real close data

1. Build a clearly labelled synthetic test set and a separate expected-outcome register. Keep fictional identifiers apart from live files.
2. Seed duplicates, missing counterparts, changed schemas and currency mismatches. Include ordinary matches that look similar but are not duplicates.
3. Run the rules and compare detected cases with expected cases. Investigate missed exceptions and false positives separately.
4. Verify that input totals equal matched, flagged and explicitly excluded totals. Test failure when an input is absent or incomplete.
5. Have an independent reviewer use the evidence trail, then agree acceptance and escalation criteria for the live pilot.

Do not report a successful synthetic rehearsal as assurance over real data. Real exports introduce unexpected formats, changes in business practice and imperfect evidence. Test the rule changes again after modifying a query, and keep unresolved cases from prior periods visible where the reconciliation requires them.

A smaller queue still needs an owner

Exception-led review is a way to allocate attention, not eliminate responsibility. Agree who refreshes the inputs, who investigates differences, who signs off the balance and who covers absence. SHORTEND’s reconciliation scope includes the controls and handover because a compact report is only useful if someone can trust, explain and maintain it.