Start with two lists and a cutoff date
The invoice PDF shows what you billed. The bank statement shows movements. To see what remains, link the two and choose a cutoff date. List each invoice with its issuer, reference, currency, total and due date. Keep a separate list of payments with dates, amounts, currencies and supporting references.
Work on a copy. If you want to test a chat tool, use fictional figures or an authorised sample with the references replaced. You can design the sheet with made-up figures and fill it in within your own workspace.
The €360 invoice
In this fictional case, invoice A-104 totals €360 and is due on 5 November. At the close of 6 November there are two linked receipts, P01 for €90 and P02 for €110. P02 appears in both the bank statement and the internal record. They document one payment, so the €110 goes into the total once.
A third receipt, P03, is for €160 and has no invoice reference. The balance based on the verified payments is €360 − €90 − €110 = €160. The matching amount makes P03 a candidate for review. If the supporting record later links it to A-104, the balance in this example becomes zero.
What to ask AI for
Try this instruction. “Prepare one row per invoice using only these two lists. Include the reference, due date, total, verified payments with their IDs, the balance and the next check. Group entries for the same transaction and retain both sources. List unlinked payments separately and say what evidence would resolve each one.”
Add a counterexample. Two customers might both pay €160 on the same day. You still need another reference to tell the payments apart. Ask for that ambiguity to be flagged and treat the result as a review draft.
Check the balance before you write
Open A-104 and check the total, currency and due date. Find P01 and P02 in the supporting records, check that each identifier appears once in the sum and trace P03 to its original entry. If your business uses several invoice series, match on issuer, series, number and currency to distinguish similar references.
Keep due dates and reconciliation in separate fields. The due date says when the payment was expected. Reconciliation records the movements you could link. An invoice can be overdue with a receipt still to check. Record the cutoff and who will handle that check.
When a spreadsheet is enough
A spreadsheet can be enough when the references are clean. Use a conditional sum to include only movements with the correct key and verified status. Check that the payment table has one row per receipt first. A formula will add both rows if a duplicate is still there.
If you already invoice through accounting software, use its reconciliation as the primary record. Some systems let you link a bank movement to an existing entry. Accept a match when you can point to the evidence that shows they describe the same payment.
Leave a clear next step
For A-104, a useful note reads “€360 billed · €200 matched · €160 balance from verified receipts · P03 to reconcile · check the reference before contacting the customer.” If you draft an email, confirm the amount first and give the customer room to clarify a recent payment.
The catalogue prompt can help you prepare the review. The final check is whether someone else can open the same records, reproduce the sum and see why a receipt is still open.

