Every finance career eventually runs into the same test: open a blank workbook, and build a model that links the income statement, balance sheet, and cash flow statement into one working file, with the balance sheet actually balancing at the end. It sounds intimidating written out like that. Broken into the right order, it's a series of manageable steps, and this guide walks through all of them.
Budget real time for your first attempt. Most beginners spend 8 to 12 hours on their first build, and a meaningful chunk of that is the slow part everyone hits: figuring out why the balance sheet doesn't balance the first time. That's normal, not a sign you're doing something wrong.
Quick Answer: What's the build order?
Start with an assumptions tab so every input lives in one place. Build the income statement first, since net income feeds both other statements. Build supporting schedules next: working capital, PP&E and depreciation, and debt. Then build the balance sheet, leaving cash for last. Finish with the cash flow statement, which starts from net income and reconciles to the ending cash balance. Add a balance check row (Assets minus Liabilities and Equity) for every period, and don't consider the model done until that row reads zero everywhere.
On this page
- The Big Picture: How the Three Statements Connect
- Step 1: Build the Assumptions Tab
- Step 2: Build the Income Statement
- Step 3: Build Supporting Schedules
- Step 4: Build the Balance Sheet
- Step 5: Build the Cash Flow Statement
- Step 6: Add the Balance Check
- Handling Circular References
- Mistakes That Cost the Most Time
- Key Takeaways
- Frequently Asked Questions (FAQs)
The Big Picture: How the Three Statements Connect
Before opening Excel, it helps to see the whole shape of what you're building. The three statements aren't three separate documents that happen to sit in the same workbook. They're one connected system, where a change in any single assumption ripples through all three.
Net income flows two directions at once: into retained earnings on the balance sheet, and as the starting line of the cash flow statement.
Step 1: Build the Assumptions Tab
Every single input your model needs, revenue growth rate, gross margin, days payable outstanding, tax rate, belongs on one dedicated tab, never scattered inside formulas across the workbook. This is the habit that separates a model you can update in seconds from one you have to rebuild from scratch every time something changes.
-- Wrong: hardcoded directly in a formula
=B5*1.05
-- Right: references a cell on the Assumptions tab
=B5*(1+Assumptions!$B$3)To derive reasonable starting assumptions, pull at least three years of historical financials and calculate average gross margin, average COGS as a percentage of revenue, and average capital expenditure as a percentage of revenue. Adjust those historical averages for anything you know is changing going forward, a new product line, a cost reduction program, a planned acquisition, to arrive at your forward-looking assumptions.
Step 2: Build the Income Statement
Start with revenue, since everything else on the income statement, and eventually the other two statements, builds down from it. Forecast revenue using either a growth rate assumption or a bottoms-up volume times price approach, whichever fits the business better.
- Forecast revenue using your growth assumption or volume and price drivers.
- Build Cost of Goods Sold as a percentage of revenue, based on historical margins.
- Forecast operating expenses, often as a percentage of revenue or with their own specific growth assumptions.
- Leave interest expense blank for now. It comes from the debt schedule you'll build in the next step, and forcing it in early is a common source of circular reference errors.
- Apply your tax rate assumption to arrive at net income.
Step 3: Build Supporting Schedules
Before touching the balance sheet, build three supporting schedules that will feed directly into it. Skipping straight to the balance sheet without these in place is one of the most common reasons beginners end up stuck with an unexplained imbalance later.
| Schedule | What it calculates | Feeds into |
|---|---|---|
| Working capital schedule | Accounts receivable, inventory, accounts payable, based on days-outstanding assumptions | Balance sheet current assets and liabilities; cash flow statement |
| PP&E and depreciation schedule | Capital expenditure, depreciation expense, ending PP&E balance | Balance sheet fixed assets; income statement depreciation; cash flow statement |
| Debt schedule | Beginning debt, new borrowing or repayment, interest expense, ending debt balance | Balance sheet debt; income statement interest expense; cash flow statement |
Step 4: Build the Balance Sheet
With the supporting schedules in place, most of the balance sheet is now just a matter of linking to them. Working capital items come from the working capital schedule. PP&E comes from the depreciation schedule. Debt comes from the debt schedule. Retained earnings links to the prior period's retained earnings plus current period net income, minus any dividends.
Leave cash for last, deliberately. The ending cash balance on the balance sheet should link directly from the cash flow statement, which you haven't built yet, which is exactly why cash is the final piece to fall into place.
Step 5: Build the Cash Flow Statement
The cash flow statement starts with net income and adjusts it for everything that affects cash but doesn't appear as cash on the income statement: adding back depreciation (a non-cash expense), and adjusting for changes in working capital accounts.
Net Income
+ Depreciation and Amortization
- Increase in Accounts Receivable
+ Increase in Accounts Payable
- Increase in Inventory
= Cash Flow from Operations
- Capital Expenditures
= Cash Flow from Investing
+ New Debt Issued
- Debt Repaid
= Cash Flow from Financing
Net Change in Cash
+ Beginning Cash Balance
= Ending Cash BalanceThat ending cash balance is what finally links back into the balance sheet's cash line, completing the loop between all three statements.
Step 6: Add the Balance Check
Add an explicit row at the bottom of your balance sheet: Total Assets minus Total Liabilities and Equity, for every single period in the model. This one row is how you know, immediately and objectively, whether the model is wired correctly. If it reads zero everywhere, your links are correct. If it doesn't, something is missing, duplicated, or has the wrong sign somewhere in the model.
If your model doesn't balance and you're not sure where to start looking, our dedicated troubleshooting guide on why a three-statement model doesn't balance walks through the most common causes in the order worth checking them.
Handling Circular References
Interest expense depends on the debt balance. The debt balance can depend on the cash flow, which in turn depends on interest expense. That loop is called circularity, and it's completely normal in models with a revolving credit facility or a cash sweep mechanism.
Handle it one of two ways: enable iterative calculation under Formulas, Calculation Options, or build a circularity switch, a simple toggle cell that can temporarily zero out the circular part of the calculation while you're auditing the rest of the model for errors. The second approach is worth setting up even if you don't need it immediately, since it makes debugging dramatically easier later.
Mistakes That Cost the Most Time
- Hardcoding a number directly into a formula instead of referencing the assumptions tab, which makes the model fragile and hard to update later.
- Building the balance sheet before the supporting schedules exist, leading to a confusing scramble later trying to figure out where numbers should come from.
- Forgetting to leave interest expense blank in the first income statement pass, which creates an unnecessary circular reference before the debt schedule even exists.
- Not adding the balance check until the very end, instead of checking it after every major addition, which turns a small, easy-to-find error into a much harder one to isolate.
- Spreading the model across many tabs when a single-sheet layout would be easier to navigate and less prone to broken links.
Key Takeaways
- Build in this order: assumptions, income statement, supporting schedules, balance sheet, cash flow statement, then the balance check.
- Every input belongs on a dedicated assumptions tab, never hardcoded inside a formula.
- Leave interest expense blank in your first income statement pass. It comes from the debt schedule, built afterward.
- Cash is the last line to complete on the balance sheet, since it links from the cash flow statement you build after the balance sheet's other lines.
- An explicit balance check row, calculated every period, is the single most useful diagnostic tool in the entire model.
Frequently Asked Questions (FAQs)
How long does it take to build a three-statement model from scratch?
For a first attempt on a simple company, budget 8 to 12 hours, most of which goes toward chasing down why the balance sheet does not balance the first time. Experienced modelers with strong Excel skills can build a straightforward model in 2 to 3 hours.
What is the very first thing to build in a three-statement model?
An assumptions tab, before touching any of the three statements themselves. Every input the model needs, growth rates, margins, payment terms, tax rate, should live in one place and be referenced by formulas, never hardcoded directly inside a calculation.
Why should the income statement be built before the balance sheet?
Net income from the income statement flows directly into both the balance sheet, through retained earnings, and the cash flow statement, as the starting point for operating cash flow. Building the income statement first gives you that number before you need it in the other two statements.
What causes circular references in a three-statement model?
Interest expense depends on the debt balance, but the debt balance can depend on cash flow, which depends on interest expense, creating a loop. This is normal in models with a revolving credit facility or cash sweep. Handle it either by enabling iterative calculation in Excel or by adding a circularity switch that can temporarily break the loop while auditing the model.
Do I need three years of historical data before I can build a forecast?
Three years is a common standard because it gives you enough history to calculate meaningful average margins, growth rates, and turnover ratios, and to smooth out any single unusual year. A model can technically be built with less, but the resulting assumptions will rest on a thinner, less reliable base.
Related Articles
External References
- Corporate Finance Institute. "What is a 3-Statement Model? Your Complete Guide." corporatefinanceinstitute.com
- Wall Street Prep. "3-Statement Model | Complete Guide (Step-by-Step)." wallstreetprep.com
- Wall Street Mastermind. "How to Build a 3-Statement Financial Model: Complete Guide." wallstmastermind.com
0 Comments