CPD lab · Excel with AI
Excel matching with AI
A week of customer receipts to match in a spreadsheet, with formulas an AI tool wrote. Test the formulas on the data, fix what breaks, and hand in the matching table.
Comes with Excel with AI and Data skills for accountants, and with the year. 29 labs open now.
The brief
You work in credit control at Kersey & Vane Building Supplies Ltd, a builders' merchant. It is Friday 30 October 2026 and this week's 13 receipts need applying before the ledger closes.
On Monday a colleague asked an AI tool to write Excel formulas to match receipts to invoices. The formulas are in the pack, untested. The pack also has the bank receipts, the open-items export (30 invoices) and the account notes. Every company and figure is invented, so you can paste it into any AI tool.
Use your AI tool to test the formulas against this data, fix them, and match every receipt. You can work in Excel or any spreadsheet, or ask the AI to reason through the data directly. You will be marked on the table and your note, not on a workbook.
Open the CSV files in a text editor as well as a spreadsheet. Exports carry quirks, such as spaces and text-formatted numbers, that a spreadsheet hides.
Give one line for each invoice a receipt pays. The amounts on a receipt's lines must add up to the receipt. If a receipt can't be applied, give one line with invoice_ref blank.
Action, one per line. MATCH: the evidence supports applying this amount, and it clears the invoice. PART: the customer has paid part of the invoice it names, under an agreed plan, and the rest stays open. TOLERANCE: applied, and a difference of 1.00 or less on the receipt is written off under the tolerance policy; put any such difference on the receipt's last line, in invoice order. UNAPPLIED: the cash can't be matched on the evidence; leave invoice_ref blank and hold it unallocated.
A bank reference that quotes an invoice of the payer's, or an amount that matches exactly one open invoice of the payer's, is enough evidence. An amount that matches another customer's invoice is not.
Then write a short review note: which formulas failed and why, what you changed, what you checked, and what goes to a person.
Objective: To be able to test and correct AI-written spreadsheet formulas for matching receipts to invoices, so that the cash application they support is complete, accurate and backed by evidence.
What's in the pack
Every name, figure and email in the pack is invented. Use your own AI tool: Claude, ChatGPT, Copilot or Gemini.
receipts.csv
Bank receipts, 26-30 October 2026 (13 lines)
open-items.csv
Sales ledger open items at 26 October 2026, as exported (30 invoices)
account-notes.txt
Credit control account notes and the tolerance policy
ai-formulas.txt
The formulas a colleague's AI tool wrote, not yet tested
The traps
The pack has 8 deliberate traps: cases where AI tools often get it wrong and a person must check. We don't list them here. Finding them is the job, and your feedback names each one you missed.
What you submit
- Your result, as a table with the columns receipt_ref, invoice_ref, amount_applied, action, note.
- The prompt you used.
- A short review note, 150 to 350 words: what the AI got wrong, what you checked, and what goes to a person.
How it's marked
- Your result, checked line by line against the answer key60%
- Your review note, marked on traits40%
The review note's traits:
- Formulas tested on the data
- Fixes that address the cause
- Matching judged on evidence
- Exceptions sent to a person
- Ledger data kept out of personal accounts
Pass at 80. Under 80, read the feedback and try again. Then write your reflection and copy the entry for your CPD record.
Price
- Excel with AI, £39. The role track and its labs, this one included. No end date.
- Data skills for accountants, £79. The role track and its labs, this one included. No end date.
- Everything for a year, £199. Every course, role track and lab for 365 days.
- Monthly, £19.99 a month, cancel any time. Every lesson, plus 8 marked tasks a month, across every course, role track and lab.
Buying for a team, or want a lab built on your own process? CPD for employers.