Build a 13-Week Cash Flow Forecast in Excel with Claude AI
Progress1 of 4
1
The finished forecast
2
Prepare your data
3
Prompt, FAQ & related
4
Quick quiz
Section 01
What the finished 13-week forecast looks like
Build a 13-Week Cash Flow Forecast in Excel with Claude AI
This course gives you a Claude prompt that turns your current bank balance, expected receipts, and planned disbursements into a week-by-week cash flow forecast spanning one full quarter. You'll get a finished Excel layout with opening balances, net movement, and closing cash for each of the 13 weeks — ready to share with your CFO or lender.
What Your Finished 13-Week Cash Flow Forecast Looks Like
Below is the output Claude produces. Each week shows cash in, cash out, and the resulting closing balance. The model rolls forward automatically — week 2's opening balance is week 1's closing balance.
A
B
C
D
E
F
1
Week
Week Starting
Opening Bal
Total In
Total Out
Closing Bal
2
1
07/07/2025
85,000
42,000
-31,500
95,500
3
2
14/07/2025
95,500
18,000
-54,200
59,300
4
3
21/07/2025
59,300
36,500
-28,000
67,800
5
4
28/07/2025
67,800
8,500
-62,000
14,300
6
…
…
…
…
…
…
14
13
29/09/2025
41,200
55,000
-38,750
57,450
Green = closing balance above minimum threshold. Amber = within 30% of minimum. Red = below minimum cash buffer. Thresholds are set in the prompt.
Week-by-week view of receipts, disbursements, and net cash position for a full quarter
Automatic roll-forward — each week's opening balance equals the prior week's close
Colour-coded closing balances that flag weeks where cash drops below your minimum threshold
A summary row showing cumulative net cash movement across all 13 weeks
Where this fits: The 13-week forecast is one of the core planning tools in our AI for Excel course library. If you already track actuals week-to-week, the cash flow dashboard course shows how to monitor real-time performance against this forecast.
Prepare Your Data for a 13-Week Cash Forecast
Claude needs three inputs: your current bank balance, a list of expected cash inflows, and a list of planned outflows. Pull these from your accounting system, AR aging report, and AP schedule. The more specific the timing, the better — "£20,000 expected week of 21 July" is far more useful than "£20,000 sometime in Q3".
Expected Cash Inflows
A
B
C
D
1
Source
Amount
Expected Week
Confidence
2
Acme Ltd – Inv 1045
42,000
Wk 1
Confirmed
3
Globex Corp – retainer
18,000
Wk 2
Confirmed
4
Initech – project milestone
36,500
Wk 3
Probable
5
Soylent Inc – overdue inv
8,500
Wk 4
At risk
Planned Cash Outflows
A
B
C
D
1
Category
Amount
Payment Week
Frequency
2
Payroll
24,000
Wk 2, 6, 10
Monthly
3
Rent
7,500
Wk 1
Monthly
4
Supplier – raw materials
15,000
Wk 2
One-off
5
VAT / sales tax
38,000
Wk 4
Quarterly
01
Start with your actual bank balance
Use today's cleared balance, not the ledger balance. The difference between the two often includes uncleared cheques and deposits in transit — those belong as separate line items in week 1.
02
Assign every item to a specific week
Vague timing kills forecast accuracy. If you know payroll runs on the 15th, put it in the week that contains the 15th. For uncertain receipts, use your best estimate and mark confidence as "At risk".
03
Separate recurring from one-off items
Recurring items (payroll, rent, subscriptions) repeat across multiple weeks. List them once with their frequency — the prompt instructs Claude to replicate them across the right weeks automatically.
Tip: If you maintain a rolling forecast model, use its monthly projections as a cross-check. The 13-week forecast should reconcile to month 1 of the rolling forecast within a reasonable margin.
The Exact Claude Prompt for a 13-Week Cash Flow Forecast
Copy this prompt into Claude. Replace the bracketed sections with your own data. The prompt tells Claude to use only the figures you provide and to flag any week where the closing balance drops below your stated minimum.
Prompt — paste into Claude
I need to build a 13-week cash flow forecast in Excel. Below are my inputs.
CURRENT BANK BALANCE (as of [date]):
[Enter your cleared bank balance here, e.g. £85,000]
MINIMUM CASH BUFFER:
[Enter the lowest acceptable closing balance, e.g. £20,000]
EXPECTED CASH INFLOWS (columns: Source, Amount, Expected Week, Confidence):
[Paste your inflows data here]
PLANNED CASH OUTFLOWS (columns: Category, Amount, Payment Week, Frequency):
[Paste your outflows data here — mark Frequency as Weekly / Monthly / Quarterly / One-off]
Rules:
- Week 1 starts on [enter date, e.g. 07/07/2025]. Each subsequent week starts 7 days later.
- Opening balance for Week 1 = the bank balance above. Every other week's opening balance = prior week's closing balance.
- Closing balance = Opening balance + Total inflows − Total outflows for that week.
- For recurring items: replicate the amount into every applicable week based on the stated frequency.
- If an inflow is marked "At risk", include it in the forecast but add a separate row showing the forecast WITHOUT at-risk items.
- Do NOT invent receipts or payments. Use ONLY the data I have provided.
- Flag any week where the closing balance falls below the minimum cash buffer.
Output:
1. A 13-week forecast table with columns: Week | Week Starting | Opening Bal | Total In | Total Out | Closing Bal
2. A second version of the same table excluding all "At risk" inflows (worst-case scenario)
3. A summary showing: Total inflows, Total outflows, Net cash movement, Lowest closing balance (and which week), Number of weeks below minimum buffer
Format as an Excel-ready table I can paste directly into a spreadsheet.
Why the 13-Week Horizon Matters
Monthly cash flow statements tell you what happened. Annual budgets tell you what you hope will happen. Neither helps when a £38,000 VAT payment lands in the same week as a delayed client payment. The 13-week forecast sits between the two — granular enough to catch the week-four crunch you can see in the sample output above, but long enough to give you time to arrange a credit line or accelerate collections.
Most lenders require a 13-week forecast during covenant discussions or restructuring. Even outside those scenarios, a treasurer who can show the board exactly which week cash dips below the safety buffer — and what drives the recovery — commands far more credibility than one reporting "cash looks tight next quarter".
Common Forecast Mistakes
A
B
C
1
Mistake
Why It Hurts
How to Avoid
2
Using ledger balance instead of bank balance
Overstates available cash by the amount of uncleared items
Always start with cleared bank balance; list uncleared items separately
3
Treating "At risk" receipts as confirmed
Creates a false sense of liquidity in critical weeks
Run two scenarios: base case and worst case (the prompt does this)
4
Spreading monthly items evenly across weeks
Masks the actual payment date, hiding intra-month shortfalls
Pin each disbursement to the specific week it settles
Frequently Asked Questions
Why 13 weeks and not 12 or 26?
Thirteen weeks equals one fiscal quarter. It's short enough to forecast with reasonable accuracy but long enough to flag upcoming liquidity gaps before they become emergencies. Most lenders and boards expect this exact horizon.
Can I extend this forecast beyond 13 weeks?
Yes. Add columns for weeks 14–26 and feed in your projected figures. Accuracy declines the further out you go, so many teams keep weeks 1–4 at line-item detail and use broader categories for weeks 5–13+.
How do I handle uncertain receipts like overdue invoices?
Include them in a separate row labelled "At-risk receipts" with a probability weighting. For example, a £20,000 invoice at 50% probability enters the forecast as £10,000. Flag these rows so reviewers can see the assumption.
Will Claude pull live bank data for the forecast?
No. Claude works with the data you provide. Export your current bank balance and upcoming commitments from your accounting system, then paste them into the prompt. Claude structures the forecast — it does not connect to external systems.
How often should I update a 13-week cash flow forecast?
Weekly. Each Monday, replace the actuals for the week just ended, extend the forecast by one week, and adjust any projected figures that have changed. This keeps the model rolling and accurate.