How to build your first three-statement forecast from scratch

You can build a profit and loss statement (P&L). Now someone wants a forecast where all three statements connect and actually balance. The leap feels bigger than it is. By the end of this guide you’ll have built a linked income statement, balance sheet, and cash flow forecast that ties out — and, more importantly, still ties out when you change an assumption.

Here’s the one-sentence intuition that makes it all click: every dollar of profit, every asset you buy, and every dollar you borrow eventually shows up as a change in cash, and the balance sheet is just the running tally. If you only know the P&L, you’re missing where the money physically goes. The three statements connect because they’re three views of the same dollars.

All numbers below are illustrative ($ in thousands), kept deliberately small so you can verify every link by hand. One historical year (Year 0) and one forecast year (Year 1).

Set up the workbook first

Before you touch a formula, lay out the grid. One column per period. A clear divider between historicals (hard-coded) and forecast (formulas). A dedicated assumptions block. And a “check” row near the top that screams when the model breaks.

Use a color convention so you never lose track: blue = input you typed, black = formula. Stick to it.

Step 1: Anchor on the most recent actual balance sheet

You can’t forecast forward without a Year 0 that already balances. Prove it before going further.

Year 0 actuals	$
Cash	100
Accounts receivable	120
Inventory	150
Net property, plant & equipment (PP&E)	500
Total assets	870
Accounts payable	90
Debt	200
Common stock	300
Retained earnings	280
Total liabilities + equity	870

Assets 870 = liabilities + equity 870. It balances. This is your starting point.

Step 2: Build assumptions from historical ratios

Use the Year 0 income statement (Revenue 1,000, COGS 600) to derive drivers:

• Accounts receivable (AR) = 12% of revenue (120/1,000)
• Inventory = 25% of cost of goods sold, or COGS (150/600)
• Accounts payable (AP) = 15% of COGS (90/600)

Then set the Year 1 assumptions: revenue growth 10%, COGS 60% of revenue, operating expenses (OpEx) 20% of revenue, capital expenditure (CapEx) 80, depreciation 50, debt interest rate 5%, tax rate 25%, dividends 0.

Step 3: Forecast the income statement down to EBIT

Build revenue, COGS, and OpEx. Leave depreciation and interest as placeholders for now. They come from supporting schedules you haven’t built yet.

Revenue 1,100, COGS 660, gross profit 440, OpEx 220.

Step 4: Build the PP&E / depreciation schedule

This is a corkscrew: opening balance plus additions minus reductions equals closing balance.

Opening net PP&E 500 + CapEx 80 − depreciation 50 = closing net PP&E 530.

Now feed that depreciation (50) back into the income statement. Earnings before interest and taxes (EBIT) = 440 − 220 − 50 = 170.

Step 5: Build the debt schedule, then finish the P&L

Opening debt 200 + draws 0 − repayments 0 = closing 200. Interest = 5% × 200 = 10.

Note: charging interest on the opening balance keeps the model simple and avoids a circular reference. Feed the 10 back into the P&L.

Pre-tax income = 170 − 10 = 160. Tax at 25% = 40. Net income = 120.

Step 6: Forecast the balance sheet, except cash

Drive the working-capital lines from your assumptions, pull PP&E and debt from the schedules, and roll retained earnings forward.

• AR = 12% × 1,100 = 132
• Inventory = 25% × 660 = 165
• AP = 15% × 660 = 99
• Net PP&E = 530 (from corkscrew)
• Debt = 200 (from schedule)
• Common stock = 300
• Retained earnings = 280 + 120 net income − 0 dividends = 400

Leave cash blank. It’s the last thing you fill in.

Step 7: Build the cash flow statement

This is the bridge. Start from net income, add back non-cash items, adjust for working capital, then handle investing and financing.

Cash flow, Year 1	$
Net income	120
Add: depreciation	50
Less: increase in AR (132−120)	−12
Less: increase in inventory (165−150)	−15
Add: increase in AP (99−90)	+9
Cash from operations	152
CapEx	−80
Cash from investing	−80
Debt / equity / dividends	0
Cash from financing	0
Net change in cash	72
Opening cash	100
Ending cash	172

Step 8: Plug ending cash back into the balance sheet

Link the 172 from the cash flow statement into the balance sheet cash line. This is the moment the loop closes. Cash is the one line you pull from the cash flow statement; never type it.

Step 9: Run the balance check

Year 1 balance sheet	$
Cash	172
Accounts receivable	132
Inventory	165
Net PP&E	530
Total assets	999
Accounts payable	99
Debt	200
Common stock	300
Retained earnings	400
Total liabilities + equity	999

999 − 999 = 0. It ties out.

Here’s the punchline. Net income (120) flowed to retained earnings. Depreciation (50) reduced PP&E but got added back in cash flow. Working-capital changes (−12, −15, +9) moved both the balance sheet lines and cash. CapEx (80) raised PP&E and consumed cash. Every number appears in two places, and that’s exactly why it balances.

Step 10 (optional): Add a revolver

In a real model, you don’t want cash to ever go negative. A revolving credit facility (“revolver”) borrows when cash falls below a minimum and repays when there’s surplus. Be warned: this creates a circular reference, because interest feeds net income, which feeds cash, which feeds the revolver, which feeds interest. Handle it by enabling iterative calculation (File > Options > Formulas) or by adding a circularity-breaker switch. Skip this on your first build if you want.

Step 11: Flex it

Change revenue growth from 10% to 15% and watch. All three statements should move, and the check row should still read 0. A model that only balances at one set of inputs isn’t a model.

How to debug a broken balance sheet

When the check row isn’t zero, work through this in order. The amount it’s off by is your best clue.

1. Confirm Year 0 balances. If your anchor was off, you’ll chase a ghost forever.
2. Is cash linked from the cash flow statement, not typed? If it’s hard-coded, the model can’t self-correct.
3. Did you add depreciation back in operations? Subtracting it on the P&L and forgetting the add-back (or vice versa) is the classic error.
4. Check working-capital signs. An increase in an asset (AR, inventory) is a use of cash (negative). An increase in a liability (AP) is a source (positive).
5. Is retained earnings rolled forward? It must equal prior RE + net income − dividends, not a re-keyed number.
6. Check the corkscrews for off-by-one column errors. Make sure each opening balance points to the prior period’s closing balance.
7. Resist the urge to plug. Never force the balance sheet to balance with a hard number. The only legitimate plug is cash (and the revolver). A forced plug just hides the real error.

Common mistakes

• Starting from a Year 0 that doesn’t balance.
• Hard-coding ending cash instead of linking it.
• Double-counting or dropping the depreciation add-back.
• Forecasting retained earnings independently instead of rolling it forward.
• Treating cash as a working-capital driver. Cash and the revolver are derived last.
• Calling it done after it balances once. Test it by flexing an assumption.

Close

Cash is the only line you’re allowed to plug. Every other line is driven.

A three-statement model isn’t harder math than a P&L. It’s bookkeeping discipline: build in the right order, let cash be the plug, and let every other line be driven. Build this small example by hand once, prove it flexes, and you’ll have the mental model for any forecast you’re handed next.