EXCEL SPREADSHEET

Sheet1

Module 5 Spreadsheet Exam – this is one long problem or case study. Please show all of your work.

hapter Eleven. Remember that the cost of debt

LEM FOR CH

PTERS TEN AN

ELEVEN

A B C D

$ 25,000,000

STOCK

STOCK

$ 30,000,000

DEBT

25

PREFERRED
CURRENT PRICE

FLOTATION COST

$ 5.00

COMMON
CURRENT PRICE

LAST DIVIDEND (D0)

9%

FLOTATION COST

NEXT DIVIDEND (D1)

,

, and

assuming no new debt or stock is issued?

new cost of capital?

(Answer should be in $)

(Answer should be in $)

(Answer should be in %; 2 decimal places, please)

Kd (Answer should be in %; 2 decimal places, please)
Kp (Answer should be in %; 2 decimal places, please)
Ke (Answer should be in %; 2 decimal places, please)

(Answer should be in %; 2 decimal places, please)

(Answer should be in %; 2 decimal places, please)

(Answer should be in %; 2 decimal places, please)

(Answer should be in %; 2 decimal places, please)

New cost of capital (Answer should be in %; 2 decimal places, please)

MGT 3 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper

25

Please place your answers to each question in each part in the outlined boxes in the yellow spaces at the bottom of the worksheet.

To do this exam you need to study the cases at the end of

C

when calculated is before tax and has to be converted to an after tax return. The returns on preferred and

common stock are already after tax so are not adjusted, which is explained in Chapter ten.

PRO

B

A

D

Saint Leo Manufacturing is going to introduce a new product line and to accomplish this

it has four projects analyzed in which it wants to invest a total of $100 million. Your job is to

find what it will cost to raise this amount of capital based on the cost of the capital as outlined

below:

PROJECTS

INVESTMENT

$ 30,000,000

$ 20,000,000

$ 25,000,000

EXPECTED RETURN

10.00%

14.00%

11.50%

16.00%

The firms capital structure consists of:

FMV

CAPITAL

PERCENTAGE

AMOUNT

DEBT

30%

$ 15,000,000

PREFERRED

10%

$ 5,000,000

COMMON

60%

$ 50,000,000

Other information about the firm:

CORPORATE TAX RATE

35%

CURRENT PRICE

$ 900.00

ANNUAL INTEREST

9.00%

CURRENT INTERST PAID SEMIANNUALLY

ORIGINAL MATURITY

YEARS, BUT NOW 20 YEARS LEFT

MATURITY VALUE

$ 1,000.00

FLOTATION COST

INSIGNIFICANT

MARKET YIELD PROJECTED:

UP TO $20 MILLION

9%

ABOVE $20 MILLION

12%

3 % additional premium

$ 50.00

LAST DIVIDEND (D0)

$ 5.00

FIXED AT 10% OF PAR

$ 2.00

NEXT DIVIDEND (D1)

$ 33.00

$ 1.50

RETAINED EARNINGS

$ 16,000,000

GROWTH RATE (g)

$ 3.00

$ 1.635

NOTE – Once retained earnings is maxed out, new common stock will need to be issued.

Any preferred stock would be new preferred stock. You may want to review the case in chapter 11.

REQUIRED:

In all of the required parts, one part builds on the previous part. If you can’t do a part, use the

set of other numbers to solve the next part.

a. What is the current

Kd

Kp

Ke

b. Since any new capital investment will require issuing new preferred stock, what would the

the new returns be for the preferred stock (knp) and the

new cost of capital?

c. What is the amount of increase (marginal cost of capital) in capital structure (in $) where the firm runs

out of retained earnings and would be forced to issue new common stock?

d. If new common stock has to be issued, what is the new return required to be (Kne) and the

Part a

Current price of the debt

(Answer should be in $)

Maturity value of the debt

Interest payment on the debt

Payment periods left on the debt

Yield rate on the debt

(Answer should be in %; 2 decimal places, please)

Annual yield on the debt

Current Cost of Capital

Part b

Use your solutions in part a to do this part, but if you couldn’t complete part a, assume Kd=7%, Kp=11%, and Ke=14%.

Knp preferred stock

New cost of capital

Part c

If the capital structure increases more than

(Answer should be in $-hint: in millions of dollars)

Part d

Kne common stock

If you could not come up with the Kne returns, do the cost of capital assuming Kd=7%, Knp=12%, and Ke=14%.

Sheet2

Sheet3