Corporate Finance Online Exam Due Tomorrow SEE ATTACHED
Bedah1991
IFM12Ch08BOC-Model.xlsxMain model
| Worksheet for Chapter 8 BOC Questions | 1/25/15 | |||||||
| We like to go through an Excel exercise both to get students more familiar with Excel and also because Exxcel | ||||||||
| is useful for showing sensitivity analysis. | ||||||||
| Questions 1 and 2. | ||||||||
| Constant Growth Input Data | Data for Different Scenarios: | |||||||
| Good | Base | Bad | ||||||
| D0 | $1.00 | $1.00 | $1.00 | These inputs are | ||||
| growth rate | 8.0% | 6.0% | 4.0% | arbitrary but | ||||
| real rRF | 2.0% | 3.0% | 4.0% | "reasonable" in that | ||||
| Inflation premium | 2.0% | 4.0% | 6.0% | such conditions do at | ||||
| rMarket | 9.0% | 11.0% | 16.0% | times exist. | ||||
| beta | 0.90 | 1.1 | 1.20 | |||||
| Nominal rrf | 4.0% | 7.0% | 10.0% | |||||
| Market risk premium (MRP) | 5.0% | 4.0% | 6.0% | |||||
| D1 = | D0 (1 + g) = | $1.08 | $1.06 | $1.04 | ||||
| rS = | = rRF + b (rM - rRF) = | 8.5% | 11.4% | 17.2% | ||||
| Price, constant g = | D0(1+g) / (rStock - g) = | $216.00 | $19.63 | $7.88 | ||||
| This example demonstrates that stock prices can experience huge changes as a result of changes in the basic parameters. Note too that the stock price gets extremely high if g approaches r. For example, if we changed g in the good scenario from 8% to 8.4%, the price would "explode" to $1,080. One must be very careful when using the constant growth model to avoid getting nonsense results. If one is not confident that the constant growth model is appropriate, then use the nonconstant model as set forth below. | ||||||||
| If this were done with the free cash flow valuation model, then we would project free cash flows, not dividends. And we'd discount these free cash flows at the Weighted Average Cost of Capital (WACC) which we discuss in this chapter, not rs. This would give the value of operations. To get the value of equity, add in marketable securities (also called short-term investments), subtract debt and preferred stock. This gives the intrinsic value of the stock. To get the intrinsic stock price, divide by the shares outstanding as shown below. Although we discuss the effects of the inflation premium, the risk free rate and the market risk premium in Chapter 11, we don't do it yet in this chapter. | ||||||||
| Constant Growth Input Data | Data for Different Scenarios: | |||||||
| Good | Base | Bad | ||||||
| FCF0 (millions) | $10.00 | $10.00 | $10.00 | |||||
| growth rate | 8.0% | 6.0% | 4.0% | |||||
| WACC | 9.0% | 9.0% | 9.0% | |||||
| Value of marketable securities (millions) | $8.00 | $8.00 | $8.00 | |||||
| Market value of all debt (millions) | $200.00 | $200.00 | $200.00 | |||||
| Shares outstanding (millions) | 8.20 | 8.20 | 8.20 | |||||
| Value of operations (millions) | $1,080.00 | $353.33 | $208.00 | FCF0(1+g) / (WACC - g) | ||||
| +Value of marketable securities (millions) | $8.00 | $8.00 | $8.00 | |||||
| = Total value of firm (millions) | $1,088.00 | $361.33 | $216.00 | |||||
| - Value of debt (millions) | $200.00 | $200.00 | $200.00 | |||||
| = Intrinsic value of equity (millions) | $888.00 | $161.33 | $16.00 | |||||
| Intrinsic price per share | $108.29 | $19.67 | $1.95 | |||||
| Question 3. Nonconstant growth--dividends. | ||||||||
| g(1-3) = | 50% | |||||||
| g(4-6) = | 30% | |||||||
| gLR = | 6% | |||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Growth | 50% | 50% | 50% | 30% | 30% | 30% | 6% | |
| Dividend | $1.00 | $1.50 | $2.25 | $3.38 | $4.39 | $5.70 | $7.41 | $7.86 |
| P6 | $145.55 | = P6 | ||||||
| Cash flows | $1.50 | $2.25 | $3.38 | $4.39 | $5.70 | $152.97 | =D7/(r-g7) | |
| PV of CFs: | $1.35 | $1.81 | $2.44 | $2.85 | $3.32 | $80.04 | ||
| P0 = Sum of PVs | $91.81 | |||||||
| P0 = | $91.81 | Price found with the NPV function (fx > Financial > NPV) | ||||||
| Question 3. Nonconstant growth--free cash flows. | ||||||||
| As in Questions 1 and 2, valuing stocks using free cash flows requires finding the present value of the expected future free cash flows, discounted at the WACC. This is the value of operations. To this, add in short-term investments and subtract debt and preferred stock to get the intrinsic value of the equity . To get the intrinsic stock price, divide by the number of shares outstanding. | ||||||||
| WACC | 10% | |||||||
| g(1-3) = | 50% | Short-term investments = | $30.00 | million | ||||
| g(4-6) = | 30% | All debt = | $900.00 | million | ||||
| gLR = | 6% | Shares outstanding = | 15.00 | million | ||||
| All dollars in millions | ||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Growth | 50% | 50% | 50% | 30% | 30% | 30% | 6% | |
| FCF | $10.00 | $15.00 | $22.50 | $33.75 | $43.88 | $57.04 | $74.15 | $78.60 |
| HV6 | $1,964.94 | = HV6 | ||||||
| Cash flows | $15.00 | $22.50 | $33.75 | $43.88 | $57.04 | $2,039.09 | =FCF7/(WACC-g7) | |
| PV of CFs: | $13.64 | $18.60 | $25.36 | $29.97 | $35.42 | $1,151.01 | ||
| Vops = Sum of PVs | $1,273.98 | |||||||
| Vops = | $1,273.98 | Price found with the NPV function (fx > Financial > NPV) | ||||||
| + Short term inv. | $30.00 | |||||||
| -Debt | $900.00 | |||||||
| = Intrinsic value of equity | $403.98 | |||||||
| Shares outstanding | 15.00 | |||||||
| Intrinsic share price | $26.93 | |||||||
| Question 4. Finding the expected rate of return on a non-constant growth stock. | ||||||||
| The dividends are as shown above, so we can copy those values as shown below. However, we do not know | ||||||||
| the discount rate--that's what we are trying to find. We first make a guess, 10% as shown below, and use | ||||||||
| the guess in the terminal value and discounting formulas. The cells where the guess is used are shown | ||||||||
| in green, and highlighted. | ||||||||
| Stock price: | $100.00 | |||||||
| Guess as to discount rate, r: | 10.000% | |||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Growth | 50% | 50% | 50% | 30% | 30% | 30% | 6% | |
| Dividend | $1.00 | $1.50 | $2.25 | $3.38 | $4.39 | $5.70 | $7.41 | $7.86 |
| P6 | $196.49 | = P6 | ||||||
| Cash flows | $1.50 | $2.25 | $3.38 | $4.39 | $5.70 | $203.91 | =D7/(r-g7) | |
| PV of CFs: | $1.36 | $1.86 | $2.54 | $3.00 | $3.54 | $115.10 | ||
| P0 = Sum of PVs | $127.40 | = calculated price | ||||||
| The calculated price, based on the 10% discount rate, is greater than the $100 offer price. This means | ||||||||
| that the expected rate of return is greater than 10%. So, we could raise the 10% to higher and higher | ||||||||
| values until we found one that "works" in the sense of making the calculated price equal the given $100 | ||||||||
| price. The discount rate that causes the calculated stock price to equal $100 is the expected rate of return. | ||||||||
| We could proceed on a trial and error basis, but we could also use Excel's "Goal Seek" function. The | ||||||||
| goal we seek is the discount rate that forces the calculated stock price to equal the market price, $100. | ||||||||
| Start by putting the pointer on the value we want to force to some other value. | ||||||||
| This is C110, which we want to force to $100. Then click Tools > Goal seek. We | ||||||||
| are on cell C110, so just hit the tab key to go to the "to value" cell, where we enter | ||||||||
| 100. We want to have Excel adjust the discount rate in cell D102, so we enter D102 | ||||||||
| in the "By changing cell." Then, when we click OK, Excel changes D102 | ||||||||
| iteratively (but almost instaneously) until the value in C110 is equal to the | ||||||||
| specified level, $100. | ||||||||
| Now look at cell D102. Its value has changed from the initial 10% to 10.997%, | ||||||||
| which rounds to 11%. Therefore, if you buy the stock and things work out as | ||||||||
| you expect, you will earn about 11% on your money. Goal seek is a very useful | ||||||||
| function, and we will use it often. | ||||||||
| Question 4. Finding the expected rate of return on a non-constant growth stock. | ||||||||
| Firms that don't pay a dividend and aren't expected to do so for some time (or perhaps ever--the intention is for the firm to be acquired before it pays a dividend) are impossible to value using the dividend growth model. However, these firms all have a free cash flow. It might be positive, it might be negative, but those free cash flows can be projected from the company's operating projections and then discounted at the WACC. So, in principle, you can always find the value of a company using the FCF model. | ||||||||
&P of &N
