Link the Balance Sheet and Cash Flow Statement
This is the lesson where the model comes alive. You will forecast every balance sheet line except cash, build the cash flow statement from those changes, and then send closing cash back to the balance sheet. When you finish, the check row should show zero for all three years.
Build in the order below. The order matters, because cash has to be calculated last.
What You'll Learn
- How to forecast receivables, inventory, payables, equipment, debt, and equity
- How to build a cash flow statement with the indirect method
- The sign rules for working capital that trip up most beginners
- How closing cash closes the loop and balances the model
Step 1: forecast the balance sheet (except cash)
Each line follows one simple rule.
| Cell | Line | Formula | Rule |
|---|---|---|---|
| C31 | Accounts receivable | =C17*C12 | Revenue times receivables % |
| C32 | Inventory | =C18*C13 | COGS times inventory % |
| C33 | PP&E (net) | =B33+C7-C6 | Last year, plus capex, minus depreciation |
| C35 | Accounts payable | =C18*C14 | COGS times payables % |
| C36 | Debt | =B36-C9 | Last year, minus repayment |
| C37 | Equity | =B37+C27-C11 | Last year, plus net income, minus dividends |
Three of these are "roll-forwards": last year's balance, plus what came in, minus what went out. PP&E, debt, and equity all work this way. It is the most important pattern in modeling.
Look at C37. This is where the income statement meets the balance sheet. Profit the company keeps (net income minus dividends) adds to equity.
Leave C30 (cash) empty for now.
Step 2: build the cash flow statement
You will use the indirect method, which most companies use. It starts with net income and adjusts it to cash.
Ask AI for the formulas first, then compare.
Your formulas should match these:
| Cell | Line | Formula |
|---|---|---|
| C42 | Net income | =C27 |
| C43 | Add back depreciation | =C22 |
| C44 | Change in receivables | =-(C31-B31) |
| C45 | Change in inventory | =-(C32-B32) |
| C46 | Change in payables | =C35-B35 |
| C47 | Cash from operations | =SUM(C42:C46) |
| C48 | Capex | =-C7 |
| C49 | Cash from investing | =C48 |
| C50 | Debt repayment | =-C9 |
| C51 | Dividends | =-C11 |
| C52 | Cash from financing | =C50+C51 |
| C53 | Net change in cash | =C47+C49+C52 |
| C54 | Opening cash | =B30 |
| C55 | Closing cash | =C54+C53 |
The sign rules (memorize these)
Working capital signs cause more broken models than anything else. Here is the logic.
Assets and cash move in opposite directions. Liabilities and cash move together.
| Criteria | Asset goes up | Liability goes up |
|---|---|---|
| Example | Receivables rise from 100 to 110 | Payables rise from 60 to 66 |
| What happened | Customers owe you more, so cash is stuck with them | You owe suppliers more, so you kept their cash for now |
| Effect on cash | Cash goes down (minus 10) | Cash goes up (plus 6) |
| Formula pattern | =-(this year - last year) | =this year - last year |
Asset goes up
- Example
- Receivables rise from 100 to 110
- What happened
- Customers owe you more, so cash is stuck with them
- Effect on cash
- Cash goes down (minus 10)
- Formula pattern
- =-(this year - last year)
Liability goes up
- Example
- Payables rise from 60 to 66
- What happened
- You owe suppliers more, so you kept their cash for now
- Effect on cash
- Cash goes up (plus 6)
- Formula pattern
- =this year - last year
A quick way to remember it: if an asset grows, you spent cash to grow it. If a liability grows, someone else is funding you.
Step 3: close the loop
Now go back to the balance sheet and fill in the last three formulas.
| Cell | Line | Formula |
|---|---|---|
| C30 | Cash | =C55 |
| C34 | Total assets | =SUM(C30:C33) |
| C38 | Total liabilities and equity | =SUM(C35:C37) |
| C39 | Balance check | =ROUND(C34-C38,2) |
C30 pulls closing cash from the cash flow statement. That is the final link. Select C30:C55 and fill right to column E. Leave the cash flow cells in column B (B42 to B55) empty. Year 0 has no prior year to compare against.
The ROUND in the check row stops tiny decimal leftovers like 0.0000001 from showing as an error.
Check your numbers
| Line | Year 1 | Year 2 | Year 3 |
|---|---|---|---|
| Cash from operations | 119.4 | 133.8 | 148.2 |
| Cash from investing | -60 | -60 | -60 |
| Cash from financing | -70 | -70 | -70 |
| Net change in cash | -10.6 | 3.8 | 18.2 |
| Closing cash | 89.4 | 93.2 | 111.4 |
| Total assets | 708.4 | 741.2 | 788.4 |
| Total liabilities and equity | 708.4 | 741.2 | 788.4 |
| Balance check | 0 | 0 | 0 |
Your income statement interest for Years 2 and 3 should now show 9 and 6, and net income should match the table from the last lesson.
If every check cell shows 0, congratulations. You have a working, fully linked 3-statement model. If not, do not worry. The next lesson is all about finding and fixing the break.
Ask AI to walk through the cash story
This is the kind of explanation a manager or exam question will ask you for. Being able to tell the cash story is worth as much as building the model.
Key Takeaways
- Build in order: balance sheet without cash, then cash flow, then cash and the check row.
- PP&E, debt, and equity are roll-forwards: last year, plus inflows, minus outflows.
- Equity (
=B37+C27-C11) links the income statement to the balance sheet. - Asset up means cash down. Liability up means cash up.
- Closing cash (
=C55) goes back to the balance sheet. A zero check row means the model is linked correctly.