Building an Integrated 3 Statement Model
Integrated 3-statement model: how to build one that balances

A founder changes one number in a financial model. Revenue growth drops from 15% to 8%. The income statement updates, but the balance sheet stops balancing and the cash line shows a figure nobody can explain. Five minutes earlier, the model looked finished.
The cause is almost always the same. The three statements sit side by side, but formulas do not link them. An integrated 3-statement model fixes that by driving every number from one set of assumptions. This guide covers the build order, a worked example that balances, and the checks that catch errors before a lender or investor does.
What an integrated 3-statement model is
A 3-statement model forecasts the income statement, balance sheet, and cash flow statement in one spreadsheet. It is integrated when formulas link them. Revenue creates receivables. Net income feeds retained earnings. The cash flow statement produces the ending cash balance that appears on the balance sheet.
The three statements tell one story in three ways. The income statement shows whether the business made money. The balance sheet shows what it owns and owes on one date. The cash flow statement shows where the cash went. The balance sheet vs income statement comparison covers the first two in detail.
Together they answer a question no single statement can: can this business fund its own growth? Founders use a simple version to find their runway and funding need. FP&A teams use it for budgets and rolling forecasts. Investors and bankers build DCF, LBO, and merger models on top of it.
How the three statements connect
Every transaction leaves a mark on at least two statements. The table shows seven common events and where each one lands.
| Event | Income statement | Balance sheet | Cash flow statement |
| Sale on credit | Revenue rises | Receivables rise | No cash yet |
| Customer pays | No change | Receivables fall, cash rises | Operating inflow |
| Equipment purchase | No change | PP&E rises, cash falls | Investing outflow |
| Depreciation | Expense rises | PP&E falls | Added back in operations |
| Loan drawn | No change | Debt and cash rise | Financing inflow |
| Interest paid | Interest expense rises | Retained earnings and cash fall | Reduces net income, the starting line |
| Dividend paid | No change | Retained earnings and cash fall | Financing outflow |
A sale on credit shows why profit and cash differ. Revenue rises on the income statement, but no cash arrives until the customer pays. The Oak guide to cash vs profit explains why profitable businesses still run short of money. An integrated model makes that gap visible in every period.
Choose the model period before you open Excel
The time period is the first design decision. It sets how much data the model needs and what it can show.
| Period | Best for | Watch out for |
| Annual | DCF valuation, LBO, long-range plans | Hides seasonal cash gaps |
| Quarterly | Investor reporting, equity research, near-term planning | Needs clean quarterly history |
| Monthly | Startups, fundraising, restructuring | More columns and more room for error |
| Weekly | Cash crunches and short-term liquidity | Practical only over about 13 weeks |
A common setup for a startup is monthly columns for the first 24 to 36 months, then annual columns to year five. Annual columns alone smooth over the months when cash runs thin. For weekly work, a 13-week cash flow model sits beside the 3-statement model. It does not replace it.
Set up the layout
Keep inputs, calculations, and outputs apart. Put every assumption in one labeled section. Place the three statements together, with supporting schedules below them or on separate tabs. Write one formula per row and copy it across all periods. Rows with different formulas in different columns are where auditors find errors.
Color-code from the first cell. Use blue for typed inputs, black for formulas, and green for links to other sheets. Never bury a typed number inside a formula. A formula such as =B12*0.32 hides the tax rate where nobody can see it or change it.

Step 1: load and clean the historical data
Start with three years of history, five if available. Public companies publish this in their annual filings. Private companies supply audited statements or management accounts.
Do not paste the numbers and move on. Read them. Remove one-off items such as a legal settlement, a grant, or an asset sale, because they distort the base year. Then calculate the ratios that will drive the forecast:
- Revenue growth and gross margin
- Operating expenses as a percentage of revenue
- Days sales outstanding (DSO): receivables divided by revenue, times 365
- Days inventory outstanding (DIO): inventory divided by cost of goods sold (COGS), times 365
- Days payable outstanding (DPO): payables divided by COGS, times 365
- Capex as a percentage of revenue
Last, confirm that the final historical balance sheet balances. If it does not, every forecast year inherits the error.
Step 2: set the forecast assumptions
Every forecast is only as good as its assumptions. Put them in one place and note where each came from. The list should cover revenue (growth, or volume times price), gross margin, operating expenses, tax rate, DSO, DIO, DPO, capex, depreciation method, debt terms, and a minimum cash balance.
Working capital days deserve the most doubt. A founder who assumes customers pay in 30 days, when they really pay in 60, overstates cash by a month of sales. Test the model with longer receivable days than the contract terms say.
Step 3: forecast the income statement to EBITDA
Forecast revenue down to EBITDA, then stop. Depreciation comes from the PP&E schedule and interest comes from the debt schedule. Neither exists yet. Leaving those lines open is normal. You return to finish the statement in Step 5.
Step 4: build the supporting schedules
Schedules do the detailed work that the main statements only summarize. Each one rolls a balance forward from one period to the next.
| Schedule | Roll-forward | Feeds |
| PP&E | Opening + capex – depreciation = closing | Balance sheet, investing cash flow, depreciation expense |
| Debt | Opening + borrowing – repayment = closing | Balance sheet, financing cash flow, interest expense |
| Retained earnings | Opening + net income – dividends = closing | Balance sheet equity |
| Working capital | Days x driver / 365 | Balance sheet, operating cash flow |
For working capital, receivables equal DSO times revenue divided by 365. Inventory and payables use the same logic with COGS. Check the loan agreements for mandatory repayments too. A missed scheduled payment is a common reason a debt balance is wrong.
Step 5: complete the income statement
Now pull depreciation from the PP&E schedule and interest from the debt schedule. Tax is pre-tax income times the tax rate. In a loss year, set tax to zero unless the business can carry losses forward. Carry-forward rules differ by country, so confirm the treatment with an accountant.
Net income is now final. It flows to two places: retained earnings on the balance sheet and the first line of the cash flow statement.
Step 6: build the balance sheet without cash
Fill every line except cash. Receivables, inventory, and payables come from the working capital schedule. PP&E comes from its schedule. Debt comes from the debt schedule. Retained earnings comes from its roll-forward. Share capital stays flat unless the business raises equity.
Leave the cash line empty. The balance sheet will not balance yet, and that is expected.
Step 7: build the cash flow statement and close the loop
The cash flow statement forecasts nothing. It reconciles the changes in the balance sheet. Every line must reference a cell elsewhere in the model. A typed number on this statement is the most common reason a balance sheet fails to balance.
Operating cash flow starts with net income, adds back depreciation, then adjusts for working capital. Investing cash flow holds capex and asset sales. Financing cash flow holds borrowings, repayments, equity raised, and dividends.
One rule fixes most sign errors. An increase in an asset uses cash. An increase in a liability provides cash. Add the three sections to opening cash to get closing cash, then link that figure to the cash line on the balance sheet. If the balance sheet now balances, the model is integrated.
Worked example: one year, three statements
The figures below are illustrative. They show how the links work and do not describe a real company.
The assumptions are: revenue of 1,000, COGS at 60% of revenue, operating expenses of 250, capex of 60, and depreciation of 40. Interest is 5% of the opening debt balance of 100. Tax is 20%. The business repays 20 of debt and pays 20 in dividends. Receivables rise from 60 to 90, and payables rise from 40 to 50.
Income statement, Year 1
| Line | Amount |
| Revenue | 1,000 |
| COGS | (600) |
| Gross profit | 400 |
| Operating expenses | (250) |
| EBITDA | 150 |
| Depreciation | (40) |
| EBIT | 110 |
| Interest | (5) |
| Pre-tax income | 105 |
| Tax at 20% | (21) |
| Net income | 84 |
Cash flow statement, Year 1
| Line | Amount |
| Net income | 84 |
| Add back depreciation | 40 |
| Increase in receivables | (30) |
| Increase in payables | 10 |
| Operating cash flow | 104 |
| Capex | (60) |
| Investing cash flow | (60) |
| Debt repayment | (20) |
| Dividends | (20) |
| Financing cash flow | (40) |
| Net change in cash | 4 |
| Opening cash | 100 |
| Closing cash | 104 |
Balance sheet
| Line | Year 0 | Year 1 |
| Cash | 100 | 104 |
| Receivables | 60 | 90 |
| Net PP&E | 200 | 220 |
| Total assets | 360 | 414 |
| Payables | 40 | 50 |
| Debt | 100 | 80 |
| Share capital | 150 | 150 |
| Retained earnings | 70 | 134 |
| Total liabilities and equity | 360 | 414 |
| Balance check | 0 | 0 |
Retained earnings moved from 70 to 134. That is 70 plus net income of 84, minus dividends of 20. Net PP&E moved from 200 to 220: 200 plus capex of 60, minus depreciation of 40.
Now run the classic linkage test. Raise depreciation by 10. Pre-tax income falls by 10, tax falls by 2, and net income falls by 8. Operating cash flow rises by 2, because net income is down 8 but depreciation adds back 10. PP&E falls by 10. Assets change by +2 and -10, which is -8. Retained earnings fall by 8. The balance sheet still balances.
Cash shortfalls: the revolver plug
Real models need a rule for what happens when cash runs short. Without one, cash goes negative and the model keeps running as if nothing happened. The usual fix is a revolving credit line that draws automatically.
Set a minimum cash balance. Calculate cash before the revolver. If that figure falls below the minimum, the revolver draws the difference. If cash sits above the minimum and the revolver has a balance, it repays. One formula handles both cases:
Revolver draw or repayment = MAX(minimum cash – cash before revolver, -opening revolver balance)
A startup without a bank facility can use the same line. Rename it “funding gap.” The number it produces is the amount the business needs to raise, which is the figure investors look for first.
Circular references: three ways to handle interest
Interest on an average debt balance creates a loop. Interest lowers net income. Net income changes cash. Cash changes the revolver. The revolver changes the debt balance, and the debt balance sets interest. Excel flags this as a circular reference.
There are three common fixes:
- Calculate interest on the opening balance. This removes the loop entirely and is the simplest option.
- Turn on iterative calculation under File, Options, Formulas. Interest can then use the average balance. The risk is that Excel also hides any accidental loops elsewhere in the model.
- Add a circularity switch. A cell set to 1 uses average balances, and a cell set to 0 falls back to opening balances. If the model throws errors, flip the switch to 0, let it clear, and flip it back.
For a startup model that investors will audit, opening-balance interest is usually enough. It is easier to explain and cannot break.
Checks that prove the model is integrated
A model that runs without errors can still be wrong. Put each check on its own row, in every period.
| Check | Formula idea | Passes when |
| Balance check | Assets – liabilities – equity | Zero in every period |
| Cash tie-out | Closing cash on cash flow – cash on balance sheet | Zero |
| Retained earnings tie | Closing balance – (opening + net income – dividends) | Zero |
| Opening balance tie | First forecast opening – last actual closing | Zero |
| Funding check | Closing cash – minimum cash | Not negative |
Add one master check cell at the top of the sheet that sums the absolute value of every check. Turn it green at zero and red otherwise.
When the balance check fails, the size of the gap points to the cause.
| The gap equals | Likely cause |
| Net income | Net income missing from retained earnings or from the cash flow statement |
| Depreciation | Not added back on the cash flow statement, or not deducted from PP&E |
| Capex | Missing from the PP&E schedule or from investing cash flow |
| A working capital change | That change is missing from operating cash flow |
| Twice a line item | The sign is flipped on that line |
| A number that grows each year | Opening balances do not link to the prior closing balances |
| A gap in the first forecast year only | The last historical balance sheet does not balance |
Scenario and sensitivity analysis
A model with one case tells one story. Build at least three: base, downside, and upside. Store each set of assumptions in a table and use a single switch cell to pick the active case. One cell then changes revenue growth, margins, and capex together.
Sensitivity analysis is narrower. It takes one output, such as free cash flow, and shows how it moves as one or two inputs change. A two-variable data table does this in Excel. The Oak article on sensitivity analysis in financial modelling shows how to set one up. Data tables recalculate the whole model for each cell, so a model with iterative calculation switched on can slow down or behave oddly.
Run one more test before any model leaves your desk. Set revenue growth to negative 50% and margins to zero. The outputs may look ugly, and the funding check may turn red. That is fine. The balance check must still read zero.
How the model feeds valuation and fundraising
A DCF discounts free cash flow, and that cash flow comes straight from the model. Unlevered free cash flow is EBIT times (1 minus the tax rate), plus depreciation, minus capex, minus the increase in working capital. A DCF built without a 3-statement model underneath usually rests on guessed cash flows.
An LBO needs the debt schedule and the cash flow to model paydown. A merger model combines two integrated models and adds the deal adjustments. In fundraising, investors read the funding gap, the runway, and the working capital days. They change those inputs themselves, so the model must survive the change.
Mistakes that break models
- Typing a cash figure instead of deriving it from the cash flow statement.
- Hardcoding a number on the cash flow statement instead of linking it.
- Holding working capital days flat for five years while the business scales.
- Using annual columns for a seasonal business that runs out of cash in one month.
- Skipping a minimum cash balance, so the model never shows a shortfall.
- Building 50 tabs and 200 line items that nobody can follow. Precision should stop where the quality of the data stops.
Frequently Asked Questions
What is the order for building a 3-statement model?
Start with historical data and assumptions. Forecast the income statement to EBITDA, then build the working capital, PP&E, and debt schedules. Finish the income statement, build the balance sheet without cash, and build the cash flow statement last. Link closing cash back to the balance sheet.
How many years should a 3-statement model cover?
Use three to five years of history. Corporate models and DCF valuations usually forecast five years. Startups raising capital often use monthly columns for 24 to 36 months, then annual columns to year five.
Why does my balance sheet not balance?
Look at the size of the gap. A gap equal to net income, depreciation, or a working capital change points to a missing link. A gap equal to twice a line item points to a sign error. A hardcoded number on the cash flow statement is the next suspect.
How do I fix a circular reference in a 3-statement model?
Calculate interest on the opening debt balance to remove the loop. If average balances matter, turn on iterative calculation and add a switch that can break the loop when errors appear.
What is the difference between a 3-statement model and a DCF?
The 3-statement model forecasts the financial statements. The DCF takes the free cash flow from that forecast and discounts it to a present value. The DCF depends on the model, but the model does not need a DCF.
Can a startup or private company build a 3-statement model?
Yes. The mechanics are identical. The difference is data. Private companies have no public filings, so the assumptions come from management accounts, early sales, and comparable businesses. Startups with little history should test each assumption at a range of values.
Should the model sit on one worksheet or several?
Many modelers keep the three statements on one sheet and put schedules on separate tabs. That reduces linking errors and makes the model easier to audit. Very large models with many subsidiaries are the exception.
Conclusion
A model that balances proves the arithmetic. It says nothing about whether the assumptions are right. Investors will test receivable days, margins, and capex against businesses they know, and they will change your inputs in their own copy.
Build the model so every question has a one-cell answer. If an investor asks what happens when customers pay in 75 days, the answer should be a number, not a promise to get back to them.
Need an investor-ready model without building it from scratch? Oak Business Consultant builds bespoke financial models with fully linked statements, scenario switches, and built-in checks. Early-stage companies can start with the startup financial model service. Book a free consultation and bring your current spreadsheet. Oak will tell you where the links break.
