Kurum-İçi Eğitim Programları
B07. Financial Modelling
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.
?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 margins
*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 riskfree
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
