Find discrepancies in a counterparty reconciliation statement

Auditor · Time: 20 min · Finance

Tools that fit this task

• ChatGPT $20/mo
• Gemini in Google Sheets $14/mo
• Copilot in Excel $20/mo
• Rossum Free

Prompt

Role: you are an accountant reconciling balances with a counterparty.
Context: the first table is our ledger for {{period}}: date | document | debit | credit. The second table is the counterparty's statement for the same period with the same columns. Our opening balance is {{amount}}, theirs is {{amount}}.
Task: match documents by date, number and amount. Find documents only on our side, only on theirs, with a different amount, with a different date, and duplicate entries. Check whether the total of the discrepancies found explains the difference in closing balances.
Format: a table discrepancy type | date | document | our amount | their amount | difference | what to check. At the end, a summary: difference in balances, total of explained discrepancies, remaining unexplained amount. Take numbers only from the tables without rounding.

Steps

1. Move your ledger and the counterparty's statement into two tables with the same columns: date, document, debit, credit
2. Bring dates and amounts to one format and remove spaces inside numbers
3. Give the assistant both tables with a prompt to match documents by date and amount
4. Get the list: documents only on our side, only on theirs, with a different amount, with a different date
5. Check every item against the source documents and record the cause
6. Write to the counterparty listing the discrepancies and asking for the documents we lack

How to check the result

The total of listed discrepancies explains the difference in closing balances to the cent, every discrepancy has a known cause, and the letter to the counterparty is sent

Pitfalls

• The model matches documents by amount and confuses two different payments of the same amount: add the document number as a second key
• A statement with the counterparty name and account numbers goes into a public service with a counterparty code; in corporate Copilot or Workspace you can use real data

Data that stays out of public AI tools

• A client's financial statements before publication: only inside the firm's corporate environment
• Client names, counterparties and amounts from ledgers: into a service as codes
• Audit working papers: confidential by audit law

Other tasks for auditors

• Select an audit sample of transactions
• Write up an audit working paper
• Write a management letter after an audit
• Reconcile the trial balance with the bank and source documents
• Extract invoices and delivery notes into a spreadsheet
• Analyze a large data export
• Update the accounting policy document
• Check a source document for required fields
• Review a batch of contracts for the same risks
• Classify expenses by category

AI tools for auditors · Prompt: Find discrepancies in a counterparty reconciliation statement