Please answer the following using Excel, As soon as Possible.
Tax rate = 40%
Please show the excel work calculation as well
Capital Structure Decisions and Degree of Leverage After performing the financial ratio analysis, assessment of the data revealed that Flagstaff Inc. uses less debt than companies in its industry. As discussed in the textbook, there are advantages and disadvantages of infusing debt in the mix. In attempts to capitalize on the potential benefits of debt financing, in 2019 the company increased debt by 93.3%. Unfortunately, the financial results imply that Flagstaff, Inc. appears to have failed to utilize the debt capital effectively and experienced a decline in overall financial performance from 2018 to 2019. Sales declined by 4.8% and NOPAT dropped by 6.3%. The 2019 FCF was a -$52.0 million which influenced the drop in the stock price by 9.1%. Given the poor performance in 2019 and evidence that the company underperformed industry in 2018 and 2019, management consulted with investment bankers to develop a capital structure strategy. As presented below, the consultants have determined the interest rate the firm would pay for different capital structures. Using data from the financial statements, Flagstaff Inc.'s current capital structure includes 26.3% debt and 73.7% equity with a debt-to-equity ratio of 35.7%. Data for the risk-free rate, the market risk premium, and an estimate of the firm's unlevered beta are also provided. The tax rate can be retrieved from the financial statements. The purpose of this assessment is to first, use the data to determine the firm's optimal capital structure and the WACC at the optimal structure. What does the data tell you about management's decision to increase the debt component from 16.2% in 2018 to 26.3% in 2019? This question should be answered in the next step which is performing an analysis. The analysis should apply relevant quantitative and qualitative data gathered about Flagstaff Inc. to recommend the best capital structure strategy in the short-run and long-run, which often differs. The analysis should include conclusions that you have already surmised about the company, the recommendations that you proposed while assessing the operating plan, a discussion of the difference between business risk and financial risk and the implications for the business, and an analysis of the new data, while considering the potential risks and rewards for creditors, shareholders, and the company. Percent Financed with Debt (wa) Before-tax Cost Debt (ra) Additional Data Risk-free rate (CRF) 2.30% Market risk premium (RPM) 9.34% Unlevered beta (bu) 0.84 Tax rate (T (Link the tax rate) 0.0% 10.0% 18.5% 26.3% 33.3% 41.0% 50.0% 66.7% 0.00% 2.54% 3.36% 4.29% 5.98% 6.72% 8.53% 11.87% Using formulas, fill in the blank cells to find the optimum capital structure. Then use Excel functions to find the WACC at optimum debt ratio and the optimum debt ratio. Debt/Value Ratio (w.) Equity/Value Ratio (ws) Debt/Equity Ratio (w/w) After-tax Cost of Debt Levered Beta (b) Cost of Equity (rs) WACC Information about how to use Excel functions to compute WACC at the optimum debt ratio and the optimum debt ratio. WACC at the optimum debt ratio Optimum debt ratio = Use this section for your analysis
