New Lecture 11 Spr 09
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View New Lecture 11 Spr 09 as PDF for free.
More details
- Words: 953
- Pages: 10
Spring 2009
NBA 5060
Lecture 11 – Valuation and Sensitivity Analysis
1. Sensitivity Analysis 2. Multiples 3. Summary of differences between residual income and DCF
Lecture 11
Page 1 of 10
Sensitivity Analysis The inherent unpredictability of the future makes all forecasts subject to error. To deal with this unpredictability, a sensitivity analysis is used to illustrate the variance of price estimates with respect to different forecasting assumptions. The assumptions that you will want to use should be with respect to variables that are likely to have (1) high variance, and (2) significant price impact. Examples: Forecasting Assumption Sales Growth (single year) Sales Growth (first year of trend*) COGS, SG&A exp
Variability High
Price Impact Low/Medium
High
Medium/High
Low
Medium/High
Other Rev/Expenses
Low
Low
Operating Asset Turnovers
Low
Medium/High
Capital Structure
Medium
Medium
Cost of Equity Capital
Medium
High
Terminal Growth Rate
Low/Medium
High
* The first year in an extrapolation between the early years and the last year of the finite forecast horizon.
Lecture 11
Page 2 of 10
Sensitivity Analysis: Example: CBRL terminal growth rate and cost of equity capital:
Current Price = $17.68 (as of 2/20/09)
Terminal Growth Rate
$20.44 1.10% 2.60% 4.10% 5.60% 7.10%
9.55% 26.13 28.60 32.42 39.15 54.14
Cost of Equity Capital 10.55% 11.55% 12.55% 21.64 18.13 15.35 23.18 19.10 15.94 25.43 20.44 16.75 29.04 22.47 17.91 35.81 25.87 19.70
13.55% 13.10 13.45 13.93 14.58 15.53
Look for the proportion of your table that is lower or higher than the current market price.
Lecture 11
Page 3 of 10
Doing sensitivity analysis using Excel’s ‘Table’ function. 1. Copy in your implied price formula into any cell. You can use a white font to ‘hide’ the price.
Terminal Growth Rate
$20.44 1.10% 2.60% 4.10% 5.60% 7.10%
9.55% 26.13 28.60 32.42 39.15 54.14
Cost of Equity Capital 10.55% 11.55% 12.55% 21.64 18.13 15.35 23.18 19.10 15.94 25.43 20.44 16.75 29.04 22.47 17.91 35.81 25.87 19.70
13.55% 13.10 13.45 13.93 14.58 15.53
2. Set up the rows and columns with the dimensions you want and the appropriate values.
3. Select the entire area of your intended table, and then go to Data > What-If Analysis > Data Table… Excel will ask you to input a row and a column. The row is the cell on your spreadsheet that contains the parameter listed horizontally. The column is the cell on the spreadsheet that contains the parameter listed vertically. It should then auto-compute the sensitivity analysis table.
Note, for this to work, both dimensions must have the input cell be (1) a single cell only, (2) be on the same page as the output Table.
Lecture 11
Page 4 of 10
Lecture 11
Page 5 of 10
Explaining P/B and P/E ratios using residual income valuation (1) P/B Ratio (= Market value of equity / book value of equity):
Equity Value = BVE0 +
ROE1 - re ROE2 - re BVE0 + BVE1 + ... (1 + re ) (1 + re ) 2
Divide both sides by BVE0 -- this gives a P/B ratio.
( ROE1 − re ) ( ROE 2 − re ) BVE1 ( ROE 3 − re ) BVE 2 Pr ice = 1+ + * + * + ... Book ( 1 + re ) ( 1 + re ) BVE 0 ( 1 + re ) BVE0
Thus, P/B should be a function of future level of abnormal earnings and expected growth in book value.
Lecture 11
Page 6 of 10
(2) P/E Ratio (Market Value of Equity / Net Income):
Equity Value = BVE0 +
NI 1 - re BVE0 NI 2 - re BVE1 + + ... (1 + re ) (1 + re ) 2
Define residual income RIt = NIt - reBVEt-1 Then, a lot of algebra and substitution will get you to the following relation:
Pt + Dt RI t 1 1 ∞ RI t + j = 1 + + − * j NI t r r ( 1 + r ) e e j =1 e NI t
∑
Notice, if future residual income is equal to current residual income, then the P/E ratio is normal.
Lecture 11
Page 7 of 10
Future Abnormal earnings
High P/E
Current Abnormal earnings Low P/E
Future Abnormal earnings High P/B
Current Abnormal earnings Low P/B
Future Abnormal earnings
High P/E High P/B
Low P/E High P/B Current Abnormal earnings
High P/E Low P/B
Low P/E Low P/B
Fig 1. Factors Affecting price/earnings ratios and price/book ratios
Lecture 11
Page 8 of 10
Summary: Differences between Residual Income and DCF (i) Intrinsic value estimates:
(ii) Terminal Value Estimates: Often the argument is made that residual Incomeputs less weight on the terminal value, which is more uncertain than the finite period estimates. Is this argument valid?
(iii) Focus on different issues:
(Iv) The impact of accounting (residual Income uses net income which we know can be skewed my accounting method choice):
Lecture 11
Page 9 of 10
Accounting Differences (continued): e.g. omitted research and development costs: Assume:
BVE NI excl. R&D R&D exp
Year: 0 Net Income BVE Ab Earn NPV Price
1000 $161.12 $1,161.12 Year: 0
Net Income BVE Ab Earn NPV Price
Lecture 11
1000 $161.12 $1,161.12
1000 cost of equity 200 R&D Cap 100 Amtz
1 100 1100 0 $177.24 $1,277.24 1
175 1175 75 $102.24 $1,277.24
0.1 100 25
2
3 200 200 1300 1500 90 70 $104.96 $45.45 $1,404.96 $1,545.45 2
3
175 175 1350 1525 57.5 40 $54.96 $20.45 $1,404.96 $1,545.45
4 200 1700 50
This firm expenses all R&D in year 1
4
175 1700 22.5
This firm capitalize s and depreciat
Page 10 of 10
NBA 5060
Lecture 11 – Valuation and Sensitivity Analysis
1. Sensitivity Analysis 2. Multiples 3. Summary of differences between residual income and DCF
Lecture 11
Page 1 of 10
Sensitivity Analysis The inherent unpredictability of the future makes all forecasts subject to error. To deal with this unpredictability, a sensitivity analysis is used to illustrate the variance of price estimates with respect to different forecasting assumptions. The assumptions that you will want to use should be with respect to variables that are likely to have (1) high variance, and (2) significant price impact. Examples: Forecasting Assumption Sales Growth (single year) Sales Growth (first year of trend*) COGS, SG&A exp
Variability High
Price Impact Low/Medium
High
Medium/High
Low
Medium/High
Other Rev/Expenses
Low
Low
Operating Asset Turnovers
Low
Medium/High
Capital Structure
Medium
Medium
Cost of Equity Capital
Medium
High
Terminal Growth Rate
Low/Medium
High
* The first year in an extrapolation between the early years and the last year of the finite forecast horizon.
Lecture 11
Page 2 of 10
Sensitivity Analysis: Example: CBRL terminal growth rate and cost of equity capital:
Current Price = $17.68 (as of 2/20/09)
Terminal Growth Rate
$20.44 1.10% 2.60% 4.10% 5.60% 7.10%
9.55% 26.13 28.60 32.42 39.15 54.14
Cost of Equity Capital 10.55% 11.55% 12.55% 21.64 18.13 15.35 23.18 19.10 15.94 25.43 20.44 16.75 29.04 22.47 17.91 35.81 25.87 19.70
13.55% 13.10 13.45 13.93 14.58 15.53
Look for the proportion of your table that is lower or higher than the current market price.
Lecture 11
Page 3 of 10
Doing sensitivity analysis using Excel’s ‘Table’ function. 1. Copy in your implied price formula into any cell. You can use a white font to ‘hide’ the price.
Terminal Growth Rate
$20.44 1.10% 2.60% 4.10% 5.60% 7.10%
9.55% 26.13 28.60 32.42 39.15 54.14
Cost of Equity Capital 10.55% 11.55% 12.55% 21.64 18.13 15.35 23.18 19.10 15.94 25.43 20.44 16.75 29.04 22.47 17.91 35.81 25.87 19.70
13.55% 13.10 13.45 13.93 14.58 15.53
2. Set up the rows and columns with the dimensions you want and the appropriate values.
3. Select the entire area of your intended table, and then go to Data > What-If Analysis > Data Table… Excel will ask you to input a row and a column. The row is the cell on your spreadsheet that contains the parameter listed horizontally. The column is the cell on the spreadsheet that contains the parameter listed vertically. It should then auto-compute the sensitivity analysis table.
Note, for this to work, both dimensions must have the input cell be (1) a single cell only, (2) be on the same page as the output Table.
Lecture 11
Page 4 of 10
Lecture 11
Page 5 of 10
Explaining P/B and P/E ratios using residual income valuation (1) P/B Ratio (= Market value of equity / book value of equity):
Equity Value = BVE0 +
ROE1 - re ROE2 - re BVE0 + BVE1 + ... (1 + re ) (1 + re ) 2
Divide both sides by BVE0 -- this gives a P/B ratio.
( ROE1 − re ) ( ROE 2 − re ) BVE1 ( ROE 3 − re ) BVE 2 Pr ice = 1+ + * + * + ... Book ( 1 + re ) ( 1 + re ) BVE 0 ( 1 + re ) BVE0
Thus, P/B should be a function of future level of abnormal earnings and expected growth in book value.
Lecture 11
Page 6 of 10
(2) P/E Ratio (Market Value of Equity / Net Income):
Equity Value = BVE0 +
NI 1 - re BVE0 NI 2 - re BVE1 + + ... (1 + re ) (1 + re ) 2
Define residual income RIt = NIt - reBVEt-1 Then, a lot of algebra and substitution will get you to the following relation:
Pt + Dt RI t 1 1 ∞ RI t + j = 1 + + − * j NI t r r ( 1 + r ) e e j =1 e NI t
∑
Notice, if future residual income is equal to current residual income, then the P/E ratio is normal.
Lecture 11
Page 7 of 10
Future Abnormal earnings
High P/E
Current Abnormal earnings Low P/E
Future Abnormal earnings High P/B
Current Abnormal earnings Low P/B
Future Abnormal earnings
High P/E High P/B
Low P/E High P/B Current Abnormal earnings
High P/E Low P/B
Low P/E Low P/B
Fig 1. Factors Affecting price/earnings ratios and price/book ratios
Lecture 11
Page 8 of 10
Summary: Differences between Residual Income and DCF (i) Intrinsic value estimates:
(ii) Terminal Value Estimates: Often the argument is made that residual Incomeputs less weight on the terminal value, which is more uncertain than the finite period estimates. Is this argument valid?
(iii) Focus on different issues:
(Iv) The impact of accounting (residual Income uses net income which we know can be skewed my accounting method choice):
Lecture 11
Page 9 of 10
Accounting Differences (continued): e.g. omitted research and development costs: Assume:
BVE NI excl. R&D R&D exp
Year: 0 Net Income BVE Ab Earn NPV Price
1000 $161.12 $1,161.12 Year: 0
Net Income BVE Ab Earn NPV Price
Lecture 11
1000 $161.12 $1,161.12
1000 cost of equity 200 R&D Cap 100 Amtz
1 100 1100 0 $177.24 $1,277.24 1
175 1175 75 $102.24 $1,277.24
0.1 100 25
2
3 200 200 1300 1500 90 70 $104.96 $45.45 $1,404.96 $1,545.45 2
3
175 175 1350 1525 57.5 40 $54.96 $20.45 $1,404.96 $1,545.45
4 200 1700 50
This firm expenses all R&D in year 1
4
175 1700 22.5
This firm capitalize s and depreciat
Page 10 of 10
Related Documents
New Lecture 11 Spr 09
May 2020 4
New Lecture 3 Spr 09
May 2020 2
New Lecture 9 Spr 09
May 2020 1
New Lecture 4 Spr 09
May 2020 3
New Lecture 8 Spr 09
May 2020 3