Excel Project When you submit the project, do it on Canvas and name it: Last name_first name HD Income Statement and HD Balance Sheet Tab Take the Income Statement and Balance Sheet and turn them into a percent of Sales and percent of Total Assets respectively. Take the Income Statement and Balance sheet and create a proforma for 2018 based on the information I have given you. Same means 2018 is the same as what 2017 was. For the income statement I have given you a growth rate for revenue and then what % of revenue each line should be. Sales % on the balance sheet tab means to grow that line by the same rate you grew revenue on the income statement. Retained earnings "fill in" means solve for ending retained endings in 2018 given a 45% payout in 2018 of dividends. Accumulated other comprehensive income line on the balance sheet will be used to solve for the plug (what is needed to make the balance sheet balance). Be sure to use equations, NOT just typing in the numbers. This includes subtraction and addition. I will be grading the exercise BASED ON your equations. The idea is to be able to change one number and have it flow throughout the entire spreadsheet. This is necessary for the valuation scenarios. Calculate the seven ratios, both for the previous five years AND for your proforma numbers 2018 2013 2014 2015 2016 2017 2018 Ratios 2013 2014 2015 2016 2017 2018E Sales % 7.5 days 72 days Sales % Current Ratio Quick Ratio Times Int Earned Total Debt/Equity Days Inventory Days Receivable Days Payable same same same same same same same same THE HOME DEPOT INC (HD) Cash Flow Flag BALANCE SHEET Fiscal year ends in January. USD in millions exce 2013-01 2014-01 2015-01 2016-01 2017-01 Assets Current assets Cash Cash and cash equivalents 2494 1929 1723 2216 2538 Receivables 1395 1398 1484 1890 2029 Inventories 10710 11057 11079 11809 12549 Other current assets 773 895 1016 1078 608 Total current assets 15372 15279 15302 16993 17724 Non-current assets Property, plant and equipment Land 8485 8375 8243 8149 8207 Fixtures and equipment 9338 10107 9602 10279 11020 Other properties 20668 20582 20668 20838 21199 Property and equipment, at cost 38491 39064 38513 39266 40426 Accumulated Depreciation -14422 -15716 -15793 -17075 -18512 Property, plant and equipment, net 24069 23348 22720 22191 21914 Goodwill 1170 1289 1353 2102 2093 Other long-term assets 473 602 571 1263 1235 Total non-current assets 25712 25239 24644 25556 25242 Total assets 41084 40518 39946 42549 42966 Liabilities and stockholders' equity Liabilities Current liabilities Short-term debt 1321 33 328 427 1252 Accounts payable 5376 5797 5807 6565 7000 Taxes payable 494 408 469 510 533 Accrued liabilities 3001 3174 3197 3458 3679 Deferred revenues 1270 1337 1468 1566 1669 Total current liabilities 11462 10749 11269 12526 14133 Non-current liabilities Long-term debt 9475 14691 16869 20888 22349 Deferred taxes liabilities 319 514 642 854 296 Other long-term liabilities 2051 2042 1844 1965 1855 Total non-current liabilities 11845 17247 19355 23707 24500 Total liabilities 27996 30624 36233 38633 Stockholders' equity Common stock 88 88 88 88 88 Additional paid-in capital 7948 8402 8885 9347 9787 Retained earnings 20038 23180 26995 30973 35519 Sales % 41 days Sales % Sales % Sales % Sales % Sales % Sales % Sales % 23307 same Sales % fill in payout ratio = 45% Valuation Tab Take the excel sheet provided (Valuation Tab) and change the numbers as follows to get subsequent valuations. Base the changes off original numbers, not additive Make sure all numbers have been returned back to your original numbers before you submit. Change COGS by 1% (lower cost) and see how the valuation changes. Copy and paste into cells F4-F6 Change the SG&A by 1% (lower) and see how the valuation changes. Copy and paste into Cells G4-46 Change the Rev by 1% (higher) and see how the valuation changes. Copy and paste into Cells H4-H6 Drop the P/E to 20 times, the P/B to 38 times and P/S to 1.8 times and see how valuation changes. You will need to look at and use the same formulas in Cells E4-E6 to calculate these numbers Copy and paste into Cells 14-16 Starting in Cell A12, comment on the results you have found above. Be sure to give your thoughts, not just read back the numbers. What do the results between changing COGS and SG&Atell you about how increasing costs can hurt you? How much does increasing Revenue by 1% change valuations? Any thoughts on the importance of finding growth? Which would you rather have, 1% growth, or 1% cost savings? How do you think you could help if you worked at Home Depot so that they might be able to make these changes happen? How sensitive are the valuations to small changes in the multiples? Are any more sensitive than the others? 2013 2014 201 2016 2017 2018E 2018E 5% 66% 1.05 65.43% 34.57% 18.50% 1.85% 1% THE HOME DEPOT INC (HD) INCOME STATEMENT Fiscal year ends in January. USD in million 2013-01 2014-01 2015-01 2016-01 2017-01 Revenue 74754 78812 83176 88519 94595 Cost of revenue 48912 51422 54222 58254 62282 Gross profit 25842 27390 28954 30265 32313 Operating expenses Sales, General and administrative 16508 16597 16834 16801 17132 Other operating expenses 1568 1627 1651 1690 1754 Total operating expenses 18076 18224 18485 18491 18886 Operating income 7766 9166 10469 11774 13427 Interest Expense 632 711 830 919 972 Other income (expense) 87 12 337 166 36 Income before income taxes 7221 8467 9976 11021 12491 Provision for income taxes 2686 3082 3631 4012 4534 Net income from continuing operations 4535 5385 6345 7009 7957 Net income 4535 5385 6345 7009 7957 Earnings per share Basic 3.03 3.78 4.74 5.49 6.47 Diluted 3 3.76 4.71 5.46 6.45 Weighted average shares outstanding Basic 1499 1425 1338 1277 1229 Diluted 1511 1434 1346 1283 1234 EBITDA 9537 10935 12592 13803 15436 22.08% 2.10% 24.18% 10.39% 0.85% 0.12% 9.66% 3.59% 6.07% 6.07% same 4.60% 0.00 0.00 1229 same 1234 same 20038 35519 payout ratio 45% Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity -10694 397 17777 41084 23180 -19194 46 12522 40518 26995 -26194 -452 9322 39946 30973 -33194 -898 6316 42549 -40194 -867 4333 42966 fill in same plug (what balances balance sheet) COGS-1% SGA-1% Rev +1% Price 2018 multiple Results P/E multiple P/B multiple P/S multiple $147.31 multiple 2018E Price 22 times 22 40 times 40 1.9 times 1.9 OOO 20 38 1.8 Comments Excel Project When you submit the project, do it on Canvas and name it: Last name_first name HD Income Statement and HD Balance Sheet Tab Take the Income Statement and Balance Sheet and turn them into a percent of Sales and percent of Total Assets respectively. Take the Income Statement and Balance sheet and create a proforma for 2018 based on the information I have given you. Same means 2018 is the same as what 2017 was. For the income statement I have given you a growth rate for revenue and then what % of revenue each line should be. Sales % on the balance sheet tab means to grow that line by the same rate you grew revenue on the income statement. Retained earnings "fill in" means solve for ending retained endings in 2018 given a 45% payout in 2018 of dividends. Accumulated other comprehensive income line on the balance sheet will be used to solve for the plug (what is needed to make the balance sheet balance). Be sure to use equations, NOT just typing in the numbers. This includes subtraction and addition. I will be grading the exercise BASED ON your equations. The idea is to be able to change one number and have it flow throughout the entire spreadsheet. This is necessary for the valuation scenarios. Calculate the seven ratios, both for the previous five years AND for your proforma numbers 2018 2013 2014 2015 2016 2017 2018 Ratios 2013 2014 2015 2016 2017 2018E Sales % 7.5 days 72 days Sales % Current Ratio Quick Ratio Times Int Earned Total Debt/Equity Days Inventory Days Receivable Days Payable same same same same same same same same THE HOME DEPOT INC (HD) Cash Flow Flag BALANCE SHEET Fiscal year ends in January. USD in millions exce 2013-01 2014-01 2015-01 2016-01 2017-01 Assets Current assets Cash Cash and cash equivalents 2494 1929 1723 2216 2538 Receivables 1395 1398 1484 1890 2029 Inventories 10710 11057 11079 11809 12549 Other current assets 773 895 1016 1078 608 Total current assets 15372 15279 15302 16993 17724 Non-current assets Property, plant and equipment Land 8485 8375 8243 8149 8207 Fixtures and equipment 9338 10107 9602 10279 11020 Other properties 20668 20582 20668 20838 21199 Property and equipment, at cost 38491 39064 38513 39266 40426 Accumulated Depreciation -14422 -15716 -15793 -17075 -18512 Property, plant and equipment, net 24069 23348 22720 22191 21914 Goodwill 1170 1289 1353 2102 2093 Other long-term assets 473 602 571 1263 1235 Total non-current assets 25712 25239 24644 25556 25242 Total assets 41084 40518 39946 42549 42966 Liabilities and stockholders' equity Liabilities Current liabilities Short-term debt 1321 33 328 427 1252 Accounts payable 5376 5797 5807 6565 7000 Taxes payable 494 408 469 510 533 Accrued liabilities 3001 3174 3197 3458 3679 Deferred revenues 1270 1337 1468 1566 1669 Total current liabilities 11462 10749 11269 12526 14133 Non-current liabilities Long-term debt 9475 14691 16869 20888 22349 Deferred taxes liabilities 319 514 642 854 296 Other long-term liabilities 2051 2042 1844 1965 1855 Total non-current liabilities 11845 17247 19355 23707 24500 Total liabilities 27996 30624 36233 38633 Stockholders' equity Common stock 88 88 88 88 88 Additional paid-in capital 7948 8402 8885 9347 9787 Retained earnings 20038 23180 26995 30973 35519 Sales % 41 days Sales % Sales % Sales % Sales % Sales % Sales % Sales % 23307 same Sales % fill in payout ratio = 45% Valuation Tab Take the excel sheet provided (Valuation Tab) and change the numbers as follows to get subsequent valuations. Base the changes off original numbers, not additive Make sure all numbers have been returned back to your original numbers before you submit. Change COGS by 1% (lower cost) and see how the valuation changes. Copy and paste into cells F4-F6 Change the SG&A by 1% (lower) and see how the valuation changes. Copy and paste into Cells G4-46 Change the Rev by 1% (higher) and see how the valuation changes. Copy and paste into Cells H4-H6 Drop the P/E to 20 times, the P/B to 38 times and P/S to 1.8 times and see how valuation changes. You will need to look at and use the same formulas in Cells E4-E6 to calculate these numbers Copy and paste into Cells 14-16 Starting in Cell A12, comment on the results you have found above. Be sure to give your thoughts, not just read back the numbers. What do the results between changing COGS and SG&Atell you about how increasing costs can hurt you? How much does increasing Revenue by 1% change valuations? Any thoughts on the importance of finding growth? Which would you rather have, 1% growth, or 1% cost savings? How do you think you could help if you worked at Home Depot so that they might be able to make these changes happen? How sensitive are the valuations to small changes in the multiples? Are any more sensitive than the others? 2013 2014 201 2016 2017 2018E 2018E 5% 66% 1.05 65.43% 34.57% 18.50% 1.85% 1% THE HOME DEPOT INC (HD) INCOME STATEMENT Fiscal year ends in January. USD in million 2013-01 2014-01 2015-01 2016-01 2017-01 Revenue 74754 78812 83176 88519 94595 Cost of revenue 48912 51422 54222 58254 62282 Gross profit 25842 27390 28954 30265 32313 Operating expenses Sales, General and administrative 16508 16597 16834 16801 17132 Other operating expenses 1568 1627 1651 1690 1754 Total operating expenses 18076 18224 18485 18491 18886 Operating income 7766 9166 10469 11774 13427 Interest Expense 632 711 830 919 972 Other income (expense) 87 12 337 166 36 Income before income taxes 7221 8467 9976 11021 12491 Provision for income taxes 2686 3082 3631 4012 4534 Net income from continuing operations 4535 5385 6345 7009 7957 Net income 4535 5385 6345 7009 7957 Earnings per share Basic 3.03 3.78 4.74 5.49 6.47 Diluted 3 3.76 4.71 5.46 6.45 Weighted average shares outstanding Basic 1499 1425 1338 1277 1229 Diluted 1511 1434 1346 1283 1234 EBITDA 9537 10935 12592 13803 15436 22.08% 2.10% 24.18% 10.39% 0.85% 0.12% 9.66% 3.59% 6.07% 6.07% same 4.60% 0.00 0.00 1229 same 1234 same 20038 35519 payout ratio 45% Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity -10694 397 17777 41084 23180 -19194 46 12522 40518 26995 -26194 -452 9322 39946 30973 -33194 -898 6316 42549 -40194 -867 4333 42966 fill in same plug (what balances balance sheet) COGS-1% SGA-1% Rev +1% Price 2018 multiple Results P/E multiple P/B multiple P/S multiple $147.31 multiple 2018E Price 22 times 22 40 times 40 1.9 times 1.9 OOO 20 38 1.8 Comments