Answer the cost of capital case in excel with formula please..

Three-Six Systems Inc. is starting its capital planning process for 2014 Bill Reichenstein, the firm's CFO, calculates the weighted cost of capital each year to be used as the firm's discount rate in its net present value analysis. You have been asked to help Reichenstein to compute the firm's cost of capital. In fact, he has asked you to gather the data you believe to be necessary to do the job. Your efforts have resulted in the following information: The company's bonds currently are rated as AA bonds and have a market value of $850 on a $1,000 par value (face value). The bond's coupon interest rate is 7 percent. The bonds mature in twelve years. A new issue would have a flotation cost of 4 percent of the market value. The firm's common stock paid a dividend of $3.50 last year. The par value of the stock is $5, and earnings per share have grown at a rate of 7 percent per year. This growth rate is expected to continue into the foreseeable future. The company maintains a constant dividend/earnings ratio of 25 percent. The stock sells for $55. The existing short-term bank notes have an interest rate of 9 percent, but the notes are due to he renewed at an 8 percent interest rate next month. The firm's current capital structure, as presented in the financial statements, is as follows: Short-term bank notes $150,000 Bonds (650 bonds outstanding) 650,000 Preferred stock (1,000 shares outstanding) 100,000 Common stock (10,000 shares) 300,000 There should be $300,000 of retained earnings available for reinvestment purposes in the coming year. The firm's average tax rate is 30 percent In talking with Mike Robinson, the vice president for operations, he mentioned that he uses four companies for peer-group comparisons. These four firms, along with their betas that have been adjusted for financial leverage effects, are as follows: Beta Southern Electronics Corp 1.10 Electronic Arts, Inc. 1.120 Adaptec, Inc 1.30 Baryan Systems, Inv. 0, 90 In addition to the company data, you have learned that Treasury bonds currently sell to yield the investors a return of 5.5 percent. The spread above Treasuries for AA bonds has averaged 4 percent. Finally, you learn from a recent publication by Ibbotson and Siriquefield that the market return premium of common stocks above risk-free securities [k_m - k_rf) has averaged 6.5 percent