How to build a 13-week cash flow forecast with AI

How to build a 13-week cash flow forecast

1. 1Export open invoices, at least six months of customer payment history, open bills, the payroll calendar and known one-off items such as tax payments or loan repayments.
2. 2Work out each customer's actual days-to-pay from the payment history, and use it to place each open invoice in the week you expect the cash.
3. 3Place bills in the week you plan to pay them, and payroll on its pay dates.
4. 4Chain the weeks: opening cash, plus collections, minus payments, equals closing cash, which becomes the next week's opening cash.
5. 5Add a line for the minimum cash you want to hold, and flag any week that falls below it.
6. 6Each week, replace the week just ended with actual figures, add a new week 13, and note why the actuals differed.

Copyable prompt: 13-week cash flow forecast

Upload the exports as CSV or as tabs in one workbook, then paste this prompt.

Build a rolling 13-week cash flow forecast for a [type of business], starting the week of [date]. Opening cash: [amount].

I have uploaded:
1. open_ar.csv: customer, invoice number, invoice date, due date, amount.
2. payments_history.csv: customer, invoice number, invoice date, date paid, amount (the last [6 to 12] months).
3. open_ap.csv: vendor, bill date, due date, amount, the week I plan to pay it if known.
4. payroll.csv: pay dates and the net pay, taxes and benefits for each.
5. one_offs.csv: date, description, amount (tax payments, loan repayments, equipment).

Please:
1. Work out each customer's average days from invoice date to payment, using payments_history.csv. For customers with no history, use [number] days and label it as an assumption.
2. Place each open invoice in the week its cash is expected, based on step 1.
3. Place bills, payroll and one-off items in their weeks.
4. Build a weekly table: opening cash, collections, payments by type, net change, closing cash. Each week's closing cash is the next week's opening cash.
5. Add a minimum cash line of [amount] and flag any week below it.
6. List every assumption you made in a separate table.

Show your formulas or workings so I can check them. Do not change any amounts from the files.

What goes into a 13-week cash flow forecast?

Mostly Metrics builds one for a fictional software company from eight messy tabs, including an invoice export with inconsistent terms, collections history, AP, a payroll calendar and one-off items^[1]. The finished model chains beginning cash, collections, disbursements, net change and ending cash week to week, and adds a covenant line that flags a breach automatically^[1].

How do you forecast collections with AI?

Use behavior, not terms. In the Mostly Metrics walkthrough, Claude first works out each customer's actual days-to-pay from collections history and uses that, rather than the payment terms on the invoice, to time the cash^[1]. You can do the same in ChatGPT by uploading the payment history as a CSV, since it writes and runs code to analyze uploaded files^[2].

Can Xero project cash flow for you?

Xero says JAX, with Analytics, can create cash flow projections and show your past and projected cash flow position from your Xero data^[3]. Compare its projection with your own list of payroll dates and one-off items before you rely on it.

Frequently asked questions

How often should I update a 13-week forecast?

Weekly. Replace the week that just ended with actual figures and add a new week at the end, so the forecast always looks 13 weeks ahead.

Can AI build the whole forecast?

It can build the structure and do the arithmetic; the Mostly Metrics walkthrough ends with a six-tab workbook^[1]. The assumptions, such as which bills you will pay and when, are yours to set.

What data should I leave out?

Bank account numbers and personal details the forecast does not need. Customer names can be swapped for codes before you upload.

Sources

1. 1.Mostly Metrics: How to build a rolling 13-week cash flow forecast using Claude and Excel
2. 2.OpenAI Academy: Data analysis with ChatGPT
3. 3.Xero Central: About Just Ask Xero (JAX)

Sources were checked when this page was last updated. Archived copies are cited where the original page could not be reached from our checker. Features and settings change, so confirm details with each vendor.