Excel Master It! Problem xlsx You want to calculate the WACC for auto parts retailer AutoZone (AZO). Complete the following steps to construct a spreadsheet that can be updated. a. Using an input for the ticker symbol, create hyperlinks to the web pages that you will need to find all of the information necessary to calculate the cost of equity. Use a market risk premium of 7 percent when using CAPM. b. Create hyperlinks to go to the FINRA bond quote website and the SEC EDGAR database and find the information for the company's bonds. Create a table that calculates the cost of debt for the company. Assume the tax rate is 21 percent. c. Finally, calculate the market value weights for debt and equity. What is the WACC for AutoZone? I have created this template for you to use to Estimate the weighted Average Cost of Capital for Swan Motors (Chapter 14 p494) Notes for estimating Swan Motors Cost of capital using TSLA data for Market Information Note that we will assume that the marginal tax rate is 21% Information needed to estimate the cost of Equity Capital for TSLA Equity Beta Input From yahoo finance Market Risk Premium (GIVEN) 8.60% From textbook pp399. Average large co stock return less Avg t-bill rate Current 3-month T-bill rate Input from Finra Return on Equity 0 CAPM Information to estimate Yield to maturity on debt TSLA had several debt issues outstanding. Ignore the Solar City Bonds You want to estimate the average Yield to maturity for the TSLA bonds outstanding. From yahoo. finance Amount outstanding in millions Interest Bearing Long Term Debt Currently Due Long Term Debt Total Interest bearing Debt $0.00 Average Yield to maturity from Morningstar 7.10% Use this estimate of YTM for Tesla. Note: I am giving you this YTM because TSLA has several convertible bonds outstanding and its quoted YTM (Morningstr) are misleading. The final step in the process is to find the MARKET VALUE weights of the TSLA capital Structure Equity Debt (Assume BV=MV) Total Capital Structure Use number of shares times stock price Only interest bearing debt here #DIV/0! $0.00 #DIV/0! $0.00 Percent Cost Tax Adjust (1-.21) AT Cost AT Cost*Weight WACC Equity Debt Excel Master It! Problem xlsx You want to calculate the WACC for auto parts retailer AutoZone (AZO). Complete the following steps to construct a spreadsheet that can be updated. a. Using an input for the ticker symbol, create hyperlinks to the web pages that you will need to find all of the information necessary to calculate the cost of equity. Use a market risk premium of 7 percent when using CAPM. b. Create hyperlinks to go to the FINRA bond quote website and the SEC EDGAR database and find the information for the company's bonds. Create a table that calculates the cost of debt for the company. Assume the tax rate is 21 percent. c. Finally, calculate the market value weights for debt and equity. What is the WACC for AutoZone? I have created this template for you to use to Estimate the weighted Average Cost of Capital for Swan Motors (Chapter 14 p494) Notes for estimating Swan Motors Cost of capital using TSLA data for Market Information Note that we will assume that the marginal tax rate is 21% Information needed to estimate the cost of Equity Capital for TSLA Equity Beta Input From yahoo finance Market Risk Premium (GIVEN) 8.60% From textbook pp399. Average large co stock return less Avg t-bill rate Current 3-month T-bill rate Input from Finra Return on Equity 0 CAPM Information to estimate Yield to maturity on debt TSLA had several debt issues outstanding. Ignore the Solar City Bonds You want to estimate the average Yield to maturity for the TSLA bonds outstanding. From yahoo. finance Amount outstanding in millions Interest Bearing Long Term Debt Currently Due Long Term Debt Total Interest bearing Debt $0.00 Average Yield to maturity from Morningstar 7.10% Use this estimate of YTM for Tesla. Note: I am giving you this YTM because TSLA has several convertible bonds outstanding and its quoted YTM (Morningstr) are misleading. The final step in the process is to find the MARKET VALUE weights of the TSLA capital Structure Equity Debt (Assume BV=MV) Total Capital Structure Use number of shares times stock price Only interest bearing debt here #DIV/0! $0.00 #DIV/0! $0.00 Percent Cost Tax Adjust (1-.21) AT Cost AT Cost*Weight WACC Equity Debt
