Basic corporate analysis and valuation, whether for one’s own company or its investments, 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 skill and solid financial concept understanding. This course provides participants with a thorough understanding of every component to building and using 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 way possible.
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.
Introduction to Corporate Modeling and Analysis
I. What is Financial Modeling?
II. Why Professionals Build Models
III. Which Models to Implement
IV. Basic Model Layout and Design
Working with a Bad Model
Building a ToolKit
I. Time Value of Money Functions
II. Mathematical/Statistical Functions
III. Logical Functions
IV. Lookup Functions
V. Naming Ranges/Data Validation Lists
VI. Function Combinations
Time Value of Money Functions
Mathematical/Statistical Functions
Logical Functions
Lookup Functions
Naming Ranges/ Data Validation Lists
Scenario Selectors: The First Step in a Model
I. Overview of a Scenario Selector
II. Specific Scenario Selector Setup & Use
Exercise: Scenario Selectors
Dates and Timing: The Framework for a Model
I. Overview of Dates and Timing Set Up
II. Specific Date and Timing Functions and Organization
Exercise: Dates & Timing
Core Concepts: Revenue and Cost Build
I. Overview of Using a Bottom Up Methodology for Revenue and Costs
II. Fixed Costs vs. Variable Costs
Exercise: A Bottom Up Approach
III. Integrating Revenue and Costs into a Model
Core Concepts: Capex and Deprecation
I. Capital Expenditures: Setting up a schedule for CAPEX
Exercise: CAPEX Schedules
II. Depreciation Calculations
Exercise: Depreciation Functions & Methodology
III. Integrating Capex & Depreciation into a Model
Core Concepts: Current and Long-Term Liabilities
I. Basic Fixed Rate Amortization
Exercise: Amortizing a Fixed Rate Loan
II. Floating Rate Amortization
Exercise: Amortizing a Floating Rate Loan
III. Revolving Credit Facilities
Exercise: Setting Up All Aspects of a Revolving Credit Facility
IV. Integrated Debt into a Model
Core Concepts: Equity
I. The Various Forms of Equity Ownership
II. Calculating Share Ownership
Exercise: Calculating Equity Ownership by Type
Core Concepts: Working Capital
I. What Working Capital is and Why it is Important
II. The Cash Conversion Cycle
Exercise: Calculating Working Capital from the Cash Conversion Cycle
Exercise: Calculating the Net Change in Working Capital
The Financial Statements: Connecting Everything
I. Income Statement, Balance Sheet, and Cash Flow Statement
II. How Each Statement Draws from the Core Concepts, Calculates, and Connects
Discounted Cash Flow Valuation
I. Free Cash Flow to the Firm (FCFF)
Exercise: Calculating FCFF in the Model
II. Free Cash Flow to Equity (FCFE)
Exercise: Calculating the FCFE
III. The Cost of Equity, Cost of Capital, & WACC
Exercise: Calculating the Correct Rates
IV. Calibrating Other Discounts: Size, Liquidity, Sovereign, and Control Risk
V. Terminal Value: Various Methodologies & Calculations
Exercise: The Differences & Sensitivity of Terminal Value Methods
Model Integrity: Ensuring Everything is Correct
I. Building Checks into the Model to Make Sure Calculations are Correct
II. Balancing the Balance Sheet Automatically
Exercise: Balance a Balance Sheet
III. Cash Checks
Output Reporting: ensuring Everything is Understandable
I. Changing the Periodicity of Results
II. Building Snapshot Views
III. Relevant Metrics and Ratios
IV. Charting
Capital Structuring Applications
I. Setting Up a Model for Capital Structure Analysis
Case Study & Exercise: Optimal Debt & Equity
II. How to Calculate the Optimal Capital Structure for a Company