Week 7: Assignment – Analyst Case Report: The Home Depot
Web Page
You have viewed this topic
Points: 30 | Due Date: Week 7, Day 7 | CLO: 1, 2, 6 | Grade Category: Assignments
Assignment Prompt
You work at The Home Depot in the corporate finance division and your boss has asked you to review the firm’s capital structure. Specifically, your boss is considering changing the firm’s debt level but he isn’t quite sure if capital structure is relevant. Although you know that capital structure is irrelevant under the conditions of perfect markets you accept the assignment and start planning.
First, you assume that the weighted average cost of capital remains constant under various levels of debt, but you must showcase this to your boss. You consider two scenarios to keep your analysis modest for your boss:
The firm issues $1 billion in new debt to repurchase stock.
The firm issues $1 billion in new stock to repurchase debt.
Step One: Analysis with Excel
Use an Excel workbook to complete the following:
Obtain the financial information you need for Home Depot and input into Excel:
Go to www.nasdaq.com, and click “Quotes.” Enter Home Depot’s stock symbol (HD) and click “Summary Quotes.” From the Stock Quote and Summary Data page, get the current stock price. Click “Stock Report” in the left column and find the number of shares outstanding. Record these numbers in your spreadsheet.
Click “Income Statement” and the annual income statement should appear. Put the cursor in the middle of the statement, right-click your mouse, and select “Export to Microsoft Excel.” Then, explore the balance sheet to Excel as well. Then, make sure that both the income statement and balance sheet are on the same worksheet by cutting and pasting appropriately.
To get the cost of debt for Home Depot, go to NASD Bondinfo (finra-markets.morningstar.com). Under “Market Data” select “Bonds,” then select the “Search” option, enter Home Depot’s symbol, select the “Corporate” Bond Type and click “Show Results.” The next page will contain information for all of Home Depot’s outstanding and recently matured bonds. Select the latest yield on an outstanding bond with the shortest remaining maturity (the maturity date can be found on the line describing each issue; sometimes the list also contains recently retired bonds, so make sure not to use one of those). For simplicity, since you are just trying to illustrate the concepts, you may use the existing yield on the outstanding bond as
rD.
Once the data has been extracted, compute the market D/E ratio for Home Depot on a worksheet that has the stock price and number of shares outstanding that you recorded in the first step. Approximate the market value of debt by the book value of net debt; include both Long-Term Debt and Short-Term Debt/Current Portion of Long-Term Debt from the balance sheet and subtract any cash holdings. Use the stock price and number of shares outstanding to calculate the market value of equity.
Compute the cost of levered equity (rE) for Home Depot using their current market debt-to-equity ratio and the Eq. 14.5 in the text. Use the same worksheet that you used for number 2.
Compute the current weighted average cost of capital (WACC) for Home Depot using Eq. 14.6 in the text given their current debt-to-equity ratio. Use the same worksheet that you used for number 2 & 3.
Repeat steps 3 and 4 for the two scenarios above. Despite knowing that the cost of debt capital rD may change in leverage, you decide to assume that rD remains constant. Report the market D/E ratio in each of these cases on a separate worksheet in your Excel workbook.
Step Two: Reporting Results
In a two to three page paper APA formatted type a report to your boss explaining the relationship between capital structure and the cost of capital. Use at least five sources outside the weekly readings to help explain this relationship and include the information you gained from your analysis. Also, discuss the implicit assumptions that are drawn from the results found in question number 5 under step one. Include an explanation of how these results may differ in the real world. Don’t forget to have a title page and reference page. You’ll turn in both your Excel spreadsheet and your paper for this assignment. Reach out to your instructor with any questions.
Expectations
Due: Monday, 11:59 pm PT
Length: excel workbook + 2 page report with title page
Style Format: APA + title page
References Required: 5 sources outside weekly readings
File Format: Word Doc and Excel Workbook
File Name Required: First Initial_Last Name_Course Number_Module Number
See USU Written Assignment Grading Rubric with APA – MBA for additional details and point weighting.
Follow all the Instructions. There is Excel Report and Words 3 Page report.
