Three-statement model example: a linked forecast in Excel

How the three statements link

Each statement answers a different question. The income statement shows the profit of the year, the balance sheet what the company owns and owes at year end, and the cash flow statement why cash changed. A handful of links tie them together:

• Net income raises retained earnings on the balance sheet and is the first line of the cash flow statement.
• Depreciation is an expense on the income statement, lowers net PP&E on the balance sheet and is added back in the cash flow statement, because no cash leaves.
• Working capital: the change in receivables, inventory and payables between two balance sheets is a line in cash from operations.
• Capex raises PP&E and is a cash outflow in investing.
• Debt repayments and dividends lower debt and retained earnings and are cash outflows in financing.
• Closing cash from the cash flow statement is the cash on the balance sheet.

If every one of these links is in place, the balance sheet balances by itself. That is the whole test of a three-statement model.

Step 1: forecast drivers, not dollars

You do not type next year's receivables or SG&A in dollars. You forecast drivers and let formulas turn them into numbers: revenue growth of 8%, a gross margin of 40%, SG&A at 24% of revenue, a 25% tax rate. Working capital is driven in days: receivables at 45 days of revenue (DSO), inventory at 60 days of cost of goods sold (DIO) and payables at 40 days of cost of goods sold (DPO). Inventory and payables use COGS, not revenue, because both are valued at cost.

In the example every driver sits in column D, on the same row as the line it drives, so you can see at a glance what moves each number. In a full model the drivers come from the company's history, which is exactly what the free first lab, Historicals & Ratios as Forecast Drivers, has you calculate.

Worked example: a one-year three-statement model

The 2025 balance sheet and the drivers in column D are inputs. Every 2026 number is a formula. The company and its numbers are fictional, amounts in $ thousands.

Worked example: a one-year three-statement model

	A	B	C	D
1	Income statement ($k)	2025A	2026F	Driver
2	Revenue (growth)	10,000.0	10,800.0	8.0%
3	Gross profit (gross margin)		4,320.0	40.0%
4	SG&A (% of revenue)		2,592.0	24.0%
5	Depreciation (% of opening PP&E)		300.0	10.0%
6	Interest (rate on opening debt)		150.0	6.0%
7	Profit before tax		1,278.0
8	Income tax (tax rate)		319.5	25.0%
9	Net income		958.5
10	Balance sheet ($k)	2025A	2026F	Driver
11	Cash (from cash flow)	800.0	821.9
12	Receivables (DSO, days)	1,200.0	1,331.5	45
13	Inventory (DIO, days)	1,000.0	1,065.2	60
14	Net PP&E (capex)	3,000.0	3,150.0	450.0
15	Total assets	6,000.0	6,368.6
16	Payables (DPO, days)	700.0	710.1	40
17	Debt (repayment)	2,500.0	2,300.0	200.0
18	Share capital	1,000.0	1,000.0
19	Retained earnings (dividends)	1,800.0	2,358.5	400.0
20	Total liabilities & equity	6,000.0	6,368.6
21	Balance check (must be 0)	0.0	0.0
22	Cash flow statement ($k)		2026F
23	Net income		958.5
24	+ Depreciation		300.0
25	Change in receivables		-131.5
26	Change in inventory		-65.2
27	Change in payables		10.1
28	Cash from operations		1,071.9
29	Cash from investing (capex)		-450.0
30	Cash from financing (repayment, dividends)		-600.0
31	Closing cash (opening + flows)		821.9
32	Cash tie-out: cash flow − balance sheet		0.0

Simplified on purpose: no minimum cash balance or revolver, no interest on cash and a flat tax rate.

Step 2: the income statement

Revenue grows from $10,000k to $10,800k. A 40% gross margin gives gross profit of $4,320k, and SG&A at 24% is $2,592k. Depreciation is 10% of opening net PP&E: $300k. Interest is 6% of the opening debt of $2,500k: $150k. Using the opening balance avoids a circular reference, because the closing debt depends on cash, and cash depends on interest. The debt and interest schedule lab shows this in detail.

Profit before tax is $1,278k. Tax uses =MAX(0,C7*D8), so a loss year shows no tax instead of a negative one: $319.5k. Net income is $958.5k.

Step 3: the balance sheet and the balance check

Working capital comes from the days: receivables = $10,800k ÷ 365 × 45 = $1,331.5k, inventory = COGS of $6,480k ÷ 365 × 60 = $1,065.2k, payables = $6,480k ÷ 365 × 40 = $710.1k. Net PP&E rolls forward: $3,000k opening + $450k capex − $300k depreciation = $3,150k. Debt falls by the $200k repayment to $2,300k.

Retained earnings roll forward too: closing = opening + net income − dividends, so $1,800k + $958.5k − $400k = $2,358.5k. Cash is the one line you do not calculate on the balance sheet. It is linked from the cash flow statement.

Total assets and total liabilities & equity are both $6,368.6k, and the balance check, total assets minus total liabilities & equity, is 0. Never force it to zero by plugging the difference into cash or equity. A plug hides the error instead of fixing it.

Step 4: the cash flow statement and the cash tie-out

The indirect method starts with net income and corrects it to cash. Add back depreciation of $300k. Then the working capital lines: an increase in an asset uses cash, so it is prior minus current (receivables −$131.5k, inventory −$65.2k). An increase in a liability is a source of cash, so it is current minus prior (payables +$10.1k). Cash from operations is $1,071.9k.

Capex is −$450k in investing. The $200k repayment and the $400k dividend make −$600k in financing. Cash rises by $21.9k, from $800k to $821.9k, and that number is the cash on the balance sheet. The tie-out row confirms it: closing cash on the cash flow statement minus cash on the balance sheet = 0.

When the balance check is not zero

Every balance sheet line except cash must appear in the cash flow statement, either as its own line or through net income. If the check shows a difference, look at its size. A difference that equals one line, for example the dividends, means that line is missing in one place. A difference of exactly twice a line usually means a sign is wrong. Check the first forecast year first: an error there carries into every later year and makes the later differences confusing.

From this example to a full model

A real three-statement model forecasts five years, copies the same formulas across, and moves the working capital, PP&E and debt calculations into their own schedules. A scenario switch for base, upside and downside cases is the next step. Once the statements balance, the cash flow statement gives you free cash flow, which is the input to a valuation: see how to build a DCF model in Excel.

Common mistakes

• Working capital signs reversed. More receivables or inventory uses cash (prior − current), more payables brings cash in (current − prior).
• Plugging the balance sheet. Forcing cash or equity to make the check zero hides the broken link. Find it and fix it.
• Interest on closing debt. It creates a circular reference. Use opening debt, or an average with iterative calculation switched on deliberately.
• Forgetting dividends. They lower retained earnings and are a financing outflow. Missing them in one place unbalances the model by exactly that amount.
• Capex in the wrong place. Capex is an investing outflow that raises PP&E. It never goes through the income statement, only its depreciation does.
• Typing numbers into forecast cells. A hard-coded value does not move when a driver changes, and the model stops balancing in another scenario.