Question
In: Finance
Construct an Excel Spreadsheet running debt to equity levels from 0% to 70% in increments of...
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?
Solutions
Expert Solution
|
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% |
jeff jeffy answered 2 years agoRelated Solutions
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of...
The target weight for equity is 70% and the the target weight for debt is 30%...
Using Excel, construct one graph showing the entire distribution from 0 to 10 for: Hypergeometric, where...
4.B Construct an Excel model with solver to solve for the profit maximizing levels of price...
In the Ratios tab of the FSAR Excel Spreadsheet, complete the Short-Term Debt Paying Ratios, Asset...
Pearson Motors has a target capital structure of 30% debt and 70% common equity, with no...
A company has equity with market value $100 million and debt with market value at $70
Data. See Excel spreadsheet “GroupProject1.xlsx”. It contains the monthly returns of value-weighted equity indexes divided into...
Determine all items that will be excluded from gross income. In a MS Excel spreadsheet, list...
Palencia Paints Corporation has a target capital structure of 30% debt and 70% common equity, with...
- song of myself by Walt Whitman summary of the poem in my own words or version
- write a c++ program . Ask the user to enter a number less than 100. Test...
- Please do answer all four questions 11. A local college hospitality restaurant has the best meals...
- Acme, LLC produces three models of paint sprayers. A limitation of only 2,000 machine hours available...
- If you can run “traceroute/tracert” between machines in your organization. List three concrete applications to use...
- Discuss the differences among the terms cultural awareness, cultural sensitivity, cultural relevancy, cultural competence, and cultural...
- 1- Suppose you were tasked with developing the customer service qualities required for an external customer...