Business Finance
Case study

FIN 319

Portland State University

Question Description

I’m working on a Excel question and need guidance to help me study.

The case study questions is provided in the excel sheets. there are five chapters each has one question from the cases in the book.(Ross:Corporate Finance, 11th).

Unformatted Attachment Preview

Corporate Finance Ross, Westerfield, and Jaffe 11th edition 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." nance and Jaffe s sis ToolPak" Chapter 8 Financing East Coast Yachts Expansion Plans with a Bond Issue Input area: Years to maturity Required return Amount needed Face value $ $ 20 7.50% 50,000,000 1,000 Coupon rate Tax rate 7.50% 35% Year bond is called Spread above Treasury Treasury rate at call Treasury rate at call 7 0.40% 4.80% 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 Cash in or outflow Value of zero in one year Zero coupon growth Zero coupon bond Cash in or outflow 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 Make whole price a Bond Issue Chapter 9 Stock Valuation at Ragan Engines Input area: Shares owned by each sibling Ragan EPS Dividend to each sibling Ragan ROE Ragan required return $ $ Blue Ribband Motors Corp. Bon Voyage Marine, Inc. Nautilus Marine Engines Industry average $ Nautilus EPS w/o write-off $ 150,000 5.35 320,000 21% 18% EPS 1.19 $ 1.26 (0.27) 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 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 Total dividends DPS 0.19 0.55 0.57 $ Stock price 16.32 13.94 23.97 ROE 10.00% 12.00% N/A R 12.00% 17.00% 16.00% 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: Bledsoe S&P 500 Index Fund Bledsoe Small-Cap Fund Bledsoe Large Company Stock Fund Bledsoe Bond Fund Risk-free rate Company stock expected return Company stock standard deviation Output area: Bledsoe S&P 500 Index Fund Bledsoe Small-Cap Fund Bledsoe Large Company Stock Fund Bledsoe Bond Fund Company stock 10-year annual 9.18% 14.12% 8.58% 6.45% 3.20% 16.00% 65.00% Standard deviation 20.43% 25.13% 23.82% 9.85% Chapter 11 A Job at East Coast Yachts, Part 2 Input area: 10-year annual Standard deviation Bledsoe Large Company Stock Fund Bledsoe Bond Fund Risk-free rate Correlation 0.15 Output area: Weight of stock fund 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Portfolio E(R) Portfolio standard deviation 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 Investment Opportunity Set Portfolio Expected Return 120.00% 100.00% Portfolio Expected Return 100.00% 80.00% 60.00% 40.00% 20.00% 0.00% 0.00% 20.00% 40.00% 60.00% 80.00% Portfolio Standard Deviation 100.00% 120.00% Chapter 13 The Cost of Capital for Swan Motors Input area: Bond maturity 3/1/2019 3/1/2021 BV of debt BV of equity per share Stock price Shares outstanding Beta 3-month Treasury bill rate Market risk premium Tax rate $ $ $ $ Book value 920,000,000 1,380,000,000 Price 94.347 92.625 10.190 232.36 129,800,000 1.400 0.06% 7.00% 35% Output area: 2) RE from CAPM 3) Company Ford General Motors Honda Toyota Fiat Chrysler Volkswagen Daimler Chrysler Beta 0.97 1.44 0.74 0.54 0.49 1.97 1.55 Industry Average RE with industry beta 4) Book value Percent of total 3/1/2019 3/1/2021 Total 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 $ $ 920,000,000 1,380,000,000 YTM 2.028% 2.754% Quoted price Market value Percent of total Yield to Maturity Book values Market values 94.347 92.625 2.028% 2.754% ...
Purchase answer to see full attachment
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

Final Answer

Here you go!

Corporate Finance
Ross, Westerfield, and Jaffe
11th edition
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."

nance

and Jaffe

s

sis ToolPak"

Chapter 8
Financing East Coast Yachts Expansion Plans with a Bond Issue
Input area:

Years to maturity
Required return
Amount needed
Face value

$
$

20
7,50%
50.000.000
1.000

Coupon rate
Tax rate

7,50%
35%

Year bond is called
Spread above Treasury
Treasury rate at call
Treasury rate at call

7
0,40%
4,80%
8,20%

Output area:

2) Price of coupon bond
# of coupon bonds needed

$

1.000
50.000

Price of zero coupon bond
# of zeroes needed

$

229,34
218.019

3) Repayment of coupon bonds

$

50.000.000

Repayment of zeroes

$

218.018.938

4) Year 1 interest payments:
Pretax coupon payment
Aftertax coupon payment

$
$

3.750.000
2.437.500

$

$246,86
$17,52 Cash in or outflow
(1.337.109,38) Cash ...

Duke University

Anonymous
Return customer, been using sp for a good two years now.

Anonymous
Thanks as always for the good work!

Anonymous
Excellent job

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags