Introduction to Financial Modeling

1
Introduction to Financial Modeling

• Brought to you by
• The EDS Financial Trading and Technology Center
• Teaching Assistants

2
The Financial Modeling Class

• Initiated by the Department of Finance and
  Assembled by FTTC Proctors
• Two classes 1 hour 1 hour exercise
• Financial Modeling I (w/ exercise)
• Financial Modeling II (w/ exercise)
• Conducted two or three times per semester

3
Class Outline

• Introduction to Financial Modeling
• Definition
• Types of Models Used
• Format
• Tips for Modeling in Excel
• Structuring the Model
• Making the Model Easy to Use
• Some Excel Tools and Keyboard Shortcuts
• Example Build-out of Several Financial Models
• DCF
• DDM
• WACC
• Comparables
• QA
• Break
• Practical Exercise (1 hour)

4
What Does a Financial Model Do?

• Compute many different types of finance problems
• Bond immunization
• Portfolio weightings
• Equity valuations
• Valuations of mergers, acquisitions, or
  divestitures
• Forecast financial performance
• Earnings growth
• Profitability
• Options
• Analyze different economic and financial
  scenarios
• Options payoffs given different expirations
• Sales growth given different economies/business
  cycles
• Profitability given different product demand
  quantities

5
Types of Financial Models (Investment Banking)

• Dynamic Forecast Model
• Merger Consequences Model - Mergers,
  acquisitions, divestitures
• Leveraged Buy-Out (LBO) Model
• Discounted Cash Flow (DCF) Model
• Recapitalization Model

6
Some Examples of Financial Models
7
Some Examples of Financial Models
8
Some Examples of Financial Models
9
What Makes a GOOD Financial Model?

• Flexibility Design the model so that it can be
  modified easily.
• ?? Tip any assumption that was made to create
  the model, such as revenue growth, or discount
  rate, should be easy to change
• Visibility Create the model under the premise
  that anyone should be able to review/audit and
  understand it quickly.
• ?? Tip refrain from embedding hard coded data in
  formulas or hiding/grouping rows/columns
• Organization and Design Organize workbook so
  that worksheets flow together logically.
• ?? Tip group all assumptions on separate tab
  name all other tabs according to the information
  or function each contains (e.g. income statement,
  balance sheet, depreciation schedule, output,
  etc.)

10
What Makes a GOOD Financial Model?
Depth Construct model to reflect the reality of
the business while making as few simplifying
assumptions as possible. ?? Tip build separate
schedules for capital expenditures, depreciation,
debt, etc. Realistic Depiction of Business
Operations Choose assumptions that are
reasonable and defensible. ?? Tip most
assumptions will be determined by a senior banker
or researcher, but research or industry reports
can provide guidance Formatting Use formatting
for a purpose. ?? Tip use one color for hard
coded data (i.e. constants or historical data),
another for links and a third for formulas
11
Format of a Financial Model

• Data and Assumptions - Inputs
• Calculations
• Solutions to Calculations - Outputs
• Links Between Calculations (Outputs become
  inputs)
• Titles and Notes for Clarification
• Model Maintenance Check (Dashboard)

12
Assumptions
13
Calculations
14
Solutions to Calculations
15
Links Between Calculations
16
Title Notes
17
Model Maintenance Check (Dashboard)
18
Common Keyboard Shortcuts