Three-statement model template

How the three statements link

Seven links hold the model together. Each one below has what it did in year 1 of the example.

1. Net income

   The last line of the income statement is the first line of the cash flow statement. It is also added to retained earnings on the balance sheet.

   Year 1: $35.4m. Retained earnings go from $105.0m to $140.4m.

2. Depreciation

   An expense on the income statement that uses no cash. The cash flow statement adds it back, and net PP&E falls by the same amount.

   Year 1: $20.0m, which is 10% of opening PP&E of $200.0m.

3. Working capital

   When receivables or inventory grow, cash is tied up. When payables grow, the company holds on to cash longer. Each change on the balance sheet shows up in cash from operations.

   Year 1: Receivables up $3.3m, inventory up $2.6m, payables up $3.4m. Together they use $2.5m of cash.

4. Capex

   Cash out in investing, added to PP&E. It reaches the income statement only later, through depreciation.

   Year 1: $21.6m. PP&E goes 200.0 + 21.6 − 20.0 = 201.6.

5. Debt and interest

   Interest on the debt is an expense on the income statement. Repaying principal is cash out in financing and lowers the debt. It never touches the income statement.

   Year 1: $10.5m of interest, 7% of $150.0m. $15.0m repaid, so debt ends at $135.0m.

6. Cash

   Ending cash on the cash flow statement becomes the cash line on the balance sheet. It has no driver of its own.

   Year 1: $40.0m + $16.4m = $56.4m.

7. The balance check

   Total assets less total liabilities and equity. It has to be zero every year. If it isn't, a link is missing or has the wrong sign.

   Year 1: $353.9m of assets, $353.9m of liabilities and equity. Zero.

Build it in this order

Six steps, the same on this page and in the spreadsheet.

1. 1. Set the drivers

   Growth, margins, capex %, days, interest rate, repayment

   Every forecast line hangs off a driver. In the spreadsheet each year has its own row, so you can type over a single year.

2. 2. Income statement

   Net income = (EBITDA − D&A − interest) × (1 − tax)

   Depreciation comes from last year's PP&E and interest from last year's debt. Using opening balances keeps the model free of circular references.

3. 3. Working capital

   Receivables = revenue × days ÷ 365

   Inventory and payables use cost of goods sold instead of revenue. These three balances go straight onto the balance sheet.

4. 4. Cash flow statement

   Cash = opening cash + operations − capex − debt repaid

   Start from net income, add back depreciation, then take the change in each working capital line from the balance sheet.

5. 5. Balance sheet

   PP&E = opening + capex − D&A, equity = opening + net income

   Roll each balance forward from last year. Cash is the ending cash from the step before, never a number of its own.

6. 6. Check

   Assets − liabilities − equity = 0

   Put the check under the balance sheet for every year, so a broken link shows up the moment you make it.

How it comes up in interviews

Interviewers ask about the links, not the spreadsheet. These are the questions, with the short version of a strong answer.

• How do the three financial statements link together?

  Start at net income and follow it: into retained earnings, then to the top of the cash flow statement, then adjust for non-cash items and working capital until you reach the change in cash, which lands on the balance sheet. The table above is that answer with numbers.

• Depreciation goes up by $10. Walk me through the statements.

  At 25% tax, net income falls $7.50. Add back the $10 and cash is up $2.50, the tax saved. PP&E falls $10, so assets are down $7.50 and retained earnings fall by the same $7.50. It balances.

• A company raises $100 of debt to buy equipment. What changes?

  Nothing on the income statement at the time of purchase. Financing brings in $100 and investing spends it, so cash is flat. PP&E and debt both rise by $100. Interest and depreciation arrive in the years after.

• How can a profitable company run out of cash?

  Growth plus slower collections ties up cash in receivables and inventory before customers pay. Set growth to 25% and receivable days to 90 in the model above: year 1 net income is $44.6m, yet cash ends the year $10.1m below zero.

More questions like these, each with a worked answer: accounting interview questions.

Common modeling mistakes

Most broken models break in one of these six places. The gap they leave tells you which one.