REFER TO THE QUESTION BELOW TO SOLVE FOR THE ONE ABOVE

XYZ Corporation would like to examine how the capital structure of this project affects its earnings per share (EPS). 2 Your task is to fill in the last column of the table below. Which capital structure maximizes the firm's expected EPS? Structure Expected EPS label Equity 9,000,000 8,000,000 7,000,000 6,000,000 5,000,000 4,000,000 3,000,000 2,000,000 1,000,000 Debt 1,000,000 2,000,000 3,000,000 4,000,000 5,000,000 6,000,000 7,000,000 8,000,000 9,000,000 Currently (before financing this project), the company has zero debt, and initial number of shares outstanding is 960,000. Below is information about the expected states of the economy and the company's expected EBIT (earnings before interest and taxes). Probability EBIT Recession Normal 0.2 0.5 5,000,000 7,000,000 Boom 0.3 11,000,000 Question 1: XYZ Corporation needs to raise $10 million to finance a capital project. It's trying to figure out an optimal capital structure for this financing; that is, it's trying to decide how much of this $10 million should be financed by debt and how much by equity. Assume that the firm wants a capital structure that minimizes the weighted average cost of capital (WACC). The company has $4.2 million in retained earnings. Its current tax rate is 30%, current stock price is $54, and the last dividend paid was $3.50. It's expected that the company will grow at the rate of 5% per year in the future. The investment banker hired by the company informs it that the flotation cost, expressed as a percentage of the proceeding of sale of new stocks, is 10%. Also, the cost of new debt for the company is as follows: Cost of new debt (aka interest rate) Before tax cost (r.) Beg. Of range 1 4,000,001 8,000,001 End of range 4,000,000 8,000,000 9% 10% 12% Your task: create a spreadsheet to help the company figure out the capital structure that minimizes WACC in financing this project. Suggestion: in your spreadsheet, the key output should be in the following format. Your task is to fill in columns 5 through 8 in the table below. Cost of debt Cost of debt Cost of before tax after tax equity WACC Label Equity 9,000,000 8,000,000 7,000,000 6,000,000 5,000,000 4,000,000 3,000,000 2,000,000 1,000,000 Debt 1,000,000 2,000,000 3,000,000 4,000,000 5,000,000 6,000,000 7,000,000 8,000,000 9,000,000 Equity pct. Debt pct. 90% 10% 80% 20% 70% 30% 60% 40% 50% 40% 60% 30% 70% 20% 80% 10% 90% 50%