3 Different Ways to Build a 13-Week Cashflow Forecast
A few weeks ago I gave you a warning about Fable 5. For building financial models it is no better than Opus, (and with some tasks Sonnet does the job well too).
But, the model is only half of the question. The other half is which tool you put around it.
So inside the club we built the exact same 13-week cashflow forecast three different ways using AI.
First in plain Excel. Then Python, with AI writing the code. Then using Excels’ own ‘Edit with Copilot’ AI function – But I did not do this to crown a winner.
Every week somebody asks me "Nicolas, which one should my team use?" and my answer is that it depends on your setup.
That answer is useless if I cannot explain you the trade-offs.
So today, I am showing you three methods, step by step.
Note that for simplicity, I kept the models simple. Because i believe a good forecasting model needs to start simple to get to 80% of your results and then you can fine tune as long as you can still manage it and others can understand it.
|
|
Method One – AI Generating Excel
For a smaller entity with one bank account this method is still fine. Just four steps, and you have your 13 weeks.
|
|
Step 1. Start with the bank balance.
Opening cash comes from the statement, not the ERP. Everything downstream inherits this number, so get it from the source.
Step 2. Schedule receipts by payment terms, not by sale date.
A product sold on 2-week terms and one sold on 3-week terms are two different rows in your model, even if they were invoiced the same day. Either use your invoice payment term date or an historical average by client.
Step 3. Put every disbursement in the week the cash leaves you.
Direct costs linked to sales (materials for example) often get paid one to three weeks before the sale they belong to, and this is where most manual forecasts break.
Step 4. Build the ending-cash line and flag the negative weeks.
Take all the following into one table: opening cash, receipts, disbursements, net change, ending cash. Then conditional formatting on any week that goes red.
To create this model in Excel here’s a prompt you can use after you’ve uploaded your data to your AI tool (just make sure it’s a secure tool with SOC2 compliance if you’re giving it sensitive data)
|
|
The Limits:
Manual data entry is everywhere. There’s no automation unless somebody wrote VBA and macros, and that person usually left the company two years ago. And as the dataset grows, Excel gets slow and crashes on you. We have been forecasting like this since 30 years. It works, and it gives you nothing to audit except your own copy-paste.
Method Two – Python
Remember, I’m a finance guy, so I do not use code. But that does not mean I can’t use Python.
Python is great when you are working with a lot of data, and want to use forecasting algorithms that you just can’t get in Excel or a standard AI chat.
In our advanced analytics masterclass, an AI-generated script "consolidated 2 million rows in 6 seconds."
The Excel version of that same job is a week of your life!
Step 1. Describe the job
Open your favorite AI tool, and describe your job, and your file structure, then ask for a script that you can give to Google (a free tool from Google where you can use Python).
You are not asking AI what your cash position is. You are asking it to build you the thing that calculates it.
|
|
Step 2. Open Google Colab
It runs in your browser, it is free, and nobody in IT needs to approve it. However, note that you use it with a Google account. So make sure to check if you are allowed to upload confidential data with your Google account.
Step 3. Run it, then read the code line by line before you believe the output.
There is no hallucination hiding inside code you can read. This is the audit that’s difficult to get from an AI chat alone.
Step 4. Next week, swap the data and run the same script.
Same inputs, same output, every single time.
Here’s a prompt you can use to generate the code to run cashflow using Python.
|
|
That last step is a good reason to use Python. Most AI models are probabilistic. They give you the most likely answer, which is not always the correct one.
Python is deterministic. Same in, same out. For a number you will put in front of a lender, that difference is super important.
Method Three – AI within Excel (Edit with Copilot)
The edit functionality of Copilot in Excel can handle a multi-tab file and gives you back real Excel formulas you can click and edit, in minutes, where other AI prompting modes can take hours to reach the same place.
Step 1. Check your license
Check if you have a supported Microsoft 365 Copilot subscription and open Excel. You will need either a Copilot Business plan or Enterprise (recommended for sensitive data use). Check out from Microsoft’s official pricing page.
|
|
Note: Pricing shown reflects business plans as of August 2026
No Copilot access? You can use Claude in Excel and/or ChatGPT in Excel add-ins if you don’t have access to a supported Microsoft 365 plan.
Step 2. Give it the whole brief
Give this in one prompt. The way you would brief a junior analyst.
In our tutorial that meant two products, unit volumes and prices, the payment terms on each, costs paid one to three weeks ahead of the sale, monthly leasing allocated by revenue, monthly salary, and two machine down-payments in weeks 5 and 8.
(Adapt it to your own situation)
Step 3. Watch the sidebar commentary while it builds, then click the cells.
It narrates what it is doing as it goes, and at the end you get a 13-week tab with the receipt schedule broken out by product and a chart flagging the negative weeks.
Here’s a prompt you can use in any AI Excel Add-in (Copilot, Claude and ChatGPT)
|
|
One of our experts, Anne-Claire Chanvin made the point in one of our workshops that pure Copilot output is probabilistic across runs, so the same prompt might not build you the same forecast twice.
Her fix is to pair Edit in Copilot with Power Query so the data refresh stays deterministic and only the build is AI.
The one thing to remember
Stop asking which tool is best. Ask how often you build the thing.
Either you build this forecast once, either you build it every week (or every month).
Once, and plain Excel is fine. Every week with lots of messy data, go Python. Every week inside a workbook your team already uses, use AI in Excel.
And whichever one you pick, the job at the end is the same. Review it, challenge it, own it. Fable 5, Opus, Sonnet, Python, Edit with Copilot.
None of them sign the forecast. You do.
Best,
Your AI Finance Expert,
– Nicolas
P.S. – To see me build this live, join me tomorrow for my 60-min masterclass, where I also answer all of your questions (it’s the best way to get my time for free)
P.P.S. – Curious why I said Fable 5 isn't worth it for financial models? Here's the full test → Is Fable 5 Actually Worth It For Finance?