| MGT 325 Module 5 Spreadsheet Exam - this is one long problem or case study. Please show all of your work. |
| 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 Chapter Eleven. Remember that the cost of debt |
| 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. |
| PROBLEM FOR CHAPTERS TEN AND ELEVEN |
| 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 |
| | A | | B | C | D |
| INVESTMENT | $ 30,000,000 | | $ 20,000,000 | $ 25,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 STOCK | | 10% | $ 5,000,000 |
| | COMMON STOCK | | 60% | $ 30,000,000 |
| | | | | $ 50,000,000 |
| Other information about the firm: |
| CORPORATE TAX RATE | 35% |
| | DEBT |
| CURRENT PRICE | $ 900.00 |
| ANNUAL INTEREST | 9.00% | | CURRENT INTERST PAID SEMIANNUALLY |
| ORIGINAL MATURITY | 25 | | 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 |
| | PREFERRED |
| CURRENT PRICE | $ 50.00 |
| LAST DIVIDEND (D0) | $ 5.00 | | FIXED AT 10% OF PAR |
| FLOTATION COST | $ 2.00 |
| NEXT DIVIDEND (D1) | $ 5.00 |
| | COMMON |
| CURRENT PRICE | $ 33.00 |
| LAST DIVIDEND (D0) | $ 1.50 |
| RETAINED EARNINGS | $ 16,000,000 |
| GROWTH RATE (g) | 9% |
| FLOTATION COST | $ 3.00 |
| NEXT DIVIDEND (D1) | $ 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, and Ke assuming no new debt or stock is issued? |
| 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 |
| new cost of capital? |
| Part a |
| Current price of the debt | | | (Answer should be in $) |
| Maturity value of the debt | | | (Answer should be in $) |
| Interest payment on the debt | | | (Answer should be in $) |
| Payment periods left on the debt |
| Yield rate on the debt | | | (Answer should be in %; 2 decimal places, please) |
| Annual yield on the debt | | | (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) |
| Current Cost of Capital | | | (Answer should be in %; 2 decimal places, please) |
| 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 | | | (Answer should be in %; 2 decimal places, please) |
| New cost of capital | | | (Answer should be in %; 2 decimal places, please) |
| Part c |
| If the capital structure increases more than | | | (Answer should be in $-hint: in millions of dollars) |
| Part d |
| Kne common stock | | | (Answer should be in %; 2 decimal places, please) |
| If you could not come up with the Kne returns, do the cost of capital assuming Kd=7%, Knp=12%, and Ke=14%. |
| New cost of capital | | | (Answer should be in %; 2 decimal places, please) |
TheVirgo757
finane_spreadsheet.xls
