Basic corporate valuation usually relies on the creation of a dynamic discounted cash flow model. Building one from a blank worksheet, using someone else’s, or trying to manipulate one that is already created is difficult without a thorough knowledge of technical spreadsheet skills and financial concepts. This course provides participants with a thorough understanding of every component needed to build and use a powerful discounted cash flow model. In addition, major concepts are expanded upon to ensure that the assumptions being used are correct and the modeling is completed in the most accurate fashion.

Learning valuation and modeling is best done with a combination of theory, demonstration and hands-on practice; each section below is designed in such a way. Almost all exercises are Excel based and contribute to understanding a fully functioning, dynamic model. The following models will be reviewed and included in the course:

  • Basic Corporate Finance Model
  • Advanced Corporate Finance Model
  • Data Stratification Tool
Day One

Introduction to Corporate Financial Modeling

  • Purpose of Financial Modeling
  • Basic Model Layout & Design
  • Dates & Timing: The Framework for a Model

Excel Finance Fundamentals

  • Commonly Used Functions
  • Power Function Combinations Exercise: Working with Functions, Combinations, and Techniques

The Financial Statements: The Income Statement

  • Analyzing Historical Information for the Income Statement
  • Projecting Information for the Income Statement
  • Focus on Important Drivers for the Income Statement

Revenue Analysis and Projection

  • Historical Analysis of Revenue Using Multiple Methods Exercise: Revenue Expectation Using Geometric Mean Advanced Exercise: Using Regression to Estimate Revenue
  • Managing Growth Rate Scenarios Exercise: Implementing a Scenario Selection System

The Financial Statements: The Balance Sheet

  • Organization and Structure of the Balance Sheet
  • Major Items on the Balance Sheet
  • Connections to the Income Statement

Asset Assumptions and Balance Sheet Integration

  • Capital Expenditures: Setting up a schedule for CAPEX
  • Depreciation Calculations
  • Intangibles: Setting up a schedule for Intangibles
  • Intangible Amortization Calculations
Day Two

Liability Assumptions and Balance Sheet Integration

  • Debt: Setting Up a System for Proper Amortization
  • Understanding Debt Priority and Repayment Exercise: Sequential versus Pro Rata
  • Integrating Debt into a Model
  • Revolvers and Liquidity Facilities
  • Calculating Tax & Integrating Taxes Payable

Balancing a Projected Model

  • Interaction between the Income Statement and Balance Sheet
  • Asset and Liability Plugs Exercise: Automating the Balance of Assets and Liabilities & Equity

Working Capital and Cash Flow

  • Understanding Working Capital in the Model

The Financial Statements: The Cash Flow Statement

  • Building a Cash Flow Statement and Accounting for All Cash Exercise: Verifying the Movement of Cash Each Period

Discounted Cash Flow Valuation

  • The Cost of Equity, Cost of Capital, & WACC
  • Terminal Value: Various Methodologies & Calculations
  • Free Cash Flow to the Firm (FCFF)
  • Free Cash Flow to Equity (FCFE)
Day Three

Internal Checks & Output Reporting

  • Internal Tests to Ensure Model Integrity
  • Appropriate Outputs for a Corporate Finance Model
  • Controlling the Output System

Important Ratios

  • Understanding the Difference and Relevance of Ratios
  • Relative Value Analysis

Capital Structuring Applications

  • Setting Up a Model for Capital Structure Analysis Case Study & Exercise: Optimal Debt & Equity. Optimal Capital Structure in the Valuation Model

Mezzanine and Leveraged Finance

  • Calculating the Value of Options and Warrants
  • Convertible Debt Calculations
  • PIK Notes and Mezzanine Finance Packages

Alternative Valuation Methods

  • Dividend Discount Model (DDM)
  • Economic Value Added (EVA)
  • Adjusted Present Value (APV)