--- title: "AI cash-flow forecasting: a weekly forecast you can defend" url: https://usingaias.com/ai-for/cash-flow-forecasting/ summary: "AI builds the weekly grid, the SUMIFS formulas and the assumptions wording. Every number comes from your aged debtors, aged creditors, payroll, PAYE and VAT dates and the bank balance." published: 2026-10-01 updated: 2026-10-01 author: "Jack Stovell" publisher: "Adapt Progress Evolve Limited" language: en-GB --- # AI cash-flow forecasting: a weekly forecast you can defend AI builds the weekly grid, the SUMIFS formulas and the assumptions wording. Every number comes from your aged debtors, aged creditors, payroll, PAYE and VAT dates and the bank balance. ## At a glance - Difficulty: Intermediate (Suits someone comfortable with Excel and with reading aged debtors and creditors reports.) - Time: About half a day to build the first version, then under an hour a week to roll it forward (our estimate, not a measured figure) - Tools: Microsoft Excel, Xero or your accounting software, ChatGPT, Claude or Copilot (an approved account) - You need: Today's bank balance, from the bank; Aged receivables and aged payables reports, exported to Excel; Payroll pay dates; Your VAT period end dates; A list of known one-off receipts and payments, with dates - Role hub: [AI for accountants and finance managers](https://usingaias.com/ai-for/accountants/) ## 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. ```text 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. ```text 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. ```text 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. ```text 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. ## Review checklist - [ ] Opening cash agrees to the bank - [ ] Receipts list totals back to the aged receivables report - [ ] Payments list covers the aged payables report, payroll and tax - [ ] PAYE on its due date (the 22nd of the next tax month if paying monthly; the 19th if by cheque) - [ ] VAT on its due date (usually one calendar month and 7 days after the period end) - [ ] Every assumption written down, with an owner and a last-checked date - [ ] Nothing left marked NEEDS SUPPORT - [ ] Cells hold formulas, not pasted values - [ ] Each week's closing cash rolls into the next week's opening cash - [ ] Last week's actual against forecast compared, and large variances explained - [ ] A person has read every line before it goes to a lender or the board ## Before you paste anything: confidentiality and UK GDPR Before you paste anything, ask two questions: is there personal data in it, and is it confidential to my organisation or a client? If the answer to either is yes, use a tool your organisation has approved, under a business contract, and send only what the task needs. **For this task:** Give the AI your layout, column headings and assumption notes, never customer or supplier names, invoice lists, bank details or payroll data. The figures stay in your own workbook. Never paste these into a personal (consumer) AI account: - Payroll reports, salaries by name, bank details or National Insurance numbers - Named employee records: health, absence, grievance or disciplinary details - Customer or supplier ledgers with names attached - Unpublished results, forecasts or board papers - Anything a client has given you - Passwords, API keys or bank logins (in any tool, ever) UK GDPR, in four lines: - **Send the minimum.** UK GDPR's data minimisation principle says personal data must be adequate, relevant and limited to what is necessary for the purpose. For most tasks on this site, the personal data the AI needs is none. ([ICO: The data minimisation principle](https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/data-protection-principles/a-guide-to-the-data-protection-principles/data-minimisation/)) - **Know who is controller and who is processor.** Under a business contract, the AI provider usually acts as your processor. On a personal consumer account, you are agreeing to the provider's own consumer terms instead. ([ICO: Controllers and processors](https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/controllers-and-processors/)) - **Check where the data goes.** Many AI services process data outside the UK. Restricted transfers need safeguards, which a business agreement usually addresses and a personal sign-up does not. ([ICO: International transfers](https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/international-transfers/)) - **The ICO has AI-specific guidance.** It covers accountability, transparency, accuracy and security when organisations use AI with personal data. ([ICO: Guidance on AI and data protection](https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/artificial-intelligence/guidance-on-ai-and-data-protection/)) Consumer plans vs business tiers (checked 2026-10-01; providers change their terms, so read the live page and your contract): - **OpenAI (ChatGPT).** Personal plans: conversations can be used to train models unless you turn off "Improve the model for everyone" in Settings > Data controls. ChatGPT Business, Enterprise, Edu and the API: not used to improve models by default. ([OpenAI Help Centre: How your data is used to improve model performance](https://help.openai.com/en/articles/5722486-how-your-data-is-used-to-improve-model-performance); [OpenAI: Enterprise privacy at OpenAI](https://openai.com/enterprise-privacy/)) - **Anthropic (Claude).** Free, Pro and Max: chats are used to train models only when the model-improvement setting is on. With it on, data is kept for up to five years; with it off, the standard is 30 days. Claude for Work and the API: inputs and outputs are not used to train models by default. ([Anthropic: Updates to Consumer Terms and Privacy Policy](https://www.anthropic.com/news/updates-to-our-consumer-terms); [Anthropic Privacy Center: Is my data used for model training? (consumer)](https://privacy.claude.com/en/articles/10023580-is-my-data-used-for-model-training); [Anthropic Privacy Center: Is my data used for model training? (commercial)](https://privacy.claude.com/en/articles/7996868-is-my-data-used-for-model-training)) - **Microsoft (Copilot).** Personal Microsoft accounts are covered by Microsoft's consumer terms, not your organisation's. Copilot and Copilot Chat used through an organisation: covered by Microsoft's Data Protection Addendum with Microsoft as processor; your data is not used to train foundation models. ([Microsoft Learn: Enterprise data protection in Microsoft Copilot and Copilot Chat](https://learn.microsoft.com/en-us/microsoft-365/copilot/enterprise-data-protection); [Microsoft Learn: Data, privacy and security for Microsoft Copilot](https://learn.microsoft.com/en-us/microsoft-365/copilot/microsoft-365-copilot-privacy)) > General information for UK readers. Not financial, legal, tax or HR advice. Example companies and figures are fictional unless a source says otherwise. Follow your organisation's policies and your professional body's rules. ## From the same studio Ours: made by the same studio that runs this site. - [How to get AI to](https://howtogetaito.com/make/a-spreadsheet/): More on getting AI to build or fix a spreadsheet, including a small test with known answers before you trust a formula. - [ScriptGrain](https://scriptgrain.com/): If the forecast goes to a board or lender with a written commentary, it drafts that commentary in your own voice from the reasons you give. ## Related - [AI for accountants and finance managers](https://usingaias.com/ai-for/accountants/) - [AI for bank reconciliation: the finance-team process](https://usingaias.com/ai-for/bank-reconciliation/) - [ChatGPT and Xero: what connects and what to export](https://usingaias.com/ai-for/xero/) - [P&L with AI: build one from a trial balance](https://usingaias.com/ai-for/p-and-l/) ## Sources - [Xero UK: Cash flow forecasting software (short-term cash flow projection)](https://www.xero.com/uk/accounting-software/analytics/cash-flow/) - [Xero Central: Use the short-term cash flow projection](https://central.xero.com/0/article/Short-term-cash-flow) - [Xero UK: JAX and plans (cash flow forecast length by plan)](https://www.xero.com/uk/jax/) - [Xero Central: Aged Receivables Summary report](https://central.xero.com/0/article/Aged-Receivables-Summary-report-New) - [Xero Central: Aged Payables Summary report](https://central.xero.com/0/article/Aged-Payables-Summary-report-New) - [Xero Central: Export and print a report](https://central.xero.com/0/article/Export-or-print-a-report) - [Xero Central: Connect Xero using the Xero connector in Claude (Small Business plugin)](https://central.xero.com/s/article/Connect-Xero-using-the-Xero-connector-in-Claude) - [GOV.UK: VAT Returns, deadlines](https://www.gov.uk/vat-returns/deadlines) - [GOV.UK: Pay employers' PAYE](https://www.gov.uk/pay-paye-tax) - [Microsoft Support: SUMIFS function](https://support.microsoft.com/en-gb/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b) ## Questions people ask ### Can AI forecast my cash flow? Use it to build the structure, write the formulas and draft the assumptions wording. The numbers come from your ledgers: the bank balance, aged debtors and creditors, payroll dates and tax due dates. Then every line traces to a source or a written assumption. ### When do PAYE and VAT go in a UK cash-flow forecast? PAYE is due by the 22nd of the next tax month if you pay monthly (the 19th by cheque), or the 22nd after the quarter end if you pay quarterly. VAT is usually due one calendar month and 7 days after the end of the VAT period, and the payment must reach HMRC by then. ### Doesn't Xero already forecast cash flow? Xero's short-term cash flow projection shows the next 7 or 30 days from today's bank balance plus invoices owed minus bills to pay, and its plans list a forecast whose length varies by plan. It doesn't know about items outside your invoices and bills unless you add them, so reconcile it to your own forecast before quoting either. ### What Excel formula builds a weekly cash-flow forecast? SUMIFS, adding each week's receipts and payments from dated lists, with closing cash as opening plus receipts minus payments, and each week's opening equal to the previous week's closing. ### What about Claude's cash flow forecast skill for Xero? Claude's Small Business plugin, used with the Xero connector, includes a skill that creates a 30/60/90-day forecast with a confidence range and flagged risks. It needs a Claude Pro, Max, Team or Enterprise plan. Treat its output as a comparison, not as your forecast.