Building a 3-Statement Financial Model

A 3-statement financial model is a comprehensive framework that links the income statement, balance sheet, and cash flow statement to provide a holistic view of a company’s financial health. This model is used for various purposes, including forecasting, financial analysis, and investment decision-making. The interconnectivity between these statements ensures that any changes in one statement reflect appropriately in the others, creating a dynamic model.

In this chapter, we’ll explore how to build a 3-statement financial model in Excel, focusing on linking the statements effectively and ensuring consistency across all three components.

What is a 3-Statement Financial Model?

A 3-statement financial model integrates the three core financial statements:

1. Income Statement: Provides an overview of revenue, expenses, and profits.
   

2. Balance Sheet: Shows the company’s assets, liabilities, and equity.
   

3. Cash Flow Statement: Details cash inflows and outflows from operating, investing, and financing activities.
   

By linking these statements, you can forecast financial performance, assess liquidity, and analyse profitability.

Step-by-Step Guide: Building the 3-Statement Model

Step 1: Build the Income Statement

The income statement shows the company's profitability over a specific period. Key components include revenue, cost of goods sold (COGS), gross profit, operating expenses, interest, taxes, and net income.

1. Revenue: Forecast revenue based on historical data or assumptions.
   

2. COGS: Estimate the cost of producing goods or services.
   

3. Gross Profit: Calculate gross profit as Revenue - COGS.
   

4. Operating Expenses: Include selling, general, and administrative (SG&A) expenses.
   

5. Operating Income: Calculate as Gross Profit - Operating Expenses.
   

6. Net Income: Include interest, taxes, and other non-operating items to arrive at net income.
   

Step 2: Build the Balance Sheet

The balance sheet provides a snapshot of the company’s financial position, showing what it owns (assets), owes (liabilities), and its equity at a given point in time.

1. Assets: Include current assets (cash, receivables, inventory) and long-term assets (property, equipment).
   

2. Liabilities: Include current liabilities (accounts payable, short-term debt) and long-term liabilities (long-term debt).
   

3. Equity: Calculate as Total Assets - Total Liabilities. Equity includes items such as common stock, retained earnings, and additional paid-in capital.
   

Step 3: Build the Cash Flow Statement

The cash flow statement tracks the cash inflows and outflows during the period. It is divided into three sections:

1. Operating Activities: Cash generated from core operations (net income adjusted for non-cash items like depreciation and changes in working capital).
   

2. Investing Activities: Cash used in or generated from investments, such as capital expenditures or asset sales.
   

3. Financing Activities: Cash from raising capital (debt or equity) and payments made, such as dividends or loan repayments.
   

Step 4: Link the Statements

The key to an effective 3-statement model is linking the financials:

1. Net Income Links: Link the Net Income from the income statement to the cash flow statement’s operating activities and retained earnings in the balance sheet.

2. Depreciation and Amortisation: These are non-cash expenses on the income statement but should be added back on the cash flow statement.

3. Capital Expenditures: Appear on the cash flow statement under investing activities and affect long-term assets on the balance sheet.

4. Working Capital: Changes in working capital items (such as receivables and payables) affect both the balance sheet and the cash flow statement.

Best Practices for Building a 3-Statement Model

1. Ensure Consistency: The balance sheet must always balance (Assets = Liabilities + Equity).
   

2. Use Formulas: Make sure to use formulas to dynamically link the statements, so any change in one statement reflects in the others.
   

3. Check for Circular References: Avoid circular references, where the result of one formula is dependent on the other in an endless loop. Excel’s iterative calculation feature can help manage this if necessary.
   

Conclusion:

Building a 3-statement financial model is crucial for forecasting and financial analysis. By linking the income statement, balance sheet, and cash flow statement, you create a dynamic model that adjusts automatically as assumptions change.

Next Chapter Preview: In the next chapter, we will explore Discounted Cash Flow (DCF) Modeling for Valuing Companies, focusing on how to use projected cash flows to calculate a company's intrinsic value. Stay tuned for detailed steps on performing DCF analysis and valuation techniques!