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