A 3-statement model links the income statement, balance sheet, and cash flow statement into a single Excel workbook, so a change in one assumption (say, revenue growth) automatically flows through profitability, the balance sheet, and cash. It is the foundation underneath almost every valuation, budgeting, or forecasting exercise in finance.
This guide walks through the entire build, in order, with the actual formulas used at each step. By the end, you will have a working model with a balance check that ties to zero.
On this page
- What you need before starting
- How the three statements connect
- Step 1: Build the assumptions tab
- Step 2: Build the income statement
- Step 3: Build the balance sheet
- Step 4: Build the cash flow statement
- Step 5: Link the three statements
- Step 6: Add the balance check
- Step 7: Stress test the model
- Common mistakes
- FAQ
What You Need Before Starting
- Excel (desktop or Microsoft 365). Google Sheets works too, but the formula examples below use Excel syntax.
- Basic comfort with relative and absolute cell references (the
$symbol), SUM, and IF. - A historical year of financials, real or assumed, to anchor your first forecast period.
- About two to four hours of focused time for a first pass.
A standalone income statement can tell you if a company is profitable. It cannot tell you if the company will run out of cash while growing. Only a linked balance sheet and cash flow statement reveal that, which is why every professional financial model is built this way.
How the Three Statements Connect
Before building anything, it helps to see the full loop. Net income flows into retained earnings on the balance sheet and starts the cash flow statement. Working capital and investing activity move cash and balance sheet items together. Ending cash closes the loop back onto the balance sheet.
Step 1: Build the Assumptions Tab
Assumptions and drivers
Put every input in one place, never buried inside a formula on another tab. This is what makes the model easy to update and audit later.
| Driver | Example value | Used in |
|---|---|---|
| Revenue growth rate | 8% per year | Income statement |
| Gross margin | 45% | Income statement |
| Operating expenses (% of revenue) | 25% | Income statement |
| Tax rate | 21% | Income statement |
| Days sales outstanding (AR) | 45 days | Balance sheet, cash flow |
| Days inventory outstanding | 60 days | Balance sheet, cash flow |
| Days payable outstanding (AP) | 30 days | Balance sheet, cash flow |
| Capital expenditure (% of revenue) | 4% | Balance sheet, cash flow |
| Depreciation | Straight-line over 10 years | Income statement, balance sheet, cash flow |
| Interest rate on debt | 6% | Income statement |
Format every assumption cell with a distinct fill color (light blue is a common convention) so anyone opening the model can instantly tell inputs from calculations.
Step 2: Build the Income Statement
Revenue through net income
Each line pulls from the assumptions tab rather than hardcoding a number. That is what lets you change one input and watch the whole model update.
| Line item | Formula pattern | Notes |
|---|---|---|
| Revenue | =Prior_Year_Revenue*(1+Growth_Rate) | First forecast year references the last historical year |
| Cost of goods sold | =-Revenue*(1-Gross_Margin) | Kept negative to sum cleanly |
| Gross profit | =Revenue+COGS | Should equal Revenue times Gross Margin |
| Operating expenses | =-Revenue*Opex_Percent | Kept negative |
| EBIT | =Gross_Profit+Operating_Expenses | Earnings before interest and taxes |
| Interest expense | =-Beginning_Debt*Interest_Rate | Uses beginning debt balance to avoid circularity |
| Earnings before tax | =EBIT+Interest_Expense | |
| Taxes | =-MAX(EBT,0)*Tax_Rate | MAX prevents a tax benefit on a loss in a simple model |
| Net income | =EBT+Taxes | Flows to both the balance sheet and cash flow statement |
Step 3: Build the Balance Sheet
Assets, liabilities, and equity
Build assets first, then liabilities and equity. Every line should reference either the income statement, the assumptions tab, or the cash flow statement, never a typed-in number.
| Line item | Formula pattern | Notes |
|---|---|---|
| Cash | =Prior_Cash+Net_Change_in_Cash | Net_Change_in_Cash comes from the cash flow statement |
| Accounts receivable | =Revenue*(DSO/365) | Higher DSO means more cash tied up in receivables |
| Inventory | =-COGS*(DIO/365) | Negative sign on COGS cancels out |
| PP&E, net | =Prior_PPE+CapEx-Depreciation | CapEx increases it, depreciation reduces it |
| Total assets | =Cash+AR+Inventory+PPE | Add any other asset lines you include |
| Accounts payable | =-COGS*(DPO/365) | |
| Debt | =Prior_Debt+Draws-Repayments | Set draws and repayments as their own assumption rows |
| Total liabilities | =AP+Debt | |
| Common stock | =Prior_Common_Stock | Held flat unless modeling an equity raise |
| Retained earnings | =Prior_RE+Net_Income-Dividends | The main link back to the income statement |
| Total equity | =Common_Stock+Retained_Earnings |
Step 4: Build the Cash Flow Statement
Operating, investing, and financing activity
The cash flow statement starts with net income and reconciles it to the actual change in cash by adjusting for non-cash items and balance sheet movements.
| Line item | Formula pattern | Notes |
|---|---|---|
| Net income | =Income_Statement!Net_Income | Starting point |
| Add back depreciation | =Depreciation | Non-cash expense, added back |
| Change in accounts receivable | =-(AR_Current-AR_Prior) | An increase in AR uses cash, hence the negative sign |
| Change in inventory | =-(Inventory_Current-Inventory_Prior) | Same logic as AR |
| Change in accounts payable | =AP_Current-AP_Prior | An increase in AP frees up cash |
| Cash from operations | =SUM(above rows) | |
| Capital expenditure | =-CapEx | Cash outflow, matches the balance sheet PP&E build |
| Cash from investing | =CapEx_row | Add any other investing items here |
| Debt draws | =Debt_Draws | Matches the balance sheet debt roll-forward |
| Debt repayments | =-Debt_Repayments | |
| Dividends paid | =-Dividends | Matches the retained earnings roll-forward |
| Cash from financing | =SUM(above rows) | |
| Net change in cash | =CFO+CFI+CFF | Flows to the balance sheet cash line |
| Ending cash | =Beginning_Cash+Net_Change_in_Cash | Should match the balance sheet cash line exactly |
Step 5: Link the Three Statements
Close the loop
- Point the balance sheet cash line at the cash flow statement's net change in cash, not a typed number.
- Point the balance sheet retained earnings line at the income statement's net income, minus any dividends assumption.
- Point the cash flow statement's depreciation, working capital changes, and CapEx at the same cells the balance sheet uses, so both statements always move together.
- Confirm the income statement's interest expense uses beginning-of-period debt (not ending), which avoids a circular reference between debt, interest, cash, and the balance sheet.
Step 6: Add the Balance Check
Confirm the model actually balances
| Line item | Formula pattern | What it should show |
|---|---|---|
| Balance check | =Total_Assets-(Total_Liabilities+Total_Equity) | Zero, in every single period |
Add conditional formatting so this cell turns red the moment it is anything other than zero. This one row will catch almost every linking error you make.
Step 7: Stress Test the Model
Flex the assumptions
Change the revenue growth rate from 8% to negative 5%, then to 20%. Change the tax rate. Change days sales outstanding. If the balance check stays at zero through every change, the model is genuinely linked, not just coincidentally balanced in the base case.
Hardcoding a plug in the balance sheet. If the model does not balance, do not force it to balance with a manual adjustment. Find the actual broken link.
Using ending debt for interest expense without enabling iterative calculations. This creates a genuine circular reference. Either use beginning-of-period debt, or turn on iterative calculation under Excel's formula options.
Forgetting to add back depreciation in the cash flow statement. It is an expense on the income statement but not an actual cash outflow.
Mixing up signs on working capital changes. An increase in a current asset like AR uses cash (negative); an increase in a current liability like AP frees up cash (positive).
Typing a number directly into a formula cell. Every input should live on the assumptions tab, not buried inside a calculation.
Quick Reference: What Feeds What
| Item | Appears on | Sourced from |
|---|---|---|
| Net income | Balance sheet (retained earnings), cash flow statement | Income statement |
| Depreciation | Income statement (embedded in opex or COGS), cash flow statement, balance sheet (PP&E) | Assumptions tab |
| Capital expenditure | Balance sheet (PP&E), cash flow statement (investing) | Assumptions tab |
| Change in receivables, inventory, payables | Cash flow statement (operating) | Balance sheet, driven by income statement and days assumptions |
| Debt draws and repayments | Balance sheet (debt), cash flow statement (financing) | Assumptions tab or a separate debt schedule |
| Ending cash | Balance sheet (cash) | Cash flow statement |
Key Takeaways
- Build in this order: assumptions, income statement, balance sheet, cash flow statement, then link them together.
- Every number should trace back to an assumption or another statement, never a typed value inside a formula.
- The balance check (Assets minus Liabilities and Equity, equal to zero) is the single most useful cell in the entire workbook.
- Use beginning-of-period debt for interest expense unless you deliberately want to work with circular references.
- A model that only balances in the base case is not actually linked. Stress test it before you trust it.
Frequently Asked Questions
What is a 3-statement financial model?
A 3-statement model links the income statement, balance sheet, and cash flow statement into one Excel workbook, so a change in one assumption automatically flows through all three statements.
How long does it take to build a 3-statement model?
A simple model for a small company can take two to four hours once you know the structure. More complex models with detailed schedules can take several days.
Why doesn't my balance sheet balance?
The most common causes are a missing link between the cash flow statement and the balance sheet cash line, an incorrect retained earnings roll-forward, or a sign error on working capital changes.
Do I need circular references for a 3-statement model?
Only if you model interest expense based on the average or ending debt balance, since debt depends on cash flow, which depends on interest. A simpler version uses beginning-of-period debt to avoid circularity.
Is a 3-statement model the same as a DCF model?
No. A 3-statement model forecasts all three financial statements together. A DCF model uses those forecasts, particularly free cash flow, to estimate the value of a business.
What Excel skills do I need before starting?
Comfort with basic formulas, absolute and relative cell references, and simple functions like SUM and IF is enough to get started.
Related Articles
External References
- Corporate Finance Institute, Financial Modeling Guide: corporatefinanceinstitute.com
- Microsoft Support, Enable or disable iterative calculation: support.microsoft.com
0 Comments