Skip to main content

How do you build an integrated three-statement model from scratch?

Understanding the three statements in isolation is one thing; seeing them work together in a dynamic model is another. When you build a three-statement model—even a simple "toy" version—you suddenly see that revenue isn't just a number on the income statement. It drives cash collections, which affect the balance sheet. It requires inventory, which ties up working capital. It changes the effective tax rate. One change ripples everywhere. Building a model forces you to internalize the connections and reveals which line items actually matter. This article walks through building a real (but simple) three-statement model step by step, so you can understand the mechanics before tackling real company financials.

Quick definition​

A three-statement integrated financial model is a dynamic spreadsheet (or calculation system) where the income statement, balance sheet, and cash flow statement are linked by formulas. A change to one input—say, a sales forecast—automatically updates net income, which flows to retained earnings, which updates equity, which must equal assets minus liabilities, which then requires the balance sheet to reconcile. The model is "integrated" because the three statements are not independent; they are a single system.

Key takeaways​

  • A toy model is best built in a spreadsheet with clear sections for inputs, the P&L, the balance sheet, and the cash flow statement.
  • Revenue is the master driver: it feeds the P&L, drives working capital, and ultimately determines cash from operations.
  • Net income flows to retained earnings, tying the P&L to the balance sheet.
  • Changes in balance-sheet items are the source of the working-capital adjustments on the cash flow statement.
  • The balance sheet must balance: assets = liabilities + equity. If it doesn't, the model is broken, and you must debug.

The big picture: what we're building​

Imagine a simple business:

  • A retailer sells widgets.
  • It buys inventory on credit, sells at a markup, and collects cash slowly.
  • It has some debt and some equipment.
  • We want to project the next three years and see cash, earnings, and the balance sheet evolve.

We'll build three exhibits:

  1. Income Statement (P&L): Revenue, cost of goods sold (COGS), gross profit, operating expenses, EBIT, interest expense, taxes, net income.
  2. Cash Flow Statement: Cash from operations (starting with net income, adding back depreciation, adjusting for working capital), capex, and financing activities.
  3. Balance Sheet: Assets (cash, receivables, inventory, equipment), liabilities (payables, debt), and equity (stock, retained earnings).

Each of these will be linked by formulas. Let's build the model section by section.

Step 1: Set up the input sheet​

Before we start calculations, we need to know our assumptions. Create a simple input section at the top of your model:

Year 1 inputs:

  • Revenue: 10,000 (in thousands, say)
  • Revenue growth rate: 10 percent per year
  • COGS as percent of revenue: 60 percent
  • Operating expenses (fixed): 1,500
  • Tax rate: 25 percent
  • Capex (annual): 200
  • Depreciation rate (on prior year PP&E): 10 percent
  • Days sales outstanding (DSO, or collection days): 30 days
  • Days inventory outstanding (DIO): 45 days
  • Days payable outstanding (DPO): 30 days
  • Starting cash: 1,000
  • Starting equipment (gross): 5,000
  • Accumulated depreciation (starting): 2,000
  • Starting debt: 2,000
  • Interest rate on debt: 5 percent

These inputs are your levers. In a real model, you'll sensitivity-test these. For now, they're fixed.

Step 2: Build the income statement​

The P&L is straightforward:

Year 1     Year 2     Year 3
Revenue                    10,000    11,000    12,100
COGS (60% of revenue)       (6,000)   (6,600)   (7,260)
Gross profit                4,000     4,400     4,840
Operating expenses        (1,500)   (1,500)   (1,500)
EBIT (operating income)     2,500     2,900     3,340
Interest expense (5% of debt) (100)   (110)     (121)
EBT (pre-tax income)        2,400     2,790     3,219
Tax (25%)                     (600)     (698)     (805)
Net income                  1,800     2,092     2,414

Key formula: Interest expense depends on the beginning-of-year debt balance (or a bit more complex if debt changes mid-year). For simplicity, use: Interest = Debt at start of year × Interest rate.

Step 3: Build the balance sheet​

The balance sheet must satisfy: Assets = Liabilities + Equity.

Assets:

  • Cash: Will be calculated from the cash flow statement (we'll get to that).
  • Accounts receivable: Revenue ÷ 365 days × DSO. If revenue is 10,000 and DSO is 30, then A/R = 10,000 ÷ 365 × 30 = ~822.
  • Inventory: COGS ÷ 365 × DIO. If COGS is 6,000 and DIO is 45, then Inventory = 6,000 ÷ 365 × 45 = ~739.
  • PP&E (net): Starting equipment minus accumulated depreciation plus capex minus depreciation this year.

Liabilities & Equity:

  • Accounts payable: COGS ÷ 365 × DPO. If COGS is 6,000 and DPO is 30, then A/P = 6,000 ÷ 365 × 30 = ~493.
  • Debt: We'll assume it stays constant at 2,000 unless we model a debt change.
  • Retained earnings: Starting balance + net income from the P&L.
  • Common stock: Assume constant (we're not issuing or buying back shares).

A simplified Year 1 balance sheet:

ASSETS
Cash                       1,000 (will be calculated in cash flow)
Accounts receivable          822 (10,000 ÷ 365 × 30)
Inventory                    739 (6,000 ÷ 365 × 45)
PP&E (gross)               5,200 (5,000 + 200 capex)
Accumulated depreciation     (700) (2,000 + 70 depreciation)
PP&E (net)                 4,500
Total assets               7,061

LIABILITIES
Accounts payable             493 (6,000 ÷ 365 × 30)
Debt                       2,000
Total liabilities          2,493

EQUITY
Common stock               2,000
Retained earnings          2,568 (starting 768 + 1,800 net income)
Total equity               4,568
Total liabilities + equity 7,061

Key insight: The balance sheet balances only if we've linked it correctly. If cash doesn't plug (if assets ≠ liabilities + equity), we've made a mistake or we need to calculate cash as a plug.

Step 4: Build the cash flow statement and reconcile to cash​

The cash flow statement bridges net income to the change in cash:

Operating cash flow:

  • Start with net income: 1,800
  • Add back depreciation (non-cash): ~70
  • Adjust for changes in working capital:
    • Decrease in A/R releases cash (if A/R goes down, we collected cash). If A/R was 1,000 last year and 822 this year, that's +178 cash.
    • Increase in inventory uses cash (if inventory goes up, we bought more goods). If inventory was 600 last year and 739 this year, that's -139 cash.
    • Increase in A/P uses less cash (we pay more slowly). If A/P was 400 last year and 493 this year, that's +93 cash.
  • Operating cash flow = 1,800 + 70 + 178 - 139 + 93 = ~2,002

Investing cash flow:

  • Capex (cash paid for equipment): -200

Financing cash flow:

  • Debt issuance/repayment: 0 (assuming we hold debt constant)
  • Dividends paid: Let's assume none for simplicity, or (say) 500 if we want to model a payout.

Net change in cash: 2,002 - 200 = ~1,802 (assuming no dividend)

Ending cash: Starting cash 1,000 + net change 1,802 = 2,802

This is the cash figure that goes on the balance sheet. If your balance sheet doesn't balance, reconcile here.

Here's where the magic happens. Your spreadsheet now has:

  1. Input cells (top of the sheet): Revenue growth, COGS percent, DSO, DIO, DPO, tax rate, interest rate, etc.
  2. P&L section: Formulas that reference the inputs and calculate revenue, COGS, EBIT, interest, net income.
  3. Balance sheet section: Formulas for A/R, inventory, PP&E, A/P, etc., that reference the P&L. Retained earnings reference the P&L net income.
  4. Cash flow section: Formulas that calculate changes in working capital by comparing balance-sheet items year-over-year, then compute operating, investing, and financing cash flows.
  5. Cash plug: Ending cash = starting cash + net change in cash from the cash flow statement.
  6. Balance sheet check: Total assets should equal total liabilities + equity. If not, debug.

An integrated example: Year 1 to Year 2​

Let's project Year 2 using the same assumptions:

Year 2 inputs:

  • Revenue grows 10 percent: 10,000 × 1.1 = 11,000
  • COGS: 11,000 × 60% = 6,600
  • EBIT: (11,000 - 6,600 - 1,500) = 2,900
  • Debt is still 2,000 (we're not modifying it)
  • Interest: 2,000 × 5% = 100 (no change, because debt didn't change)
  • Wait—let's recalculate: Interest should be 5 percent of the beginning of Year 2 debt, which is the end of Year 1 debt. If we track debt properly, it's 2,000. Assume it stays at 2,000. So interest stays at 100. (If we modeled debt repayment, it would change.)

Actually, let's say we don't repay debt, so debt stays 2,000.

  • Tax: (2,900 - 100) × 25% = 700
  • Net income: 2,900 - 100 - 700 = 2,100

Wait, I realize I made a mistake in my Year 1 P&L. Let me recalculate more carefully:

Year 1 (recalculated):

  • Revenue: 10,000
  • COGS: 6,000
  • Gross profit: 4,000
  • OpEx: 1,500
  • EBIT: 2,500
  • Interest (5% × 2,000): 100
  • EBT: 2,400
  • Tax (25%): 600
  • Net income: 1,800

Year 2:

  • Revenue: 11,000
  • COGS: 6,600
  • Gross profit: 4,400
  • OpEx: 1,500
  • EBIT: 2,900
  • Interest (5% × 2,000): 100 (assuming no debt paydown in Year 1)
  • EBT: 2,800
  • Tax (25%): 700
  • Net income: 2,100

Year 2 Balance sheet:

  • A/R: 11,000 ÷ 365 × 30 = 904
  • Inventory: 6,600 ÷ 365 × 45 = 815
  • PP&E: Prior year net + capex - depreciation = 4,500 + 200 - (5,200 - 70) × 10% = 4,500 + 200 - 510 = 4,190

(Depreciation is 10 percent of gross PP&E at start of year: (5,000 + 200) × 10% = 520. Accumulated depreciation grows to 700 + 520 = 1,220. Net PP&E: 5,200 - 1,220 = 3,980.)

Let me recalculate once more, carefully:

Year 2 ending PP&E:

  • Gross PP&E at start of Year 2: 5,000 + 200 capex in Year 1 = 5,200
  • Depreciation in Year 2: 5,200 × 10% = 520
  • Accumulated depreciation at end of Year 2: 2,000 + 70 (Year 1) + 520 (Year 2) = 2,590
  • Capex in Year 2: 200 (new)
  • Gross PP&E at end of Year 2: 5,200 + 200 = 5,400
  • Net PP&E: 5,400 - 2,590 = 2,810

Actually, we need to be clearer. Let's use a simpler approach:

  • Starting net PP&E: 3,000 (which is 5,000 gross - 2,000 accumulated depreciation)
  • Add capex: 200
  • Subtract depreciation: 300
  • Ending net PP&E: 2,900

Year 2 working capital:

  • Change in A/R: 904 - 822 = +82 (uses cash)
  • Change in inventory: 815 - 739 = +76 (uses cash)
  • Change in A/P: 493 (let's assume it stays constant for simplicity, or 500)

Year 2 operating cash flow:

  • Net income: 2,100
  • Add depreciation: 300
  • Less change in A/R: -82
  • Less change in inventory: -76
  • Plus change in A/P: 0 (or small)
  • Cash from operations: ~2,242

Year 2 investing cash flow:

  • Capex: -200

Year 2 financing cash flow:

  • Dividend (assume zero)

Net change in cash: 2,242 - 200 = 2,042 Ending Year 2 cash: (depends on Year 1 ending cash)

If Year 1 ended with 2,802 cash (from earlier), then Year 2 ends with 2,802 + 2,042 = 4,844.

The critical step is that all three statements now move together. If you change the revenue growth assumption from 10 percent to 15 percent, the model automatically updates the P&L, working capital, cash flow, and balance sheet.

Mermaid: three-statement model dataflow​

Real-world example: fast-growing SaaS company​

A SaaS company with 1 million in annual recurring revenue (ARR) and 80 percent year-over-year growth looks great on the P&L. But in the cash flow statement, the model reveals that deferred revenue (a liability representing pre-collected cash) is growing as fast as revenue, which means cash from operations is not as strong as earnings suggest. Meanwhile, capex for servers and infrastructure is eating into free cash flow. And on the balance sheet, the company's burn of equity (because it's investing for growth) means the equity base is shrinking. The three-statement model makes these dynamics visible.

Common challenges and how to solve them​

Challenge 1: The balance sheet doesn't balance.

  • Check that retained earnings includes the current year net income.
  • Verify that depreciation is correctly subtracted from gross PP&E.
  • Ensure that debt and other liabilities are correctly carried forward.
  • If all else fails, use cash as a plug: set the cash line equal to (total liabilities + equity - all other assets). This forces the balance sheet to balance, and if the plug is negative, you've run out of cash.

Challenge 2: Interest expense changes, but you forgot to update it.

  • Interest should be a formula: Interest = Debt × Rate. If you model debt paydown, interest will automatically decrease.
  • If you hard-code an interest amount, it won't update if debt changes.

Challenge 3: Working capital changes are backward or missing.

  • A working capital adjustment is a change in a balance-sheet line item from one year to the next.
  • Increase in A/R = more cash tied up = negative impact on cash flow.
  • Increase in A/P = paying suppliers later = positive impact on cash flow.
  • Use: Change = Ending balance - Beginning balance.

Challenge 4: Depreciation is double-counted or missing.

  • On the P&L, depreciation is an expense that reduces EBIT.
  • On the cash flow statement, depreciation is a non-cash item that is added back to net income.
  • On the balance sheet, accumulated depreciation reduces the net value of PP&E.
  • All three need to be consistent.

FAQ​

What if the company has multiple business segments?​

You can build a simplified single-segment model first, then expand to multiple segments. Each segment has its own revenue, COGS, and often its own working capital profiles. The segments roll up to a consolidated model.

How do I model debt paydown?​

If you assume the company pays down debt each year by a fixed amount (or a formula based on free cash flow), then the debt line becomes dynamic. Debt starts at 2,000; if the company pays down 100 per year, then Year 2 debt is 1,900, Year 3 is 1,800, etc. Interest then decreases automatically as debt shrinks.

What about taxes and deferred taxes?​

In a simple model, assume taxes are a fixed percent of pre-tax income. In a more sophisticated model, you'd track deferred tax assets and liabilities, and model a schedule of when they reverse. For now, keep it simple.

How do I handle shares outstanding and EPS?​

In your model, include a line for shares outstanding (assume constant unless you model a buyback or issuance). EPS = net income ÷ shares outstanding. If you're modeling a buyback funded by debt, adjust debt up and shares down.

Can I use this model for valuation?​

Yes. Once you've built a three-statement model, you can project free cash flow (operating cash flow - capex) out 5-10 years, discount it back at a cost of capital, and estimate enterprise value. This is the basis of a discounted cash flow (DCF) valuation.

  • Free cash flow (FCF): Operating cash flow minus capex. The cash available to equity holders and debt holders.
  • Cash conversion cycle: The sum of days inventory outstanding (DIO), days sales outstanding (DSO), minus days payable outstanding (DPO). It tells you how long cash is tied up in working capital.
  • Terminal value: In a DCF, the value of the company at the end of the projection period, usually calculated as a perpetuity.
  • Sensitivity analysis: Testing how the model output (e.g., valuation) changes when you vary inputs (e.g., growth rate, margin).

Summary​

Building a toy three-statement model is the fastest way to understand how the income statement, balance sheet, and cash flow statement interact. Start with inputs (revenue growth, margins, working capital terms), build a P&L, then a balance sheet (making sure it balances), then a cash flow statement. Link them with formulas so that a change in one input cascades through all three. The model should reconcile: ending cash from the cash flow statement should match the cash on the balance sheet, and assets should equal liabilities plus equity. Once you've built this simple model, you've developed an intuition that will serve you well when reading real company financials.

Next​

Continue to Circular references when modelling interest expense, where we tackle one of the trickier challenges in integrated modeling: the circular reference between interest expense and debt level.