How to Build a 3-Statement Financial Model: A Step-by-Step Guide (With Excel Tips)

A 3-statement financial model is the foundation of financial planning and analysis. It combines the income statement, balance sheet, and cash flow statement into a single dynamically linked model. When any assumption changes, such as revenue growth, cost margins, or capital expenditure, the change flows through all three statements automatically, showing the full financial impact.

This guide walks through how to build one from scratch: the assumptions section, the income statement, the balance sheet, the cash flow statement, the supporting schedules that most guides skip, and the validation checks that confirm the model is working correctly.

Read: Financial Statement Analysis: The Complete Guide (With Step-by-Step Process)

A standalone income statement tells you whether the business was profitable. A standalone balance sheet tells you what it owns and owes at a point in time. A standalone cash flow statement tells you how cash moved. None of the three alone can answer the question a CFO or FP&A manager actually needs answered: if I change this assumption, what happens to cash, debt, and equity?

The integrated 3-statement model answers that question. A revenue assumption change flows to gross profit, then to operating income, then to net income, then to retained earnings on the balance sheet and to operating cash flow, which then affects the ending cash balance and the balance sheet simultaneously. That chain of consequences is what makes the model powerful. Building it carefully is essential.

All serious 3-statement models begin with a dedicated assumptions section, not with the income statement. The assumptions section defines every key driver that feeds the model: revenue growth rates, COGS as a percentage of revenue, operating expense growth, capital expenditure assumptions, depreciation rates, interest rates on debt, effective tax rate, and dividend policy.

The practical approach is to create a separate Assumptions tab in Excel. Every input in the three statements references a cell in this tab rather than a hardcoded number. When you want to test a different scenario, you change one assumption in one place and every dependent cell updates automatically across all three statements.

To derive the assumptions, collect at least three years of historical financial statements. Calculate average gross margin, average COGS as a percentage of revenue, and average capital expenditure as a percentage of revenue from the historical data. These become the baseline assumptions. Adjust for any known changes in the business (a new product line, a cost reduction programme, a planned acquisition) to arrive at the forward assumptions.

Never hardcode a number directly into a formula. If the interest rate is 5%, put 5% in the assumptions tab and reference that cell. A model where assumptions are scattered as hardcoded values throughout formulas is extremely difficult to audit or update.

The income statement is always built first because its bottom line, net income, is the starting point that feeds into both the balance sheet and the cash flow statement. The sequence within the income statement follows the natural P&L structure.

Read: Consolidated Cash Flow Statement: Definition, Example, and Modern Approach

Begin by estimating total revenue for each period. Base your projection on historical data, business-specific drivers, or a combination. The most defensible approach references actual operational metrics rather than applying a simple percentage growth rate.

For a coffee shop chain with 20 locations, each generating approximately $300,000 per year, with 10 new locations planned for next year, the revenue calculation is:

• Existing 20 locations: 20 x $300,000 = $6,000,000
• New 10 locations at 50% first-year ramp (based on historical data): 10 x $300,000 x 0.5 = $1,500,000
• Total projected revenue: $6,000,000 + $1,500,000 = $7,500,000

Breaking revenue into its key drivers (number of locations, revenue per location, ramp factor for new openings) is more reliable than applying a single growth percentage, because it makes the assumptions explicit and allows each driver to be updated independently when conditions change.

COGS covers direct costs of producing or purchasing the goods sold: ingredients, materials, direct labour, and other production costs. For the coffee shop, this includes coffee beans, milk, sugar, cups, and similar direct inputs.

Project COGS as a percentage of revenue based on the historical gross margin. If COGS has averaged 50% of revenue over the past three years and no structural change is expected, use 50% as the forward assumption. Revenue minus COGS gives gross profit.

Operating expenses cover costs of running the business that are not directly tied to production: salaries, rent, utilities, marketing, and general and administrative costs (SG&A). Project these either as a percentage of revenue (if they scale with the business) or using a growth rate (if they are more fixed in nature).

Gross profit minus operating expenses gives operating income, also called EBIT (Earnings Before Interest and Taxes).

Include non-operating items: interest income on cash balances (positive) and interest expense on debt (negative). Interest expense will be calculated in the debt schedule built in Step 5. Leave this as a placeholder for now and return to it after the supporting schedules are built.

Apply the effective tax rate to pre-tax income. The effective tax rate is derived from historical tax payments as a percentage of pre-tax income, adjusted for any known changes in the tax position.

Net income follows this structure:

Net Income = Revenue – COGS – Operating Expenses – Interest Expense – Income Taxes

Net income is the critical output from Step 1 because it feeds directly into both the balance sheet (via retained earnings) and the cash flow statement (as the starting point for operating activities).

Unlike the income statement, which covers a period, the balance sheet is a snapshot of financial position at a point in time. It always follows the accounting equation: Assets = Liabilities + Equity.

Build the balance sheet in this order: current assets, non-current assets, current liabilities, non-current liabilities, and equity. Cash is always the last item to complete on the balance sheet. It is derived from the ending cash balance of the cash flow statement, which cannot be calculated until Step 3.

Current assets include cash and cash equivalents (from the cash flow statement; leave as a placeholder for now), accounts receivable, inventory, and other short-term assets. Project accounts receivable using Days Sales Outstanding (DSO): Accounts Receivable = (Revenue / 365) x DSO. Project inventory using Days Inventory Outstanding.

Non-current assets primarily consist of Property, Plant and Equipment (PP&E), net of accumulated depreciation. The ending net PP&E balance comes from the PP&E schedule built in Step 5. Other non-current assets include intangibles, long-term investments, and deferred tax assets.

Current liabilities include accounts payable (projected using Days Payable Outstanding), accrued expenses, and the current portion of long-term debt.

Non-current liabilities primarily consist of long-term debt. The ending debt balance comes from the debt schedule built in Step 5.

Equity includes common stock, additional paid-in capital, and retained earnings. Retained earnings is the balance sheet’s link back to the income statement:

Retained Earnings = Beginning Retained Earnings + Net Income – Dividends

This formula ensures that every dollar of net income from the income statement either accumulates in retained earnings or is distributed as dividends. A profitable year increases retained earnings and therefore increases equity. A loss reduces them.

Read: Consolidation vs Equity Method – How To Choose The Right Approach

When net income flows from the income statement to retained earnings on the balance sheet, it changes total equity. Since Assets must always equal Liabilities plus Equity, a change in retained earnings that is not matched by a corresponding change in assets or other liabilities will cause the balance sheet to fail its check. This is why maintaining the linkages precisely is so important.

The cash flow statement explains how the business generated and used cash during the period. It is built in three sections and starts with net income from the income statement, then adjusts for items that affected the P&L without affecting cash (non-cash items) and for changes in working capital.

Start with net income. Then add back non-cash charges, specifically depreciation (which reduced net income but did not reduce cash). Then adjust for changes in working capital:

• Accounts Receivable increase: subtract (revenue recorded but cash not yet received).
• Inventory increase: subtract (cash spent on stock not yet sold).
• Accounts Payable increase: add back (goods received but cash not yet paid to supplier).

For the coffee shop chain, if quarterly revenue is $8 million but customers on account pay an additional $200,000 late, the AR increases by $200,000. This is subtracted from operating cash flow because the company earned the revenue but has not received the cash yet. If the coffee shop bought $300,000 of coffee beans and baked goods on credit from a supplier but has not yet paid, the AP increases by $300,000, which is added back because the company has the goods but still holds the cash.

Investing activities show cash flows from purchasing or selling long-term assets.

• Capital Expenditures (Capex): cash paid for new equipment, buildings, or leasehold improvements. Recorded as a negative cash flow. If the coffee shop chain purchases new espresso machines for $5,000, this appears as -$5,000.
• Asset Sales: cash received from selling long-term assets. Recorded as a positive cash flow.

Financing activities cover cash flows related to debt and equity:

• New borrowings: positive cash flow (cash received).
• Debt repayments: negative cash flow (cash paid).
• Dividends paid: negative cash flow (cash paid to shareholders).

The cash flow statement concludes with the calculation of ending cash:

Ending Cash = Beginning Cash + Cash from Operating Activities + Cash from Investing Activities + Cash from Financing Activities

This ending cash balance flows back to the cash line on the balance sheet, completing the link between the cash flow statement and the balance sheet. This is also the line that the Cash Check validation (Step 6) verifies.

With all three statements built, verify the four core linkages that make the model integrated:

When all four core linkages are correctly implemented, changing any assumption in the assumptions section will cascade automatically through all three statements consistently. This is what ‘integrated model’ means: one change, one consistent output across all three financial statements.

Two items in a 3-statement model cannot be calculated directly from within the three statements. They require separate supporting schedules that calculate the figures and feed them back into the relevant statements.

Depreciation affects three places in the model: the income statement (as an operating expense reducing net income), the cash flow statement (as a non-cash add-back to operating activities), and the balance sheet (as accumulated depreciation reducing PP&E to net book value). All three must reference the same depreciation number.

The PP&E schedule calculates:

• Beginning gross PP&E (from prior period balance sheet)
• Plus: capital expenditures for the period (from investing activities)
• Minus: disposals for the period
• Equals: ending gross PP&E
• Depreciation for the period (calculated as a percentage of gross PP&E or straight-line based on asset life)
• Cumulative accumulated depreciation
• Ending net PP&E (gross minus accumulated depreciation): this flows to the balance sheet

Interest expense on the income statement depends on the outstanding debt balance. But the debt balance changes through each period as borrowings and repayments occur in the cash flow statement. And the cash flow statement depends on net income, which depends on interest expense. This creates a circular reference.

The standard fix is to calculate interest expense based on the beginning-of-period debt balance rather than the average or end-of-period balance. This breaks the circularity without materially affecting the model’s accuracy for planning purposes.

The debt schedule calculates:

• Beginning debt balance (from prior period balance sheet)
• Plus: new borrowings for the period
• Minus: repayments for the period
• Equals: ending debt balance, which flows to the balance sheet non-current liabilities
• Interest expense: beginning balance multiplied by interest rate, which flows to the income statement

With these two schedules in place, the purple placeholder cells (interest expense on the income statement, depreciation on the cash flow statement, net PP&E and debt on the balance sheet) can all be linked to their correct calculated sources.

Colour code inputs and formulas. The standard convention is blue text for hardcoded input values and black text for formula cells that reference other cells. A model reviewer can immediately see which cells are assumptions and which are calculations. Every number that drives the model should be an input in the assumptions tab, not a hardcoded value inside a formula.

Assumptions tab first. All key drivers live in a single dedicated tab. Revenue growth rate, COGS percentage, capex assumptions, interest rate, and tax rate: all in one place with clear labels. Scenarios are run by changing values in this tab; the three statements update automatically.

Keep formulas simple and auditable. A formula that can be read and understood in 10 seconds is more valuable than an elegant nested formula that takes 10 minutes to audit under time pressure. Where needed, break complex calculations into intermediate cells with labels.

Consistent number formatting throughout. Use the same unit scale throughout (thousands or millions, not both). Apply consistent decimal places: one decimal for large numbers, two for per-share figures, three for share counts and percentages.

No 3-statement model should be presented or used for decision-making without running these three checks.

Check 1: The Balance Sheet Check. Add a check row: Total Assets minus Total Liabilities minus Total Equity. This must equal zero for every period. If it does not, there is a broken link somewhere in the retained earnings calculation, PP&E, debt, or working capital sections. Do not advance until this check passes for all periods

Check 2: The Cash Check. The ending cash balance on the cash flow statement must exactly equal the cash line on the balance sheet for the same period. This is the most common error in 3-statement models: the cash flow statement calculates correctly but the balance sheet cash cell references the prior period or a hardcoded value rather than the cash flow output. A check cell that subtracts one from the other flags this immediately.

Check 3: The Retained Earnings Check. Retained earnings on the balance sheet must equal prior period retained earnings plus net income minus dividends for every period. This verifies that the income statement net income is flowing correctly into the balance sheet equity section and that the dividend assumption is being applied.

The practical approach is to create a small checks dashboard in a corner of the model showing green for pass and red for fail for each check. When presenting the model, a clean all-green checks section builds immediate confidence. A red flag prompts investigation before the model is used.

A 3-statement financial model is not just a reporting exercise. It is the infrastructure that allows a finance team to test decisions before making them, understand the full financial consequences of a change in any assumption, and plan with coherence across profitability, capital, and liquidity simultaneously.

The steps in this guide follow the sequence that professional modellers use: assumptions first, income statement before balance sheet, cash flow statement building on both, supporting schedules resolving the items that require separate calculation, and validation checks confirming the model is correctly integrated.

Building the first model takes time. Maintaining a well-structured model with clear assumptions, colour-coded cells, and passing validation checks takes significantly less time than maintaining a poorly structured one, and produces materially better decisions as a result.