Financial Modeling Boot Camp

I. Course Overview and Introductions

In this critical introduction to the course, we'll discuss why financial modeling is important, the strategies you need to incorporate to create a solid financial model, how you need to "think" and what tools you will need to construct a complete working model.

A. Instructor/Student Introductions
    1. Determining skill levels
    2. Student's goals
    3. Course goals and direction

B. What is and why Financial Modeling
    1. Linked/Integrated Financial Statements
    2. Historical/Future Projections
    3. Operating and Financial Assumptions

C. The Art and Science of Accurate Financial Modeling
    1. Model like an Analyst, think like a Managing Director
    2. Financial Market Awareness
    3. Client's Needs
    4. Strategic Analysis
    5. "Outside the box" Due Diligence
    6. Original/Thoughtful Research

D. Relevant Financial Websites and Support Groups
    1. Yahoo Finance
    2. SmartMoney MarketMap
    3. Google Alerts
    4. Relevant Industry Websites
    5. Bookmark File

II. A Thorough Review of Accounting Principles
Implied in Financial Modeling

A financial model is an interactive representation of financial statements, interwoven into a seamless, interactive valuation tool, providing you an automated method for calculating accurate assumptions. In this segment, we'll review all of the key financial concepts and accounting principles that you need to understand, what each represents and how each piece interacts and affects your modeling techniques and valuations.

A. Balance Sheet Components
B. Income Statement Components Review
C. Statement of Cash Flows
D. Ratio Calculation and Analysis
E. EBITDA vs. Earnings vs. Cash Flow
F. Market Capitalization vs. Book Value
G. Key SEC Filings and Source Documents
H. Importance of Footnotes

III. Excel Functions/Skills and Financial Modeling

In this hands-on section, working with the instructor, you'll cover key Excel functions, understanding what modeling tools are available in Excel, short cuts you can use, customizations that you'll want to make. You'll end by knowing the capabilities of Excel and how to navigate and work comfortably.

A. Color Coding - Making Spreadsheets Understandable
B. Using Cumulative Checks and SUM Bars to Avoid
     Errors.
C. Yes/No Triggers for Alternate Transaction Scenarios
D. Working with Circular References
E. Catching Errors in Formulas
F. Using MIN and MAX for Optional Debt Repayment
    Formulas
G. Using Custom Number Formats
H. Formatting Numeric Values as Text
I. Mixing Links and Text via the '&' Function
J. Converting Numeric Values using the TEXT Function
K. Tracking Precedents/Dependents in Native Excel
L. Jumping to Newly Defined Names
M. Creating Data Tables to Automate Repetitive
     Calculations
N. Using OFFSET to Run Scenario Analyses.

Engaging Case Studies and Group Exercises

Not only intense, this class can also be fun. You'll enjoy working with other members of the group on in-class exercises and participate in discussions of some interesting case studies. Discussions of real-world examples and business situations will help enforce what you are covering and cement your understanding of the various course topics.

IV. Building the Financial Model

It's time to incorporate your skills and financial knowledge. In this segment, you'll create a working model in class. With the instructor, you'll incorporate your financial statements, find the "bugs", problem solve, enhance your model, use polishing techniques and ultimately create a presentable and reliable model.

A. Setting up and Building the Integrated Financial Model
    from Scratch.
    1. Linking of Income Statement, Balance Sheet,
        Statement of Cash Flows

B. Establishing the flow of a Financial Model
    1. Historical & Projected Operating Assumptions
        a. Computing Revenue Growth
        b. Calculating Cost of Goods Sold
        c. Depreciation of Capital Expenditures
        d. Amortization of Intangibles and Financial Costs
        e. Capital Expenditures of Long Term Assets
    2. Analysis of Working Capital Assumptions
        a. Accounts Receivable % of sales, As a Measure
            of Collection Prowess
        b. Days Receivable, and Its Relation to Sales Growth
        c. Inventory % of COGS, Measuring Inventory
            Obsolescence
        d. Inventory Turns, Measuring Management
            Efficiency
        e. Calculating Prepaid Expenses % of COGS
        f. Accounts Payable % of COGS, and Its Relation to
           Financial Float
        g. Calculating Accrued Expenses % of COGS
    3. Latest Balance Sheet
    4. Income Statement (Historical and Projected without
        Average Interest)
    5. Forecasting the Balance Sheet - Up to Debt & Cash
    6. Creating a Cash Flow Statement
    7. Understanding & Creating a Debt Schedule -
        Sources and Uses of Funds
    8. Completed Forecast, Balance Sheet, Income
        Statement, Cash Flow Statement

C. Valuation
     1. Discounted Cash Flow (DCF) Valuation Using
         Excel's Table Function to Calculate Valuation
         Ranges
      2. Using Comparable Company Analysis as a Method
          of Relative Evaluation
      3. Using Precedent Transactions as a Basis for
          Takeover Values
      4. Calculating Enterprise Value for Valuing Firms
          According to the Indirect Method
      5. Computing Free Cash Flow for Use in
          Enterprise Valuation
      6. Using the Capital Asset Pricing Model to Estimate
          Equity Discount Rates
      7. Calculating Weighted Average Cost of Capital
          to Properly Estimate Enterprise Value Discount
          Rates
      8. Using Implied Multiple Analysis to Develop
          Valuation Intuition

D. Advanced Modeling/Valuation Techniques
    1. Using Scenario Analysis to Model Best, Worst
        and Standard Business Cases
    2. Using Operating Buildup to Create Detailed
        Revenue and Expense Projections
    3. Average Interest - the only valid circular reference
        in a Financial Model
    4. Sensitivity Analysis to Determine Model
        Robustness

E. Checks and Balances to Eliminate Errors and
    "Rookie" Mistakes

F. Creating a Summary / Creating a Cover Page

G. Recap of Websites and Support Groups

An Interactive Workshop - Bring a Laptop

In this interactive workshop, you will be supplied and instructor-developed modeling template. You'll explore the structure, algorithms, data tables, graphs and other critical components of a Financial Model hands-on, in class! You'll understand the interaction of model components and you'll have the ability to confidently perform analytical work using your model. What's more, you'll take this new skill back to the office to share with your co-workers and staff.