Examine the return on equity (ROE) of borth firms in the excel file. What is driving the variation of returns to shareholders between the firms?
You should break this down along two dimensions; operational and financial. This is a Dupont equation question
A B D E F G H J M N o P R R S S T HOME DEPOT Financials 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 47,220 48,815 50,208 50,521 53,417 56,223 59,074 65,017 34.9 35.1 34.6 34.3 34,6 34.8 34.81 34.61 3,112 3,560 3,277 3,560 4,149 4,792 4,971 5,846 6.6 7.3 6.5 7 7.8 8.5 9 Revenue USD Mil Gross Margin % Operating Income USD Mil Operating Margin % 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 66,176 67,997 70,395 74,754 78,812 83,176 88,519 94,595 33.9 34.3 345 34,6 34.2 34.1 34.2 34.2 4,803 5,839 6,661 7,766 9,166 10,469 11,774 13,427 7.3 8.6 9.5 10.4 11.6 12.6 13.3 14.2 37.1 1,692 1,570 1 LOWE'S 2 3 4 Revenue USD Mil 5 Gross Margin % 6 Operating Income USD Mil 7 Operating Margin % 8 9 Dividend Payout Ratio %* 10 Shares Mil 11 Book Value Per Share USD 12 Operating Cash Flow USD Mil 13 Cap Spending USD Mil 14 Free Cash Flow USD Mil 15 Working Capital USD Mil 16 17 Key Ratios -> Profitability "NEY 18 Margins % of Sales 19 Revenue 20 COGS 21 Gross Margin 22 SG&A 23 Operating Margin 24 EBT Margin 25 29.7 29.6 35.7 1,464 1,403 4,405 1.273 1,152 13.31 13.38 13.2 12.65 4,054 3,852 4,349 3,762 -1,799 -1,329 -1,829 -1,211 2,255 2,523 2,520 2,551 2,377 2,848 2.181 2,076 324 1,061 12.28 4,111 -940 3,171 1,420 32.3 990 11.18 4,929 -880 4,049 732 32.1 929 9.21 4,784 -1.197 3,587 69 45.6 881 7.25 5,617 -1,167 4,450 26 Payout Ratio %. Share Shares Mil Book Value Per Share - USD Operating Cash Flow USD Mi Cap Spending USD Mill Free Cash Flow USD Mil Working Capital USD Mil 65.6 47 42.1 41.1 1,658 1.511 11.41 11.64 11.64 11.86 5,125 4,585 6,651 6,975 -966 -1,096 -1,221 -1,312 4,159 3,489 5,430 5,663 3,537 3,357 5,144 3.910 39.5 1,434 10.09 7,628 -1,389 6,239 4,530 40.9 41.9 1,346 1,283 7.67 6.09 8,242 9,373 -1,442 -1,503 6,800 7,870 4,033 4,467 43.1 1,234 4.67 9,783 -1,621 8,162 3,591 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 100 100 100 100 100 100 100 100 65.14 64.86 65.44 65.7 65.41 65.21 65.18 65.45 34.86 35.14 34.56 34.3 34.59 34.79 34.82 34.55 24.86 24.59 25.08 24.24 24.08 23.61 23.88 23.27 6.59 7.29 6.53 7.05 7.77 8.52 8.41 8.99 5.98 6.61 5.79 6.21 6.88 7.61 7.48 8 Key Ratios -> Profitability Margins % of Sales Revenue COGS Gross Margin SG&A Operating Margin EBT Margin 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 100 100 100 100 100 100 100 100 66.13 65.73 65.53 65.43 65.85 65.87 65.81 65.84 33.87 34.27 34.47 34.57 34.15 34.13 34.19 34.16 24.03 23.31 22.77 22.08 20.46 19.57 18.98 18.11 7.26 9.46 10.39 11.63 12.59 13.3 14.19 6.02 7.75 8.62 9.66 11.99 12.45 13.2 8.59 10.74 A B C D E F G H J K L M N o P Q R S T HOME DEPOT Key Ratios -> Growth 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 0.21 5.31 0.37 2.45 1.35 1.36 2.28 0.91 3.05 2.06 3.84 3.55 5.35 3.89 6.77 5.31 Revenue % 3-Year Average 5-Year Average - 10.02 -1.97 -4.2 -3.56 -0.42 -4.97 4.15 -0.68 5.04 2.03 5.72 4.68 5.8 5.42 6.27 6.09 1 LOWE'S 26 Key Ratios -> Growth 27 28 Revenue % 29 3-Year Average 30 5-Year Average 31 32 Key Ratios -> Cash Flow 33 Cash Flow Ratios 34 Cap Ex as a % of Sales 35 Free Cash Flow/Sales % 36 4 37 38 Balance Sheet Items (in %) so 39 Cash & Short-Term Investments 40 Accounts Receivable 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 3.81 2.72 3.64 2.4 1.76 1.57 2.03 1.79 4.78 5.17 5.02 5.05 5.94 7.2 6.07 6.84 Key Ratios -> Cash Flow Cash Flow Ratios Cap Ex as a % of Sales Free Cash Flow/Sales % 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 1.46 1.61 1.73 1.76 1.76 1.73 1.7 1.71 6.28 5.13 7.71 7.58 7.92 8.18 8.89 8.63 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 3.2 3.33 3.87 2.04 1.76 1.86 2.28 1.91 41 Inventory 42 Total Current Assets 43 Net PP&E re 44 Total Assets 45 Total Current Liabilities 46 Long-Term Debt 47 Total Liabilities 48 Total Stockholders' Equity 49 Total Liabilities & Equity 50 24.99 24.69 29.49 29.58 68.17 65.55 100 100 22.28 21.13 13.72 19,4 42.22 46.25 57.78 53.75 100 100 24.9 30.01 65.47 100 23.51 20.96 50.73 49.27 100 26.33 29.95 65.75 100 23.6 27.64 57.58 42.42 100 27.88 31.46 63.65 100 27.12 30.81 63.79 36.21 100 28 31.67 62.95 100 29.37 33.98 68.68 31.32 100 30.25 33.78 62.61 100 33.56 36.93 75.52 24.48 100 30.39 34.88 57.98 100 34.8 41.83 81.3 18.7 100 Balance Sheet Items (in %) 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 Cash & Short-Term Investme 3.49 1.36 4.9 6.07 4.76 4.31 5.21 5.91 Accounts Receivable 2.36 2.7 3.07 3.4 3.45 3.72 4.44 ce 4.72 Inventory 24.92 26.48 25.48 26.07 wenn 27.29 27.73 27.75 29.21 Total Current Assets 34 33.59 35.84 37.42 37.71 38.31 39.94 41.25 Net PP&E 62.5 62.45 60.34 58.58 57.62 56.88 52.15 51 Total Assets 100 100 100 100 100 100 100 100 Total Current Liabilities 25.35 25.23 23.14 27.9 26.53 28.21 29.44 32.89 Long-Term Debt 21.19 21.7 26.55 23.06 36.26 42.23 49.09 52.02 Total Liabilities 52.56 52.92 55.83 56.73 69.1 76.66 85.16 89.92 Total Stockholders' Equity 47.44 47.08 44.17 43.27 30.9 23.34 14.84 10.08 Total Liabilities & Equity 100 100 100 100 100 100 100 100 B C D E F H 1 K L M N O P Q R S T 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 1.32 1.4 1.28 1.27 1.16 1.08 1.01 1 0.14 0.16 0.16 0.09 0.06 0.06 0.07 0.06 1.73 1.86 2.03 2.36 2.76 3.19 4.08 5.35 0.24 0.36 0.43 0.65 0.85 1.08 1.51 2.24 HOME DEPOT Liquidity/Financial Health Current Ratio Quick Ratio Financial Leverage Debt/Equity 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 1.34 1.33 1.55 1.34 1.42 1.36 1.36 1.25 0.23 0.16 0.34 0.34 0.31 0.28 0.33 0.32 2.11 2.12 2.26 2.31 3.24 4.29 6.74 9.92 0.45 0.46 0.6 0.53 1.17 1.81 3.31 5.16 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 97.66 95.51 92.62 93.22 92.59 89.78 87.06 85.42 49.82 49.79 48.34 49.53 50.48 50.43 50.99 52.68 A 1 LOWE'S 51 Liquidity/Financial Health 52 Current Ratio 53 Quick Ratio 54 Financial Leverage 55 55 Debt/Equity 56 56 52 57 Key Ratios -> Efficiency Ratios 58 50 59 Days Inventory 60 Payables Period 61 Cash Conversion Cycle 62 Receivables Turnover so 63 Inventory Turnover 64 Fixed Assets Turnover 55 65 Asset Turnover 66 57 67 co 68 Profitability 69 Tax Rate % 20 70 71 71 Return on Equity % 72 Asset Turnover (Average) 7 73 Return on Assets % 74 Financial Leverage (Average) 75 Return on Invested Capital % 76 Interest Coverage 77 Key Ratios -> Efficiency Ratios 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 Days Inventory 86.99 84.99 82.88 78.49 77.25 74.51 71.7 71.37 Payables Period 40.39 39.12 37.87 38.18 39.65 39.06 38.76 39.75 Cash Conversion Cycle 51.94 51.37 51.05 46.75 44.07 41.77 39.9 39.19 Receivables Turnover 68.36 66.37 60.42 56.63 56.44 57.72 52.47 48.28 Inventory Turnover 4.2 4.29 4.4 4.65 4.72 4.9 5.09 5.11 Fixed Assets Turnover 2.56 2.69 2.84 3.08 3.32 3.61 3.94 4.29 Asset Turnover 1.61 1.68 1.75 1.83 1.93 2.07 2.14 2.21 3.74 2.09 1.44 3.82 2.19 1.46 3.94 2.28 1.49 3.92 2.33 1.53 3.94 2.52 1.63 4.07 2.75 1.74 4.19 2.98 1.87 4.27 3.29 1.98 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 36.89 37.73 36.72 37.55 37.76 36.9 42.39 40.53 Profitability Tax Rate % 2010-01 2011-01 2012-01 2013-01 2014-01 2015-01 2016-01 2017-01 34.2 36.7 36.01 37.2 36.4 36.4 36.4 36.3 9.54 1.44 5.39 1.73 8.09 10.29 10.81 1.46 6.03 1.86 9.08 10.38 10.62 1.49 5.47 2.03 8.48 8.71 12.89 1.53 5.92 2.36 9.42 7.96 17.78 1.63 6.99 2.76 11.4 8.59 24.58 1.74 8.31 3.19 13.76 9.18 28.76 1.87 8.03 4.08 13.76 8.93 43.47 1.98 9.32 5.35 16.26 8.92 Return on Equity% Asset Turnover (Average) Return on Assets % Financial Leverage (Average Return on Invested Capital 9 Interest Coverage 14.32 1.61 6.49 2.11 10.62 6.89 17.44 1.68 8.24 2.12 12.7 10.95 21.11 1.75 9.63 2.26 14.87 11.01 25.42 1.83 11.11 2.31 17.18 12.43 35.55 1.93 13.2 3.24 20.89 12.91 58.09 2.07 15.77 4.29 24.77 13.02 89.64 149.44 2.14 2.21 16.99 18.61 6.74 9.92 27.66 30.79 12.99 13.85
