Home Depot -- 2019 Pg. 2 CAUTION Do NOT do a Cuf operation on any cell in the project. Vertical Analysis of the Income Statement (Common-Size Statements) Use the data from Home Depot's Consolidated Statements of Earnings to fill in Fiscal 2019 below. Enter the amounts exactly as they are shown on the financial statement, which are in millions. The Home Depot, Inc. Consolidated Statements of Earnings (Dollars in Millions) Fiscal 2019 ended February 2, 2020 Dollars % Fiscal 2018 ended February 3, 2019 Dollars % Fiscal 2017 ended January 28, 2018 Dollars % $ 110,225 100.00% 72,653 65.91% 37,572 34.09% $ 108,203 100.00% 71,043 65.66% 37,160 34.34% $ 100,904 100.00% 66,548 65.95% 34,356 34.05% Impairment Net Sales Cost of Sales Gross Profit Operating Expenses: Selling, General and Administrative Depreciation and Amortization loss Total Operating Expenses Operating Income Interest and Other (Income) Expense: Interest and Investment Income Interest Expense Other Interest and Other, net Earnings Before Provision for Income Taxes Provision for Income Taxes Net Earnings 19,740 1,989 0 21,729 15,843 17.91% 1.80% 0.00% 19.71% 14.37% 19,513 1,870 247 21,630 15,530 18.03% 1.73% 0.23% 19.99% 14.35% 17,864 1,811 0 19,675 14,681 17.70% 1.79% 0.00% 19.50% 14.55% (73) (0.07%) 1,201 1.09% 0 0.00% 1,128 1.02% 14,715 13.35% 3,473 3.15% (93) 1.051 16 974 14,556 3,435 (0.09%) 0.97% 0.01% 0.90% 13.45% 3.17% (74) 1,057 0 983 13,698 5,068 (0.07%) 1.05% 0.00% 0.97% 13.58% 5.02% S 11,242 10.20% $ 11,121 10.28% $ 8,630 8.55% Tip: Home Depot labels Net Income as "Net Earnings" Basic Earnings per Share $10.29 $9.78 $7.33 ** % columns may not add-up due to rounding Home Depot -- Fiscal 2019 Pg. 3 Horizontal Analysis of the Income Statement CAUTION Do NOT do a Cut operation on any cell in the project. Enter the amounts for Fiscal 2019 for the following income statement items. Then calculate and enter the Dollar Change and the % Change from Fiscal 2018 to Fiscal 2019. Enter the dollar amounts just as they are shown on the financial statement. Round the percentages to 2 decimal places. xx.xx% Fiscal 2018 Dollar Change % Change 2017 to 2018 Fiscal 2017 Fiscal 2019 Sxxx,xxx $110,225 Tip: Most cells that require input have a cell format indicator above or below the cell. These format indicators show the type of number required for the cell. These format indicators do not show the size of the number expected for the cell. For example: $xxx,xxx shows that the input for the cell should be whole dollars with no decimal point. The $xxx,xxx does not indicate that the number should be a 6 digit number. The correct answer for the cell could be as small as 1 digit or as large as 14 digits. Net Sales $108,203 $100,904 Dollar Change % Change 2018 to 2019 Sxxx xxx $2,022 1.87 % xx.xx% $XXX,XXX $1,610 2.27 % xx.xx% Sxxx.xxx $7,299 7.23% Sxxx,XXX $72,653 Cost of Sales $71,043 $66,548 $4,495 6.75 % Sxxx, xxx Gross Profit $37,572 $412 $37,160 $2,804 8.16 % $34,356 Tip: When entering a formula, be sure to begin the formula with an "-" sign. $XXX,XXX $21,729 1.11 % xx.20% $XXX,XXX $99 0.46 % xx.xx% Total Operating Expenses $21,630 $19,675 $1,955 9.94% Sxxx.xxx $15,843 Operating Income $15,530 $14,681 $849 5.78 % Sxxx,XXX $3,473 $3,435 $5,068 Provision for Income Taxes ($1,633) (32.22) % $xxx,xxx $313 2.02 xx.xx% $XXX,XXX $38 1.11 % xx.xx% Sxxx XXX $121 1.09 % xx.xx% Sxx.xx $0.51 5.21 % xx.xx% Sxxx,xxx $11,242 Net Earnings $11,121 $2,4911 $8,630 28.86 % $xx.xx Basic Earnings Per Share $10.29 $9.78 $2.45 $7.33 33.42% Fix $xx.xx Cell Format Home Depot -- 2019 Pg. 4 CAUTION Do NOT do a Cut operation on any cell in the project. Vertical Analysis of the Balance Sheet (Common-Size Statements) Use the data from Home Depot's Consolidated Balance Sheets to fill in Fiscal 2019 below. Enter the amounts, exactly as they are shown on the financial statement, which are in millions. The Home Depot Consolidated Balance Sheets (Dollars in Milions) Fiscal 2019 as of February 2, 2020 Dollars % Fiscal 2018 as of February 3, 2019 Dollars % $ $ Assets Current Assets Cash and Cash Equivalents Receivables, net Merchandise Inventories Other Current Assets Total Current Assets Net Property and Equipment Operating Lease Right-of-Use Assets Goodwill Other Assets Total Assets 2,133 4.16% 2,106 4.11% 14,531 28.36% 1,040 2.03% 19,810 38.66% 22,770 44.44% 5,595 10.92% 2,254 4.40% 807 1.58% 51,236 100.00% 1,778 4.04% 1,936 4.40% 13.925 31.65% 890 2.02% 18,529 42.11% 22,375 50.85% 0 0.00% 2.252 847 1.92% 44,003 100.00% 5.12% $ $ $ $ 974 7,787 1.90% 15.20% 2.92% 1,494 Liabilities and Stockholders' Equity Current Liabilities: Short-Term Debt Accounts Payable Accrued Salaries and Related Expenses Sales Taxes Payable Deferred Revenue Income Taxes Payable Current Installments of Long-Term Debt Current Operating Lease Liabilities Other Accrued Expenses Total Current Liabilities 1.18% 605 2,116 55 1,839 828 2,677 18,375 4.13% 0.11% 3.59% 1.62% 5.22% 35.86% 1,339 7,755 1,506 656 1,782 11 1,056 0 2,611 16,716 3.04% 17.62% 3.42% 1.49% 4.05% 0.02% 2.40% 0.00% 5.93% 37.99% Long-Term Debt, excluding current installments Long-Term Operating Lease Liabilities Deferred Income Taxes Other Long-Term Liabilities Total Liabilities 28,670 55.96% 5,066 9.89% 706 1.38% 1,535 3.00% 54,352 106.08% 26,807 0 491 1,867 45,881 60.92% 0.00% 1.12% 4.24% 104.27% Stockholders' Equity: Common Stock Paid-In Capital Retained Earnings Accumulated Other Comprehensive (Loss) Income Treasury Stock, at cost Total Stockholders' (Deficit) Equity Total Liabilities and Stockholders' Equity 89 0.17% 11,001 21.47% 51,729 100.96% (739) (1.44%) (65, 196) (127.25%) (3,116) (6.08%) 51,236 100.00% 89 0.20% 10,578 24.04% 46,423 105.50% (772) (1.75%) (58,196) (132.25%) ) (1,878) (4.27%) 44,003 100.00% $ $ ** % columns may not add-up due to rounding Home Depot - Fiscal 2019 Pg. 5 Horizontal Analysis of the Balance Sheet CAUTION Do NOT do a Cut operation on any cell in the project. For the following balance sheet items enter the amounts for Fiscal 2019, and then calculate the Dollar Change and the % Change from 2018 to 2019. Enter the 2019 dollar amounts as shown on the financial statement, which are in millions. Round the percentages to 2 decimal places. xx.xx% Tip: Most cells that require input have cell format indicator above or below the cell. These format indicators show the type of number required for the cell. These format indicators do not show the size of the number expected for the cell. Fiscal 2018 Fiscal 2019 Sxxx,xxx $2,133 Cash and Cash Equivalents $1,778 For example: $XXX,XXX shows that the input for the cell should be whole dollars with no decimal point (no cents). The $XXX,XXX does not indicate that the number should be a 6 digit number. The correct answer for the cell could be as small as 1 digit or as large as 14 digits. Sxxx.xxx $14,531 Dollar Change % Change 2018 to 2019 Sxxx, xxx $355 19.97 % xox.xo% Sxxx,xxx $606 4.35 % xx.xx% Sxxx.xxx $7,233 16.44 % xx.x% Merchandise Inventories $13,925 Sxxx.xxx $51,236 Tip: When entering a formula, be sure to begin the formula with an "=" sign. Total Assets $44,003 Sxxx,xxx $7,787 Accounts Payable Sxxx.xxx $32 0.41 % XX.XX% $7,755 Sxxx, xxx Total Current Liabilities $18,375 $16,716 Sxxx.xxx $28,670 $26,807 Long-Term Debt, excluding current installments Sxxx.xxx $54,352 Total Liabilities $45,881 Sxxx,xxx $1,659 9.92 % xx.xx% Sxxx.xxx $1,863 6.95 % xx.xx% Sxx.xocx $8,471 18.46 % oc.xx% Sxxx.xxx $5,306 11.43 % xx.0% Sxxx.xxx ($1,245) 66.29% XX.XX% Sxxx.xxx Retained Earnings $51,729 $46,423 Tip: The sign of the % Change should be the same as the sign of the Dollar Change. Since the Dollar Change will be negative, the % Change should also be negative. Sxxx,xxx ($3,116) Total Stockholders' (Deficit) Equity ($1,878) Check your % Change answer by using the Excel workbook 2-Percent Change Calculator Home Depot -- Fiscal 2019 Pg. 6 DuPont Analysis -- Return on Assets CAUTION Do NOT do a Cut operation on any cell in the project. Apply DuPont Analysis and the Return on Assets % to evaluate Home Depot's overall financial performance. Use the information in Home Depot's Income Statement and Balance Sheets for Fiscal 2019 and Fiscal 2018 to calculate the four DuPont Ratios and the Return on Assets %. In the left-hand column enter the names of the financial statement items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round all averages to decimal places -- a whole number $xxx, xxx Round the percentages to 2 decimal places. xx.x% Round the decimal results to 2 decimal places. XX.XX Note: Since each ratio is being rounded, it is possible that Return on Equity % will not exactly equal (Profitability x Efficiency x Leverage). Calculate each ratio separately. Do not calculate Return on Equity % by multiplying (Profitability x Efficiency x Leverage). Fix xx.xx% and xx.xx Cell Formats Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot's financial statements. These names will not be graded by the grading software, but might be graded by your instructor. DuPont Analysis 2019 2018 (Profitability) Net Profit Margin % Sxxx.xxx Net Earnings XX.XX% % 10.28 % Sxxx.xxx Tip: If you use formulas to calculate the Average cells, be sure to use the Round function to round to 0 decimal places. (Efficiency) Total Assets Turnover Ratio Sxxx.xxx For example, the following formula will calculate the value in cell 136 and round the result to 0 decimal places: XX.XX times 2.44 Beg. Yr. Amt. End. Yr. Amt. Average =Round(( E36 + G36)/2,0) $xxx XXX Sxxx XXX , Sxxx,xxx X (Leverage) Assets-to-Equity Ratio Sxxx.xxx N/A to 1 NIA Average SxXX,XXX The Assets-to-Equity Ratio and the Return on Equity % are grayed out because the ratios are N/A for 2018 and 2019. Home Depot had negative Stockholders' Equity for 2018 and 2019, which makes these ratios meaningless. Stockholders' Equity was negative due to a large amount of repurchases of Common Stock. Tip: The calculation of Average Total Assets and the calculation of Average Total Stockholders' Equity are only shown the first time you need the average amount. Once you calculate the average amount, then use that average amount as needed in the other ratio calculations. Return on Equity % Sxxx.XXX % N/A % % N/A XX.XX% $xxx,xxx Return on Assets 2019 2018 Return on Assets % Sxxx.xxx XX.XX% % 25.12% Sxxx,xxx Home Depot -- Fiscal 2019 Pg. 7 CAUTION Do NOT do a Cut operation on any cell in the project. Profitability Indicators Apply the three Profitability Indicators to evaluate Home Depot's profitability. Use the information in Home Depot's Fiscal 2019 Income Statement to calculate the three Profitability Indicators. In the left-hand column enter the names of the items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round the percentages to 2 decimal places. xx.xx% Fix xx.xx% Cell Formats 2019 2018 Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot's financial statements. These names will not be graded by the grading software, but might be graded by your instructor. Gross Profit Margin % $xxx.xxx 34.34 % % XX.XX% $xxx,xxx Operating Profit Margin % $xxx.xxx 14.35 % % xx.xx% $xxx,xxx Net Profit Margin % $XXX, XXX % 10.28 % xx.xx% $xxx,xxx Home Depot -- Fiscal 2019 Pg. 8 CAUTION Do NOT do a Cut operation on any cell in the project. Efficiency Indicators Apply the Efficiency Indicators to drill down into Home Depot's efficiency. Use the information in Home Depot's Income Statement and Balance Sheets for Fiscal 2019 and Fiscal 2018 to calculate the Efficiency Indicators. In the left-hand column enter the names of the financial statement items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round all averages to 0 decimal places -- a whole number $xcx, xx Round the decimal results to 2 decimal places. xx.xx Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot's financial statements. These names will not be graded by the grading software, but might be graded by your instructor Fix xx.xx Cell Formats 2019 2018 Accounts Receivable Turnover Ratio Sxxx.xxx Tip: If you use formulas to calculate the times cells, be sure to use the Round() function to round to 2 decimal places. For example, the following formula will calculate the value in cell J21 and round the result to 2 decimal places: times Beg Yr. Amt End Yr Amt 55.66 Average XOLOK =Round( 120 / 122, 2) Socx, xox Sxxx.xxx $xxx, XXX Number of Days' Sales in Receivables Days in a Year (365) days 6.56 Accounts Receivable Turnover Ratio XOLOK xx.xx Tip: If you use formulas to calculate the Average cells, be sure to use the Round function to round to 0 decimal places For example, the following formula will calculate the value in cell 122 and round the result to 0 decimal places: =Round(( E22+G22)/2, 0) Inventory Turnover Ratio Sxxxxxx times Beg Yr Amt. End. Yr Amt. Average 5.33 XOLOK Sucxcx, xox Sxxx, xxx $xxx, xxx Number of Days' Sales in Inventory Days in a Year (365) XXX days 68.48 Inventory Turnover Ratio XL XX XX. XX Tip: If you use formulas to reference the days cells when calculating the Cash-to-Cash Days, be sure to use the Round) function to round all the days cells to 2 decimal places. For example, the following formula will calculate the value in days cell J28 and round the result to 2 decimal places: =Round( 127 / 129,2) 2 You can then use the following formula in cell J68: =J28 Accounts Payable Turnover Ratio times 9.47 Beg Yr. Amt End. Yr. Amt. Average XXXX Sxcxxxx Sxxx xxx Sxxx, XXX Number of Days' Purchases in Accounts Payable XXX Days in a Year (365) days 38.54 Accounts Payable Turnover Ratio XXXX XXXX Cash-to-Cash Cycle XXL XOK Number of Days' Sales in Inventory less Number of Days' Purchases in Accounts Payable Net Number of Days Cash is Invested in Inventory plus Number of Days' Sales in Receivables Cash-to-Cash Days 68.48 (38.54) 29.94 6.56 36.50 Home Depot -- Fiscal 2019 Pg. 9 CAUTION Do NOT do a Cut operation on any cell in the project. Leverage Indicators (at year-end) -- Interest Coverage Use the information in Home Depot's Fiscal 2019 Balance Sheet and Income Statement to calculate the two "year-end" Leverage Indicators and the Times Interest Earned Ratio. In the left-hand column enter the names of the items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round the percentages to 2 decimal places. xx.xx% Fix xx.xx% and xx.xx Round the decimal results to 2 decimal places. XX.XX Cell Formats 2019 2018 Debt % $xxx XXX % 104.27 % xx.xx% $XXX, XXX $ The Debt-to-Equity Ratio is grayed out because the ratio is N/A for 2018 and 2019. Home Depot had negative Stockholders' Equity for 2018 and 2019, which makes this ratio meaningless. Debt-to-Equity Ratio $xxx.xxx N/A N/A to 1 XX.XX $xxx, xxx Tip: Calculate "Earnings before interest and taxes" as follows: Times Interest Earned Ratio $XXX XXX Net Eamings plus Provision for Income Taxes plus Interest Expense equals Earnings before interest and taxes Earnings before interest and taxes 14.85 times XX.XX Interest Expense $xxx, xxx Home Depot -- Fiscal 2019 Pg. 10 CAUTION Do NOT do a Cut operation on any cell in the project. Liquidity Indicators Use the information in Home Depot's 2019 Balance Sheet to calculate the three Liquidity Indicators. In the left-hand column enter the names of the items that will be used in the calculation. Enter the financial statement dollar amounts just as they are shown on the Balance Sheet. Round the decimal results to 2 decimal places. XX.XX Fix xx.xx Cell Formats 2019 2018 Working Capital $xxx xxx Tip: Enter "Current Liabilities" as a negative number Working Capital $1,813 Current Ratio Sxxx XXX to 1 1.11 XX.XX Sxxx xxx (Acid Test Ratio) Quick Ratio $xxx, xxx Cash and Near-Cash Assets to 1 0.22 Tip: Home Depot does not have any Short-Term Investments XX.XX $xxx,XXX Home Depot -- Fiscal 2019 Pg. 11 CAUTION Do NOT do a Cut operation on any cell in the project. Statement of Cash Flows Indicators Use the information in Home Depot's Fiscal 2019 Consolidated Statements of Cash Flows to calculate the three Statement of Cash Flows Indicators. In the left-hand column enter the names of the items that will be used in the calculation. Enter the financial statement dollar amounts in millions of dollars as shown on the Consolidated Statements of Cash Flows. Round the decimal results to 2 decimal places. XX.XX Flx xx.xx Cell Formats 2019 2018 (Quality of Income Ratio) Cash Flow to Net Income Ratio Sxxx xxx to 1 1.17 XX.XX Sxxx,XXX (Cash Flow Adequacy Ratio) Cash Flow to Capital Expenditures Ratio Sxxx.xxx to 1 5.41 Net Cash Paid to Purchase PP&E XX.XX Sxxx.xxx Tip: "Net Cash Paid to Purchase PP&E" is calculated as follows: "Capital Expenditures" (make this a positive number) less "Proceeds from Sales of Property and Equipment equals Net Cash Paid to Purchase PP&E Free Cash Flow Sxxx xxx Net Cash Paid to Purchase PP&E Free Cash Flow $5,925 Home Depot - Fiscal 2019 Pg. 12 CAUTION Do NOT do a Cut operation on any cell in the project. Equity Investor Indicators Use the information in Home Depot's Consolidated Statements of Stockholders' Equity to calculate Home Depot's Dividend Payout %. Use the Year-End Closing Market Price of 1 Common Share and Home Depot's Consolidated Statement of Earnings to calculate the F Use the Year-End Closing Market Price of 1 Common Share and the information in Home Depot's Stockholders' Equity Footnote to calculate Home Depot's Dividend Yield % Enter the financial statement dollar amounts just as they are shown on the financial statements. Round the percentages to 2 decimal places. xx.xx% Fix xx.xx% and xx.xx Round the decimal results to 2 decimal places. XX.XX Cell Formats Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot 's financial statements. These names will not be graded by the grading software. 2019 2018 Dividend Payout % $xxx.xxx Tip: Enter the cash dividend amount as a positive number. % 42.30 % XLX0% $xxx.xxx Year-End Closing Market Price of 1 Common Share The Year-End Closing Market Price of 1 share of HD's Common Stock is not given in HD's annual report. The Year-End Closing Market Price is used in the two calculations below. The last day in HD's 2019 fiscal year was Sunday, Feb. 2, 2020. HD's common stock trades on the New York Stock Exchange (NYSE) and the NYSE is not open on weekends Thus HD's year-end closing price is as of Friday, Jan. 31, 2020. On Jan. 31, 2020, HD's common stock closed at $228.10. Enter that price in the box below. 2019 Year-End Closing Market Price of 1 Common Share: Sxxxx 2019 2018 (PE Ratio) Price-Earnings Ratio $xxxx times 18.85 Basic Eamings Per Share XX.XX $xx.xx Dividend Yield % $xx.xx Cash Dividends per share % xx.x% 2.23 % Tip: Look on the Stockholders' Equity Footnote (report page 56) for the Cash Dividends per share amount. $xxx Home Depot -- Fiscal 2019 Pg. 13 Analytical Observations CAUTION Do NOT do a Cut operation on any cell in the project. You will now utilize the information developed in Pages 2 to 12 to answer various analytical questions. Tip: You may find it easier to complete pages 13 to 16, if you print pages 2 to 12. 1. Vertical Analysis of the Income Statement (Pg. 2) 1> Which fiscal year had the highest Operating Income in terms of total dollars? ["x" only one answer ] 2019 2018 2017 2> Which fiscal year had the highest Total Operating Expenses as a % of Net Sales? ["x" only one answer ] 2019 2018 2017 3> Based on Gross Profit as a % of Net Sales, rank the fiscal years 2019, 2018, and 2017 in terms of which year had the highest. Place a "1" in the box of the highest year, a "2" in the box of the second highest year, etc. 2019 2018 2017 4> Which fiscal year had the highest Net Earnings as a % of Net Sales? ["x" only one answer ] 2019 2018 2017 2. Horizontal Analysis of the Income Statement (Pg. 3) 1> Rank the following items in terms of which had the largest % increase from Fiscal 2017 to Fiscal 2018. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Net Sales Cost of Sales Operating Income Net Earnings 2> Rank the following items in terms of which had the largest % increase from Fiscal 2018 to Fiscal 2019. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Net Sales Cost of Sales Operating Income Net Earnings 3> True or False? In terms of % Change, Basic Earnings Per Share grew more than Net Earnings from Fiscal 2018 to Fiscal 2019. ["x" only one answer ] True False 4> True or False? In terms of % Change, from Fiscal 2018 to Fiscal 2019 Total Operating Expenses increased more than Net Sales. ["x" only one answer ] True False 5> True or False? In terms of % Change, Cost of Sales had a smaller increase than Net Sales from Fiscal 2018 to Fiscal 2019. ["x" only one answer ] True False Home Depot -- Fiscal 2019 Pg. 14 Analytical Observations CAUTION Do NOT do a Cut operation on any cell in the project. 3. Vertical Analysis of the Balance Sheet (Pg.4) 1> For fiscal year 2019, rank the following assets in terms of which was the largest as a % of Total Assets. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Cash and Cash Equivalents Receivables, net Merchandise Inventories Net Property and Equipment 2> For fiscal year 2019, rank the following items in terms of which was the largest as a % of Total Liabilities and Stockholders' Equity. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Long-Term Debt, excluding Accounts Payable current installments Paid-in Capital Retained Earnings 4. Horizontal Analysis of the Balance Sheet (Pg. 5) 1> Rank the following items in terms of which had the largest % increase from Fiscal 2018 to Fiscal 2019. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Merchandise Long-Term Debt, excluding Inventories Accounts Payable current installments Retained Earnings 2> Rank the following items in terms of which had the largest Dollar increase from Fiscal 2018 to Fiscal 2019. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Merchandise Long-Term Debt, excluding Inventories Accounts Payable current installments Retained Earnings 5. DuPont Analysis -- Return on Assets Ratio (Pg. 6) 1> True or False? Profitability increased from Fiscal 2018 to Fiscal 2019? ["x" only one answer ] True False 2> True or False? Efficiency increased from Fiscal 2018 to Fiscal 2019? ["x" only one answer ] True False 3> Which fiscal year has the highest Return on Assets %? ["X' only one answer ] 2019 2018 6. Profitability Indicators (Pg. 7) 1> Which fiscal year, 2019 or 2018, had higher profitability based on Gross Profit Margin % and Net Profit Margin ["x" only one answer ] 2019 2018 Home Depot -- Fiscal 2019 Pg. 15 CAUTION Do NOT do a Cut operation on any cell in the project. Analytical Observations 7. Efficiency Indicators (Pg.8) 1> Based on both of the Accounts Receivable Efficiency Indicators, in which fiscal year, 2019 or 2018, did Home Depot more efficiently manage the Accounts Receivable asset? [ "x" only one answer] .2019 2018 2> Based on both of the Inventory Efficiency Indicators, in which fiscal year, 2019 or 2018, did Home Depot have an item in inventory for the shortest amount of time? ["x" only one answer ] 2019 2018 3> Based on the Number of Days' Purchases in Accounts Payable, in which fiscal year, 2019 or 2018, did Home Depot hold on to its cash longer before paying the cash to vendors? [ "x" only one answer ] 2019 2018 4> Which fiscal year, 2019 or 2018, had the better Cash-to-Cash Cycle? [ "x" only one answer 2019 2018 5> In which fiscal year, 2019 or 2018, did Home Depot have the better Fixed Asset Turnover Ratio? [ "x" only one answer ] 2019 2018 8. Leverage Indicators (at year-end) - Interest Coverage (Pg. 9) 1> Based on the Debt %, who had the greatest claim to Home Depot's assets in Fiscal 2019? ["x" only one answer ] Creditors Shareholders 2> True or False? In Fiscal 2019 more than half of Home Depot's Total Assets were financed with debt? ["x" only one answer ] True False 3> Based on the Times Interest Earned Ratio, were the creditors' interest payments more secure in Fiscal 2019 or Fiscal 2018? ["x" only one answer ] 2019 2018 9. Liquidity Indicators (Pg. 10) 1> Based on Working Capital and the Current Ratio, did Liquidity increase or decrease from Fiscal 2018 to Fiscal 2019? ["x" only one answer ] Liquidity Increased Liquidity Decreased Home Depot -- Fiscal 2019 Pg. 16 CAUTION Do NOT do a Cut operation on any cell in the project. Analytical Observations 10. Statement of Cash Flows Indicators (Pg. 11) 1> True or False? By looking at the Cash Flow to Capital Expenditure Ratio it can be seen that Home Depot was able to internally fund the purchases of Fixed Assets in Fiscal 2019 and in Fiscal 2018. ["x" only one answer ] True False 2> In which fiscal year, 2019 or 2018, did Home Depot have more Free Cash Flow? ["x" only one answer ] 2019 2018 11. Equity Investor Indicators (Pg. 12) 1> True or False? Based on the Dividend Payout % it can be seen that Home Depot paid out a larger percent of Net Income, in the form of Cash Dividends, in Fiscal 2019 than in Fiscal 2018. ["x" only one answer ] True False 2> Based on the Price-Earnings Ratio, in which fiscal year, 2019 or 2018, did investors place a higher value on Home Depot's earnings? ["x" only one answer ] 2019 2018 3> Based on the Dividend Yield %, in which fiscal year, 2019 or 2018, did an investor in Home Depot's Common Stock earn a higher return from the cash dividends she received on her Home Depot Common Stock? ["x" only one answer ) 2019 2018 Home Depot -- 2019 Pg. 2 CAUTION Do NOT do a Cuf operation on any cell in the project. Vertical Analysis of the Income Statement (Common-Size Statements) Use the data from Home Depot's Consolidated Statements of Earnings to fill in Fiscal 2019 below. Enter the amounts exactly as they are shown on the financial statement, which are in millions. The Home Depot, Inc. Consolidated Statements of Earnings (Dollars in Millions) Fiscal 2019 ended February 2, 2020 Dollars % Fiscal 2018 ended February 3, 2019 Dollars % Fiscal 2017 ended January 28, 2018 Dollars % $ 110,225 100.00% 72,653 65.91% 37,572 34.09% $ 108,203 100.00% 71,043 65.66% 37,160 34.34% $ 100,904 100.00% 66,548 65.95% 34,356 34.05% Impairment Net Sales Cost of Sales Gross Profit Operating Expenses: Selling, General and Administrative Depreciation and Amortization loss Total Operating Expenses Operating Income Interest and Other (Income) Expense: Interest and Investment Income Interest Expense Other Interest and Other, net Earnings Before Provision for Income Taxes Provision for Income Taxes Net Earnings 19,740 1,989 0 21,729 15,843 17.91% 1.80% 0.00% 19.71% 14.37% 19,513 1,870 247 21,630 15,530 18.03% 1.73% 0.23% 19.99% 14.35% 17,864 1,811 0 19,675 14,681 17.70% 1.79% 0.00% 19.50% 14.55% (73) (0.07%) 1,201 1.09% 0 0.00% 1,128 1.02% 14,715 13.35% 3,473 3.15% (93) 1.051 16 974 14,556 3,435 (0.09%) 0.97% 0.01% 0.90% 13.45% 3.17% (74) 1,057 0 983 13,698 5,068 (0.07%) 1.05% 0.00% 0.97% 13.58% 5.02% S 11,242 10.20% $ 11,121 10.28% $ 8,630 8.55% Tip: Home Depot labels Net Income as "Net Earnings" Basic Earnings per Share $10.29 $9.78 $7.33 ** % columns may not add-up due to rounding Home Depot -- Fiscal 2019 Pg. 3 Horizontal Analysis of the Income Statement CAUTION Do NOT do a Cut operation on any cell in the project. Enter the amounts for Fiscal 2019 for the following income statement items. Then calculate and enter the Dollar Change and the % Change from Fiscal 2018 to Fiscal 2019. Enter the dollar amounts just as they are shown on the financial statement. Round the percentages to 2 decimal places. xx.xx% Fiscal 2018 Dollar Change % Change 2017 to 2018 Fiscal 2017 Fiscal 2019 Sxxx,xxx $110,225 Tip: Most cells that require input have a cell format indicator above or below the cell. These format indicators show the type of number required for the cell. These format indicators do not show the size of the number expected for the cell. For example: $xxx,xxx shows that the input for the cell should be whole dollars with no decimal point. The $xxx,xxx does not indicate that the number should be a 6 digit number. The correct answer for the cell could be as small as 1 digit or as large as 14 digits. Net Sales $108,203 $100,904 Dollar Change % Change 2018 to 2019 Sxxx xxx $2,022 1.87 % xx.xx% $XXX,XXX $1,610 2.27 % xx.xx% Sxxx.xxx $7,299 7.23% Sxxx,XXX $72,653 Cost of Sales $71,043 $66,548 $4,495 6.75 % Sxxx, xxx Gross Profit $37,572 $412 $37,160 $2,804 8.16 % $34,356 Tip: When entering a formula, be sure to begin the formula with an "-" sign. $XXX,XXX $21,729 1.11 % xx.20% $XXX,XXX $99 0.46 % xx.xx% Total Operating Expenses $21,630 $19,675 $1,955 9.94% Sxxx.xxx $15,843 Operating Income $15,530 $14,681 $849 5.78 % Sxxx,XXX $3,473 $3,435 $5,068 Provision for Income Taxes ($1,633) (32.22) % $xxx,xxx $313 2.02 xx.xx% $XXX,XXX $38 1.11 % xx.xx% Sxxx XXX $121 1.09 % xx.xx% Sxx.xx $0.51 5.21 % xx.xx% Sxxx,xxx $11,242 Net Earnings $11,121 $2,4911 $8,630 28.86 % $xx.xx Basic Earnings Per Share $10.29 $9.78 $2.45 $7.33 33.42% Fix $xx.xx Cell Format Home Depot -- 2019 Pg. 4 CAUTION Do NOT do a Cut operation on any cell in the project. Vertical Analysis of the Balance Sheet (Common-Size Statements) Use the data from Home Depot's Consolidated Balance Sheets to fill in Fiscal 2019 below. Enter the amounts, exactly as they are shown on the financial statement, which are in millions. The Home Depot Consolidated Balance Sheets (Dollars in Milions) Fiscal 2019 as of February 2, 2020 Dollars % Fiscal 2018 as of February 3, 2019 Dollars % $ $ Assets Current Assets Cash and Cash Equivalents Receivables, net Merchandise Inventories Other Current Assets Total Current Assets Net Property and Equipment Operating Lease Right-of-Use Assets Goodwill Other Assets Total Assets 2,133 4.16% 2,106 4.11% 14,531 28.36% 1,040 2.03% 19,810 38.66% 22,770 44.44% 5,595 10.92% 2,254 4.40% 807 1.58% 51,236 100.00% 1,778 4.04% 1,936 4.40% 13.925 31.65% 890 2.02% 18,529 42.11% 22,375 50.85% 0 0.00% 2.252 847 1.92% 44,003 100.00% 5.12% $ $ $ $ 974 7,787 1.90% 15.20% 2.92% 1,494 Liabilities and Stockholders' Equity Current Liabilities: Short-Term Debt Accounts Payable Accrued Salaries and Related Expenses Sales Taxes Payable Deferred Revenue Income Taxes Payable Current Installments of Long-Term Debt Current Operating Lease Liabilities Other Accrued Expenses Total Current Liabilities 1.18% 605 2,116 55 1,839 828 2,677 18,375 4.13% 0.11% 3.59% 1.62% 5.22% 35.86% 1,339 7,755 1,506 656 1,782 11 1,056 0 2,611 16,716 3.04% 17.62% 3.42% 1.49% 4.05% 0.02% 2.40% 0.00% 5.93% 37.99% Long-Term Debt, excluding current installments Long-Term Operating Lease Liabilities Deferred Income Taxes Other Long-Term Liabilities Total Liabilities 28,670 55.96% 5,066 9.89% 706 1.38% 1,535 3.00% 54,352 106.08% 26,807 0 491 1,867 45,881 60.92% 0.00% 1.12% 4.24% 104.27% Stockholders' Equity: Common Stock Paid-In Capital Retained Earnings Accumulated Other Comprehensive (Loss) Income Treasury Stock, at cost Total Stockholders' (Deficit) Equity Total Liabilities and Stockholders' Equity 89 0.17% 11,001 21.47% 51,729 100.96% (739) (1.44%) (65, 196) (127.25%) (3,116) (6.08%) 51,236 100.00% 89 0.20% 10,578 24.04% 46,423 105.50% (772) (1.75%) (58,196) (132.25%) ) (1,878) (4.27%) 44,003 100.00% $ $ ** % columns may not add-up due to rounding Home Depot - Fiscal 2019 Pg. 5 Horizontal Analysis of the Balance Sheet CAUTION Do NOT do a Cut operation on any cell in the project. For the following balance sheet items enter the amounts for Fiscal 2019, and then calculate the Dollar Change and the % Change from 2018 to 2019. Enter the 2019 dollar amounts as shown on the financial statement, which are in millions. Round the percentages to 2 decimal places. xx.xx% Tip: Most cells that require input have cell format indicator above or below the cell. These format indicators show the type of number required for the cell. These format indicators do not show the size of the number expected for the cell. Fiscal 2018 Fiscal 2019 Sxxx,xxx $2,133 Cash and Cash Equivalents $1,778 For example: $XXX,XXX shows that the input for the cell should be whole dollars with no decimal point (no cents). The $XXX,XXX does not indicate that the number should be a 6 digit number. The correct answer for the cell could be as small as 1 digit or as large as 14 digits. Sxxx.xxx $14,531 Dollar Change % Change 2018 to 2019 Sxxx, xxx $355 19.97 % xox.xo% Sxxx,xxx $606 4.35 % xx.xx% Sxxx.xxx $7,233 16.44 % xx.x% Merchandise Inventories $13,925 Sxxx.xxx $51,236 Tip: When entering a formula, be sure to begin the formula with an "=" sign. Total Assets $44,003 Sxxx,xxx $7,787 Accounts Payable Sxxx.xxx $32 0.41 % XX.XX% $7,755 Sxxx, xxx Total Current Liabilities $18,375 $16,716 Sxxx.xxx $28,670 $26,807 Long-Term Debt, excluding current installments Sxxx.xxx $54,352 Total Liabilities $45,881 Sxxx,xxx $1,659 9.92 % xx.xx% Sxxx.xxx $1,863 6.95 % xx.xx% Sxx.xocx $8,471 18.46 % oc.xx% Sxxx.xxx $5,306 11.43 % xx.0% Sxxx.xxx ($1,245) 66.29% XX.XX% Sxxx.xxx Retained Earnings $51,729 $46,423 Tip: The sign of the % Change should be the same as the sign of the Dollar Change. Since the Dollar Change will be negative, the % Change should also be negative. Sxxx,xxx ($3,116) Total Stockholders' (Deficit) Equity ($1,878) Check your % Change answer by using the Excel workbook 2-Percent Change Calculator Home Depot -- Fiscal 2019 Pg. 6 DuPont Analysis -- Return on Assets CAUTION Do NOT do a Cut operation on any cell in the project. Apply DuPont Analysis and the Return on Assets % to evaluate Home Depot's overall financial performance. Use the information in Home Depot's Income Statement and Balance Sheets for Fiscal 2019 and Fiscal 2018 to calculate the four DuPont Ratios and the Return on Assets %. In the left-hand column enter the names of the financial statement items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round all averages to decimal places -- a whole number $xxx, xxx Round the percentages to 2 decimal places. xx.x% Round the decimal results to 2 decimal places. XX.XX Note: Since each ratio is being rounded, it is possible that Return on Equity % will not exactly equal (Profitability x Efficiency x Leverage). Calculate each ratio separately. Do not calculate Return on Equity % by multiplying (Profitability x Efficiency x Leverage). Fix xx.xx% and xx.xx Cell Formats Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot's financial statements. These names will not be graded by the grading software, but might be graded by your instructor. DuPont Analysis 2019 2018 (Profitability) Net Profit Margin % Sxxx.xxx Net Earnings XX.XX% % 10.28 % Sxxx.xxx Tip: If you use formulas to calculate the Average cells, be sure to use the Round function to round to 0 decimal places. (Efficiency) Total Assets Turnover Ratio Sxxx.xxx For example, the following formula will calculate the value in cell 136 and round the result to 0 decimal places: XX.XX times 2.44 Beg. Yr. Amt. End. Yr. Amt. Average =Round(( E36 + G36)/2,0) $xxx XXX Sxxx XXX , Sxxx,xxx X (Leverage) Assets-to-Equity Ratio Sxxx.xxx N/A to 1 NIA Average SxXX,XXX The Assets-to-Equity Ratio and the Return on Equity % are grayed out because the ratios are N/A for 2018 and 2019. Home Depot had negative Stockholders' Equity for 2018 and 2019, which makes these ratios meaningless. Stockholders' Equity was negative due to a large amount of repurchases of Common Stock. Tip: The calculation of Average Total Assets and the calculation of Average Total Stockholders' Equity are only shown the first time you need the average amount. Once you calculate the average amount, then use that average amount as needed in the other ratio calculations. Return on Equity % Sxxx.XXX % N/A % % N/A XX.XX% $xxx,xxx Return on Assets 2019 2018 Return on Assets % Sxxx.xxx XX.XX% % 25.12% Sxxx,xxx Home Depot -- Fiscal 2019 Pg. 7 CAUTION Do NOT do a Cut operation on any cell in the project. Profitability Indicators Apply the three Profitability Indicators to evaluate Home Depot's profitability. Use the information in Home Depot's Fiscal 2019 Income Statement to calculate the three Profitability Indicators. In the left-hand column enter the names of the items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round the percentages to 2 decimal places. xx.xx% Fix xx.xx% Cell Formats 2019 2018 Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot's financial statements. These names will not be graded by the grading software, but might be graded by your instructor. Gross Profit Margin % $xxx.xxx 34.34 % % XX.XX% $xxx,xxx Operating Profit Margin % $xxx.xxx 14.35 % % xx.xx% $xxx,xxx Net Profit Margin % $XXX, XXX % 10.28 % xx.xx% $xxx,xxx Home Depot -- Fiscal 2019 Pg. 8 CAUTION Do NOT do a Cut operation on any cell in the project. Efficiency Indicators Apply the Efficiency Indicators to drill down into Home Depot's efficiency. Use the information in Home Depot's Income Statement and Balance Sheets for Fiscal 2019 and Fiscal 2018 to calculate the Efficiency Indicators. In the left-hand column enter the names of the financial statement items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round all averages to 0 decimal places -- a whole number $xcx, xx Round the decimal results to 2 decimal places. xx.xx Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot's financial statements. These names will not be graded by the grading software, but might be graded by your instructor Fix xx.xx Cell Formats 2019 2018 Accounts Receivable Turnover Ratio Sxxx.xxx Tip: If you use formulas to calculate the times cells, be sure to use the Round() function to round to 2 decimal places. For example, the following formula will calculate the value in cell J21 and round the result to 2 decimal places: times Beg Yr. Amt End Yr Amt 55.66 Average XOLOK =Round( 120 / 122, 2) Socx, xox Sxxx.xxx $xxx, XXX Number of Days' Sales in Receivables Days in a Year (365) days 6.56 Accounts Receivable Turnover Ratio XOLOK xx.xx Tip: If you use formulas to calculate the Average cells, be sure to use the Round function to round to 0 decimal places For example, the following formula will calculate the value in cell 122 and round the result to 0 decimal places: =Round(( E22+G22)/2, 0) Inventory Turnover Ratio Sxxxxxx times Beg Yr Amt. End. Yr Amt. Average 5.33 XOLOK Sucxcx, xox Sxxx, xxx $xxx, xxx Number of Days' Sales in Inventory Days in a Year (365) XXX days 68.48 Inventory Turnover Ratio XL XX XX. XX Tip: If you use formulas to reference the days cells when calculating the Cash-to-Cash Days, be sure to use the Round) function to round all the days cells to 2 decimal places. For example, the following formula will calculate the value in days cell J28 and round the result to 2 decimal places: =Round( 127 / 129,2) 2 You can then use the following formula in cell J68: =J28 Accounts Payable Turnover Ratio times 9.47 Beg Yr. Amt End. Yr. Amt. Average XXXX Sxcxxxx Sxxx xxx Sxxx, XXX Number of Days' Purchases in Accounts Payable XXX Days in a Year (365) days 38.54 Accounts Payable Turnover Ratio XXXX XXXX Cash-to-Cash Cycle XXL XOK Number of Days' Sales in Inventory less Number of Days' Purchases in Accounts Payable Net Number of Days Cash is Invested in Inventory plus Number of Days' Sales in Receivables Cash-to-Cash Days 68.48 (38.54) 29.94 6.56 36.50 Home Depot -- Fiscal 2019 Pg. 9 CAUTION Do NOT do a Cut operation on any cell in the project. Leverage Indicators (at year-end) -- Interest Coverage Use the information in Home Depot's Fiscal 2019 Balance Sheet and Income Statement to calculate the two "year-end" Leverage Indicators and the Times Interest Earned Ratio. In the left-hand column enter the names of the items that will be used in the numerator and the denominator of the ratios. Enter the financial statement dollar amounts just as they are shown on the financial statements. Round the percentages to 2 decimal places. xx.xx% Fix xx.xx% and xx.xx Round the decimal results to 2 decimal places. XX.XX Cell Formats 2019 2018 Debt % $xxx XXX % 104.27 % xx.xx% $XXX, XXX $ The Debt-to-Equity Ratio is grayed out because the ratio is N/A for 2018 and 2019. Home Depot had negative Stockholders' Equity for 2018 and 2019, which makes this ratio meaningless. Debt-to-Equity Ratio $xxx.xxx N/A N/A to 1 XX.XX $xxx, xxx Tip: Calculate "Earnings before interest and taxes" as follows: Times Interest Earned Ratio $XXX XXX Net Eamings plus Provision for Income Taxes plus Interest Expense equals Earnings before interest and taxes Earnings before interest and taxes 14.85 times XX.XX Interest Expense $xxx, xxx Home Depot -- Fiscal 2019 Pg. 10 CAUTION Do NOT do a Cut operation on any cell in the project. Liquidity Indicators Use the information in Home Depot's 2019 Balance Sheet to calculate the three Liquidity Indicators. In the left-hand column enter the names of the items that will be used in the calculation. Enter the financial statement dollar amounts just as they are shown on the Balance Sheet. Round the decimal results to 2 decimal places. XX.XX Fix xx.xx Cell Formats 2019 2018 Working Capital $xxx xxx Tip: Enter "Current Liabilities" as a negative number Working Capital $1,813 Current Ratio Sxxx XXX to 1 1.11 XX.XX Sxxx xxx (Acid Test Ratio) Quick Ratio $xxx, xxx Cash and Near-Cash Assets to 1 0.22 Tip: Home Depot does not have any Short-Term Investments XX.XX $xxx,XXX Home Depot -- Fiscal 2019 Pg. 11 CAUTION Do NOT do a Cut operation on any cell in the project. Statement of Cash Flows Indicators Use the information in Home Depot's Fiscal 2019 Consolidated Statements of Cash Flows to calculate the three Statement of Cash Flows Indicators. In the left-hand column enter the names of the items that will be used in the calculation. Enter the financial statement dollar amounts in millions of dollars as shown on the Consolidated Statements of Cash Flows. Round the decimal results to 2 decimal places. XX.XX Flx xx.xx Cell Formats 2019 2018 (Quality of Income Ratio) Cash Flow to Net Income Ratio Sxxx xxx to 1 1.17 XX.XX Sxxx,XXX (Cash Flow Adequacy Ratio) Cash Flow to Capital Expenditures Ratio Sxxx.xxx to 1 5.41 Net Cash Paid to Purchase PP&E XX.XX Sxxx.xxx Tip: "Net Cash Paid to Purchase PP&E" is calculated as follows: "Capital Expenditures" (make this a positive number) less "Proceeds from Sales of Property and Equipment equals Net Cash Paid to Purchase PP&E Free Cash Flow Sxxx xxx Net Cash Paid to Purchase PP&E Free Cash Flow $5,925 Home Depot - Fiscal 2019 Pg. 12 CAUTION Do NOT do a Cut operation on any cell in the project. Equity Investor Indicators Use the information in Home Depot's Consolidated Statements of Stockholders' Equity to calculate Home Depot's Dividend Payout %. Use the Year-End Closing Market Price of 1 Common Share and Home Depot's Consolidated Statement of Earnings to calculate the F Use the Year-End Closing Market Price of 1 Common Share and the information in Home Depot's Stockholders' Equity Footnote to calculate Home Depot's Dividend Yield % Enter the financial statement dollar amounts just as they are shown on the financial statements. Round the percentages to 2 decimal places. xx.xx% Fix xx.xx% and xx.xx Round the decimal results to 2 decimal places. XX.XX Cell Formats Tip: When entering the names of the financial statement items, use the terminology that is used in Home Depot 's financial statements. These names will not be graded by the grading software. 2019 2018 Dividend Payout % $xxx.xxx Tip: Enter the cash dividend amount as a positive number. % 42.30 % XLX0% $xxx.xxx Year-End Closing Market Price of 1 Common Share The Year-End Closing Market Price of 1 share of HD's Common Stock is not given in HD's annual report. The Year-End Closing Market Price is used in the two calculations below. The last day in HD's 2019 fiscal year was Sunday, Feb. 2, 2020. HD's common stock trades on the New York Stock Exchange (NYSE) and the NYSE is not open on weekends Thus HD's year-end closing price is as of Friday, Jan. 31, 2020. On Jan. 31, 2020, HD's common stock closed at $228.10. Enter that price in the box below. 2019 Year-End Closing Market Price of 1 Common Share: Sxxxx 2019 2018 (PE Ratio) Price-Earnings Ratio $xxxx times 18.85 Basic Eamings Per Share XX.XX $xx.xx Dividend Yield % $xx.xx Cash Dividends per share % xx.x% 2.23 % Tip: Look on the Stockholders' Equity Footnote (report page 56) for the Cash Dividends per share amount. $xxx Home Depot -- Fiscal 2019 Pg. 13 Analytical Observations CAUTION Do NOT do a Cut operation on any cell in the project. You will now utilize the information developed in Pages 2 to 12 to answer various analytical questions. Tip: You may find it easier to complete pages 13 to 16, if you print pages 2 to 12. 1. Vertical Analysis of the Income Statement (Pg. 2) 1> Which fiscal year had the highest Operating Income in terms of total dollars? ["x" only one answer ] 2019 2018 2017 2> Which fiscal year had the highest Total Operating Expenses as a % of Net Sales? ["x" only one answer ] 2019 2018 2017 3> Based on Gross Profit as a % of Net Sales, rank the fiscal years 2019, 2018, and 2017 in terms of which year had the highest. Place a "1" in the box of the highest year, a "2" in the box of the second highest year, etc. 2019 2018 2017 4> Which fiscal year had the highest Net Earnings as a % of Net Sales? ["x" only one answer ] 2019 2018 2017 2. Horizontal Analysis of the Income Statement (Pg. 3) 1> Rank the following items in terms of which had the largest % increase from Fiscal 2017 to Fiscal 2018. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Net Sales Cost of Sales Operating Income Net Earnings 2> Rank the following items in terms of which had the largest % increase from Fiscal 2018 to Fiscal 2019. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Net Sales Cost of Sales Operating Income Net Earnings 3> True or False? In terms of % Change, Basic Earnings Per Share grew more than Net Earnings from Fiscal 2018 to Fiscal 2019. ["x" only one answer ] True False 4> True or False? In terms of % Change, from Fiscal 2018 to Fiscal 2019 Total Operating Expenses increased more than Net Sales. ["x" only one answer ] True False 5> True or False? In terms of % Change, Cost of Sales had a smaller increase than Net Sales from Fiscal 2018 to Fiscal 2019. ["x" only one answer ] True False Home Depot -- Fiscal 2019 Pg. 14 Analytical Observations CAUTION Do NOT do a Cut operation on any cell in the project. 3. Vertical Analysis of the Balance Sheet (Pg.4) 1> For fiscal year 2019, rank the following assets in terms of which was the largest as a % of Total Assets. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Cash and Cash Equivalents Receivables, net Merchandise Inventories Net Property and Equipment 2> For fiscal year 2019, rank the following items in terms of which was the largest as a % of Total Liabilities and Stockholders' Equity. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Long-Term Debt, excluding Accounts Payable current installments Paid-in Capital Retained Earnings 4. Horizontal Analysis of the Balance Sheet (Pg. 5) 1> Rank the following items in terms of which had the largest % increase from Fiscal 2018 to Fiscal 2019. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Merchandise Long-Term Debt, excluding Inventories Accounts Payable current installments Retained Earnings 2> Rank the following items in terms of which had the largest Dollar increase from Fiscal 2018 to Fiscal 2019. Place a "1" in the box of the largest, a "2" in the box of the second largest, etc. Merchandise Long-Term Debt, excluding Inventories Accounts Payable current installments Retained Earnings 5. DuPont Analysis -- Return on Assets Ratio (Pg. 6) 1> True or False? Profitability increased from Fiscal 2018 to Fiscal 2019? ["x" only one answer ] True False 2> True or False? Efficiency increased from Fiscal 2018 to Fiscal 2019? ["x" only one answer ] True False 3> Which fiscal year has the highest Return on Assets %? ["X' only one answer ] 2019 2018 6. Profitability Indicators (Pg. 7) 1> Which fiscal year, 2019 or 2018, had higher profitability based on Gross Profit Margin % and Net Profit Margin ["x" only one answer ] 2019 2018 Home Depot -- Fiscal 2019 Pg. 15 CAUTION Do NOT do a Cut operation on any cell in the project. Analytical Observations 7. Efficiency Indicators (Pg.8) 1> Based on both of the Accounts Receivable Efficiency Indicators, in which fiscal year, 2019 or 2018, did Home Depot more efficiently manage the Accounts Receivable asset? [ "x" only one answer] .2019 2018 2> Based on both of the Inventory Efficiency Indicators, in which fiscal year, 2019 or 2018, did Home Depot have an item in inventory for the shortest amount of time? ["x" only one answer ] 2019 2018 3> Based on the Number of Days' Purchases in Accounts Payable, in which fiscal year, 2019 or 2018, did Home Depot hold on to its cash longer before paying the cash to vendors? [ "x" only one answer ] 2019 2018 4> Which fiscal year, 2019 or 2018, had the better Cash-to-Cash Cycle? [ "x" only one answer 2019 2018 5> In which fiscal year, 2019 or 2018, did Home Depot have the better Fixed Asset Turnover Ratio? [ "x" only one answer ] 2019 2018 8. Leverage Indicators (at year-end) - Interest Coverage (Pg. 9) 1> Based on the Debt %, who had the greatest claim to Home Depot's assets in Fiscal 2019? ["x" only one answer ] Creditors Shareholders 2> True or False? In Fiscal 2019 more than half of Home Depot's Total Assets were financed with debt? ["x" only one answer ] True False 3> Based on the Times Interest Earned Ratio, were the creditors' interest payments more secure in Fiscal 2019 or Fiscal 2018? ["x" only one answer ] 2019 2018 9. Liquidity Indicators (Pg. 10) 1> Based on Working Capital and the Current Ratio, did Liquidity increase or decrease from Fiscal 2018 to Fiscal 2019? ["x" only one answer ] Liquidity Increased Liquidity Decreased Home Depot -- Fiscal 2019 Pg. 16 CAUTION Do NOT do a Cut operation on any cell in the project. Analytical Observations 10. Statement of Cash Flows Indicators (Pg. 11) 1> True or False? By looking at the Cash Flow to Capital Expenditure Ratio it can be seen that Home Depot was able to internally fund the purchases of Fixed Assets in Fiscal 2019 and in Fiscal 2018. ["x" only one answer ] True False 2> In which fiscal year, 2019 or 2018, did Home Depot have more Free Cash Flow? ["x" only one answer ] 2019 2018 11. Equity Investor Indicators (Pg. 12) 1> True or False? Based on the Dividend Payout % it can be seen that Home Depot paid out a larger percent of Net Income, in the form of Cash Dividends, in Fiscal 2019 than in Fiscal 2018. ["x" only one answer ] True False 2> Based on the Price-Earnings Ratio, in which fiscal year, 2019 or 2018, did investors place a higher value on Home Depot's earnings? ["x" only one answer ] 2019 2018 3> Based on the Dividend Yield %, in which fiscal year, 2019 or 2018, did an investor in Home Depot's Common Stock earn a higher return from the cash dividends she received on her Home Depot Common Stock? ["x" only one answer ) 2019 2018