* i posted this question like 6 hours before but its unanswered. please help me in this question.

You must use a spreadsheet to do this part. Input data can be entered manually, but everything else in the spreadsheet must be automated using formulas. Please email answers as an Excel file attachment. If you don't have Excel, you can use Google Sheet, then download it as an Excel file and submit the Excel file. Please submit only ONE Excel file for both questions. Create two sheets in the same file, one for each question. Reason: some answers or numbers from Question 1 will be used in question 2 so It's convenlent to have two sheets in one file and link the two sheets Tips: There will be lots of repetitive parts as you construct the financial model. To save time, you should copy and paste the formulas If you manually do the same thing again and again, you will likely run out of time and not be able to finish the question. Remember to add the dollar sign where necessary before you copy and paste formulas otherwise the formulas will not transfer correctly. Question 1: XYZ Cordoration 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 (0) Ber. 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. Equity ect. Louity 90.000 8.000000 7000 000 60X0000 5,000,000 4000.000 1,000,000 2.000.000 1.000.000 Det 1.000.000 2,000,000 3 000 000 4,000,000 5,000,000 6000 000 7,00 8.000.000 9.000.000 Cost of debt cost of debt Cost of Debt pct. before tax larter tax jequity 108 20MM UNITED 30MM 4ON SON CON NON Hint: This question is similar to a practice question we did in class, but not completely similar. The question in class requires the calculation of break points because in that question, there are multiple projects to be considered. But this question does NOT require the calculation of break points because there is only one project being considered. Question 2: Continue to use information from question 1. XYZ Corporation would like to examine how the capital structure of this project affects its earnings per share (EPS). Your task is to fill in the last column of the table below. Which capital structure maximizes the firm's expected EPS? Structure Label Expected EPS Equity 9.000.000 3,000,000 7.000.000 6.000.000 5.000.000 4000.000 3,000,000 2.000.000 1.000.000 Debt 1.000.000 2,000,000 3.000000 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 faces outstanding is 960,000. OF UZURU Below is information about the expected states of the economy and the company's expected EBIT (earnings before interest and taxes). EBIT Recession Normal | Boom Probability 0.2 0.5 0 .3 5,000,000 7,000,000 11,000,000 Reminders: Information about tax rate and current stock price is provided in question 1. You will need data on the cost of debt (aka interest rate) in order to calculate interest payment on debt; remember that you calculated the cost of debt in question 1. Hint to help you check your answers: the optimal capital structure that minimizes WACC in question 1 should be the same as the optimal capital structure that maximizes EPS in