PROBLEM 9-16 WEIGHTED AVERAGE COST OF CAPITAL The capital structure for the Carion Corporation is provided here. The company plans to maintain its debt structure in the future. If the firm has a 5.5 percent after-tax cost of debt, a 13.5 percent cost of preferred stock, and an 18 percent cost of common stock, what is the firm's weighted average cost of capital? Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in mumerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. DATA Capital Individual Structure Costs Bonds $1,083 5.5% Preferred stock 268 13.5% Common stock 3,681 18.0% $5,032 SOLUTION Weights Bonds Preferred stock Common stock Weighted average cost of capital Requirements: 1. Start Excel. Download and open the workbook named: Keown_Martin_Petty_Problem_9-16_Start. Important note: All calculations must be shown using cell references. Do NOT enter numerical values in the cells. 2. In cell B15, calculate the weight of bonds in the capital structure. (1 point) 3. Copy cell B15 and paste onto cells B16:B17. (1 point) 4. In cell C19, use the function SUMMPRODUCT to calculate the weighted average cost of capital. (1 point) 5. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed