Excel Case STUDY 2
vk4queen
CaseStudies23.xlsxCase Studies
| Corporate Finance | |||
| Ross, Westerfield, and Jaffe | |||
| 11th edition | |||
| Case Studies #2 | |||
| Input boxes in tan | |||
| Output boxes in yellow | |||
| Given data in blue | |||
| Calculations in red | |||
| Answers in green | |||
| NOTE: Some functions used in these spreadsheets may require that | |||
| the "Analysis ToolPak" or "Solver Add-in" be installed in Excel. | |||
| To install these, click on "Tools|Add-Ins" and select "Analysis ToolPak" | |||
| and "Solver Add-In." | |||
Chapter 8
| Chapter 8 | |||||
| Financing East Coast Yachts Expansion Plans with a Bond Issue | |||||
| Input area: | |||||
| Years to maturity | 20 | ||||
| Required return | 7.50% | ||||
| Amount needed | $ 50,000,000 | ||||
| Face value | $ 1,000 | ||||
| Coupon rate | 7.50% | ||||
| Tax rate | 35% | ||||
| Year bond is called | 7 | ||||
| Spread above Treasury | 0.40% | ||||
| Treasury rate at call | 4.80% | ||||
| Treasury rate at call | 8.20% | ||||
| Output area: | |||||
| 2) | Price of coupon bond | $ 1,000 | |||
| # of coupon bonds needed | 50,000 | ||||
| Price of zero coupon bond | $ 229.34 | ||||
| # of zeroes needed | 218,016 | ||||
| 3) | Repayment of coupon bonds | $ 50,000,000 | |||
| Repayment of zeroes | $ 218,016,000 | ||||
| 4) | Year 1 interest payments: | ||||
| Pretax coupon payment | $ 3,750,000 | Cash in or outflow | |||
| Aftertax coupon payment | $ 2,437,500 | Cash outflow | |||
| Value of zero in one year | $ 246.86 | ||||
| Zero coupon growth | $ 17.52 | Cash in or outflow | |||
| Zero coupon bond | $ (1,336,929.23) | Cash inflow | |||
| During the life of a bond, the zero generates cash inflows to the firm | |||||
| in the form of the interest tax shield of debt. | |||||
| 5) | Make whole price | $ 1,215.38 | |||
| Make whole price | $ 914.90 | ||||
Chapter 9
| Chapter 9 | |||||||
| Stock Valuation at Ragan Engines | |||||||
| Input area: | |||||||
| Shares owned by each sibling | 150,000 | ||||||
| Ragan EPS | $ 5.35 | ||||||
| Dividend to each sibling | $ 320,000 | ||||||
| Ragan ROE | 21% | ||||||
| Ragan required return | 18% | ||||||
| EPS | DPS | Stock price | ROE | R | |||
| Blue Ribband Motors Corp. | $ 1.19 | $ 0.19 | $ 16.32 | 10.00% | 12.00% | ||
| Bon Voyage Marine, Inc. | 1.26 | 0.55 | 13.94 | 12.00% | 17.00% | ||
| Nautilus Marine Engines | (0.27) | 0.57 | 23.97 | N/A | 16.00% | ||
| Industry average | $ 0.73 | $ 0.44 | $ 18.08 | $ 0.11 | $ 0.15 | ||
| Nautilus EPS w/o write-off | $ 2.07 | ||||||
| Output area: | |||||||
| 1) | Total earnings | $ 1,605,000.00 | |||||
| Payout ratio | 39.88% | ||||||
| Retention ratio | 60.12% | ||||||
| Growth rate | 12.63% | ||||||
| Total dividends next year | $ 720,807.48 | ||||||
| Total equity value | $ 13,413,286.96 | ||||||
| Value per share | $ 44.71 | ||||||
| 2) | Industry EPS | $ 1.51 | |||||
| Industry payout ratio | 28.98% | ||||||
| Industry retention ratio | 71.02% | ||||||
| Industry growth rate | 7.81% | ||||||
| Year | Total dividends | ||||||
| 1 | $ 720,807.48 | ||||||
| 2 | $ 811,817.84 | ||||||
| 3 | $ 914,319.33 | ||||||
| 4 | $ 1,029,762.82 | ||||||
| 5 | $ 1,159,782.41 | ||||||
| 6 | $ 1,250,384.00 | ||||||
| Stock value in Year 5 | $ 17,395,308.29 | ||||||
| Total stock value today | $ 11,655,749.48 | ||||||
| Value per share | $ 38.85 | ||||||
| 3) | Industry PE | 12.00 | |||||
| Ragan PE (original assumption) | 8.36 | ||||||
| Ragan PE (revised assumption) | 7.26 | ||||||
| Stock price implied by | |||||||
| industry PE | $ 64.19 | ||||||
| 4) | Total earnings | $ 1,605,000.00 | |||||
| Cash cow value | $ 10,700,000.00 | ||||||
| Percentage not attributable to | |||||||
| growth opportunities | 91.80% | ||||||
| Percentage attributable to | |||||||
| growth opportunities | 8.20% | ||||||
| 5) | ROE | 12.99% | |||||
Chapter 10
| Chapter 10 | ||||
| A Job at East Coast Yachts | ||||
| Input area: | ||||
| 10-year annual return | Standard deviation | |||
| Bledsoe S&P 500 Index Fund | 9.18% | 20.43% | ||
| Bledsoe Small-Cap Fund | 14.12% | 25.13% | ||
| Bledsoe Large Company Stock Fund | 8.58% | 23.82% | ||
| Bledsoe Bond Fund | 6.45% | 9.85% | ||
| Risk-free rate | 3.20% | |||
| Company stock expected return | 16.00% | |||
| Company stock standard deviation | 65.00% | |||
| Output area: | ||||
| Bledsoe S&P 500 Index Fund | 0.2927 | |||
| Bledsoe Small-Cap Fund | 0.4345 | |||
| Bledsoe Large Company Stock Fund | 0.2259 | |||
| Bledsoe Bond Fund | 0.3299 | |||
| Company stock | 0.1969 | |||
Chapter 11
| Chapter 11 | ||||
| A Job at East Coast Yachts, Part 2 | ||||
| Input area: | ||||
| 10-year annual return | Standard deviation | |||
| Bledsoe Large Company Stock Fund | 8.58% | 23.82% | ||
| Bledsoe Bond Fund | 6.45% | 9.85% | ||
| Risk-free rate | 3.20% | |||
| Correlation | 0.15 | |||
| Output area: | ||||
| Weight of stock fund | Portfolio E(R) | Portfolio standard deviation | ||
| 0% | 6.45% | 9.8500% | ||
| 10% | 6.66% | 9.5182% | ||
| 20% | 6.88% | 9.8006% | ||
| 30% | 7.09% | 10.6484% | ||
| 40% | 7.30% | 11.9417% | ||
| 50% | 7.52% | 13.5536% | ||
| 60% | 7.73% | 15.3843% | ||
| 70% | 7.94% | 17.3648% | ||
| 80% | 8.15% | 19.4493% | ||
| 90% | 8.37% | 21.6077% | ||
| 100% | 8.58% | 23.8200% | ||
| Dominant portfolio: | ||||
| Weight of stock fund | 20.82% | |||
| Weight of bond fund | 79.18% | |||
| Standard deviation | 9.85% | |||
| Expected return | 6.89% | |||
| Minumum variance portfolio: | ||||
| Weight of large cap stock fund | 10.41% | |||
| Weight of bond fund | 89.59% | |||
| Expected return | 6.67% | |||
| Variance | 0.009059 | |||
| Standard deviation | 9.52% | |||
| Sharpe optimal portfolio: (Using Solver) | ||||
| Weight of large cap stock fund | -19.13% | |||
| Sharpe ratio | 0.2382 | |||
| Weight of large cap stock fund | 19.76% | |||
| Weight of bond fund | 80.24% | |||
| Expected return | 6.87% | |||
| Standard deviation | 9.79% | |||
| Sharpe ratio | 0.3751 | |||
Investment Opportunity Set
9.8500000000000004E-2 9.5182497340635067E-2 9.8006220210760109E-2 0.10648372739531613 0.11941692844818946 0.13553624976367024 0.15384342949895519 0.17364786264161156 0.1944927073182951 0.21607706912118185 0.2382 6.4500000000000002E-2 6.6630000000000009E-2 6.8760000000000016E-2 7.0889999999999995E-2 7.3020000000000002E-2 7.5149999999999995E-2 7.7280000000000001E-2 7.9409999999999994E-2 8.1540000000000001E-2 8.3669999999999994E-2 8.5800000000000001E-2Portfolio Standard Deviation
Portfolio Expected Return
Chapter 13
| Chapter 13 | ||||||||||
| The Cost of Capital for Swan Motors | ||||||||||
| Input area: | ||||||||||
| Bond maturity | Book value | Price | YTM | |||||||
| 3/1/19 | $ 920,000,000 | 94.347 | 2.028% | |||||||
| 3/1/21 | $ 1,380,000,000 | 92.625 | 2.754% | |||||||
| BV of debt | $ 2,300,000,000 | |||||||||
| BV of equity per share | $ 10.190 | |||||||||
| Stock price | $ 232.36 | |||||||||
| Shares outstanding | 129,800,000 | |||||||||
| Beta | 1.400 | |||||||||
| 3-month Treasury bill rate | 0.06% | |||||||||
| Market risk premium | 7.00% | |||||||||
| Tax rate | 35% | |||||||||
| Output area: | ||||||||||
| 2) | RE from CAPM | 9.86% | ||||||||
| 3) | Company | Beta | ||||||||
| Ford | 0.97 | |||||||||
| General Motors | 1.44 | |||||||||
| Honda | 0.74 | |||||||||
| Toyota | 0.54 | |||||||||
| Fiat Chrysler | 0.49 | |||||||||
| Volkswagen | 1.97 | |||||||||
| Daimler Chrysler | 1.55 | |||||||||
| Industry Average | 1.10 | |||||||||
| RE with industry beta | 7.76% | |||||||||
| 4) | Book value | Percent of total | Quoted price | Market value | Percent of total | Yield to Maturity | Book values | Market values | ||
| 3/1/19 | $ 920,000,000 | 0.40 | 94.347 | $ 867,992,400 | 0.40 | 2.028% | 0.81% | 0.82% | ||
| 3/1/21 | $ 1,380,000,000 | 0.60 | 92.625 | 1,278,225,000 | 0.60 | 2.754% | 1.65% | 1.64% | ||
| Total | $ 2,300,000,000 | 1.00 | 2,146,217,400.00 | 1.00 | 2.46% | 2.46% | ||||
| 5) | Book value of debt | $ 2,300,000,000 | ||||||||
| Book value of equity | $ 1,322,662,000 | |||||||||
| Book value of company | $ 3,622,662,000 | |||||||||
| Market value of equity | $ 30,160,328,000 | |||||||||
| Market value of company | $ 32,306,545,400 | |||||||||
| WACC using book value | 4.62% | |||||||||
| WACC using market value | 9.38% | |||||||||
