Building a Three-Statement Model: The Valuation Foundation

Introduction

The three-statement model is the foundation on which every other investment banking model is built. A DCF model extracts unlevered free cash flow from the three-statement model. An LBO model layers a debt schedule and returns analysis on top of it. A merger model combines two three-statement models with transaction adjustments. Without a reliable three-statement model, none of these advanced analyses can be trusted.

Building a three-statement model requires understanding how the three financial statements link to each other: the income statement feeds the cash flow statement (net income is the starting point), the cash flow statement feeds the balance sheet (the ending cash balance connects), and the balance sheet feeds back to the income statement (debt balances drive interest expense, cash balances drive interest income). This circular linkage is what makes the model "integrated" and is both the model's greatest strength and its primary technical challenge.

Start by entering 3-5 years of historical financial data from the company's 10-K and 10-Q filings. Input the income statement, balance sheet, and cash flow statement in their entirety, in blue font (since these are hard-coded inputs from reported data). Present historical data on the left side of each tab, with projections extending to the right.

The historical data serves two purposes: it provides the base period from which projections are built, and it reveals the company's historical trends in growth, margins, capital intensity, and working capital that inform the projection assumptions.

When inputting historical data, pay attention to several details that affect model accuracy. First, ensure the three statements reconcile: the net income on the income statement must match the starting point of the cash flow statement, and the ending cash on the CFS must match the balance sheet. If they do not (which occasionally happens due to rounding or supplemental disclosure differences), investigate before proceeding. Second, separate recurring operations from non-recurring items (restructuring charges, gains/losses on asset sales, impairments) because the projection will be based on normalized, recurring earnings, not reported figures. Third, note any significant acquisitions or divestitures during the historical period that may make year-over-year comparisons misleading: a company that acquired a major business mid-year will show revenue that is not representative of organic growth.

With the raw historical data entered, calculate the key ratios that will drive the projections:

These ratios are displayed alongside or below the historical data, forming the bridge between history and projections. The analyst examines trends across periods and uses them (alongside management guidance and industry context) to set the projection assumptions.

Build the income statement from the top down:

Revenue: The most important assumption, projected using the methods discussed in projecting revenue and operating assumptions (bottom-up drivers, top-down market sizing, or growth rate assumptions).

Cost of goods sold: Projected as an implied gross margin percentage of revenue, based on historical trends and management guidance.

Operating expenses (SG&A, R&D): Projected as a percentage of revenue, with adjustments for operating leverage (fixed vs. variable cost mix) and specific investments or cost reduction plans.

D&A: Projected as a percentage of revenue or modeled from the depreciation schedule (based on the existing asset base and new CapEx assumptions).

At this point, the income statement is complete through EBIT. Interest expense and taxes require the debt schedule and tax calculations, which are built in the next steps.

A critical modeling choice at this step is whether to project each cost line item as a percentage of revenue (the simpler approach, adequate for most models) or to build a driver-based model where each cost line has its own projection logic (headcount x average compensation for labor costs, volume x unit material cost for COGS, square footage x rent per square foot for facilities). Driver-based models are more accurate but significantly more complex and are typically reserved for management presentations and detailed operating models rather than standard valuation models.

For the projection period length, most investment banking models project 5 years for mature companies and 7-10 years for high-growth companies. The projection period should extend until the company reaches a normalized, steady-state growth and margin profile that is appropriate for the terminal value calculation. Ending the projection period while the company is still in a high-growth or restructuring phase produces an unreliable terminal value because the terminal year cash flow is not representative of the company's long-term potential.

Debt Schedule

The debt schedule tracks each tranche of debt: beginning balance, new borrowings, repayments, and ending balance. Interest expense is calculated as the average of opening and closing debt balances multiplied by the applicable interest rate. This average convention is more accurate than using just the opening or closing balance, but it creates the circular reference discussed below.

Depreciation Schedule

The depreciation schedule projects D&A based on the existing asset base (with its remaining useful life) and new CapEx (which creates new depreciable assets). In a simple model, D&A is projected as a percentage of revenue or a percentage of gross PP&E. In a more detailed model, each asset class has its own depreciation schedule.

Working Capital Schedule

Project each working capital component (accounts receivable, inventory, accounts payable, other current assets/liabilities) using the DSO, DIO, and DPO ratios from Step 2. The net change in working capital flows to the cash flow statement.

The working capital projection requires careful treatment of the change rather than the absolute level. The income statement and balance sheet work in levels (total revenue, total receivables), but the cash flow statement needs the period-over-period change. If receivables increase from $100 million to $120 million, the $20 million increase represents cash consumed (revenue was recognized but not yet collected), which reduces operating cash flow. Conversely, if payables increase, it represents cash preserved (expenses were incurred but not yet paid), which increases operating cash flow.

A common modeling error is confusing the sign convention: an increase in a current asset (receivables, inventory) is a cash outflow (negative on the CFS), while an increase in a current liability (payables, accrued expenses) is a cash inflow (positive on the CFS). Getting this wrong inverts the working capital impact and breaks the balance sheet.

Supporting Schedule

A detailed calculation tab within a financial model that projects a specific line item or group of line items in greater detail than the main financial statements allow. Common supporting schedules include the debt schedule (tracking each tranche of debt with interest calculations), the depreciation schedule (projecting D&A from the existing and new asset base), the working capital schedule (projecting receivables, inventory, payables from operating ratios), and the share count schedule (tracking dilution from options, RSUs, and convertibles). Supporting schedules feed into the three financial statements and are essential for model accuracy.

With the debt schedule complete, interest expense can be calculated and plugged into the income statement below EBIT. Similarly, interest income is calculated from the average cash balance (which creates the second half of the circular reference).

Income tax expense is calculated as pre-tax income multiplied by the effective (or marginal) tax rate, with adjustments for deferred taxes if the model is detailed enough to require them.

The income statement is now complete from revenue through net income.

The cash flow statement links the income statement to the balance sheet. It starts with net income (from the income statement) and adjusts for:

Operating activities: Add back non-cash charges (D&A, stock-based compensation, deferred taxes) and subtract/add changes in working capital (from the working capital schedule).

Investing activities: Subtract capital expenditures, add/subtract proceeds from asset sales or acquisitions.

Financing activities: Add new debt borrowings, subtract debt repayments, subtract dividends, add/subtract stock issuances/repurchases.

The net change in cash (the sum of operating, investing, and financing cash flows) is added to the beginning cash balance to arrive at the ending cash balance, which is the final entry on the balance sheet.

With the cash flow statement done, the balance sheet can be completed. Most balance sheet items are already populated from the supporting schedules (debt from the debt schedule, PP&E from the depreciation schedule, working capital items from the working capital schedule). The ending cash balance from the cash flow statement is the final plug that completes the balance sheet.

The balance sheet must balance (Assets = Liabilities + Equity) in every projected period. If it does not, there is an error that must be traced and corrected before the model can be used for any valuation.

To make the linking concrete, consider a simplified Year 1 projection:

Every number on the balance sheet traces to either the income statement, the cash flow statement, or a supporting schedule. The ending cash balance ($120M) is the final item calculated, and it makes the balance sheet balance. If it does not balance, there is an error in one of these linkages.

Balance Sheet Balance Check

A validation row built into every three-statement model that calculates Assets minus Liabilities minus Equity for each projected period. The result must equal zero (or a very small rounding difference) in every period. If the check shows a non-zero value, there is a modeling error, almost always in the cash flow statement. Best practice uses conditional formatting to turn the check row red when it fails, providing an immediate visual alert. Some modelers also include separate checks for the cash flow statement (verifying that the change in cash from the CFS matches the change in the balance sheet cash line) and the debt schedule (verifying that ending balance equals beginning balance plus new debt minus repayments).

The three-statement model creates a circular reference: interest expense depends on the debt balance, which depends on the cash flow statement, which depends on net income, which depends on interest expense. Similarly, interest income depends on the cash balance, which depends on the cash flow, which depends on interest income.

How to Handle It

Option 1: Enable iterative calculations in Excel (File > Options > Formulas > Enable iterative calculation). This tells Excel to calculate the circular reference through multiple iterations until it converges. Most investment banking models use this approach.

Option 2: Use a circular switch (a toggle cell that breaks the circularity by replacing the interest calculation with a manual override when needed for debugging). The switch is set to "on" (circular active) during normal use and "off" (circularity broken) when the model needs troubleshooting.

Option 3: Use the prior period's balance for interest calculation instead of the average. This eliminates the circularity but is slightly less accurate. Some banks prefer this approach for simplicity.

Stock-Based Compensation

SBC appears on the income statement as an operating expense (reducing EBIT and net income) and is added back on the cash flow statement as a non-cash charge (increasing operating cash flow). On the balance sheet, SBC increases additional paid-in capital (equity). These three entries must be consistent: the SBC expense on the income statement must equal the SBC add-back on the CFS, which must equal the increase in APIC (before considering exercises and repurchases).

Deferred Taxes

The difference between the book tax provision (on the income statement) and actual cash taxes paid (on the cash flow statement) creates a deferred tax asset or liability on the balance sheet. A simple model may ignore this distinction and assume cash taxes equal the book provision. A more detailed model separates book and cash taxes, with the difference flowing to the deferred tax line on the balance sheet.

Minority Interest

If the company consolidates a subsidiary it does not 100% own, the income statement includes 100% of the subsidiary's revenue and expenses. Below net income, a line item subtracts the minority interest's share of earnings. On the balance sheet, the minority interest appears in the equity section. On the cash flow statement, the minority interest add-back must be handled carefully to avoid double-counting.

Extending to a DCF

The DCF model is built directly on the three-statement model's output. The analyst creates a separate tab that extracts UFCF from the projected income statement and balance sheet:

Each component comes from the three-statement model: EBIT from the income statement, D&A from the depreciation schedule, CapEx from the cash flow statement, and NWC changes from the balance sheet. The DCF tab then adds the terminal value, discounts at WACC, and produces the implied enterprise value.

Extending to an LBO

The LBO model replaces the three-statement model's existing debt structure with the new LBO capital structure, layers on the sources and uses, purchase price allocation, and detailed debt schedule. The three-statement model's revenue and operating projections remain, but the capital structure and balance sheet are rebuilt to reflect the leveraged transaction.

Extending to a Merger Model

The merger model combines two three-statement models (acquirer and target), applies transaction adjustments (PPA, new financing, fee amortization), and produces pro forma financial statements for the combined entity. The accretion/dilution analysis is derived from comparing the acquirer's standalone EPS to the pro forma combined EPS.