Financial Modeling and Valuation

  1. Building a Three-Statement Financial Model
    1. Model Setup and Structure
      1. Input and Assumptions Sheet
        1. Key Business Assumptions
          1. Scenario Inputs
            1. Market Assumptions
              1. Operational Assumptions
              2. Historical Financials Sheet
                1. Data Import Methods
                  1. Data Organization
                    1. Historical Analysis
                    2. Projection Sheets
                      1. Income Statement Projections
                        1. Balance Sheet Projections
                          1. Cash Flow Statement Projections
                          2. Supporting Schedules
                            1. Depreciation Schedule
                              1. Debt Schedule
                                1. Working Capital Schedule
                                2. Output Summary Sheet
                                  1. Key Performance Metrics
                                    1. Valuation Outputs
                                      1. Ratio Analysis
                                    2. Gathering and Inputting Historical Data
                                      1. Data Sources
                                        1. SEC Filings
                                          1. 10-K Annual Reports
                                            1. 10-Q Quarterly Reports
                                              1. 8-K Current Reports
                                              2. Company Annual Reports
                                                1. Financial Databases
                                                2. Data Cleaning and Normalization
                                                  1. Adjusting for Non-Recurring Items
                                                    1. Standardizing Line Items
                                                      1. Currency Adjustments
                                                        1. Accounting Method Adjustments
                                                        2. Historical Analysis
                                                          1. Calculating Growth Rates
                                                            1. Margin Analysis
                                                              1. Ratio Analysis
                                                                1. Trend Analysis
                                                              2. Forecasting Assumptions and Drivers
                                                                1. Identifying Key Business Drivers
                                                                  1. Volume Drivers
                                                                    1. Price Drivers
                                                                      1. Market Share Factors
                                                                        1. Competitive Dynamics
                                                                        2. Revenue Forecasting Methods
                                                                          1. Top-Down Approach
                                                                            1. Bottom-Up Approach
                                                                              1. Market-Based Approach
                                                                              2. Cost and Expense Assumptions
                                                                                1. Gross Margin Projections
                                                                                  1. Operating Margin Assumptions
                                                                                    1. Fixed vs Variable Cost Analysis
                                                                                      1. Expense Growth Rates
                                                                                      2. Capital Investment Assumptions
                                                                                        1. Maintenance Capital Expenditures
                                                                                          1. Growth Capital Expenditures
                                                                                            1. Asset Replacement Cycles
                                                                                            2. Working Capital Assumptions
                                                                                              1. Days Sales Outstanding
                                                                                                1. Days Inventory Outstanding
                                                                                                  1. Days Payable Outstanding
                                                                                                    1. Seasonal Variations
                                                                                                  2. Projecting the Income Statement
                                                                                                    1. Revenue Forecasting
                                                                                                      1. Revenue Line Items
                                                                                                        1. Growth Rate Application
                                                                                                          1. Seasonality Adjustments
                                                                                                          2. Cost of Goods Sold Forecasting
                                                                                                            1. Variable Cost Components
                                                                                                              1. Fixed Cost Components
                                                                                                                1. Margin Analysis
                                                                                                                2. Gross Profit Calculation
                                                                                                                  1. Operating Expense Forecasting
                                                                                                                    1. Selling, General & Administrative
                                                                                                                      1. Research & Development
                                                                                                                        1. Other Operating Expenses
                                                                                                                        2. EBITDA Calculation
                                                                                                                          1. Depreciation and Amortization
                                                                                                                            1. EBIT Calculation
                                                                                                                              1. Interest Expense Projection
                                                                                                                                1. Tax Calculation
                                                                                                                                  1. Effective Tax Rate
                                                                                                                                    1. Tax Shield Benefits
                                                                                                                                    2. Net Income Calculation
                                                                                                                                    3. Projecting the Balance Sheet
                                                                                                                                      1. Building Supporting Schedules
                                                                                                                                        1. Property, Plant & Equipment Schedule
                                                                                                                                          1. Beginning Balance
                                                                                                                                            1. Capital Expenditure Additions
                                                                                                                                              1. Depreciation Calculation
                                                                                                                                                1. Asset Disposals
                                                                                                                                                  1. Ending Balance
                                                                                                                                                  2. Working Capital Schedule
                                                                                                                                                    1. Accounts Receivable
                                                                                                                                                      1. Inventory
                                                                                                                                                        1. Accounts Payable
                                                                                                                                                          1. Other Working Capital Items
                                                                                                                                                          2. Debt Schedule
                                                                                                                                                            1. Debt Balances by Type
                                                                                                                                                              1. Interest Rate Assumptions
                                                                                                                                                                1. Amortization Schedule
                                                                                                                                                                  1. New Debt Issuance
                                                                                                                                                                  2. Equity Schedule
                                                                                                                                                                    1. Retained Earnings Rollforward
                                                                                                                                                                      1. Dividend Payments
                                                                                                                                                                        1. Share Issuance and Repurchase
                                                                                                                                                                      2. Asset Projections
                                                                                                                                                                        1. Current Assets
                                                                                                                                                                          1. Non-Current Assets
                                                                                                                                                                            1. Total Assets
                                                                                                                                                                            2. Liability Projections
                                                                                                                                                                              1. Current Liabilities
                                                                                                                                                                                1. Non-Current Liabilities
                                                                                                                                                                                  1. Total Liabilities
                                                                                                                                                                                  2. Equity Projections
                                                                                                                                                                                    1. Share Capital
                                                                                                                                                                                      1. Retained Earnings
                                                                                                                                                                                        1. Other Equity Components
                                                                                                                                                                                        2. Balance Sheet Balancing
                                                                                                                                                                                          1. Ensuring Assets Equal Liabilities Plus Equity
                                                                                                                                                                                            1. Cash Plug or Debt Plug
                                                                                                                                                                                              1. Troubleshooting Imbalances
                                                                                                                                                                                            2. Projecting the Cash Flow Statement
                                                                                                                                                                                              1. Cash Flow from Operations
                                                                                                                                                                                                1. Net Income Starting Point
                                                                                                                                                                                                  1. Non-Cash Adjustments
                                                                                                                                                                                                    1. Depreciation and Amortization
                                                                                                                                                                                                      1. Stock-Based Compensation
                                                                                                                                                                                                        1. Other Non-Cash Items
                                                                                                                                                                                                        2. Working Capital Changes
                                                                                                                                                                                                          1. Change in Accounts Receivable
                                                                                                                                                                                                            1. Change in Inventory
                                                                                                                                                                                                              1. Change in Accounts Payable
                                                                                                                                                                                                                1. Other Working Capital Changes
                                                                                                                                                                                                              2. Cash Flow from Investing
                                                                                                                                                                                                                1. Capital Expenditures
                                                                                                                                                                                                                  1. Asset Sales
                                                                                                                                                                                                                    1. Acquisitions
                                                                                                                                                                                                                      1. Other Investing Activities
                                                                                                                                                                                                                      2. Cash Flow from Financing
                                                                                                                                                                                                                        1. Debt Issuance and Repayment
                                                                                                                                                                                                                          1. Equity Issuance and Repurchase
                                                                                                                                                                                                                            1. Dividend Payments
                                                                                                                                                                                                                              1. Other Financing Activities
                                                                                                                                                                                                                              2. Net Change in Cash Reconciliation
                                                                                                                                                                                                                                1. Cash Flow Statement Checks

                                                                                                                                                                                                                              Previous

                                                                                                                                                                                                                              3. Spreadsheet Skills for Financial Modeling

                                                                                                                                                                                                                              Go to top

                                                                                                                                                                                                                              Next

                                                                                                                                                                                                                              5. Valuation Methodologies