Steps to Perform: Step Instructions Points Possible 2 1 3 1 4 1 ch 5 I 1 6 1 Start Excel. Download and open the workbook named Zutter_Spreadsheet_Chapter_9_Start.xlsx In cell F35, by using cell references to the given data, calculate the annual coupon payment In cell F36. by using cell references to the given data, calculate the net proceeds In cell F37, by using cell references to the given data and the function RATE, calculate the before tax cost of debl In cell F38, by using cell references to the given data, calculate the after-tax cost of debt In cell F46, by using cell references to the given data, calculate the annual dividend payment. In cell F47, by using cell references to the given data, calculate the net proceeds In cell F48, by using cell references to the given data, calculate the cost of preferred stock In cell F56, by using cell references to the given data, calculate the cost of retained earnings Remember that retained eamings have no flotation costs In cell F60, by using cell references to the given data, calculate the cost of new common stock In cell F64, by using cell references to the given data, calculate the WACC using retained eamings In cell F68, by using cell references to the given data, calculate the WACC using new common stock Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed 7 1 8 1 9 9 1 10 11 12 0 H D Spreadsheet Exercise: Chapter 9 1 2 Nova Corporation is interested in measuring the cost of each specific type of capital as well as the weighted average cost of capital. Historically, the firm has raised capital in the following manner 4 5 6 Source of capital Long-term debit Preferred stock Common stock equity Weight 35% 1296 5396 7 9 10 11 The tax rate of the firm is currently 21%. The needed financial information and data are as follows: Debt: Nova can raise debt by selling $1,000-par-value, 6.5% coupon interest rate, 10-year bonds on which mal interest payments will be made. To sell the issue, an average discount of $20 per bond needs to be given. There is also an associated flotation cost of 2% of par value. Preferred stock: Preferred stock can be sold under the following terms. The security has a par value of $100 per share, the annual dividend rate is 6% of the par value, and the flotation cost is expected to be 54 per share. The preferred stock is expected to sell for $102 before cost considerations. Common stock: The current price of Nova's common stock is $35 per share. The cash dividend is expected to be $3.25 per share next year. The firm's dividends have grown at an annual rate of 5%, and it is expected that the dividend will continue at this rate for the foreseeable future. The flotation costs are expected to be approximately $2 per share. Nova can sell new common stock under these terms. 12 13 D H K 13 AB Common stock: The current price of Nova's common stock is $35 per share. The cash dividend is expected to be $3.25 per share next year. The firm's dividends have grown at an annual rate of 5%, and it is expected that the dividend will continue at this rate for the foreseeable future. The flotation costs are expected to be approximately 52 per share. Nova can sell new common stock under these terms. Retained earnings: The firm expects to have available $100,000 of retained earnings in the coming year. Once these retained earnings are exhausted, the firm will use new common stock as the form of common stock equity financing. (Note: When measuring this cost, the firm does not concern itself with the tax bracket or brokerage fees 14 16 12 8 9 To Do Create a spreadsheet to answer the following questions: a. Calculate the after-tax cost of debt. b. Calculate the cost of preferred stock. c. Calculate the cost of retained earnings. d. Calculate the cost of new common stock. e. Calculate the firm's weighted average cost of capital using retained earnings and the capital structure weights shown in the table above. f. Calculate the firm's weighted average cost of capital using new common stock and the capital structure weights shown in the table above. 1 Solution 2M 25 26 27 a. Calculate the after-tax cost of debt $ s 29 30 31 S Par value Coupon rate Maturity (yrs) Discount Flotation cost Tax Rate Annual coupon payment Net proceeds Before-tax cost of debt After-tax cost of debt 32 33 34 35 36 37 38 1,000 6.50% 10 20.00 2.00% 21% 65.00 960.00 7.07% 5.59% S 39 40 b. Calculate the cost of preferred stock. 41 S 42 100.00 6.00% 4.00 Par value Annual dividend rate Flotation cost Chapter 9 43 S 44 A G S 43 44 45 Par value Annual dividend rate Flotation cost Sales price before cost consideration Annual dividend payment Net proceeds Cost of preferred stock $ $ S S 100.00 6.00% 4.00 102.00 6.00 98.00 6.12% 46 47 48 49 50 c. Calculate the cost of retained earnings. 51 S s 52 53 54 55 Current price Expect cash dividend Constant growth rate Flotation cost Cost of retained earnings 35.00 3.25 5.00% 2.00 14.29% s 66 7 d. Calculate the cost of new common stock. 8 9 14.85% Cost of new common stock 1 e. Calculate the firm's weighted average cost of capital using retained earnings and the capital structure weights H A B D d. Calculate the cost of new common stock 58 59 60 61 Cost of new common stock 14.85% e. Calculate the firm's weighted average cost of capital using retained earnings and the capital structure weights shown in the table above. 62 63 5.00% WACC using retained earnings 55 1. Calculate the firm's weighted average cost of capital using new common stock and the capital structure weights shown in the table above. 66 -7 8 WACC using new common stock