Question

Construct an Excel Spreadsheet running debt to equity levels from 0% to 70% in increments of 1% and determine the optimal capital structure for the firm given the information in the problem below. Submit in readable formatted Excel that shows formulas or allows me to click on cells to see formulas.

Ruby Slippers Co. sells shoes in the Land of Oz. The demand for shoes is always stable; each of the 555 citizens buys 1 pair of shoes each year, for $99. The company incurs production costs of $77 / pair of shoes, and pays a $9999 yearly salary to its CEO, Dorothy. There is no inflation and the demand for the shoes is expected to continue forever, with the same cost structure. The company is financed by all equity, the risk free rate in the Land of Oz is 4%, the market return is 10%, and the firm's equity beta is 1.
The Wizard of Oz has decided to introduce a 35% corporate tax rate starting tomorrow. Dorothy would like your help in determining the optimal amount of debt financing under the new corporate tax law. She obtained the following information about the cost of debt from the Yellow Brick Bank:

Percent of debt financing
Interest rate
0-19%
6%
20-29%
6.1%
30-34%
6.2%
35-39%
6.3%
40-44%
6.8%
45-49%
7.2%
50-54%
7.5%
55-59%
8%
60-64%
8.5%
65-69%
9%
70-75%
9.5%

Prepare a table that analyses the effect of leverage on the cost of capital and firm value for various debt financing percentages (e.g. by 1% increments). Ruby Slippers plans on rearranging its capital structure by borrowing debt and repurchasing equity, e.g. not by adding additional financing on top of the equity it has now. First, calculate the change in beta for the corporation's equity given by BetaL = BetaU X [1 + (1-T) X (D/S)]. Where T is the tax rate, D is the percentage of Debt, and S is the percentage of equity. Then proceed to calculate the required return on equity, the firm's WACC, the firm's value, and the dollar amount of debt and equity in the firm. Also calculate the effect of leverage on EBT and Net Income. Compute the present value of interest tax shield and the financial distress costs (implied financial distress costs=VL - VU - PV(Tax shield)). Plot the firm value and the cost of capital (for debt, equity, and the firm) in two charts as a function of leverage.
What is the optimum leverage for Ruby Slippers?

Answer 1

Equity (%)	Debt (%)	Risk free rate	Market return	Beta	Cost of equity	Cost of debt	Tax rate	Effective cost off debt	Total cost of financing
E	D	Rf	Mr	B	RE= Rf + (Mr-Rf)*B	Bank rate	Tax	RD=Bank rate*(1-Tax)	E*RE + D*RD
100%	0%	4%	10%	1.00	10.0%	6%	35%	3.900%	10.00%
99%	1%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.94%
98%	2%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.88%
97%	3%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.82%
96%	4%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.76%
95%	5%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.70%
94%	6%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.63%
93%	7%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.57%
92%	8%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.51%
91%	9%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.45%
90%	10%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.39%
89%	11%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.33%
88%	12%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.27%
87%	13%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.21%
86%	14%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.15%
85%	15%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.09%
84%	16%	4%	10%	1.00	10.0%	6%	35%	3.900%	9.02%
83%	17%	4%	10%	1.00	10.0%	6%	35%	3.900%	8.96%
82%	18%	4%	10%	1.00	10.0%	6%	35%	3.900%	8.90%
81%	19%	4%	10%	1.00	10.0%	6%	35%	3.900%	8.84%
80%	20%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.79%
79%	21%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.73%
78%	22%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.67%
77%	23%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.61%
76%	24%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.55%
75%	25%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.49%
74%	26%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.43%
73%	27%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.37%
72%	28%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.31%
71%	29%	4%	10%	1.00	10.0%	6.10%	35%	3.965%	8.25%
70%	30%	4%	10%	1.00	10.0%	6.20%	35%	4.030%	8.21%
69%	31%	4%	10%	1.00	10.0%	6.20%	35%	4.030%	8.15%
68%	32%	4%	10%	1.00	10.0%	6.20%	35%	4.030%	8.09%
67%	33%	4%	10%	1.00	10.0%	6.20%	35%	4.030%	8.03%
66%	34%	4%	10%	1.00	10.0%	6.20%	35%	4.030%	7.97%
65%	35%	4%	10%	1.00	10.0%	6.30%	35%	4.095%	7.93%
64%	36%	4%	10%	1.00	10.0%	6.30%	35%	4.095%	7.87%
63%	37%	4%	10%	1.00	10.0%	6.30%	35%	4.095%	7.82%
62%	38%	4%	10%	1.00	10.0%	6.30%	35%	4.095%	7.76%
61%	39%	4%	10%	1.00	10.0%	6.30%	35%	4.095%	7.70%
60%	40%	4%	10%	1.00	10.0%	6.80%	35%	4.420%	7.77%
59%	41%	4%	10%	1.00	10.0%	6.80%	35%	4.420%	7.71%
58%	42%	4%	10%	1.00	10.0%	6.80%	35%	4.420%	7.66%
57%	43%	4%	10%	1.00	10.0%	6.80%	35%	4.420%	7.60%
56%	44%	4%	10%	1.00	10.0%	6.80%	35%	4.420%	7.54%
55%	45%	4%	10%	1.00	10.0%	7.20%	35%	4.680%	7.61%
54%	46%	4%	10%	1.00	10.0%	7.20%	35%	4.680%	7.55%
53%	47%	4%	10%	1.00	10.0%	7.20%	35%	4.680%	7.50%
52%	48%	4%	10%	1.00	10.0%	7.20%	35%	4.680%	7.45%
51%	49%	4%	10%	1.00	10.0%	7.20%	35%	4.680%	7.39%
50%	50%	4%	10%	1.00	10.0%	7.50%	35%	4.875%	7.44%
49%	51%	4%	10%	1.00	10.0%	7.50%	35%	4.875%	7.39%
48%	52%	4%	10%	1.00	10.0%	7.50%	35%	4.875%	7.34%
47%	53%	4%	10%	1.00	10.0%	7.50%	35%	4.875%	7.28%
46%	54%	4%	10%	1.00	10.0%	7.50%	35%	4.875%	7.23%
45%	55%	4%	10%	1.00	10.0%	8%	35%	5.200%	7.36%
44%	56%	4%	10%	1.00	10.0%	8%	35%	5.200%	7.31%
43%	57%	4%	10%	1.00	10.0%	8%	35%	5.200%	7.26%
42%	58%	4%	10%	1.00	10.0%	8%	35%	5.200%	7.22%
41%	59%	4%	10%	1.00	10.0%	8%	35%	5.200%	7.17%
40%	60%	4%	10%	1.00	10.0%	8.50%	35%	5.525%	7.32%
39%	61%	4%	10%	1.00	10.0%	8.50%	35%	5.525%	7.27%
38%	62%	4%	10%	1.00	10.0%	8.50%	35%	5.525%	7.23%
37%	63%	4%	10%	1.00	10.0%	8.50%	35%	5.525%	7.18%
36%	64%	4%	10%	1.00	10.0%	8.50%	35%	5.525%	7.14%
35%	65%	4%	10%	1.00	10.0%	9%	35%	5.850%	7.30%
34%	66%	4%	10%	1.00	10.0%	9%	35%	5.850%	7.26%
33%	67%	4%	10%	1.00	10.0%	9%	35%	5.850%	7.22%
32%	68%	4%	10%	1.00	10.0%	9%	35%	5.850%	7.18%
31%	69%	4%	10%	1.00	10.0%	9%	35%	5.850%	7.14%
30%	70%	4%	10%	1.00	10.0%	9.50%	35%	6.175%	7.32%
29%	71%	4%	10%	1.00	10.0%	9.50%	35%	6.175%	7.28%
28%	72%	4%	10%	1.00	10.0%	9.50%	35%	6.175%	7.25%
27%	73%	4%	10%	1.00	10.0%	9.50%	35%	6.175%	7.21%
26%	74%	4%	10%	1.00	10.0%	9.50%	35%	6.175%	7.17%
25%	75%	4%	10%	1.00	10.0%	9.50%	35%	6.175%	7.13%