How to Build a Simple Three-Statement Financial Model in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
A three-statement financial model in Excel links an income statement, a balance sheet and a cash flow statement to one assumptions sheet. Net income flows into the cash flow statement and retained earnings. The cash flow statement's closing cash becomes the balance sheet's cash. If total assets equal liabilities plus equity, the links are right.
Key takeaways
- Put every input on an Assumptions sheet; the statements contain only formulas that point to it.
- Build in order: income statement, balance sheet without cash, cash flow statement, then link closing cash back.
- Net income feeds both the cash flow statement and retained earnings, which is what ties the statements together.
- Add a check row, total assets minus liabilities and equity, that must show 0 in every year.
On this page+
A three-statement financial model in Excel links the income statement, balance sheet and cash flow statement so that one change in the assumptions flows through all three. This guide builds the smallest version that still works: one assumptions sheet, three statement sheets and one forecast year. When it's done, the balance sheet balances, which proves the links are right. The figures are examples, checked in Python; the guide teaches the spreadsheet structure, not financial advice.
What a three-statement model is#
Each statement answers a different question, and the model connects them:
| Statement | Question it answers | Sheet name used here |
|---|---|---|
| Income statement | How much profit did we make this year? | IS |
| Balance sheet | What do we own and owe at the year end? | BS |
| Cash flow statement | Why did our cash change? | CF |
The links are what make it a model rather than three separate reports. Net income feeds the cash flow statement and retained earnings. Closing cash on the cash flow statement becomes cash on the balance sheet. If every link is right, total assets equal total liabilities plus equity.
You'll use cross-sheet references such as =IS!B11, which means "cell B11 on the IS sheet". If a sheet name contains a space, Excel adds quotes: ='Income Statement'!B11. The guide on how to reference another sheet covers the syntax.
Step 1: Put every input on an Assumptions sheet#
Inputs are the numbers you choose; everything else is a formula. Keep them together on a sheet named Assumptions, in column B:
| Row | Assumption | Value |
|---|---|---|
| 2 | Revenue | 500,000 |
| 3 | Cost of goods sold, % of revenue | 40% |
| 4 | Operating expenses | 150,000 |
| 5 | Depreciation | 20,000 |
| 6 | Interest rate on debt | 5% |
| 7 | Tax rate | 25% |
| 8 | Receivables, % of revenue | 10% |
| 9 | Capital expenditure | 30,000 |
| 10 | Debt repayment | 20,000 |
Many modellers colour input cells blue and formulas black, so anyone can see what is safe to change.
Step 2: Build the income statement#
The income statement starts with revenue and subtracts costs down to net income. On the IS sheet, column B:
| Row | Line | Formula | Year 1 |
|---|---|---|---|
| 2 | Revenue | =Assumptions!B2 | 500,000 |
| 3 | Cost of goods sold | =B2*Assumptions!B3 | 200,000 |
| 4 | Gross profit | =B2-B3 | 300,000 |
| 5 | Operating expenses | =Assumptions!B4 | 150,000 |
| 6 | Depreciation | =Assumptions!B5 | 20,000 |
| 7 | Operating profit | =B4-B5-B6 | 130,000 |
| 8 | Interest | =BS!B6*Assumptions!B6 | 5,000 |
| 9 | Profit before tax | =B7-B8 | 125,000 |
| 10 | Tax | =B9*Assumptions!B7 | 31,250 |
| 11 | Net income | =B9-B10 | 93,750 |
Interest uses the opening debt (BS!B6). Using the average of opening and closing debt is more accurate, but it can create a circular reference, so start simple. For a fuller income statement layout, see how to build a profit and loss statement.
Step 3: Build the balance sheet, except cash#
The balance sheet has the opening position in column B (Year 0, typed in) and the forecast in column C. Fill in every line except cash, which comes from the cash flow statement in Step 5:
| Row | Line | Year 0 | Year 1 formula | Year 1 |
|---|---|---|---|---|
| 2 | Cash | 50,000 | =CF!B9 | 103,750 |
| 3 | Receivables | 40,000 | =IS!B2*Assumptions!B8 | 50,000 |
| 4 | Equipment (net) | 200,000 | =B4+Assumptions!B9-IS!B6 | 210,000 |
| 5 | Total assets | =SUM(B2:B4) | =SUM(C2:C4) | 363,750 |
| 6 | Debt | 100,000 | =B6-Assumptions!B10 | 80,000 |
| 7 | Share capital | 100,000 | =B7 | 100,000 |
| 8 | Retained earnings | 90,000 | =B8+IS!B11 | 183,750 |
| 9 | Liabilities and equity | =SUM(B6:B8) | =SUM(C6:C8) | 363,750 |
Year 0 totals 290,000 on both sides. Each Year 1 line is "last year plus or minus what happened": equipment grows by capital spending and shrinks by depreciation, debt falls by the repayment, and retained earnings grow by net income. The SUM function gives the totals.
Step 4: Build the cash flow statement#
The cash flow statement explains the change in cash in three sections. On the CF sheet, column B:
| Row | Line | Formula | Year 1 |
|---|---|---|---|
| 2 | Net income | =IS!B11 | 93,750 |
| 3 | Add back depreciation | =IS!B6 | 20,000 |
| 4 | Increase in receivables | =-(BS!C3-BS!B3) | -10,000 |
| 5 | Cash from operations | =SUM(B2:B4) | 103,750 |
| 6 | Capital expenditure | =-Assumptions!B9 | -30,000 |
| 7 | Debt repayment | =-Assumptions!B10 | -20,000 |
| 8 | Net change in cash | =B5+B6+B7 | 53,750 |
| 9 | Closing cash | =BS!B2+B8 | 103,750 |
Depreciation is added back because it reduced profit without any cash leaving. Receivables rose by 10,000, which is sales not yet collected in cash, so it is subtracted. Watch the signs: cash going out is negative.
If your cash flow lines carry a section label, SUMIF can total each section. With labels in A2:A6 and amounts in C2:C6, =SUMIF(A2:A6,"Operating",C2:C6) returns 103,750. SUMIF is also handy for building statement lines from a detailed list, such as all expenses tagged "Rent". The SUMIF function guide explains the arguments.
Step 5: Link cash and check that it balances#
Now close the loop. Balance sheet cash, BS!C2, is =CF!B9, the closing cash of 103,750. Then add a check row on the balance sheet:
=C5-C9Total assets of 363,750 minus liabilities and equity of 363,750 gives 0. A zero proves that every change on the balance sheet has a matching line in the cash flow statement. If it shows any other number, look for a balance sheet line that changed without a cash flow line, or a sign the wrong way round. Many modellers format the check cell red when it isn't zero.
Extend the model to more years#
To forecast several years, add columns: Year 2 in column C of the IS and CF sheets, column D of the balance sheet, and so on. Two habits make this work:
- Lock references to single input cells. If the tax rate is one cell for all years, write
Assumptions!$B$7so it doesn't shift when you fill right. Press F4 on Windows or ⌘T on a Mac. The dollar sign guide explains locked and mixed references. - Put yearly assumptions in columns. For revenue growth, give each year its own input column and let the references move with the fill.
Keep one formula per row across all years, so a reviewer can check one cell and trust the rest. For a month-by-month cash view instead, see how to build a cash flow forecast. Real models add more lines, such as inventory, payables and dividends, but each one follows the same "last year plus change" pattern and needs a matching cash flow line. Three-statement modelling is a common topic in finance interviews; Excel skills for finance jobs lists the others.
Practise it#
Try the exercise on this page: one SUMIF totals cash from operations. Then practise worksheets and cross-sheet references in a live spreadsheet, practise absolute references for multi-year formulas, and build the department budget practice sheet. When you're ready, the finance Excel mock test puts these skills under time pressure.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Total the cash from operating activities. Click E2 and use SUMIF to add the amounts in C2:C6 where the section in A2:A6 is "Operating". Press Enter.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Amount | |||||
| 2 | Cash from operations | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: SUMIF takes the range to check (A2:A6), the word to match in quotes, then the amounts to add (C2:C6).
Practise this in a lesson
Frequently asked questions
What are the three statements in a financial model?
The income statement (profit and loss), the balance sheet and the cash flow statement. The income statement shows profit for a period, the balance sheet shows what the business owns and owes at a date, and the cash flow statement explains how cash changed.
How are the three financial statements linked?
Net income from the income statement starts the cash flow statement and adds to retained earnings on the balance sheet. The cash flow statement's closing cash becomes cash on the balance sheet. Depreciation, capital spending and debt changes also link the statements.
Why doesn't my balance sheet balance?
Usually one item changes the balance sheet without a matching line in the cash flow statement, or a sign is wrong. Check each balance sheet change against the cash flow statement line by line until the check row shows 0.
Should a financial model go on one sheet or several?
Either works, but separate sheets for assumptions and each statement are easier to read and audit. Link them with references such as =IS!B11, and keep all inputs on the assumptions sheet.