9 3 3 D 31 0 3 page This assignment is designed to familiarize you with the use of Excel to evaluate different investment opportunities with capital budgeting techniques. Statement of Facts Victor Alexander owns Prospect Manufacturing, Inc. Prospect is a C corporation, which is currently taxed at 21% for Federal taxes. Prospect previously issued $1,000 par value corporate bonds that pays 6% annual interest. The corporate bonds have 12 years until their maturity. The current market price for the corporate bonds is $935. Prospect previously issued $70 par value preferred stock that pays 9% annual dividends. When the preferred stock was issued, it cost $2.50 per share to issue and sell. The current market price for the common stock is $47 per share. Prospect recently paid a dividend of $3.27 per share to the common stockholders. Prospect has historically been increasing the dividends it pays by 7% annually. The current capital structure of the business is 25% long-term debt, 15% preferred stock, and 60% common stock equity. Victor is considering purchasing new equipment for Prospect's factory and has identified four mutuall exclusive investment opportunities, which have the following estimated cash flows: Project A Project B Project C Project D Investment Year 1 Year 2 Year 3 Year 4 Year 5 (37,500) 12,500 12,500 12,500 12,500 12,500 (75,000) 19,000 21,000 22,000 20,000 18,000 (100,000) 40,000 35,000 30,000 20,000 20,000 (100,000) 25,000 25,000 35,000 30,000 30,000 x Autosum sert ction WEDDI A Logical A Text v 10 1 13 fx Recently Used Financial X PV-Market Price of Bonds FV- Par Value of Bonds Coupon Interest Rate PMT-Interest Payment N-Years Until Maturity RATE-Yield to Maturity Cost of Preferred Stock Par Value Dividend Rate Cost of Issuing and Selling Per Share Net Proceeds From Sale Cost of Preferred Stock Long-Term Debt Preferred Stock Common Stock Equity Weighted Average Cost of Capital Cost of Capital [Math & Trig Date & Time B Cost of Long-Term Debt Lookup & Reference v More Functions v C 935.00 1,000.00 6% 12.00 70.00 9% 2.50 Capital Budgeting Analysis Weight 25% 15% 60% + D Cost Name Manager E Define Name v Use in Formula Create from Selection Weighted Cost Trace Precedents Trace Dependents Vfx Show FRemove Arrows Formulasa G After-Tax Cost of Long-Term Debt Before-Tax Cost of Debt Tax Rate After-Tax Cost of Long-Term Debt Market Price Per Share Recently Paid Dividend Dividend Growth Rate Next Year's Dividend Cost of Common Stock Equity Cost of Common Stock Equity 21% 47.00 3.27 7%