You will work out of excel for this problem. Add a worksheet that says Solutions. Locate the yield to maturity for ten-year Treasury bonds by going to finance.yahoo.com. Then, under the Markets tab select the US Treasury Bond Rates. You'll find this number listed as 10 Yr. Bond. make sure to record this number in your solutions worksheet. In the box at the top of the screen, type Walt Disney's ticker symbol (DIS) and press enter. Once you see the basic information for Disney, find and click Statistics under the quote. From the key statistics, collect the following: Disney's market capitalization (its market value of equity) Enterprise value (market-value equity+net debt) Cash Beta Go to finra-markets.morningstar.com. Under Market Data select Bonds. Under Search, click Corporate, and type Disney's ticker symbol (DIS).
A list of Disney's outstanding bond issues will appear. Assume that Disnye's policy is to use the expected return on noncallable ten-year obligations as its cost of debt. Find the noncallable bond issue that is at least 10 years from maturity. (HInt: you will see a column titled Callable make sure the issue you choose has No in this column. Bonds may appear on multiple pages.) Find the credit rating and yield to maturity for your chosen bond issue (it is in the column titled Yield). Hold the mouse over the table of Disneyâ€™s bonds and right-click. Select Export to Microsoft Excel. Once the Excel spreadsheet appears move the data into a worksheet in your Excel workbook. This data will be used for analysis.
Return to the Web page, click Walt Disney Company in the first row. This brings up a Web page with all of the information about the bond issue. Scroll down until you find Amount Outstanding on the right-hand side of the browser screen. (Note that this amount is quoted in thousands of dollars, e.g. $60,000 means $60 million = $60,000,000). Record the issue amount in the appropriate row of our worksheet and repeat this step for all the bond issues. Steps for Analysis: To calculate the market value of Disney's debt you will multiply the amount outstanding by (Price/100). Remember that the price for each bond issue in your spreadsheet is reported as a percentage of the bond's par value. For example, 104.50 means that the bond issue is trading at 104.50% of its par value. You'll place the total market value of Disney's debt in your solutions section of your Excel workbook. Compute the weights for Disney's equity and debt based on the market value of equity and Disney's market value of debt, computed in step number one of this section. These should be reported as percentages on your solutions worksheet in your Excel workbook. Calculate Disney's cost of equity capital using the CAPM, see Steps to Obtaining Data Step 1 to obtain the risk-free rate and use a market risk premium of 5%.
Report this calculation on your solutions worksheet in your Excel workbook. Assuming that Disney has a tax rate of 20%, calculate its after-tax debt cost of capital. Report this calculation on your solutions worksheet in your Excel workbook Calculate Disney's WACC and place it in the solution worksheet of Excel workbook. Calculate Disney's net debt by subtracting its cash (see Steps to Obtaining Data Step 2) from its debt. Then, recalculate the weights for the WACC using the market value of equity, net debt, and enterprise value. Recalculate Disney's WACC using the weights based on net debt. Then, in a one to two page paper discuss how much WACC changes when recalculating using net debt versus not using net debt. Are you confident in your estimation? Why or why not? Finally, detail implicit assumptions you made during the exercise of collecting the data. Why were these assumptions made? Because this assignment is an analysis exercise requesting your input, you will not be required to put it in APA formatting. However, please be sure to have a title page along with your paper. Then, post your paper and your Excel Finally, reach out to your professor with any questions.