Find discrepancies in a counterparty reconciliation statement

Finance manager · Time: 20 min · Finance

Tools that fit this task

• ChatGPT $20/mo
• Copilot in Excel $20/mo
• Gemini in Google Sheets $14/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

• Management reports with real names of divisions, products and clients: into a public service with codes and placeholder names
• Receivables and payables with counterparty names and amounts: a trade secret, replace them with codes
• Terms of loans, collateral and investor agreements: never into third-party services without a corporate agreement

• Reconcile a counterparty statement with Gemini in Google Sheets

Other tasks for finance managers

• Analyze budget variances, actual versus plan
• Build a 12-week cash flow forecast
• Explain the numbers to the business owner
• Design a KPI dashboard for the business owner
• Get a spreadsheet formula from a plain-language request
• Run a quick counterparty check before a deal
• Build a quarterly results presentation
• Reconcile the trial balance with the bank and source documents
• Categorize a bank statement by cash flow line
• Write a sales proposal
• Run a risk review of a contract for your side
• Write a weekly report to your manager

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