How to Build a 3-Statement Financial Model (Step-by-Step)

Learn the mechanics of linking the income statement, balance sheet, and cash flow statement to create a dynamic model for forecasting, valuation, and strategic planning.

A three-statement financial model is the bedrock of corporate finance and valuation. It connects a company's income statement, balance sheet, and cash flow statement into a single, dynamic system. This integrated structure allows you to analyse how operational decisions, such as a new product launch, and financing activities, like raising debt, affect the company's overall financial health. It is the foundation for more advanced analyses, including discounted cash flow (DCF) valuations and merger models.

How the Three Financial Statements Connect

Structuring Your Model in Excel

A well-organised model is less prone to errors. The best practice is to separate inputs from calculations and outputs. Use the following sheet structure:

1. Assumptions: A dedicated sheet for all input drivers.
2. Income Statement: Calculation of profit and loss.
3. Balance Sheet: Calculation of assets, liabilities, and equity.
4. Cash Flow Statement: Calculation of cash movements.

The most important rule is to never hardcode an assumption directly into a formula. Every input, from the revenue growth rate to the tax rate, must be in the Assumptions sheet. This allows for rapid scenario analysis and makes the model easy to audit.

As a first step, create a "Model Start Year" input on the Assumptions sheet. Every date header in your model should then derive from this single cell, ensuring your timeline is always consistent.

// In cell C5 on your timeline sheet
=Assumptions!B1

// In cell D5
=C5+1

Step 1: Building the Income Statement

The income statement shows a company's profitability over a period. The build flows logically from top to bottom.

Revenue and Cost of Goods Sold (COGS)

Avoid using a single growth rate for the entire business. This is a common simplification that masks underlying business dynamics. Instead, break revenue into distinct segments, such as product lines, service types, or geographies. This allows you to model shifts in the revenue mix, which is critical if segments have different margin profiles.

COGS should be directly linked to your revenue segments. What to include depends on the business. For a SaaS company, it includes cloud hosting fees and third-party data services. For a manufacturer, it includes raw materials and direct labour. Sales commissions and general marketing costs are operating expenses, not COGS.

Worked Example: Revenue Mix Shift

Consider a software company selling two products: a high-margin 'Pro' licence and a low-margin 'Lite' licence.

Income Statement Calculation:

As the sales mix shifts towards the higher-margin 'Pro' product, the company's overall gross margin improves, even though individual prices and cost percentages remain constant. A single top-line growth assumption would miss this insight.

Operating Expenses (OpEx)

OpEx includes Sales & Marketing (S&M), Research & Development (R&D), and General & Administrative (G&A). These can be driven as a percentage of revenue, by headcount, or with a fixed growth rate for more stable costs.

• S&M: Often driven as a percentage of revenue.
• R&D: Can be linked to revenue or headcount for product-focused companies.
• G&A: Can be a fixed growth assumption, as it scales less directly with revenue.

After subtracting OpEx from Gross Profit, you arrive at Operating Income, or EBIT (Earnings Before Interest and Taxes).

Depreciation, Interest, and Taxes

These items are placeholders for now, as they depend on schedules we have not yet built.

• Depreciation: This is a non-cash expense that will be calculated in the PP&E schedule on the Balance Sheet.
• Interest Income/Expense: This depends on the average cash and debt balances held during the period. It creates a circular reference that we will resolve in a later step.
• Taxes: Calculated as EBT * Tax Rate. The tax rate itself should be an input on the Assumptions sheet.

The final line is Net Income. This figure is the primary link between the Income Statement and the other two statements.

Step 2: Building the Balance Sheet

The Balance Sheet presents a snapshot of a company's assets, liabilities, and equity at a single point in time. Its fundamental equation, Assets = Liabilities + Equity, must always hold true.

Assets

The asset side is typically split between current and non-current assets.

Working Capital: AR and Inventory Accounts Receivable (AR) and Inventory are key components of working capital. They should not be static; they must scale with the business's activity. Use standard efficiency ratios as drivers.

• Days Sales Outstanding (DSO): The average number of days it takes to collect payment after a sale.
• Days Inventory Outstanding (DIO): The average number of days the company holds inventory before selling it.

The formulas are:

// Accounts Receivable
= (Annual Revenue * DSO) / 365

// Inventory
= (Annual COGS * DIO) / 365

Worked Example: Calculating AR If a company has annual revenue of £2,500,000 and a DSO of 45 days, its Accounts Receivable balance is:

AR = (£2,500,000 * 45) / 365 = £308,219

Property, Plant & Equipment (PP&E) PP&E requires a supporting schedule to track the flow of assets.

Closing PP&E = Opening PP&E + Capital Expenditures (CapEx) - Depreciation

• Opening PP&E: The prior period's closing balance.
• CapEx: A key assumption from your Assumptions sheet, often expressed as a percentage of revenue or a fixed amount for expansion.
• Depreciation: Calculated based on the asset base. For simplicity, you can use a percentage of the average PP&E balance. This calculated depreciation figure is the number that populates the Income Statement.

Cash Cash is the final plug. Its value is derived from the Cash Flow Statement. Do not try to calculate it on the Balance Sheet.

Liabilities and Equity

Working Capital: AP Accounts Payable (AP) represents money owed to suppliers. It is driven by Days Payable Outstanding (DPO).

• Days Payable Outstanding (DPO): The average number of days it takes for the company to pay its invoices.

// Accounts Payable
= (Annual COGS * DPO) / 365

Debt Like PP&E, debt requires a schedule. This is essential for correctly modelling interest expense and distinguishing between short-term and long-term portions.

Closing Debt = Opening Debt + New Debt Raised - Repayments

A common mistake is failing to reclassify debt. The portion of long-term debt due within the next 12 months must be shown as a current liability.

Retained Earnings This is the second critical link in the model. Retained Earnings represents the cumulative net income of the company less any dividends paid to shareholders.

Closing Retained Earnings = Opening Retained Earnings + Net Income - Dividends

• Opening Retained Earnings: The prior period's closing balance.
• Net Income: Linked directly from the bottom line of the Income Statement.
• Dividends: Often driven by a 'Dividend Payout Ratio' from the Assumptions sheet.

Step 3: Building the Cash Flow Statement

The Cash Flow Statement (CFS) reconciles the Net Income from the Income Statement with the actual change in cash on the Balance Sheet. We use the indirect method, which is most common in financial modelling.

Cash Flow from Operations (CFO)

CFO starts with Net Income and adjusts for non-cash items and changes in working capital.

1. Start with Net Income: Link from the Income Statement.
2. Add Back Non-Cash Expenses: The largest of these is Depreciation, linked from your PP&E schedule.
3. Adjust for Changes in Working Capital: This is where many models break. The change is the difference between the current period's balance sheet value and the prior period's. The sign convention is critical.

An increase in an asset like AR means the company has recognised revenue but not yet received the cash, so it is a negative adjustment. An increase in a liability like AP means the company has recorded an expense but not yet paid for it, preserving cash, so it is a positive adjustment.

Cash Flow from Investing (CFI) and Financing (CFF)

• CFI: This is primarily driven by CapEx. The value comes from your PP&E schedule and is shown as a negative figure (a use of cash).
• CFF: This section tracks cash related to financing activities.
  • Debt: Link new debt raised (inflow) and repayments (outflow) from the debt schedule.
  • Equity: Model any new equity issued (inflow).
  • Dividends: Link from your Retained Earnings calculation (outflow).

The Final Connection

Summing the three sections gives you the Net Change in Cash for the period.

Closing Cash = Opening Cash + Net Change in Cash

This Closing Cash balance is the figure that links back to the Cash line item on the Balance Sheet for the current period.

Step 4: Balancing the Model and Handling Circularity

With all the links in place, the model should balance.

The Balance Check At the bottom of your Balance Sheet, add a check formula in every column:

= Total Assets - (Total Liabilities + Total Equity)

This cell must equal zero in every period. If it does not, there is an error in one of your links. Common culprits are an incorrect sign in the working capital cash flow adjustment or a broken link from the CFS to the Balance Sheet cash line.

Solving the Interest Circularity Interest expense is calculated on the debt balance, and interest income is calculated on the cash balance. But the cash and debt balances are affected by Net Income, which itself is affected by interest. This creates a circular reference.

• Option 1 (Simple, No Circularity): Calculate interest based on the prior period's closing balance. This is simple, robust, and avoids circularity. It is slightly less precise but often sufficient.

  // Interest Expense in Year 2
  = Interest Rate * Closing Debt Balance from Year 1
  

• Option 2 (Precise, Requires Iteration): Calculate interest based on the average of the opening and closing balances for the period. This is more accurate but creates a circular reference. To handle this, you must enable iterative calculations in Excel (File > Options > Formulas > Enable iterative calculation).

  // Interest Expense in Year 2
  = Interest Rate * AVERAGE(Opening Debt Year 2, Closing Debt Year 2)
  

Common Mistakes to Avoid

Building a three-statement model requires precision. Here are some of the most common pitfalls and their impact. For a full breakdown, see our guide to common financial modelling mistakes.

1. The Static Retained Earnings Formula: Using a cumulative formula like =SUM(Net Income) for Retained Earnings is incorrect. It double-counts profits and ignores dividends. The only correct way is Closing RE = Opening RE + Net Income - Dividends.
2. Interest on a Proxy: Calculating interest expense as a simple growth rate (e.g., Prior Year Interest * 1.05) completely disconnects it from the actual debt obligations on the balance sheet. If debt is repaid, interest expense should fall. This method will overstate interest and understate net income.
3. Depreciation on New Assets Only: Forgetting to depreciate the existing asset base from the opening balance sheet. Depreciation expense will be understated, and Net Income and assets will be overstated.
4. Inconsistent Sign Conventions: Using a negative sign for an AR increase in one formula and a positive sign in another. This will break your CFO calculation and unbalance the model. Be systematic.
5. Unconnected Assumptions: Including a "Dividend Payout Ratio" on the Assumptions sheet but having a hardcoded 0 in the dividend formula on the CFS. This makes the assumption useless and is a frequent audit finding.