How to build a 13-week cash flow forecast in Excel with Copilot and Claude

A 13-week cash flow forecast is the short-term liquidity tool that treasurers, controllers and lenders rely on when the question is whether the company can meet payroll, suppliers, tax payments and debt service over the next quarter. It is built week by week on a receipts-and-disbursements basis, starting from the actual bank balance, and it is rolled forward every week as actual cash replaces forecast cash.

The structure of the forecast is simple, while the detail is demanding. Every line depends on timing assumptions: how many weeks customers take to pay, which week payroll falls in, when the VAT return is settled, when a loan instalment leaves the account. A wrong lag in a single receipts line can move the low point of the forecast by several weeks and hide a breach of the minimum cash balance.

Microsoft documents Copilot in Excel as able to edit worksheets directly, create formulas and generate and transform data, while Anthropic documents Claude for Excel as able to build models or populate templates, explain formulas with cell-level citations and change assumptions while preserving formula relationships. Both vendors also state in their documentation that outputs must be reviewed before anyone relies on them. The practical approach is to give the AI a clearly structured set of inputs, ask it to build the model, and then test its result against a figure computed independently, as in the worked example below.

··········

WHAT A 13-WEEK CASH FLOW FORECAST CONTAINS.

The forecast uses the direct method: it lists cash coming in and cash going out, week by week, instead of starting from profit and adjusting for non-cash items. The difference between the two approaches is the same one that separates the two formats of the cash flow statement, described in the site's article on direct versus indirect cash flow statements.

A standard layout has four blocks. The opening position is the actual bank balance at the start of week 1. Receipts are customer collections, split between collections of invoices already outstanding (taken from the receivables aging) and collections of invoices that will be raised during the forecast period. Disbursements cover payroll, supplier payments, rent, taxes, debt service and capital expenditure. The closing block shows net cash flow, ending cash, the minimum cash balance the company wants to keep, and the headroom or shortfall against that minimum.

The most error-prone part is the receipts block. Collections of new sales depend on a lag pattern (for example, a share collected four weeks after invoicing and another share six weeks after), and the lag must push cash into later weeks without double-counting invoices that already sit in the opening receivables. Disbursements are easier to model but depend on calendars: a biweekly payroll, a monthly rent date, a quarterly tax payment.

Finance teams use the result for three decisions: whether a revolving credit facility will need to be drawn and in which week, whether payments to suppliers can be scheduled differently, and whether collection efforts need to be focused on specific customers. Those decisions connect directly to the working-capital levers covered in the site's article on working capital management and cash flow optimization.

··········

COPILOT IN EXCEL AND CLAUDE FOR EXCEL: DOCUMENTED REQUIREMENTS.

Both tools work inside the Excel workbook, but they are licensed, installed and governed differently. The table below summarizes what each vendor's official documentation states, as verified on 1 November 2026.

........

Item	Copilot in Excel	Claude for Excel
Vendor	Microsoft	Anthropic
Access	Microsoft 365 plans listed in Microsoft's FAQ	Add-in for Claude Pro, Max, Team and Enterprise
Installation	Built into Excel	Claude for Microsoft 365 add-in from Microsoft AppSource
Platforms	Windows, Mac and web; chat on mobile	Excel on the web, Windows and Mac builds listed by Anthropic
Working modes	Edit, plan and chat	Chat in the add-in sidebar, with edits to the workbook
Documented limits	Editing requires automatic calculation	No Excel data tables, no macros or VBA
Review warning	Verify everything before relying on it	Not for audit-critical calculations without verification

........

Microsoft's FAQ for Copilot in Excel, last updated on 12 October 2026, lists the eligible plans: a Microsoft 365 Personal or Family plan with an AI credits plan, a Microsoft 365 Premium subscription, a commercial Microsoft Copilot subscription, or a Microsoft 365 or Office 365 business or enterprise subscription eligible for Copilot Chat. Enterprise availability depends on the license and on the tenant configuration. The FAQ states that Copilot editing is supported only when Calculation Options are set to Automatic, that files saved in Strict Open XML Spreadsheet format are not supported and should be saved as .xlsx, and that SharePoint files requiring check-out can block Copilot on Windows and Mac. Copilot works in three modes: edit changes the workbook directly, plan proposes a plan first, and chat keeps the answers in the pane. The documented capabilities include creating formulas, charts, PivotTables and formatting, sorting and filtering, and generating and transforming data. A practical overview of the interface is in the site's article on how to use Copilot in Excel.

Anthropic's documentation describes Claude for Excel as generally available to Pro, Max, Team and Enterprise plans. It supports Excel on the web, Excel on Windows with a Microsoft 365 subscription (build 16.0.13127.20296 or later) and Excel on Mac (version 16.46 or later); it does not run on Excel 2016 or 2019 perpetual licenses, on iPad or on Android. The documented capabilities include answers with cell-level citations, changing assumptions while keeping formula relationships intact, tracing errors, building new models or populating templates, and working across multi-tab workbooks. Excel data tables and macros or VBA operations are listed as unsupported, so a scenario grid for this tool should be built with ordinary formulas.

··········

WORKED EXAMPLE: ILLUSTRATIVE INPUTS FOR A 13-WEEK FORECAST.

The example uses an illustrative company with invented figures, chosen to be small enough to check by hand. Opening cash at the start of week 1 is 420,000 and the company wants to keep a minimum cash balance of 250,000.

Receipts come from two sources. Collections of the opening receivables are taken from the aging: 190,000 in week 1, 185,000 in week 2, 170,000 in week 3, 150,000 in week 4, 95,000 in week 5 and 60,000 in week 6, for a total of 850,000. New sales are invoiced at 200,000 per week from week 1; 60% of each week's invoices is collected four weeks later, 35% six weeks later, and the remaining 5% falls outside the forecast horizon.

........

Disbursement	Amount	Timing
Payroll	145,000	Weeks 2, 4, 6, 8, 10 and 12
Supplier payments	95,000	Every week
Rent	38,000	Weeks 1, 5, 9 and 13
VAT payment	72,000	Week 4
Loan instalment	60,000	Weeks 6 and 13
Capital expenditure	110,000	Week 8

........

The workbook should have an Inputs sheet with these assumptions in labelled cells and a Forecast sheet with weeks 1 to 13 in columns. Keeping the assumptions in separate cells lets both tools change a lag or an amount without rewriting formulas, and lets the reviewer see every driver in one place.

A prompt for Copilot in plan mode, or for Claude for Excel, can describe the whole structure in one request:

Build a 13-week direct cash flow forecast on a new sheet called Forecast,
using the assumptions on the Inputs sheet. Weeks 1 to 13 go in columns.
Rows: beginning cash; collections of opening receivables; collections of
new sales; total receipts; payroll; supplier payments; rent; VAT; loan
instalment; capital expenditure; total disbursements; net cash flow;
ending cash; minimum cash; headroom (ending cash minus minimum cash).
New sales of 200,000 per week are collected 60% four weeks after the
invoice week and 35% six weeks after. Do not hard-code numbers in the
Forecast sheet: every value must be a formula that refers to Inputs.
Highlight in red any week where ending cash is below the minimum.

After the model is built, a second prompt asks the tool to explain its own logic so the reviewer can compare it with the specification:

For week 7 and week 10, explain which invoice weeks the collections of
new sales come from and show the cells used. Then list every week where
ending cash is below 250,000 and the amount of the shortfall.

··········

THE CORRECT RESULT OF THE 13-WEEK FORECAST EXAMPLE.

The figures below were computed independently in Python from the same inputs. They are the reference values against which the AI-built workbook should be checked.

........

Week	Receipts	Disbursements	Net flow	Ending cash	Headroom
1	190,000	133,000	57,000	477,000	227,000
2	185,000	240,000	-55,000	422,000	172,000
3	170,000	95,000	75,000	497,000	247,000
4	150,000	312,000	-162,000	335,000	85,000
5	215,000	133,000	82,000	417,000	167,000
6	180,000	300,000	-120,000	297,000	47,000
7	190,000	95,000	95,000	392,000	142,000
8	190,000	350,000	-160,000	232,000	-18,000
9	190,000	133,000	57,000	289,000	39,000
10	190,000	240,000	-50,000	239,000	-11,000
11	190,000	95,000	95,000	334,000	84,000
12	190,000	240,000	-50,000	284,000	34,000
13	190,000	193,000	-3,000	281,000	31,000

........

Collections of new sales start in week 5 at 120,000 (60% of the week 1 invoices) and stay at 120,000 in week 6. From week 7 onwards they reach 190,000 per week: 120,000 from the invoices of four weeks earlier plus 70,000 from the invoices of six weeks earlier. Opening receivables run off by week 6, so receipts settle at 190,000 per week from week 7.

The control totals for the 13 weeks are receipts of 2,420,000 and disbursements of 2,559,000, a net outflow of 139,000 that takes cash from 420,000 to 281,000. Disbursements break down into payroll of 870,000, supplier payments of 1,235,000, rent of 152,000, VAT of 72,000, loan instalments of 120,000 and capital expenditure of 110,000. The low point is week 8, where the capital expenditure falls in a payroll week and ending cash drops to 232,000, a shortfall of 18,000 against the minimum. Week 10 shows a second, smaller shortfall of 11,000. At the end of week 13, 1,030,000 of the invoices raised during the period is still uncollected, which is why the forecast should always be read together with the receivables balance.

A sensitivity test shows how much the result depends on the collection lag. If the 35% tranche is collected eight weeks after invoicing instead of six, the low point is still week 8 but ending cash falls to 92,000, ending cash stays below the minimum from week 8 to week 13, the largest shortfall is 158,000, and cash at the end of week 13 is 141,000. A two-week slip in one collection tranche turns a short-term dip of 18,000 into a funding need that lasts six weeks. Because Claude for Excel does not support Excel data tables, this scenario is best built as a second column of lag assumptions on the Inputs sheet with a switch cell, which both tools can work with through ordinary formulas.

··········

REVIEWING AN AI-BUILT 13-WEEK CASH FLOW FORECAST.

The most common errors in an AI-built forecast are timing errors. The tool may apply the collection lag in the wrong direction, start new-sales collections in week 4 instead of week 5, or count the opening receivables and the new invoices twice. The week-by-week receipts row should be compared with the reference table above, and the explanation prompt for weeks 7 and 10 should name invoice weeks 3 and 1 for week 7, and weeks 6 and 4 for week 10.

The second group of errors concerns calendars. A biweekly payroll can be placed in odd weeks instead of even weeks, a monthly rent can be repeated every week, and a tax payment can be spread across several periods. Checking the disbursement totals by category against the control totals catches most of these mistakes in a few seconds.

The third group concerns the structure of the workbook. The prompt asked for formulas only, so the reviewer should select the Forecast sheet and confirm that no cell contains a typed number. A simple identity check in a spare row, ending cash equal to beginning cash plus receipts minus disbursements, should return zero difference in every week:

=ROUND(C14-(C2+C5-C12),2)

The cell references depend on the layout the tool actually built, so the formula has to be adjusted to the rows of the workbook. Beginning cash in each week must also equal ending cash of the previous week, and week 1 must start from the actual available bank balance, excluding restricted cash and cash in transit.

Some decisions stay with people: the choice of collection percentages and lags, the minimum cash balance, the treatment of disputed invoices, and any decision to draw on a credit line or delay supplier payments. The headroom row in the forecast connects naturally to the liquidity measures explained in the site's article on liquidity ratios, but the forecast gives a week-by-week view that a ratio at a single balance-sheet date cannot provide.

··········

DATA HANDLING IN COPILOT IN EXCEL AND CLAUDE FOR EXCEL.

A cash forecast contains bank balances, customer names and payment plans, so the data policies of each tool belong in the review. Microsoft states that Copilot processes prompts and responses within the Microsoft 365 service boundary with enterprise data protection, honours existing permissions and sensitivity labels, and does not use prompts, responses or documents to train the underlying foundation models. The Copilot in Excel FAQ adds that changes saved to a workbook are visible to everyone with access to the file, including coauthors, and that administrators cannot switch off Copilot in Excel separately from the Copilot Chat controls.

Anthropic's documentation for Claude for Excel states that, when a user signs in with a Claude account, conversations are sent to Anthropic, including the content Claude reads from the workbook and any attached files. Inputs and outputs are deleted on the backend within 30 days, with exceptions described in Anthropic's privacy documentation, and chat history is stored locally in the browser. The add-in does not inherit custom data retention settings and its activity is not included in Enterprise audit logs, although Enterprise organizations with the Compliance API enabled have Claude for Excel sessions included there. Anthropic also warns that spreadsheets from external sources can contain hidden instructions, and recommends using the add-in only with trusted files and confirming risky operations carefully.

··········

13-WEEK CASH FLOW FORECASTING WITH AI: WHAT THE TOOLS DO AND WHAT STAYS WITH FINANCE.

Copilot in Excel and Claude for Excel can both work on a forecast workbook from a written specification: Microsoft documents edits, formulas and data generation in Copilot's edit and plan modes, and Anthropic documents model building, cell-level explanations and assumption changes that keep formula links intact. Their requirements differ: Copilot depends on a qualifying Microsoft 365 or Copilot plan and on automatic calculation, while Claude for Excel requires a Claude Pro, Max, Team or Enterprise plan and an add-in installation on a supported Excel build, without support for data tables or macros.

In the worked example, the correct forecast shows a net outflow of 139,000 over 13 weeks, a low point of 232,000 in week 8 and shortfalls against the 250,000 minimum in weeks 8 and 10. A two-week delay in the second collection tranche pushes the low point to 92,000 and keeps cash below the minimum for six weeks. Those reference values are the test that an AI-built workbook has to pass before anyone uses it.

The collection assumptions, the minimum cash level and the funding decisions remain judgements for the finance team, and neither vendor documents its tool as a substitute for that review. The weekly roll-forward, with actual receipts and payments replacing the forecast, is also a human-controlled routine: the AI can update the workbook, but the comparison between forecast and actual cash is what shows whether the lags in the model reflect how customers really pay.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org