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
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
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
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
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
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