Build a Rolling 13-Week Cash Flow Forecast with AI and Excel
What the source describes
In this Mostly Metrics tutorial, the author records building a 13-week rolling cash forecast for a fictional software company from eight messy data tabs, including an invoice export with inconsistent terms, collections history, AP, a payroll calendar and one-off items. Claude first works out each customer's actual days-to-pay from collections history rather than nominal terms, then builds a forecast with beginning cash, collections, disbursements, net change and ending cash chained week to week. The model includes a covenant line with an automatic OK/BREACH flag.
Result or outcome
According to the source, Claude turned the scattered data into a six-tab workbook ending in a 13-week forecast that chains weekly balances and flags a covenant breach automatically; the walkthrough is a demo on fictional data rather than a client case study.
How to run it
- Gather the client's open AR, a year of paid invoices with payment dates, open AP, payroll calendar and one-off payments into one Excel workbook.
- Ask your assistant (the source used Claude in the desktop app and the Excel sidebar) to audit the data for duplicates, credit memos and blank terms and calculate each customer's actual days-to-pay.
- Decide how to handle each flagged item, such as dropping duplicates, defaulting blank terms and forecasting on actual payment behavior instead of invoice terms.
- Run the build prompt on this page to create the 13-week forecast with chained weekly balances, a minimum cash line and an alert flag.
- Spot-check where large receipts land, tie actual weeks to the bank, then roll the forecast forward weekly with an actual-versus-forecast variance tab.
Try it yourself
Copyable prompt
I am attaching an Excel workbook for [client business name] with tabs for open AR, 12 months of paid invoices with invoice and payment dates, open AP, payroll calendar, and one-off payments. Step 1: clean the AR data (standardize payment terms, flag duplicates and credit memos) and calculate each customer's average actual days-to-pay versus terms. Step 2: build a 13-week cash forecast starting [date] with opening cash of [amount]. Place each receipt in the week the customer actually tends to pay and each disbursement in the week cash leaves. Rows: beginning cash, collections, payroll, AP, taxes and one-offs, net change, ending cash. Add a minimum cash line of [amount] with an alert. Use formulas, and list every assumption on a separate tab.
Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.
What to review
Confirm opening cash ties to the bank balance and that each week's beginning cash equals the prior week's ending cash. Check that large customer receipts fall in the week their payment history supports, not the invoice due date, and that payroll and tax payment dates are correct. The source walkthrough uses fictional data, so test every formula on this client's books.
Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))
Source: Mostly Metrics (newsletter)
Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.
This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.
Related workflows
- Reporting and Analysis
Trace Formula Errors in an Inherited Workbook with Claude for Excel
Anthropic's documentation describes Claude for Excel as an add-in for Pro, Max, Team and Enterprise plans that brings Claude into Excel. It can answer questions about an open workbook with cell-level citations, adjust assumptions while keeping formula relationships intact, identify errors and their root causes, and work across multi-tab workbooks. It can also sort, filter, edit pivot tables and add data validation dropdowns. The documentation warns that files from external sources can contain hidden instructions that try to manipulate the add-in, and says to start with a trusted copy before asking Claude to edit widely.
Claude for Excel, Microsoft Excel
Claude Docs (Claude for Excel) - Reporting and Analysis
Find Trends and Outliers in Ledger Exports with Copilot in Excel
Microsoft Support explains that Copilot in Excel lets users ask questions in their own words to surface summaries, trends and outliers, returning results as charts, PivotTables or summaries. It can also add formula columns and rows (for example profit margin or percent of total), suggest single-cell formulas, build lookups such as XLOOKUP, and explain existing formulas. Microsoft advises being specific about which columns to analyze and to review and verify anything Copilot creates.
Microsoft Copilot, Microsoft Excel
Microsoft Support - Reconciliations
Reconcile a bank statement export against your chart of accounts with Claude Cowork
Claude Academy walks through giving Cowork a folder with a checking account statement export and the chart of accounts, then asking it to match transactions to accounts, flag anything that does not line up, and draft journal entries for discrepancies. Claude may ask clarifying questions and build a plan for review, then returns the reconciliation logic, specific discrepancies and journal entries ready for review. Suggested follow-ups include exporting entries as CSV and creating a reconciliation summary for the records.
Claude, Claude Cowork +1
Claude Academy (Anthropic tutorial) - Reconciliations
Build a Formatted Reconciliation Workbook from CSVs with Claude
Anthropic's help article says Claude can execute code to create and work with files directly in a conversation, producing Excel spreadsheets, PowerPoint presentations, Word documents and PDFs that you can download or save to Google Drive. It says code execution and file creation are available on Free, Pro, Max, Team and Enterprise plans, on the web, Claude Desktop and Claude Mobile. Its examples include building financial models in Excel with working formulas and performing advanced analyses on uploaded data, which is what a reconciliation workbook built from bank and ledger exports needs.
Claude, Microsoft Excel
Claude Help Center
If you want somewhere to check the numbers from this report or analysis, the Small Business Bookkeeping Spreadsheet Bundle is on our sister store.