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 data 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\mathrm{.}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.