Financial Modeling and Valuation

4.

Building a Three-Statement Financial Model

4.1.

Model Setup and Structure

4.1.1.

Input and Assumptions Sheet

4.1.1.1.

Key Business Assumptions

4.1.1.2.

Scenario Inputs

4.1.1.3.

Market Assumptions

4.1.1.4.

Operational Assumptions

4.1.2.

Historical Financials Sheet

4.1.2.1.

Data Import Methods

4.1.2.2.

Data Organization

4.1.2.3.

Historical Analysis

4.1.3.

Projection Sheets

4.1.3.1.

Income Statement Projections

4.1.3.2.

Balance Sheet Projections

4.1.3.3.

Cash Flow Statement Projections

4.1.4.

Supporting Schedules

4.1.4.1.

Depreciation Schedule

4.1.4.2.

4.1.4.3.

Working Capital Schedule

4.1.5.

Output Summary Sheet

4.1.5.1.

Key Performance Metrics

4.1.5.2.

Valuation Outputs

4.1.5.3.

4.2.

Gathering and Inputting Historical Data

4.2.1.

4.2.1.1.

4.2.1.1.1.

10-K Annual Reports

4.2.1.1.2.

10-Q Quarterly Reports

4.2.1.1.3.

8-K Current Reports

4.2.1.2.

Company Annual Reports

4.2.1.3.

Financial Databases

4.2.2.

Data Cleaning and Normalization

4.2.2.1.

Adjusting for Non-Recurring Items

4.2.2.2.

Standardizing Line Items

4.2.2.3.

Currency Adjustments

4.2.2.4.

Accounting Method Adjustments

4.2.3.

Historical Analysis

4.2.3.1.

Calculating Growth Rates

4.2.3.2.

Margin Analysis

4.2.3.3.

4.2.3.4.

4.3.

Forecasting Assumptions and Drivers

4.3.1.

Identifying Key Business Drivers

4.3.1.1.

4.3.1.2.

4.3.1.3.

Market Share Factors

4.3.1.4.

Competitive Dynamics

4.3.2.

Revenue Forecasting Methods

4.3.2.1.

Top-Down Approach

4.3.2.2.

Bottom-Up Approach

4.3.2.3.

Market-Based Approach

4.3.3.

Cost and Expense Assumptions

4.3.3.1.

Gross Margin Projections

4.3.3.2.

Operating Margin Assumptions

4.3.3.3.

Fixed vs Variable Cost Analysis

4.3.3.4.

Expense Growth Rates

4.3.4.

Capital Investment Assumptions

4.3.4.1.

Maintenance Capital Expenditures

4.3.4.2.

Growth Capital Expenditures

4.3.4.3.

Asset Replacement Cycles

4.3.5.

Working Capital Assumptions

4.3.5.1.

Days Sales Outstanding

4.3.5.2.

Days Inventory Outstanding

4.3.5.3.

Days Payable Outstanding

4.3.5.4.

Seasonal Variations

4.4.

Projecting the Income Statement

4.4.1.

Revenue Forecasting

4.4.1.1.

Revenue Line Items

4.4.1.2.

Growth Rate Application

4.4.1.3.

Seasonality Adjustments

4.4.2.

Cost of Goods Sold Forecasting

4.4.2.1.

Variable Cost Components

4.4.2.2.

Fixed Cost Components

4.4.2.3.

Margin Analysis

4.4.3.

Gross Profit Calculation

4.4.4.

Operating Expense Forecasting

4.4.4.1.

Selling, General & Administrative

4.4.4.2.

Research & Development

4.4.4.3.

Other Operating Expenses

4.4.5.

EBITDA Calculation

4.4.6.

Depreciation and Amortization

4.4.7.

EBIT Calculation

4.4.8.

Interest Expense Projection

4.4.9.

Tax Calculation

4.4.9.1.

Effective Tax Rate

4.4.9.2.

Tax Shield Benefits

4.4.10.

Net Income Calculation

4.5.

Projecting the Balance Sheet

4.5.1.

Building Supporting Schedules

4.5.1.1.

Property, Plant & Equipment Schedule

4.5.1.1.1.

Beginning Balance

4.5.1.1.2.

Capital Expenditure Additions

4.5.1.1.3.

Depreciation Calculation

4.5.1.1.4.

Asset Disposals

4.5.1.1.5.

4.5.1.2.

Working Capital Schedule

4.5.1.2.1.

Accounts Receivable

4.5.1.2.2.

4.5.1.2.3.

Accounts Payable

4.5.1.2.4.

Other Working Capital Items

4.5.1.3.

4.5.1.3.1.

Debt Balances by Type

4.5.1.3.2.

Interest Rate Assumptions

4.5.1.3.3.

Amortization Schedule

4.5.1.3.4.

New Debt Issuance

4.5.1.4.

Equity Schedule

4.5.1.4.1.

Retained Earnings Rollforward

4.5.1.4.2.

Dividend Payments

4.5.1.4.3.

Share Issuance and Repurchase

4.5.2.

Asset Projections

4.5.2.1.

4.5.2.2.

Non-Current Assets

4.5.2.3.

4.5.3.

Liability Projections

4.5.3.1.

Current Liabilities

4.5.3.2.

Non-Current Liabilities

4.5.3.3.

Total Liabilities

4.5.4.

Equity Projections

4.5.4.1.

4.5.4.2.

Retained Earnings

4.5.4.3.

Other Equity Components

4.5.5.

Balance Sheet Balancing

4.5.5.1.

Ensuring Assets Equal Liabilities Plus Equity

4.5.5.2.

Cash Plug or Debt Plug

4.5.5.3.

Troubleshooting Imbalances

4.6.

Projecting the Cash Flow Statement

4.6.1.

Cash Flow from Operations

4.6.1.1.

Net Income Starting Point

4.6.1.2.

Non-Cash Adjustments

4.6.1.2.1.

Depreciation and Amortization

4.6.1.2.2.

Stock-Based Compensation

4.6.1.2.3.

Other Non-Cash Items

4.6.1.3.

Working Capital Changes

4.6.1.3.1.

Change in Accounts Receivable

4.6.1.3.2.

Change in Inventory

4.6.1.3.3.

Change in Accounts Payable

4.6.1.3.4.

Other Working Capital Changes

4.6.2.

Cash Flow from Investing

4.6.2.1.

Capital Expenditures

4.6.2.2.

4.6.2.3.

4.6.2.4.

Other Investing Activities

4.6.3.

Cash Flow from Financing

4.6.3.1.

Debt Issuance and Repayment

4.6.3.2.

Equity Issuance and Repurchase

4.6.3.3.

Dividend Payments

4.6.3.4.

Other Financing Activities

4.6.4.

Net Change in Cash Reconciliation

4.6.5.

Cash Flow Statement Checks

Previous

3. Spreadsheet Skills for Financial Modeling

Go to top

Next

5. Valuation Methodologies