Automate a Cash Flow Dashboard in Excel with Claude AI
Progress1 of 4
1
How the dashboard works
2
What to feed Claude
3
Prompt, FAQ & related
4
Quick quiz
Section 01
Revenue is an opinion. Cash is a fact. This dashboard tracks the fact.
Most finance teams can tell you last month's revenue within seconds. Ask them how much cash went out the door last week, split by operating vs investing vs financing — and the answer takes an hour of spreadsheet work. That's the gap this dashboard fills.
You paste bank transactions. Claude categorises them, calculates weekly and monthly totals by type, and adds a runway calculation that tells you how many weeks of cash you have at the current burn rate. The result is a dashboard you update in minutes, not hours.
The Pipeline: Raw Transactions → Actionable Dashboard
Bank export
Categorise
Op / Inv / Fin totals
Runway calc
The Three Buckets of Cash Flow
Operating
Customer receipts
Supplier payments
Payroll
Rent & utilities
Tax payments
= Day-to-day cash burn
Investing
Equipment purchases
Asset disposals
Software / IT spend
Security deposits
= Long-term asset moves
Financing
Loan drawdowns
Loan repayments
Equity injections
Dividend payments
Line of credit moves
= How you fund the business
The Output: What Claude Produces
A
B
C
D
E
1
Wk 1
Wk 2
Wk 3
Wk 4
2
Operating Cash Flow
3
Customer receipts
85,000
62,000
91,000
78,000
4
Supplier payments
-42,000
-38,000
-51,000
-44,000
5
Payroll
—
-95,000
—
-95,000
6
Net Operating
43,000
-71,000
40,000
-61,000
7
Investing
8
Equipment purchase
—
—
-28,000
—
9
Financing
10
Loan repayment
-12,500
—
—
—
11
Net Cash Change
30,500
-71,000
12,000
-61,000
12
Closing Balance
530,500
459,500
471,500
410,500
Payroll hits weeks 2 and 4 (biweekly). The pattern is immediately visible. Opening balance: $500,000. Runway at current average burn: ~21 weeks.
Key insight: The dashboard doesn't just sum inflows and outflows — it separates why cash moved. A -$71k week looks alarming until you see it's payroll week and operating receipts simply hit the following Monday. Without categories, you're reacting to noise instead of signal.
What to Feed Claude
The dashboard is only as good as the data behind it. Claude needs a clean bank transaction export — no summary rows, no running balances, just one row per transaction. The quality of the categorisation depends entirely on how descriptive the transaction data is.
Bank Transaction Layout
A
B
C
D
1
Date
Description
Amount
Reference
2
02/06/2025
Acme Ltd – inv 1044
28,500
CR-8821
3
02/06/2025
ADP Payroll – Jun W1
-95,000
DD-4401
4
03/06/2025
Dell Financial – server
-28,000
PO-0312
5
04/06/2025
HSBC loan – monthly
-12,500
LN-0087
01
Delete summary rows and running balances
Bank exports often include "Opening Balance", "Closing Balance", and "Total Debits" rows. Delete them. Claude should see only individual transactions — one row, one cash movement.
02
Use a consistent sign convention
Positive = cash in. Negative = cash out. If your bank uses separate debit and credit columns, consolidate into a single Amount column before pasting. Tell Claude which convention you're using.
03
Include the opening cash balance
Claude needs a starting point. State it clearly: Opening cash balance as of 01/06/2025: $500,000. Without this, the Closing Balance row in the dashboard is meaningless.
04
Specify what "weekly" means to you
Monday-to-Sunday? Monday-to-Friday? Some teams use the calendar week; others use the working week. Tell Claude so the weekly groupings match how your team already thinks about cash.
Common mistake: Treating a transfer between your own accounts as a cash inflow. Moving $50,000 from savings to chequing is not operating cash — it's a reclassification. The prompt tells Claude to flag internal transfers separately so they don't inflate your operating receipts.
Pro tip: If your bank descriptions are cryptic (e.g. "POS 04/06 REF 882314"), add a fifth column with your own plain-English descriptions before pasting. Claude categorises based on text — better descriptions produce better categorisation. Alternatively, make sure your data is first reconciled against the GL where descriptions are usually cleaner.
The Claude Prompt for a Cash Flow Dashboard
Structured in sections so Claude knows exactly what role to play, what constraints to follow, and how to format the output.
Prompt — paste into Claude
ROLE
You are a treasury analyst building a weekly cash flow dashboard in Excel.
OBJECTIVE
Take a bank transaction export and produce a categorised cash flow dashboard with weekly columns, a monthly summary, and a runway calculation.
DATA
Opening cash balance as of [date]: $[amount]
Date format: DD/MM/YYYY
Week definition: [Monday–Sunday / Monday–Friday]
Transactions (columns: Date, Description, Amount, Reference):
[Paste your bank transactions here]
CATEGORISATION RULES
Assign each transaction to exactly one category:
- OPERATING INFLOW: customer payments, refunds received, interest earned
- OPERATING OUTFLOW: supplier payments, payroll, rent, utilities, insurance, tax, professional fees
- INVESTING: equipment/asset purchases, asset sales, software purchases, security deposits
- FINANCING: loan drawdowns, loan repayments, equity injections, dividends, line of credit
- INTERNAL TRANSFER: movements between the company's own bank accounts
- UNCATEGORISED: anything you cannot classify with confidence — flag these, do not guess
CONSTRAINTS
- Do NOT guess categories for ambiguous transactions — mark them UNCATEGORISED and flag for review.
- Do NOT fabricate transaction amounts or descriptions.
- Internal transfers must NOT appear in Operating, Investing, or Financing totals.
- Every transaction must appear in exactly one category. No double-counting.
OUTPUT FORMAT
1. TRANSACTION LOG: Full list with columns: Date | Description | Amount | Reference | Category
2. WEEKLY DASHBOARD: Rows grouped by category (Operating Inflows, Operating Outflows, Net Operating, Investing, Financing, Net Cash Change, Closing Balance). One column per week.
3. MONTHLY SUMMARY: Same structure, one column for the full month.
4. RUNWAY: Current cash balance ÷ average weekly net burn (trailing 4 weeks) = estimated weeks of runway.
5. FLAGGED ITEMS: List of all UNCATEGORISED and INTERNAL TRANSFER transactions for manual review.
Format as tab-separated tables I can paste directly into Excel.
Dashboard vs Statement: Different Tools, Different Jobs
A cash flow statement is a formal financial report — prepared at period-end, under accounting standards, with accruals and adjustments. A cash flow dashboard is operational — it shows where cash is right now, how fast it's moving, and when you'll run out. The statement tells the board what happened. The dashboard tells the CFO what's happening.
If you need the formal statement for a 3-statement financial model, that course covers the indirect method CFS linked to the IS and BS. This dashboard feeds a different need: operational visibility and cash planning. For teams tracking overdue payments that affect future cash, the accounts receivable tracking course provides the receivables data that predicts next week's inflows.
Common Categorisation Mistakes
A
B
C
1
Transaction
Wrong Category
Correct Category
2
Transfer to savings account
Operating Outflow
Internal Transfer (excluded)
3
Server purchase — $28k
Operating Outflow
Investing (asset purchase)
4
Credit line drawdown
Operating Inflow
Financing (debt drawdown)
5
Customer refund issued
Operating Outflow (new)
Operating Outflow (reversal of inflow) — correct but must net against receipts
Getting categories wrong doesn't change total cash — but it distorts the operating burn rate, which distorts the runway calculation. Small errors compound.
Frequently Asked Questions
What's the difference between a cash flow dashboard and a cash flow statement?
The statement is a formal financial report prepared at period-end under accounting standards. The dashboard is operational — it shows where cash is now, how fast it's moving, and when you'll run out. The statement tells the board what happened. The dashboard tells the CFO what's happening.
Can Claude categorise bank transactions into operating, investing, and financing?
Yes, if the descriptions are reasonably clear. The prompt instructs Claude to flag anything it can't classify with confidence. You review the flagged items and assign them manually — Claude never guesses silently.
How do I calculate cash runway from this dashboard?
Current cash balance divided by average weekly net cash burn over the trailing period you specify (typically 4–8 weeks). This gives estimated weeks of runway at the current burn rate.
Bank data or GL data — which should I use?
Bank data shows actual cash movements and is better for operational dashboards. GL data may include accruals that haven't settled. For a cash-focused dashboard, bank transactions are cleaner. Reconcile against the GL periodically.
How often should I update this dashboard?
Weekly is the most common cadence. Daily during cash-constrained periods. Monthly if the dashboard is primarily for board reporting rather than day-to-day management.