with excel formulas please

Alpha Company has the following capital structure:

Debt (at 6.5%) $20,000,000

Preferred stock (at 8%) 5,000,000

Common Stock 10,000,000

Retained Earnings 15,000,000

Total Capitalization $50,000,000

Earnings per share have a grown steadily from $0.93 in to $2.00 estimated in 19x8. Expecting this growth to continue, the investment community has applied a price/earnings ratio of 10 to the firms aging yielding a market price for its stock of $20.00. Alphas last annual dividend was $1.25, and securities analyst indicate they expect the divided to continue to grow with earnings. After taxes at 34% and dividends this year, Alpha anticipates adding $4,000,000 to retained earnings. Assuming the current capital structured is maintained, new securities could be sold in the following sequence.

Bonds:

1) Up to $5 million of 30 year bonds with a 10% yield and a 3% flotation cost

2) Next, up to $3 million of additional 30 year bonds with a 11% yield and 5% flotation

cost.

3) Next up to $2 million of additional 30 year bonds with a 13% coupon and an 8% flotation cost.

Preferred stock:

1) Up to $1 million with a 12% dividend and a 4% flotation cost

2) Next, up to another $1 million with a 13% dividend and a 6% flotation cost

3) Next, up to third $1 million with a 15% dividend and a 8% flotation cost

Common stock:

1) Up to $6 million at $20 per share less a $2.50 per share flotation cost

2) Over $6 million al $19 per share less a $4.00 per share flotation cost.

Required:

1) Calculate the cost of each tranche of bond, preferred and common stock financing

2) At what dollar amounts of new capital will breaks occur in the marginal cost of capital (MCC) schedule?

3) Calculate the MCC in the interval between each break, then plot the MCC curve.

4) What factors in the real world would tend to make the MCC curve smooth rather than step-function as you have graphed it?