Bank Reconciliation with AI, Without Letting It Do the Math
- Difficulty
- Beginner
- Time
- 45 minutes the first time
Before you start
- The bank statement export and the ledger (cash account) export for one period, as spreadsheet files
- A spreadsheet program you already use (Excel, Google Sheets or similar)
- An AI chat assistant your company has approved for finance work
- Your usual reconciliation process and sign-off rules
This playbook speeds up a bank reconciliation with AI while keeping every number in the spreadsheet. Chat assistants are good at writing formulas and explaining patterns. They are not a reliable calculator: a model can add a column wrong and still sound certain, a kind of hallucination. So the AI never sees your real statement data or does your arithmetic. It writes the formulas, you run them on your own files, and the spreadsheet does the math you can audit. If you were looking for a ChatGPT bank reconciliation prompt, the prompts below are plain text and work the same way in any mainstream chat assistant.
Step 1: Describe your columns, not your data
Use the AI assistant your company has approved for this kind of work, and follow your company's AI policy. If you don't know which tool is approved, ask before you paste anything. Bank and ledger exports contain account numbers, payee names and amounts. You don't need to paste any of them for this playbook. Write down only the column headers of each file, and if it helps, make up two or three fake sample rows in the same format.
Step 2: Ask for matching formulas
I'm reconciling a bank statement to our ledger in [Excel / Google Sheets].
Bank sheet columns: [e.g. Date, Description, Amount]
Ledger sheet columns: [e.g. Date, Ref, Memo, Debit, Credit]
Here are made-up sample rows in the same format: [paste fake rows]
Bank deposits match ledger debits to cash, and bank withdrawals match
ledger credits. Write formulas that:
1. Mark each bank row MATCHED if the ledger has the same amount within
[3] days, NEAR if the amount matches but the date is further out or
the amount differs by less than [1.00], and UNMATCHED otherwise.
2. Add a Match count column to each bank row: how many ledger rows it
matched (use COUNTIFS). If the count is above 1, the row shows
CHECK instead of MATCHED.
3. Do the same two things from the ledger side.
Explain each formula in one sentence and tell me which cell to put it in.
Do not calculate any totals yourself.
Step 3: Run the formulas and check them
Paste the formulas into a copy of your workbook, never the original. Then check the logic by hand on seven rows you already know: two that should match, one near match, two that shouldn't, and two bank rows with the same amount (two equal payments, or a duplicate charge) where the ledger has only one entry. Both bank rows can show MATCHED here, because each one finds the same single ledger entry. That is wrong, and two things should flag it: the ledger entry shows CHECK, because two bank rows match it, and the matched totals check below doesn't balance. If either flag is missing, ask the assistant to fix the formulas before you go on. If any comes out wrong, tell the assistant which row and what you expected, and ask for a fix.
Any row marked CHECK, or with a Match count above 1, matched more than one entry. Treat it as a near match and check it by hand, because a false match hides exactly the errors a reconciliation is meant to find.
Use the spreadsheet's own SUM for every total. Run two checks. First, on the bank side, the totals of MATCHED, NEAR, CHECK and UNMATCHED rows add up to the SUM of every bank row for the period, so no rows were dropped. Second, the net MATCHED total on the bank side (deposits minus withdrawals) equals the MATCHED debits minus MATCHED credits on the ledger side. If they differ, something was matched twice or matched to the wrong side; find it before you go on.
Step 4: Sort the exceptions into three tables
Filter or pivot your sheet into three tables: matched, near match (including every CHECK row) and unmatched. The near and unmatched tables are your exception list, and the work you'd do anyway in a reconciliation.
Step 5: Ask for likely causes, without real data
Describe the exception patterns in general terms (for example, "four bank debits on the last two days of the month with no ledger entry"), not the transactions themselves.
In a bank reconciliation, these patterns are left after matching:
[describe patterns, no names or account numbers]
List the usual causes for each (for example timing, bank fees,
duplicates, transposed digits) and what document I should check to
confirm each one. Do not tell me which cause is right.
Step 6: Draft the memo
Draft a short reconciliation memo template with: period, accounts,
statement balance, ledger balance, reconciling items by type, items
still under investigation, prepared by, reviewed by. Leave every number
as a placeholder like [AMOUNT]. I will fill them in from the spreadsheet.
Fill every placeholder from your spreadsheet. Have the memo reviewed under your normal sign-off rules.
What to do next
Save the formulas in your workbook template so next month starts with them. Turn the steps into a month-end checklist with the SOP playbook. For why AI helps on some tasks and hurts on others, see the task inventory playbook.
Rather have someone set it up for you?
That's a fair call. LTFI, the agentic business operating system from Kief Studio, can set up the work these guides describe and run it with you. No website yet? Kief Studio will build you one for free.

