Need help with (only) question 6 of the data case:

Here is all the required data:

I would appreciate it if the answer is in-depth (not merely 1 or 2 sentences) and describes the relationship between the change of debt and the change of share price as well as discussing whether it is a good idea to raise $1 billion as opposed to $5 billion of debt to repurchase stock (why or why not). Also, please discuss any discuss potential issues that the executives of Home Depot raise.

Corporate Finance (4th Edition 2017) Jonathan Berk, Peter DeMarzo.pdf (page 583 of 1,172) Q Search Your boss was impressed with your presentation regarding the irrelevance of capital structure from Chapter 14 but, as expected, has realized that market imperfections like taxes must be accounted for. You have now been asked to include taxes in your analysis. Your boss knows that interest is deduct- ible and has decided that the stock price of Home Depot should increase if the firm increases its use of debt. Thus, your boss wants to propose a share repurchase program using the proceeds from a new debt issue and wants to present this plan to the CEO and perhaps to the Board of Directors. Your boss would like you to examine the impact of two different scenarios, adding a modest level of debt and adding a higher level of debt. In particular, your boss would like to consider issuing $1 billion in new debt or $5 billion in new debt. In either case, Home Depot would use the proceeds to repurchase stock. 1. Using the financial statements for Home Depot that you downloaded in Chapter 14, determine the average corporate tax rate for Home Depot over the last four years by dividing Income Tax by Earnings before Tax for each of the last four years. 2. Begin by analyzing the scenario with $1 billion in new debt. Assuming the firm plans to keep this new debt outstanding forever, determine the present value of the tax shield of the new debt. What additional assumptions did you need to make for this calculation? 3. Determine the new stock price if the $1 billion in debt is used to repurchase stock. a. Use the current market value of Home Depot's equity that you calculated in Chapter 14. b. Determine the new market value of the equity if the repurchase occurs. c. Determine the new number of shares and the stock price after the repurchase is announced. 4. What will Home Depot's D/E ratio based on book values be after it issues new debt and repur- chases stock? What will its market value D/E ratio be? 5. Repeat Steps 2-4 for the scenario in which Home Depot issues $5 billion in debt and repurchases stock. 6. Based on the stock price, do the debt increase and stock repurchase appear to be a good idea? Why or why not? What issues might the executives of Home Depot raise that aren't considered in your analysis? Note: Updates to this data case may be found at www.berkdemarzo.com. engan Austrana Table 2: Scenario Analysis - "$1 billion versus $5 billion of debt?" Assuming debt is permanent Panel A: Raising $1 billion of debt Assuming debt to per Table 2. Scenario Analysis - "$1 billion versus S5 billion Step 1: Recap Annouced Step 2: Debt Issuance Step 3: Share Repurchase Market Value of Balance Sheet Current iket Value of Balance sheet in sCurrent0840900 Annouces 474.0560 Issuances 474.0560 Repurchase. 4.0560 1190000 1190000 1190000 1081704.7090 (2) Num. Shr Outstanding (integer) (3) Price per shr (in $) = (1)/(2) (rounded to two digits) 9.11 9.23 9.23 9.23 Num. of Shr repurchased = 108295.2910 "Fair" Repurchase Price (in $) = 9.2340 Note: All numbers in thousands except for 'Price per shr' and 'Fair" Repurchase price Panel B: Raising $5 billion of debt Step 1: Recap Step 2: Debt Step 3: Share Market Value of Balance Sheet Current Annouced Issuance Repurchase (1) Equity (in $) 10840900 11578770.2820 11578770.2820 6578770.2820 1190000 1190000 1190000 676128.5046 (2) Num. Shr Outstanding (integer) (3) Price per shr (in $) = (1)/(2) (rounded to two digits) 9.11 9.73 9.73 9.73 Num. of Shr repurchased | "Fair" Repurchase Price (in $) = Note: All numbers in thousands 513871.4954 9.7301 Table 3: Scenario Analysis - Leverage and WACC (0) corporate tax rate (in %) 14.7574% Scenario 1: $1billion of debt to repurchase Scenario 2: $5billion of stocks debt to repurchase stocks (1) Market Value of Net Debt (approximated by book value of net debt, in $) 4593600 8593600 (2) Market Value of Equity (in $) 9988474.0563 6578770.2815 (3) D/E ratio (rounded to 4 digits) 0.4599 1.3063 (4) E/(D+E) 0.6850 0.4336 (5) D/(D+E) 0.3150 0.5664 (6) r D (in %) 1.8080% 1.8080% (7) r_E (in %) 16.6872% 25.3134% (8) Pre-tax WACC (in %) 12% 12% (9) WACC (in %) 11.9159% 11.8489% Note: All numbers in thousands (except for all ratios and rates) Corporate Finance (4th Edition 2017) Jonathan Berk, Peter DeMarzo.pdf (page 583 of 1,172) Q Search Your boss was impressed with your presentation regarding the irrelevance of capital structure from Chapter 14 but, as expected, has realized that market imperfections like taxes must be accounted for. You have now been asked to include taxes in your analysis. Your boss knows that interest is deduct- ible and has decided that the stock price of Home Depot should increase if the firm increases its use of debt. Thus, your boss wants to propose a share repurchase program using the proceeds from a new debt issue and wants to present this plan to the CEO and perhaps to the Board of Directors. Your boss would like you to examine the impact of two different scenarios, adding a modest level of debt and adding a higher level of debt. In particular, your boss would like to consider issuing $1 billion in new debt or $5 billion in new debt. In either case, Home Depot would use the proceeds to repurchase stock. 1. Using the financial statements for Home Depot that you downloaded in Chapter 14, determine the average corporate tax rate for Home Depot over the last four years by dividing Income Tax by Earnings before Tax for each of the last four years. 2. Begin by analyzing the scenario with $1 billion in new debt. Assuming the firm plans to keep this new debt outstanding forever, determine the present value of the tax shield of the new debt. What additional assumptions did you need to make for this calculation? 3. Determine the new stock price if the $1 billion in debt is used to repurchase stock. a. Use the current market value of Home Depot's equity that you calculated in Chapter 14. b. Determine the new market value of the equity if the repurchase occurs. c. Determine the new number of shares and the stock price after the repurchase is announced. 4. What will Home Depot's D/E ratio based on book values be after it issues new debt and repur- chases stock? What will its market value D/E ratio be? 5. Repeat Steps 2-4 for the scenario in which Home Depot issues $5 billion in debt and repurchases stock. 6. Based on the stock price, do the debt increase and stock repurchase appear to be a good idea? Why or why not? What issues might the executives of Home Depot raise that aren't considered in your analysis? Note: Updates to this data case may be found at www.berkdemarzo.com. engan Austrana Table 2: Scenario Analysis - "$1 billion versus $5 billion of debt?" Assuming debt is permanent Panel A: Raising $1 billion of debt Assuming debt to per Table 2. Scenario Analysis - "$1 billion versus S5 billion Step 1: Recap Annouced Step 2: Debt Issuance Step 3: Share Repurchase Market Value of Balance Sheet Current iket Value of Balance sheet in sCurrent0840900 Annouces 474.0560 Issuances 474.0560 Repurchase. 4.0560 1190000 1190000 1190000 1081704.7090 (2) Num. Shr Outstanding (integer) (3) Price per shr (in $) = (1)/(2) (rounded to two digits) 9.11 9.23 9.23 9.23 Num. of Shr repurchased = 108295.2910 "Fair" Repurchase Price (in $) = 9.2340 Note: All numbers in thousands except for 'Price per shr' and 'Fair" Repurchase price Panel B: Raising $5 billion of debt Step 1: Recap Step 2: Debt Step 3: Share Market Value of Balance Sheet Current Annouced Issuance Repurchase (1) Equity (in $) 10840900 11578770.2820 11578770.2820 6578770.2820 1190000 1190000 1190000 676128.5046 (2) Num. Shr Outstanding (integer) (3) Price per shr (in $) = (1)/(2) (rounded to two digits) 9.11 9.73 9.73 9.73 Num. of Shr repurchased | "Fair" Repurchase Price (in $) = Note: All numbers in thousands 513871.4954 9.7301 Table 3: Scenario Analysis - Leverage and WACC (0) corporate tax rate (in %) 14.7574% Scenario 1: $1billion of debt to repurchase Scenario 2: $5billion of stocks debt to repurchase stocks (1) Market Value of Net Debt (approximated by book value of net debt, in $) 4593600 8593600 (2) Market Value of Equity (in $) 9988474.0563 6578770.2815 (3) D/E ratio (rounded to 4 digits) 0.4599 1.3063 (4) E/(D+E) 0.6850 0.4336 (5) D/(D+E) 0.3150 0.5664 (6) r D (in %) 1.8080% 1.8080% (7) r_E (in %) 16.6872% 25.3134% (8) Pre-tax WACC (in %) 12% 12% (9) WACC (in %) 11.9159% 11.8489% Note: All numbers in thousands (except for all ratios and rates)