It would be great if you could also list the formulas used. Thank you in advance!

Comprehensive Problem 9-26. Stone Wood Products has a capital structure of 35 percent debt and 65 percent common equity. The managers consider this mix to be optimal and want to maintain it in the future. Net income for the coming year is expected to be $1.2 million dollars. Duke Mantee, the loan officer at the local bank, has set up the following schedule for Stone Wood Products borrowings. There are 40,000 shares of common stock outstanding. The firm's interest subsidy tax rate is 40 percent. For more, see 8e Spreadsheet Templates for Microsoft Excel. Interest Rate Loan Amount $0 to $750,000 >$750,000 10% 12% The market price per share of Stone Wood Products common stock is $50 per share. They have declared a $5 dividend to be paid in one year. The company's expected growth rati is 9 percent. The flotation costs for new common stock issued are set at 8 percent of the market price. The managers are considering several investment opportunities for the upcoming year. They have asked the senior financial analyst, Gabrielle Maple, to recommend which of the following projects the firm should undertake. Because you are the newest member of her team and need the experience, she has passed this management request on to you. Investment Opportunities Project Rate of Return Initial Investment in millions) $0.5 $1.6 $0.6 $1.5 16% 12% 15% 18% a. Calculate all of Stone Wood Products' component costs of capital (after-tax cost of debt, cost of equity, and cost of new equity). b. Calculate all of the MCC break points. c. Calculate all of the MCC figures. d. Make an IOS by listing the projects from the highest to the lowest internal rates of return. e. Plot the MCC values and the IOS values on the same graph. f. Which projects will you recommend management adopt for the next year? A B C D E F G H I MO Student instructions: This worksheet is for problem 9-26. Information necessary to solve the problem is listed below in the section marked "given." Enter formulas in the blanks where indicated to complete the calculations needed for Questions a through d. When the answers have been calculated, the MCC and IOS values for question e will automatically be plotted on the graph on the next tab. Click on the text box on the next tab to enter your comment for question f. PROBLEM 9-26 Stone Wood Products Cost of Capital 12 13 14 15 Given: 16 17 18 19 20 Optimal Capital Structure: 35% debt 65% equity Net income for the coming year: $1,200,000 21 22 Borrowing Limits and Interest Rates: 23 Amount Borrowed 0 to $750,000 over $750,000 Interest Rate 10% 12% 24 25 01: Stock price: 26 27 F1 % 29 Tax rate: 30 31 Investment Opportunities: $50 $5 for the coming year 9% 8% of the market price 40% 28 Project 32 33 Cost $500,000 $1,600,000 $600,000 $1,500,000 IRR 16% 12% 15% 18% 34 35 36 a. Component costs of capital: 41 42 After-tax cost of debt, ATkd (1) After-tax cost of debt, ATkd (2) Cost of existing equity, ks Cost of new equity, kn per AT kd = BT kd(1-TR) up to $750,000 borrowed per AT kd = BT kd(1-TR) if over $750,000 borrowed per the dividend growth model per the dividend growth model 43 44 45 b. MCC break points: 46 47 Debt break point: based on 35% debt financing 49 50 Equity break point: Net Income - Dividends = RE available 51 $0 based on 65% equity financing 53 Break point = 54 55 c. MCC figures: 56 57 MCC up to 1st break point (equity): MCC between 1st & 2nd break points: MCC after 2nd break point: 0.00% 0.00% new stock issued 0.00% expensive debt and new stock 59 Stive debate and new stoc 60 61 d. Investment Opportunity Schedule (IOS): 62 63 Project Cost IRR 64 65 66 67 68 $0 69 Data Graph C D E G H Student instructions: This worksheet is for problem 9-26. Begin the problem on the previous tab containing questions a through d. This tab contains the MCC/IOS graph called for in question e. Click on the text box below to enter your comment for question f. MONO PROBLEM 9-26 Stone Wood Products Cost of Capital Note: Graph is below data list 12 13 e. MCC/IOS Graph: 14 15 Data for graph: 16 17 Capital Budget 18 Size ($000s) 19 IOS $0 20 21 22 0% 0% 0% 0% 0% 0% 0% 23 24 25 MCC 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0% 26 27 28 29 0% 0% 0% 0% 0% 30 31 32 33 34 MCC/IOS Schedule Stone Wood Products 35 36 37 38 _ Project D Costs of Capital and IRRS 19.00% 18.00% 17.00% 16.00% 15.00% 14.00% 13.00% 12.00% 11.00% + $0 45 $0 $1 $1 $0 $1 $1 Capital Budget Size ($000s) 47 49 50 MCC 105 51 52 53 f. Comments: 54 55 Comments 56 57 58