Find discrepancies in a counterparty reconciliation statement

Accountant · 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

• Employee names, tax IDs, dates of birth and salaries: into a public service only as [Employee 1] and [amount]
• Account numbers, tax numbers and counterparty names from bank statements and the trial balance: banking and trade secrets, replace them with codes C1, C2
• Digital signature keys and passwords to online banking or the taxpayer portal: never, anywhere

Other tasks for accountants

• Reconcile the trial balance with the bank and source documents
• Categorize a bank statement by cash flow line
• Check a source document for required fields
• Prepare a reply to a tax authority request
• Write an overdue payment reminder
• Get a spreadsheet formula from a plain-language request
• Summarize changes in legislation for colleagues
• Analyze budget variances, actual versus plan
• Explain the numbers to the business owner
• Draft an internal company order
• Write a business letter
• Summarize a long PDF
• Keep a contract register and track deadlines

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