Our calculations concluded that the minimum WACC was reached at 30%. In fact, the values of the WACC for three consequent debt ratios were as following:

Wd	WACC
20%	11.28%
30%	11.01%
40%	11.04%

You may remember from our discussions in class that, this calculation did a very superficial analysis, in the sense that it only considered 20%, 30%, 40%, and so on, but did not go into a more detailed analysis of the Debt Ratios between 20%-40%. Among the 20%, 30%, 40% choices, we have the minimum WACC at 30%. It looks like from 20% to 30%, WACC always decreased, and from 30% to 40% WACC always increased. However, we need to consider the following scenarios:

Maybe, the minimum WACC was between 20%-30%, so from 20%, WACC actually first decreased, reached the minimum, and started to increase towards the 30% ratio. So maybe, minimum WACC is lower than 11.01% and it is reached between 20%-30%.

Maybe, the minimum WACC was between 30%-40%, so from 30%, WACC actually continued to decrease, then reached the minimum, and started to increase towards the 40% ratio. So maybe, minimum WACC is lower than 11.01% and it is reached between 30%-40%.

Considering all this, please use Microsoft Excel to calculate the optimal capital structure at which you get the actual minimum WACC. To be able to do so, you need to make the following assumptions:

We know that r_d=8%, 8.5%, 10% for w_d=20%, 30%, 40%, respectively. But now, you need to, for example, know the r_d when w_d=25%.

Therefore, simply assume r_d will be equally distributed between 8%-8.5%, for 20%d<30% (For example, r_d=8.25% when w_d=25%).

In addition, assume r_d will be equally distributed between 8.5%-10%, for 30%d<40% (For example, r_d=9.25% when w_d=35%).

Other than this, you have all the necessary information given to you for this assignment.

Hint: Consider creating one single equation for WACC, which includes all inputs necessary. That equation should be one that changes WACC as you change wD values. You may do so by simply merging all steps we followed in class together in one equation.

BONUS (5 Points):

Use the SOLVER function to find the optimal capital structure that minimizes the WACC. SOLVER can help you minimize a cell, by changing the values in another cell, which is a very appropriate tool for WACC minimization. Also, the hint above will make it easier for you to use SOLVER.

Please recognize that you need to include a screenshot of your SOLVER Window to get the full Bonus Points.