Estimating Optimal Capital Structure Project - Fall 2021 This assignment calls for you to construct a spreadsheet to estimate the optimal capital structure for a hypothetical company. The attached spreadsheet contains all the information you will need. However, to work out the solution, you will need to complete the spreadsheet by entering appropriate formulas. Note that in most instances, proper linking will mean that you only need to enter the appropriate formula once and then can copy it to other relevant cells in a particular column. Section 15.6 of the textbook has a similar example which provides guidance on how the calculations would be conducted. In particular, Figure 15-6 provides a similar table (although in a transposed layout). The Notes below that table provide essentially all the information you need to complete your own spreadsheet. Note that you are given the pre-tax cost of debt at different levels of financial leverage, but must calculate the after-tax cost of debt. Also, you will need to estimate the beta at various levels of financial leverage using the Hamada Model. From there you will need to use the CAPM to calculate the cost of equity at each level of financial leverage. Once you have done that, the value of the firm is just the discounted present value of the expected Free Cash Flows cash flow at the implied WACC. Note that we are assuming no growth in the FCFs, so the discounting is done with the regular perpetuity model. After you complete your solution spreadsheet, you should upload key values into the associated answer entry assignment. Your project work will need to be uploaded by November 7. Excel File Edit View Insert Tools Data 73% Wed 11:26 AM % Q E Format C... Window Help FIN401 Project 3 - Optimal Capital Structure Estimation Fall 2021 AutoSave OF Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (body) v 11 v AP A Wraa Text General 4X Ay.O. F (O) LE Wid Paste B 1 U OA Merge & Center $ -% % Insert Y Delete Farmat Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Dala F 1 K 1 M N 0 A S T T U w X Y 7 H FCF in mil . 481 Tax Rate 0.28 Ml. Shares 101 S&P Bond Rating S&P500 10 yr. T Note Value of Firm WACC B 1.0500 10.00% VA A A. N16 + x X SX fx R C F 1 FIN401 Project 3 - Estimating Optimal Capital Structure 2 Fall 2021 3 Pretax After tac 4 w. w 5 0.009 1.000 2.65% 1.91% 6 0.065 0.935 2.16%. 7 0.133 0.870 3.25% 0.195 0.005 3.56% 9 0.263 0.740 3.75% 10 0.325 0.675 3.89 1: 0.390 0.610 4.01% 12 0.455 0.54 4.659 13 0.520 0.480 5.25% 14 0.585 0.415 6.23% 15 0.650 0.350 7.27%. 16 0.715 0.28 17 0.780 0.220 9.12% 18 0.845 0.155 10.56% 19 0.913 0.090 12.628 20 0.975 0.025 19.45 21 22 23 24 25 26 27 28 29 30 3: 3 32 33 34 A- RRR BUL COC+ CCC CCC- CC C D 36 37 1 Version 1 + Ready El + + 100% 5 20 7 A O W o Estimating Optimal Capital Structure Project - Fall 2021 This assignment calls for you to construct a spreadsheet to estimate the optimal capital structure for a hypothetical company. The attached spreadsheet contains all the information you will need. However, to work out the solution, you will need to complete the spreadsheet by entering appropriate formulas. Note that in most instances, proper linking will mean that you only need to enter the appropriate formula once and then can copy it to other relevant cells in a particular column. Section 15.6 of the textbook has a similar example which provides guidance on how the calculations would be conducted. In particular, Figure 15-6 provides a similar table (although in a transposed layout). The Notes below that table provide essentially all the information you need to complete your own spreadsheet. Note that you are given the pre-tax cost of debt at different levels of financial leverage, but must calculate the after-tax cost of debt. Also, you will need to estimate the beta at various levels of financial leverage using the Hamada Model. From there you will need to use the CAPM to calculate the cost of equity at each level of financial leverage. Once you have done that, the value of the firm is just the discounted present value of the expected Free Cash Flows cash flow at the implied WACC. Note that we are assuming no growth in the FCFs, so the discounting is done with the regular perpetuity model. After you complete your solution spreadsheet, you should upload key values into the associated answer entry assignment. Your project work will need to be uploaded by November 7. Excel File Edit View Insert Tools Data 73% Wed 11:26 AM % Q E Format C... Window Help FIN401 Project 3 - Optimal Capital Structure Estimation Fall 2021 AutoSave OF Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (body) v 11 v AP A Wraa Text General 4X Ay.O. F (O) LE Wid Paste B 1 U OA Merge & Center $ -% % Insert Y Delete Farmat Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Dala F 1 K 1 M N 0 A S T T U w X Y 7 H FCF in mil . 481 Tax Rate 0.28 Ml. Shares 101 S&P Bond Rating S&P500 10 yr. T Note Value of Firm WACC B 1.0500 10.00% VA A A. N16 + x X SX fx R C F 1 FIN401 Project 3 - Estimating Optimal Capital Structure 2 Fall 2021 3 Pretax After tac 4 w. w 5 0.009 1.000 2.65% 1.91% 6 0.065 0.935 2.16%. 7 0.133 0.870 3.25% 0.195 0.005 3.56% 9 0.263 0.740 3.75% 10 0.325 0.675 3.89 1: 0.390 0.610 4.01% 12 0.455 0.54 4.659 13 0.520 0.480 5.25% 14 0.585 0.415 6.23% 15 0.650 0.350 7.27%. 16 0.715 0.28 17 0.780 0.220 9.12% 18 0.845 0.155 10.56% 19 0.913 0.090 12.628 20 0.975 0.025 19.45 21 22 23 24 25 26 27 28 29 30 3: 3 32 33 34 A- RRR BUL COC+ CCC CCC- CC C D 36 37 1 Version 1 + Ready El + + 100% 5 20 7 A O W o