Database Prototyping Assignment
Database Prototyping Assignment.
Database Prototyping Assignment
Case Studies
| Corporate Finance |
| Ross, Westerfield, Jaffe, and Jordan |
| 12th edition |
| Chapters 8-11 & 13 Case Studies |
| 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 | 21% |
| 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 |
| # of coupon bonds needed | |
| Price of zero coupon bond | |
| # of zeroes needed | |
| 3) | Repayment of coupon bonds |
| Repayment of zeroes | |
| 4) | Year 1 interest payments: |
| Pretax coupon payment | |
| Aftertax coupon payment | |
| Value of zero in one year | |
| Zero coupon growth | |
| Zero coupon bond | |
| 5) | Make whole price |
| Make whole price |
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 | |||||
| Nautilus EPS w/o write-off | $ 2.07 | ||||
| Output area: | |||||
| 1) | Total earnings | ||||
| Payout ratio | |||||
| Retention ratio | |||||
| Growth rate | |||||
| Total dividends next year | |||||
| Total equity value | |||||
| Value per share | |||||
| 2) | Industry EPS | ||||
| Industry payout ratio | |||||
| Industry retention ratio | |||||
| Industry growth rate | |||||
| Year | Total dividends | ||||
| 1 | |||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| Stock value in Year 5 | |||||
| Total stock value today | |||||
| Value per share | |||||
| 3) | Industry PE | ||||
| Ragan PE (original assumption) | |||||
| Ragan PE (revised assumption) | |||||
| Stock price implied by | |||||
| industry PE | |||||
| 4) | Total earnings | ||||
| Cash cow value | |||||
| Percentage not attributable to | |||||
| growth opportunities | |||||
| Percentage attributable to | |||||
| growth opportunities | |||||
| 5) | ROE |
Chapter 10
| Chapter 10 | ||
| A Job at East Coast Yachts | ||
| Input area: | ||
| 10-year annual return | Standard deviation | |
| Bledsoe S&P 500 Index Fund | 11.04% | 18.45% |
| Bledsoe Small-Cap Fund | 16.14% | 29.18% |
| Bledsoe Large Company Stock Fund | 12.15% | 24.43% |
| Bledsoe Bond Fund | 6.93% | 9.96% |
| Risk-free rate | 3.20% | |
| Company stock expected return | 16.00% | |
| Company stock standard deviation | 58.00% | |
| Output area: | ||
| Bledsoe S&P 500 Index Fund | ||
| Bledsoe Small-Cap Fund | ||
| Bledsoe Large Company Stock Fund | ||
| Bledsoe Bond Fund | ||
| Company stock |
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 | ||
| Bledsoe Bond Fund | ||
| Risk-free rate | ||
| Correlation | 0.15 | |
| Output area: | ||
| Weight of stock fund | Portfolio E(R) | Portfolio standard deviation |
| 0% | ||
| 10% | ||
| 20% | ||
| 30% | ||
| 40% | ||
| 50% | ||
| 60% | ||
| 70% | ||
| 80% | ||
| 90% | ||
| 100% | ||
| Dominant portfolio: | ||
| Weight of stock fund | ||
| Weight of bond fund | ||
| Standard deviation | ||
| Expected return | ||
| Minumum variance portfolio: | ||
| Weight of large cap stock fund | ||
| Weight of bond fund | ||
| Expected return | ||
| Variance | ||
| Standard deviation | ||
| Sharpe optimal portfolio: (Using Solver) | ||
| Weight of large cap stock fund | ||
| Sharpe ratio | ||
| Weight of large cap stock fund | ||
| Weight of bond fund | ||
| Expected return | ||
| Standard deviation | ||
| Sharpe ratio |
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 | 21% | |||||||
| Output area: | ||||||||
| 2) | RE from CAPM | |||||||
| 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 | ||||||||
| RE with industry beta | ||||||||
| 4) | Book value | Percent of total | Quoted price | Market value | Percent of total | Yield to Maturity | Book values | Market values |
| 3/1/19 | ||||||||
| 3/1/21 | ||||||||
| Totals | ||||||||
| 5) | Book value of debt | |||||||
| Book value of equity | ||||||||
| Book value of company | ||||||||
| Market value of equity | ||||||||
| Market value of company | ||||||||
| WACC using book value | ||||||||
| WACC using market value |
Database Prototyping Assignment
“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”
Save your time - order a paper!
Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines
Order Paper Now