Build a 3-Statement Financial Model in Excel with Claude AI
Progress1 of 4
1
How the model works
2
What Claude needs from you
3
Prompt, FAQ & related
4
Quick quiz
Section 01
The challenge isn't building the statements — it's making them work together
Build a 3-Statement Financial Model Excel Template with Claude AI
Every finance professional eventually needs a three-statement model. The hard part isn't the individual statements — it's connecting them so that a single change in revenue cascades correctly through operating profit, retained earnings, working capital, and cash. One broken link and the balance sheet stops balancing.
Claude can generate the structure, formulas, and validation logic in minutes. Your job shifts from building to reviewing — checking that the linkages are correct and the assumptions make sense for your business.
The Architecture: Three Statements, One System
Income Statement
Revenue
COGS
Operating Expenses
Depreciation & Amort.
Interest & Tax
→ Net Income
Balance Sheet
Cash ← from CFS
AR, Inventory
PP&E (net)
AP, Debt
Retained Earnings ← from IS
Check: A = L + E
Cash Flow (Indirect)
Net Income ← from IS
+ D&A add-back
± Working capital Δ
− CapEx
± Debt changes
→ Ending Cash → to BS
How the Numbers Flow
This is the path a revenue change takes through the entire model. If any of these links breaks, the balance sheet won't balance — and the Balance Check row will tell you immediately.
Revenue
Operating Profit
Net Income
Retained Earnings (BS)
Cash from Operations (CFS)
Ending Cash (BS)
Balance Check = 0 ✓
The Output: What Claude Produces
A
B
C
D
1
FY24 (Actual)
FY25 (Proj)
FY26 (Proj)
2
Income Statement
3
Revenue
2,400,000
2,640,000
2,904,000
4
Net Income
192,000
228,000
262,000
5
Balance Sheet
6
Total Assets
1,800,000
1,980,000
2,190,000
7
Total L & Equity
1,800,000
1,980,000
2,190,000
8
Balance Check
0
0
0
9
Cash Flow Statement
10
Ending Cash
340,000
415,000
510,000
Condensed view — the full model includes COGS, OpEx, D&A, working capital, CapEx, debt, and equity detail. Blue cells are linked values. Balance Check = Assets − (L + E); must be zero.
Key insight: Finance teams don't ask Claude to build spreadsheets. They ask Claude to build the accounting logic — the formulas that connect Net Income to Retained Earnings, CapEx to PP&E, and working capital changes to operating cash flow. The spreadsheet is just where that logic lives.
What Claude Needs From You
Claude builds the model from two inputs: one year of historical financials (the anchor) and a set of forward-looking assumptions (the engine). Skip the history and you get a blank template. Skip the assumptions and you get a static snapshot. Provide both and you get a working model that responds to changes.
The Historical Anchor
A
B
1
Income Statement — FY 2024
2
Revenue
2,400,000
3
COGS
960,000
4
OpEx (excl. D&A)
1,080,000
5
Depreciation & Amort.
120,000
6
Interest Expense
18,000
7
Tax Rate
25%
01
Separate depreciation from other OpEx
This is non-negotiable. D&A must be its own line because it flows differently — it's an expense on the IS, an add-back on the CFS, and accumulated on the BS. Lumping it into OpEx breaks two out of three linkages. If your depreciation comes from a detailed asset schedule, use that total here.
02
Provide a complete balance sheet
At minimum: Cash, AR, Inventory, PP&E (net), AP, Accrued Liabilities, Debt (current + long-term), and Retained Earnings. Every working capital line becomes a CFS adjustment. Missing one means a missing cash flow link — and a balance sheet that won't balance.
03
State your assumptions as numbers, not directions
Revenue grows 10% per year is a usable assumption. Revenue will increase is not. Claude needs specific percentages, dollar amounts, and ratios to build formulas. Vague inputs produce vague models.
Circular reference trap: If interest expense depends on average debt, and debt depends on cash, and cash depends on interest expense — the model loops. The prompt instructs Claude to use beginning-of-period debt for interest calculations, which breaks the circle cleanly.
Pro tip: Put all assumptions on a dedicated tab. Every forecast cell references that tab. Change one number, and the entire three-statement model recalculates. This is the difference between a model that's built once and a model that's used every month.
The Claude Prompt for a 3-Statement Model
This prompt is structured in sections — Role, Objective, Data, Constraints, Output — because Claude performs measurably better with explicit structure than with a single block of instructions.
Prompt — paste into Claude
ROLE
You are a senior FP&A analyst building an Excel financial model.
OBJECTIVE
Build a fully linked 3-statement financial model: Income Statement, Balance Sheet, and Cash Flow Statement (indirect method). One base year of actuals, projected forward [3] years.
HISTORICAL DATA — BASE YEAR (FY [year])
Income Statement:
[Paste: Revenue, COGS, OpEx excl. D&A, Depreciation & Amortisation, Interest Expense, Tax Rate]
Balance Sheet (as of [date]):
[Paste: Cash, Accounts Receivable, Inventory, Other Current Assets, PP&E (net), Total Assets, Accounts Payable, Accrued Liabilities, Current Debt, Long-Term Debt, Retained Earnings, Total Equity]
PROJECTION ASSUMPTIONS
- Revenue growth: [e.g. 10% per year]
- COGS as % of revenue: [e.g. 40%]
- OpEx (excl. D&A) growth: [e.g. 5% per year]
- CapEx per year: [e.g. $200,000]
- Depreciation: straight-line on net PP&E over [X] years
- DSO (days sales outstanding): [e.g. 45 days]
- DPO (days payable outstanding): [e.g. 30 days]
- Inventory days: [e.g. 60 days]
- Debt repayment: [e.g. $50,000 per year]
- Interest rate on debt: [e.g. 5%]
- Tax rate: [e.g. 25%]
CONSTRAINTS
- Net Income must flow: IS → Retained Earnings (BS) → starting line of CFS.
- Working capital changes (ΔAR, ΔAP, ΔInventory) must link BS movements to CFS operating section.
- CapEx on CFS must increase PP&E on BS. D&A must flow IS → CFS add-back → BS accumulated depreciation.
- Ending Cash on CFS must equal Cash on BS.
- Add a Balance Check row: Total Assets − (Total Liabilities + Equity). Must show zero.
- Use beginning-of-period debt for interest expense — no circular references.
- All projection cells must use formulas, not hard-coded numbers.
- Do NOT invent financial data or assumptions I haven't provided.
OUTPUT FORMAT
- Three sections clearly labelled: Income Statement, Balance Sheet, Cash Flow Statement.
- Write formulas explicitly (e.g. =B3*1.10) so I can audit the logic.
- Tab-separated tables, ready to paste into Excel.
Why This Model Matters
A 3-statement model isn't an academic exercise — it's the foundation for every downstream financial decision. Fundraising decks pull from it. Board presentations reference it. Cash runway calculations depend on it. When the model is broken, the decisions it informs are broken too.
Claude handles the formula plumbing. You handle the assumptions and the judgment calls: Is 10% revenue growth realistic? Should CapEx increase in year two? Does the DSO assumption reflect actual collection experience? Those questions require business context that no AI has.
If any link breaks, the Balance Check row shows a non-zero value. That's your first and fastest diagnostic: find the zero, then trace backward to the broken formula.
Frequently Asked Questions
What links the three financial statements together?
Net income flows from the IS into retained earnings on the BS and is the starting point for the CFS. Working capital changes feed the CFS operating section. CapEx on the CFS increases PP&E on the BS. Ending cash on the CFS must equal cash on the BS.
Can Claude build this model without historical data?
It can build the structure and formulas, but the output is a blank template. For a working model, provide at least one year of historical financials so Claude can populate the base period and project from real numbers.
How does the balance sheet check work?
Claude adds a row that calculates Total Assets minus Total Liabilities and Equity. If it shows anything other than zero, there's a linkage error. This is the single most important integrity check in any 3-statement model.
Direct or indirect method for the cash flow statement?
Indirect. It starts with net income and adjusts for non-cash items and working capital changes. This is the standard for financial modelling. The direct method is occasionally used for management reporting but is rarely seen in models.
Can I extend this to a 5-year projection?
Yes — change the horizon in the prompt. Provide assumptions for each year and Claude extends the model. Keep in mind that projections beyond 3 years are increasingly speculative; treat them as directional.