Complete the following problem sets in Financial Management using Microsoft ® Ex

Question

Complete the following problem sets in Financial Management using Microsoft® Excel®:

Changing WACC and optimal choice. Austin Enterprises is currently an all-equity firm. The firm is considering selling debt (bonds) and retiring some of the equity. However, at each level of debt, debt becomes more expensive (cost of debt is rising), and the riskiness of the equity also rises with more and more debt. Using a spreadsheet, determine the best combination of debt and equity for Austin Enterprises if

The current beta of Austin Enterprises is 0.85.

The current market return is 12%.

The current risk-free rate is 3%.

The total equity is 20,000,000 shares at $25 per share.

Debt is sold in units of $2,000,000.

The first unit of debt has a cost of 7.5%.

The tax rate of Austin Enterprises is 40%.

For each additional unit of debt (each additional $2,000,000), the cost of debt rises by 0.85%, and the beta of Austin Enterprises rises by 0.025.

Where is the WACC the lowest? Graph the results of the changing WACC.

Explanation / Answer

WACC is lowest present only equity situation i.e 10.7%

Level of debt($ Mn) 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 54 56 58 60 62 64 66 68 70 72 74 76 78 Equity amount($ Mn) 500 498 496 494 492 490 488 486 484 482 480 478 476 474 472 470 468 466 464 462 460 458 456 454 452 450 448 446 444 442 440 438 436 434 432 430 428 426 424 422 Total Debt and Equity($ Mn) 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 Proportion of Debt 0 0.004 0.008 0.012 0.016 0.02 0.024 0.028 0.032 0.036 0.04 0.044 0.048 0.052 0.056 0.06 0.064 0.068 0.072 0.076 0.08 0.084 0.088 0.092 0.096 0.1 0.104 0.108 0.112 0.116 0.12 0.124 0.128 0.132 0.136 0.14 0.144 0.148 0.152 0.156 Proportion of equity 1 0.996 0.992 0.988 0.984 0.98 0.976 0.972 0.968 0.964 0.96 0.956 0.952 0.948 0.944 0.94 0.936 0.932 0.928 0.924 0.92 0.916 0.912 0.908 0.904 0.9 0.896 0.892 0.888 0.884 0.88 0.876 0.872 0.868 0.864 0.86 0.856 0.852 0.848 0.844 Equity beta 0.85 0.875 0.9 0.925 0.95 0.975 1 1.025 1.05 1.075 1.1 1.125 1.15 1.175 1.2 1.225 1.25 1.275 1.3 1.325 1.35 1.375 1.4 1.425 1.45 1.475 1.5 1.525 1.55 1.575 1.6 1.625 1.65 1.675 1.7 1.725 1.75 1.775 1.8 1.825 Market rate of return 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 Risk free rate o return 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 Market risk premium 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 0.09 Cost of Equity 0.1065 0.10875 0.111 0.11325 0.1155 0.11775 0.12 0.12225 0.1245 0.12675 0.129 0.13125 0.1335 0.13575 0.138 0.14025 0.1425 0.14475 0.147 0.14925 0.1515 0.15375 0.156 0.15825 0.1605 0.16275 0.165 0.16725 0.1695 0.17175 0.174 0.17625 0.1785 0.18075 0.183 0.18525 0.1875 0.18975 0.192 0.19425 Interest rate 0 0.075 0.0835 0.092 0.1005 0.109 0.1175 0.126 0.1345 0.143 0.1515 0.16 0.1685 0.177 0.1855 0.194 0.2025 0.211 0.2195 0.228 0.2365 0.245 0.2535 0.262 0.2705 0.279 0.2875 0.296 0.3045 0.313 0.3215 0.33 0.3385 0.347 0.3555 0.364 0.3725 0.381 0.3895 0.398 1-tax rate 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 0.6 Cost of debt 0 0.045 0.0501 0.0552 0.0603 0.0654 0.0705 0.0756 0.0807 0.0858 0.0909 0.096 0.1011 0.1062 0.1113 0.1164 0.1215 0.1266 0.1317 0.1368 0.1419 0.147 0.1521 0.1572 0.1623 0.1674 0.1725 0.1776 0.1827 0.1878 0.1929 0.198 0.2031 0.2082 0.2133 0.2184 0.2235 0.2286 0.2337 0.2388 WACC 0.107 0.108 0.111 0.113 0.115 0.117 0.119 0.121 0.123 0.125 0.127 0.130 0.132 0.134 0.137 0.139 0.141 0.144 0.146 0.148 0.151 0.153 0.156 0.158 0.161 0.163 0.166 0.168 0.171 0.174 0.176 0.179 0.182 0.184 0.187 0.190 0.193 0.195 0.198 0.201 Increase 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.002 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003 0.003

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.