Financial Modeling for Corporate Finance

Starting with basic modeling functions, the course will progress through complex modeling skills, including lookups, indexing and valuation approaches. In addition to discussing the mechanics of how to build a model it will also cover techniques and calculations specific to corporate finance. Throughout the course we focus on various mehods to valuing assets. Best practices of both design and implementation are covered.

Topics Include: Projecting financial statements, Discounted cash flow modles (DCF), Valuations (DCF, APV and multiples), Net present value (NPV) calculations, Weighted average costs of capital (WACC), Cost of capital (CAPM), Sensitivity analysis of EPS, WACC and Long Term growth, RAYTM and estimating market value of dept, Dilution effect of options & warrants.

No sessions currently available. Contact client services to get the next available date.
This course will benefit business managers, financial managers, investors, corporate finance analysts, and board members.
No advance preparation required.
Students will be able to:
  • Develop hands-on, pro forma modeling skills using Excel
  • Calculate the cost of capital and determine WACC
  • Create DCF models and determine NPV at varying levels of cost of capital
  • Valuation for M&A transactions with sensitivity analysis
Participants should have a solid knowledge of financial statements, the basics of corporate finance, such as TVM, cash flows, and discounting, as well as ExcelTM.
"This course had hands-on, comprehensive, useful models for every-day work."
"The course covered a good range of material."
  • Mergers and Acquisitions - Structuring the Deal
  • Mergers and Acquisitions - Concepts and Theories
    • Scheduling Note
    There will be no class on Monday, February 18, 2008 due to the President's Day holiday.
    Session I

    Introduction

    • Overview of class, objective and subject outline
    • Discussion of practical applications

    Some useful Excel tips for modeling

    • Modeling best practices
    • Tips & tricks that will help you speed up your modeling
    • Creating your own formats, custom lists, etc.

    Practical and Hands-On

    • Linking sheets
    • Drilling down
    • Logical tests (“IF” statements)
    • Build an amortization schedule
    • Summing & counting functions
    • IS functions
    • Errors & warnings
    • Control sheets, naming cells & arrays
    • Vlookups, Hlookups, Choose functions

    Session II

    Pro-Forma Modeling

    • Financial ratios relative to sales
    • Projecting value drivers
    • Internal and external financing
    • The “Plug” – modeling the financing of the firm
    • Effective sensitivity analysis
    • Gordon’s Growth Model
    • Terminal values

    Case Study Exercises – Analyze Financial Model

    • Estimating sales growth, aggregating domestic and international sales
    • Financial ratio analysis
    • Complete a DCF valuation model. Compare your results to the most recent traded price of select company.

    Case Study Exercises

    • Pro-forma statements
    • *Modeling debt or cash as the “plug”
    • *Debt repayment schedule
    • Complete a DCF valuation model. Compare your results to the most recent traded price of select company.

    Session III

    Cost of Capital and CAPM

    • Determine the cost of capital using dividend history
    • * Seven steps using CAPM
    • * Four steps using the PE multiple
    • Calculate the weighted average cost of capital
    • Four models to estimate the cost of debt
    • Valuation of interest tax shield

    Exercise 1 Return on equity

    • Calculate the beta of a selected company
    • Determine the market risk premium
    • Determine the risk free rate

    Exercise 2 Seven steps using CAPM to determine the cost of capital

    • Calculate beta of assets
    • Calculate the return on assets
    • Determine the implied beta of debt

    Exercise 3 Implied risk premium in the current P/E multiple

    • Estimate the market risk premium in forward looking prices
    • Calculate beta of assets
    • Calculate the return on assets
    • Determine the implied beta of debt

    Exercise 4 Four steps using the P/E multiple to determine the cost of capital

    • Determine the average payout ratio
    • Calculate the return on assets

    Case Study Exercises

    • Analyze a financial model for a selected company
    • Calculate the financial ratios to develop the pro-forma model

    Session IV

    Accrual Accounting Valuation

    • Measure of economic profit
    • Residual earnings capture the value added in a strategy
    • Valuation is driven by ROCE and growth in book values

    Exercise 1 Accrual accounting valuation of a selected company #1

    • Calculate the price per share
    • Book value per share
    • Return on common equity
    • Residual earnings
    • Continuing Value
    • Estimate growth rate in residual earnings

    Exercise 2 Accrual accounting for selected company #2

    • Calculate the price per share
    • Book value per share
    • Return on common equity
    • Residual earnings
    • Continuing Value
    • Estimate growth rate in residual earnings

    Valuation Using Multiples

    • Basic and leading P/E multiples
    • Choosing logical multiples for an industry
    • Procedures for valuation using multiples

    Session V

    Discussion

    • Control premiums and minority discounts
    • Acquisition transactions
    • Adjustments for non-operating assets and non-recurring items

    Special Valuation Issues

    • Valuing high growth stocks
    • PEG ratio and PSG ratio
    • Determine implied profit margins
    • Three-stage valuation model
    • Valuation of internet stock

    Exercise 6 Implied profit margin

    • Calculate the implied profit margin in the P/S multiples using following assumptions:

    Exercise 7 Valuation model

    • Determine the price of a growing company, using PEG=1; required rate of return = 12%, implied long-term EBIT margin, and Tax Rate = 30%

    Exercise 8

    • Develop an accrual accounting valuation model for a selected company
    • *Use the projected financial statements
    • *Calculate the residual earnings and terminal value
    • *Calculate the price per share
    • Value selected company, using multiples
    • *Equity multiples and whole firm multiples

    Session VI

    Warrants and Executive Stock Options

    • Warrant pricing model
    • Warrants issues

    Exercise 1 Warrant valuation

    • Calculate the value of the outstanding warrants to account for the dilution effect
    • Determine the exercise price; the risk-free interest rate; the asset volatility; and the time to maturity

    Exercise 2 Value a selected company's outstanding warrants

    • Calculate the value for each range of exercise prices
    • Adjust for missed dividends payments
    • Example 1: Carry forwards/backwards
    • Example 2: Calculating deferred taxes

    Debt Valuation

    • Valuation of outstanding debt
    • Estimating the appropriate rating

    Example 3 Debt rating analysis

    • Bond valuation

    Exercise 4 Expected debt return

    • Calculate the expected cash flow and the return on a one-year bond using the following assumptions: the probability of not defaulting; the recovery rate of the face value; the implied bond beta

    Exercise 5 Expected cash flow and return on debt

    • Calculate the return on debt using a transition matrix. Determine the weighted average cost of debt for all outstanding debt.

    Presentation of selected company’s Valuation, and Conclusion

    • DCF assumptions and valuation

    Clients who register for this course will receive a complimentary 6 month subscription to the Financial Times and FT.com. The Financial Times is the world's most respected financial newspaper providing a broad assessment on finance, business and the industrial sector. Subscriptions will start within 6-8 weeks of the application process, and are limited to one per client. For questions about your subscriptions call 800-628-8088 or email uscirculation@ft.com. US and Canada enrollees only.

    Lunch included for all students taking day classes.