Find discrepancies in a counterparty reconciliation statement

Procurement 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

• Supplier prices and terms from sealed bids: a trade secret of both sides, into a service with codes
• Annual procurement plans and budgets: before a tender is announced, only inside a corporate environment
• Contracts with real parties and amounts: into a public service as [Party A], [Party B], [amount]

Other tasks for procurement managers

• Run a quick counterparty check before a deal
• Build a competitor price review
• Run a risk review of a contract for your side
• Compare two versions of a contract
• Write a formal claim letter to a counterparty
• Keep a contract register and track deadlines
• Write an email to a foreign partner in their language
• Translate a contract and keep the terminology consistent
• Get a spreadsheet formula from a plain-language request
• Write a business letter

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