Bank reconciliation with AI help
Listen to this lesson
Nigerian voice narration
Fictional training case. The cash book and September bank statement disagree by ₦412,500, and the reconciliation is due before books close. This lesson shows how AI can spot likely reconciling items and write the Excel formulas to flag them automatically. Work through the example, identify the evidence needed and practise the method with cleared or synthetic inputs. Any additional figures in the explanation are separate practice examples, not claimed rows in a downloadable file.
Worked example
Ada removes account numbers from both the cash book and bank statement lists, then pastes them into AI asking it to find items appearing in one list but not the other — the classic suspects being unpresented cheques, bank charges, and uncredited lodgements. AI flags a ₦412,500 unpresented cheque dated 28 September as the likely cause of the full gap, matching it almost exactly. Rather than accepting this blindly, Ada asks AI to write a COUNTIFS formula comparing Sheet1 (cash book) against Sheet2 (bank statement) by date and amount, runs it herself, and confirms the flagged cheque is indeed the only unmatched item before closing the reconciliation.
The method
Strip account numbers and names before sharing bank data with any AI tool. Ask AI to identify items present in one list but absent in the other, naming common reconciling items (unpresented cheques, bank charges, uncredited lodgements) explicitly in your prompt. Have AI write the actual Excel formula (COUNTIFS, XLOOKUP) rather than just describing the answer, then run and verify it yourself.
Worked practice and common mistakes
Date-and-amount matching is a first pass, not proof of reconciliation. Posting dates can differ, and legitimate transactions can share an amount. Use transaction references where available. Classify timing differences separately from cash-book corrections. An item matching the gap does not establish completeness: support every reconciling item and confirm that the adjusted balances agree before the authorised review.
Review before using the result
Support each reconciling item with a source record and confirm that adjusted balances agree. One amount matching the gap is only a lead, not proof that the reconciliation is complete. Date-and-amount matching can miss posting delays or repeated amounts, so use references where available and obtain the normal authorised review.
Your workplace practice
Use the recommended free assistant with this lesson's fictional example. Write an Excel COUNTIFS formula that flags rows in my cash book (Sheet1, columns A=Date, C=Amount) not found in the bank statement (Sheet2, same columns). Keep a copy of the input and draft. Compare the result with the verification steps above, record any correction, and retain placeholders where evidence is missing. The expected result is a checked draft for human review, not an automatically approved decision. Course examples and practice responses are fictional; amounts, dates, rules and thresholds are not official guidance or supplier quotations.
Open Google Gemini (free tier) and paste this prompt there. Suggests reconciliation formulas from small synthetic tables; apply and test them in your spreadsheet yourself.
Write an Excel COUNTIFS formula that flags rows in my cash book (Sheet1, columns A=Date, C=Amount) not found in the bank statement (Sheet2, same columns).
Why this tool: Suggests reconciliation formulas from small synthetic tables; apply and test them in your spreadsheet yourself.
Hit a free limit? Switch to ChatGPT or Gemini and continue — the prompt works in any assistant.