AI cash-flow forecasting: a weekly forecast you can defend

What you’ll end up with

A weekly rolling forecast in Excel. Opening cash, receipts by source, payments by type, closing cash, an assumptions sheet, and a variance-to-actual column so you can see how last week’s forecast held up.

The trick is who does what. AI builds the structure, writes the formulas and drafts the wording of your assumptions. Every number comes from your ledgers: aged debtors and creditors, payroll dates, VAT and PAYE due dates, and the bank balance. If a line can’t be traced to a source or a written assumption, it doesn’t belong in there.

That’s what makes it defensible. When someone asks “where did that figure come from?”, you’ve got an answer.

Before you start

Gather the raw material before you open any AI tool. This is the bit people skip, and it’s the bit that matters most.

You need today’s bank balance, taken from the bank itself. You need your aged receivables and aged payables. In Xero, the Aged Receivables Summary shows what customers owe and how long it has been outstanding, and the Aged Payables Summary shows what you owe and whether it’s overdue. Export both to Excel: open the report, click Export and choose Microsoft Excel.

Then the dates. Payroll pay dates, your VAT period ends, and any known one-offs (an annual insurance payment, a piece of equipment, a bonus, whatever applies to you). Write each one-off down with an amount and a date, because a one-off you remember in week six is one you’ve already got wrong.

If you use Xero, look at its short-term cash flow projection too. It shows expected cash flow for the next 7 or 30 days, based on today’s bank balance plus invoices owed minus bills to pay, and you can add expected payment dates to overdue invoices. Xero’s UK plans also list a built-in cash flow forecast whose length varies by plan; check Xero’s pricing page for the current horizon. It’s a useful cross-check later, but don’t treat it as your forecast.

Steps

1. Decide the horizon and the weekly grid

Pick how far ahead you’re going: far enough to catch the next VAT payment and a couple of payroll runs, not so far that you’re inventing things.

Then fix the weekday. Every week ends on the same day, say a Friday, and every column is a week ending on that day. This matters more than it sounds. If the weeks drift, the formulas later will quietly drop or double-count items, and you won’t see it.

Checkpoint: you can state the horizon in weeks, the weekday the weeks end on, and the date of the first week-ending. They’re written at the top of the workbook.

2. Ask AI for the structure only

Now ask for the skeleton. Sheets, row labels, column headings. No numbers, and you say so in the prompt.

I need the structure of a rolling weekly cash-flow forecast in Excel for a UK business. Do not put in any figures, sample or otherwise.

Horizon: [NUMBER] weeks, weeks ending on a [WEEKDAY], first week ending [DATE].

Propose: the sheets I need; the row labels for opening cash, receipts by source, payments by type (including payroll, PAYE, VAT and suppliers), and closing cash; the column headings; and a variance-to-actual column. Leave every cell that needs a number empty. Include a dated list of receipts, a dated list of payments and an assumptions sheet.

Look for an assumptions sheet, the two dated lists and separate rows for PAYE and VAT. If it hands you a table full of tidy round numbers, tell it to take them out and try again.

Checkpoint: a layout with headings and empty number cells, with anything renamed that doesn’t match how your business talks about money.

3. Pull the numbers from your ledgers

This step is all you. Take the aged receivables export and, for each invoice, decide the week you expect the cash. Not the due date: the week you realistically expect it. Where a customer usually pays late, use their usual pattern and write it down as an assumption.

Do the same with the aged payables, putting each bill in the week you’ll actually pay it. Payroll goes in on pay dates. PAYE goes in on its due date: GOV.UK says to pay by the 22nd of the next tax month if you pay monthly (it must reach HMRC by the 19th if you pay by cheque), or by the 22nd after the end of the quarter if you pay quarterly. VAT goes in on its due date too, which is usually one calendar month and 7 days after the end of the VAT period, and the payment must reach HMRC by then.

Put it all into the two dated lists, one row per item: date, amount, source or type, and a note. The bank balance goes in as week one’s opening cash.

Checkpoint: the receipts list totals back to the aged receivables report, the payments list covers the aged payables report plus payroll and tax, and opening cash agrees to the bank.

4. Get AI to write the formulas

AI is good at this, as long as you give it the layout and not the data.

Here is my layout. The dated receipts list is on sheet [SHEET NAME], with dates in column [LETTER], amounts in column [LETTER] and source in column [LETTER]. The dated payments list is on sheet [SHEET NAME], with the same columns and type in column [LETTER]. The forecast weeks run across row [NUMBER] of sheet [SHEET NAME], each cell holding the week-ending date.

Write Excel formulas that: use SUMIFS to add each week's receipts by source and payments by type, picking up items dated after the previous week-ending and on or before this week-ending; set closing cash as opening cash plus receipts minus payments; and set each week's opening cash equal to the previous week's closing cash. Use cell references only, no typed-in numbers. Explain each formula in one line.

SUMIFS adds values that meet several criteria at once, which is exactly what “this source, this week” needs. Paste what it gives you, then test one week by hand.

Checkpoint: one week’s receipts agree to a manual count from your list, and changing the first opening balance flows all the way across to the last closing balance.

5. Write the assumptions sheet

Every judgement call goes here. A customer that pays about [NUMBER] days late; a supplier paid the week after the due date; a one-off expected in a given week. Write your notes in plain words first, then let AI tidy them.

Here are my cash-flow forecast assumptions as rough notes: [PASTE YOUR NOTES, WITH CUSTOMER AND SUPPLIER NAMES REPLACED BY CODES]. Turn each into one clear sentence for an assumptions sheet. Do not add any assumption I haven't written. If a note is vague or can't be supported by what I've given you, don't fix it: mark it "NEEDS SUPPORT" and say what is missing.

That last instruction is the important bit. AI will happily fill a gap with something that sounds sensible. You want it to flag the gap instead. Put an owner’s name against each assumption and the date it was last checked.

Checkpoint: every assumption is written down, every one has an owner, and nothing is still marked “NEEDS SUPPORT”.

6. Roll it forward weekly and compare with actual

Each week, move the week just gone into actuals, add a new week at the far end, refresh the exports and update the dated lists. Fill the variance column: actual minus forecast.

Small gaps are normal. Big ones need a reason written next to them, because the reason is how the next forecast improves. Over a few weeks your assumptions about when customers really pay stop being guesses.

Checkpoint: last week’s actual and forecast sit side by side, and every large variance has a written explanation.

7. Stress-test the logic with AI, never the numbers

Here AI is useful as a second pair of eyes on the logic. Ask it to review the formulas and the wording, and to suggest what-ifs.

Review this cash-flow forecast logic and the assumptions text below. Do not change or suggest any figures. Tell me: where a formula could double-count or miss an item; where an assumption is ambiguous; and three what-if scenarios I should test, such as a large customer paying [NUMBER] weeks late or a one-off payment moving by a week.

Formulas: [PASTE FORMULAS]
Assumptions: [PASTE ASSUMPTIONS TEXT]

Then run the what-ifs yourself, with your own numbers. Xero’s projection lets you add or subtract one-off amounts to test scenarios if you want a cross-check. If you use Claude’s Small Business plugin with the Xero connector, it has a cash flow forecast skill that creates a 30/60/90-day forecast with a confidence range and flagged risks (the plugin needs a Claude Pro, Max, Team or Enterprise plan). Treat that as a comparison, not as your forecast.

Checkpoint: you’ve answered each what-if with your own figures, and every formula issue it raised is either fixed or deliberately dismissed.

Common mistakes

• Letting AI type numbers. It will if you let it, and they’ll look plausible. Plausible isn’t sourced.
• Assuming customers pay on the due date. Many don’t. Use the week you expect the cash, and write down why.
• Forgetting VAT and PAYE. They’re large, they have fixed dates, and they’re due whether or not the cash is there.
• Closing cash not rolling into the next opening. One broken link and every later week is wrong, quietly. A formula that refers back to itself is the other version of the same problem.
• Mixing the software’s projection with your own without reconciling. They start from different information. Reconcile the differences before you quote either.

When to keep AI out of it

Keep it away from the figures, always. And if the forecast is going to a lender or the board, a person reads every line before it leaves.