How to Use AI for Reconciliation and Spotting Anomalies

A hands-on synthetic reconciliation practice follows the explanation below.

Hypotheses, never conclusions

There is one sentence that makes AI safe and useful in reconciliation work, and you should tape it to your monitor:

AI generates hypotheses. You generate conclusions.

Everything in this lesson follows from that. The tool is allowed to say "these two entries look like they might be duplicates". It is never allowed to say "these are duplicates" and it is certainly never allowed to say "the reconciliation is complete". The first is a lead worth two minutes of your time. The second is an assertion about your client's records that only you can make, because only you can be held to it.

Frame it that way and the tool becomes genuinely valuable. It is fast, tireless and pattern-hungry across material that makes human eyes slide off the page after forty minutes. It just has no idea whether it is right.

Four things worth asking it to look for

Possible duplicates. Not just identical rows, which your spreadsheet already finds. The interesting ones are the near-misses: same supplier, same amount, invoice numbers one apart, dates four days apart. That is either a genuine second invoice or the same one entered twice, and only a human looking at the document can say which.

Unusual amounts. Round numbers where round numbers are rare. Amounts that sit just under an approval threshold. A supplier who normally invoices in the hundreds suddenly invoicing in the thousands. None of these are wrong. All of them are worth a glance.

Timing oddities. Entries dated at the very end or very start of a period. Backdated postings. A supplier whose invoices arrive monthly and then arrive twice in one week. Gaps where a regular payment should be.

Description mismatches. The narrative says one thing and the coding says another. A description mentioning a deposit sitting in an expense code. A payment described as a refund with a positive sign.

Here is a list of transactions. I want you to flag things that
LOOK unusual. You are generating hypotheses for me to test.
You are not concluding anything and you must not say
that anything is confirmed, correct, or an error.
Flag and group under these headings:

Possible duplicates, including near-matches on supplier,
amount, reference or date. Say what makes each pair suspicious.
Unusual amounts, given the surrounding pattern.
Timing oddities: period boundaries, backdating, unexpected frequency.
Description and coding mismatches.
For every item, give me: the row reference, what caught your attention
in one sentence, and the specific check I should perform to confirm
or rule it out.
Rank by how much money is at stake, largest first.
Do not flag anything you cannot give me a check for.

That last line is the quality control. If it cannot tell you how to test a flag, the flag is noise.

Keep the method and conclusions reviewable

Be clear about the split. Use an inspectable spreadsheet formula, accounting-system calculation or code-backed method for the arithmetic and matching, and verify it against the source records. What AI adds is help with the residue: the unmatched items, the difference you cannot explain, the six entries that refuse to tie out.

I have an unexplained difference on a reconciliation.
The amount is [state it]. The account is [describe it in general terms].
Do not perform any calculation and do not tell me the cause.
List the classic signatures a difference of this shape can have,
such as transpositions, sign reversals, doubled entries,
timing cut-offs and rounding accumulation.
For each signature, tell me the arithmetic test I should run myself
in the spreadsheet to see whether it applies.

Proving it yourself

A flag is worth nothing until you have gone to the source. That means the invoice, the statement, the contract, the remittance advice, the person who authorised it. Not a second opinion from the same tool, and not a longer explanation from it either, because a longer explanation is just more generated text about the same unverified guess.

Keep a short note of what you checked and what you found, including the flags that turned out to be nothing. Your working papers should show the flag, the check and the outcome. If somebody later asks why you looked at that entry, "the pattern matching flagged a near-duplicate and I traced it to the supplier's original invoice" is a professional answer. "The AI thought it was fine" is not an answer at all.

Take the flags you produced and turn them into a checklist for me.
One line per item, in this format:
row reference, what to look at, what would confirm it,
what would rule it out.
Add a column for me to write the outcome.
Do not add any new flags and do not remove any existing ones.
Do not suggest what the outcome will be.

The honest scorecard

Run this properly and you should expect most flags to be nothing. That is not failure, it is how screening works. A smoke alarm that only went off during real fires would be a miracle, not a smoke alarm.

What you are buying is coverage. The tool reads all four hundred rows at hour six of a long day with exactly the same attention it gave row one, which is more than any of us can honestly claim. You bring the judgment, the source documents and the signature. It brings the stamina.

Reconciliation practice with an audit trail

Try this synthetic exercise before marking the lesson complete. No AI, paid tool or account is required. Keep your first attempt and corrections; this small practice does not establish professional competence.

Learner files: Export A (CSV), Export B (CSV), blank discrepancy log (CSV), and learner guide (plain text). The CSVs contain data only, not formulas or macros. Import as comma-delimited UTF-8, keep row 1 as headers and check that the six columns are separate. Amounts use a decimal point and a leading minus sign; confirm column F is numeric before calculating. Do not treat FC as a real currency or apply currency conversion.

Use two small fictional exports to identify an extra duplicate row, a missing reference and an amount mismatch. Your result is a checked discrepancy log, not an instruction to change a real ledger. This practice extends the existing reconciliation lesson; it does not require AI, a paid tool or an external account.

Read the rules first

All records, source notes and amounts in this pack are invented. FC means fictional credits, not a currency. The scenario date is 3 November 2026, a teaching date rather than a publication date. Both exports cover the same fictional account, day and unit. Positive values increase the practice total; negative values reduce it. These conventions are for this exercise only.

Keep both raw files unchanged. Copy them into a separate practice workbook or inspect the printed tables. A row ID identifies an export row; reference identifies the underlying practice item. Use exact reference matching within account and unit. Do not match different references merely because amounts are equal.

Flag a reference that appears more than once before any one-to-one match. For this pack, a clean match has one row in each export, equal reference, date, account, unit and signed amount. Keep duplicate groups outside the clean-match count until reviewed. No fuzzy matching, rounding tolerance or automatic date shifting is needed.

What you will hand in

1. Row and reference counts for both files, plus raw signed totals. 2. A clean-match list and a discrepancy log with row IDs. 3. A signed bridge explaining B minus A. 4. A short handoff stating what the evidence establishes and which causes remain unknown.

Do a first pass before reading the answer pages. You can calculate by hand or use a spreadsheet. Preserve your first attempt and corrections. This is self-directed practice, not an assessment of professional competence.

Learner data and source notes

CSV column order is row_id, reference, scenario_date, account, unit, amount. Dates, account and unit are identical on every row. The compact table shows the changing fields. Headers are on row 1; A has data rows 2 to 11, B has rows 2 to 12.

Fictional source notes

S1: The exercise export log states that B04 repeats the same source item as B03, reference R103. It is an extra export row. This does not establish that any payment happened twice.

S2: The exercise receipt register lists R108 and R109 as two separate items of 75.00 FC each. Neither is a duplicate of the other.

S3: No additional source record is supplied for R104, R105 or R111. The exports reveal their differences, but do not establish which record is authoritative or why they differ. Do not invent a timing issue, fee, fraud, payment or corrective journal.

R106 is a valid signed negative practice item. Do not discard it or convert it to positive. No opening balance, bank account, tax treatment or real accounting period is supplied.

Work through the comparison

Step 1 Check the input

Count data rows, distinct references and numeric amounts. Check the negative value and the last row. Record the units and date scope before calculating. In your copied sheets, an amount total covers F2:F11 for A and F2:F12 for B. A spreadsheet formula is only as reliable as its range and input types; confirm every imported amount is numeric.

Step 2 Separate repeats from matches

List references repeated within either file. For each reference, count its A rows and B rows. Only then compare one-to-one records. Keep row IDs beside each decision so a reviewer can trace the input. Equal totals do not prove equal records.

Step 3 Explain the residue

Use the blank discrepancy-log.csv. For each exception, record A and B row IDs, raw subtotals, B minus A, observed difference, evidence and next check. Use zero only for the subtotal of an absent side; absence is not a claim that the transaction value was zero. Retain missing sides explicitly as absent.

Step 4 Review an AI explanation if you choose

Optional prompt: Using only these fictional exports and S1 to S3, suggest a matching method and a discrepancy log. Preserve raw rows and signed amounts. Flag repeated references before one-to-one matches. Separate observed differences from hypotheses. Cite row IDs and source notes. Do not invent causes or alter records. If you calculate with code, show the code, input row counts and checks.

Check any output yourself. A prompt is not a technical permission boundary; keep practice disconnected from live records and tools. OpenAI documents that some data-analysis tasks use Python and recommends reviewing code, outputs and assumptions. Availability varies; this lab was not tested in a learner ChatGPT account.

Step 5 Test a tempting shortcut

A proposed result says: Delete every repeated amount, ignore the negative number and add 222.00 to A to make it balance. Explain why all three instructions are unsafe. State whether a bridge that sums to the raw difference resolves every exception.

Check the spreadsheet range

For a short example of a formula that misses the final row, see verify the range in Excel. Check source rows and numeric types as well as the formula; matching totals alone do not prove that records match.

Source and limits

OpenAI's data-analysis guidance explains that some tasks run Python. Review the input, generated code, assumptions and outputs. The CSV arithmetic and range examples were checked computationally and in a spreadsheet formula engine; this is not a claim of testing in Microsoft Excel or in a learner ChatGPT account. No learner trial or professional assessment is claimed.