Project The required tasks are detailed below: (1) Prepare vertical common-size income statements and balance sheets for both companies. Note: Use "Total Sales" and "Total Assets" as the denominators for income statement and balance sheet, respectively. Compute for 2018, 2017, and 2016. (2) Prepare horizontal analysis on income statements and balance sheets for both companies. (3) Prepare ratio analyses (for 2018, 2017, and 2016) for both companies. You should include the following ratios in your computations: Profitability ratios o Gross Profit margin O Profit margin O Return on assets o Return on equity Productivity o Accounts Receivable Turnover o Average Collection Period o Inventory Turnover o days inventory outstanding o PPE Turnover o Asset Turnover Solvency o Debt-to-equity o Times interest earned o Return on Financial leverage Liquidity O Current Ratio Quick Ratio o Working capital (3) Comment on the analytical results of the two companies based on your work in excel. In addition to contrasting the ratios between the companies, you should interpret the numbers and make suggestions as to why the ratio of one company might be higher/lower than the other Note: General discussions of the ratios are given in our textbook, which will help you structure your comments. HOME DEPOT NOTE: 2015 BALANCE SHEET INFORMATION SHOULD BE USED ONLY FOR RATIOS 2018 2016 3 Report Date 2015 $1,778 $1,936 $13,925 $890 $18,529 $22,375 $2,252 5847 $44,003 $3,595 $1,952 $12,748 $638 $18,933 $22.075 $2,275 $1,246 $44,529 $2,538 $2,029 $12,549 $608 $17,724 $21,914 $2,093 $1 235 $42,966 $2,216 $1,890 $11,809 $569 $16,484 $22, 191 $2,102 $1,196 $41,973 5 $ in Millions 6 Current assets: 7 Cash and cash equivalents 8 Receivables net 9 Merchandise inventories 10 Other current assets 11 Total current assets 12 Net property and equipment 13 Goodwill 14 Other assets 15 Total assets 16. Current Liabilities: 17 Short-term debt 18 Accounts payable 19 Accrued salaries and related expenses 20 Sales taxes payable 21 Deferred revenue 22 Income taxes payable 23 Current installments of long-term debt 24 Other accrued expenses 25 Total current liabilities 26 Long-term debt, excluding current installments 27 Deferred income taxes 28 Other long-term liabilities 29 Total liabilities 30 Common stock, par value $0.05, authorized: 10,000 shares iss 31 Paid-in capital 32 Retained earnings 33 Accumulated other comprehensive loss R 34 Treasury stock, at cost, 677 shares at February 3, 2019 and 62 35 Total stockholders' (deficit) equity 36 Total liabilities and stockholders' equity $1,339 $7,755 $1,506 $656 $1,782 $11 $1,056 $2.611 $16,716 $26.807 $491 $1,867 $45,881 $89 $10.578 $46,423 $772 -558.196 $1,878 $1,559 $7244 $1,640 $520 $1,805 $54 $1,202 $2,170 $16,194 $24 267 $440 $2.174 $43,075 589 $10,192 $710 $7,000 $1,484 $508 $1,669 $25 $542 $2,195 $14,133 $22,349 $296 $1,855 $38,633 $350 $6,565 $1,515 $476 $1,566 $34 $77 $1,941 $12,524 $20,789 $379 $1,965 $35,657 $88 $88 $39,935 -$566 -$48, 196 $1,454 $44,529 $9,787 $35,519 -5867 -$40.194 $4,333 $42,966 $9,347 $30,973 -5898 -$33,194 $6,316 $41,973 C414003 40 Additional Information 41 No allowance account is created for receivables. Le Net receivables equal to gross receivables 42. 43 Property and Equipment, at cost: 44 Land 45 Buildings 46 Furniture, Foctures and Equipment 47 Leasehold Improvements 48 Construction in Progress 49 Capital Leases 50 Less Accumulated Depreciation and Amortization 51 Net Property and Equipment $8.363 18199 12460 1705 820 1392 20564 22375 8352 $18,073 11506 1637 538 1308 19339 22075 $8.207 $17.772 11,020 1,519 739 1.169 18,512 21.914 8,149 17,667 10,279 1,481 670 1,020 17,075 22.191 2018 2017 2016 $108,203 71,043 37,160 $100,904 66,548 34,356 $94,595 62,282 32,313 17,864 1,811 17,132 1,754 19,513 1,870 247 21,630 15,530 19,675 14,681 18,886 13,427 1 shares in Millions, $ in Millions 2 Income Statement 3 Net Sales 4 Cost of sales 5 Gross profit 6 Operating expenses: 7 Selling, general and administrative 8 Depreciation and amortization 9 Impairment loss 10 Total operating expenses 11 Operating income 12 Interest and other (income) expense: 13 Interest and investment income 14 Interest expense 15 Other 16 Interest and other, net 17 Earnings before provision for income taxes 18 Provision for income taxes 19 Net earnings 20 21 Additional Information 22 Basic weighted average common shares 23 Cash dividend per share 24 Operating cash flow for the year -74 -93 1,051 -36 972 1,057 16 974 14,556 3,435 $11,121 983 13,698 5.068 $8,630 936 12,491 4,534 $7,957 1,137 4.12 13.038 1,178 3.56 12.031 1,229 2.76 9.783 LOWES 2015 NOTE 2015 BALANCE SHEET INFORMATION SHOULD BE USED ONLY FOR RATIOS 3 Report Date 2018 2017 2016 4 S in Thousands 5 Cash & cash equivalents $511,000 $588,000 $558,000 6 Short-term investments $218,000 $102,000 H $100,000 7 Merchandise inventory, net $12,561,000 $11,393,000 8 Other current assets $938,000 $689,000 $884,000 9 Total current assets $14,228,000 $12,772,000 $12,000,000 10 Property, less accumulated depreciation $18,432,000 $19,721,000 $19,949,000 11 Long-term investments $256.000 $408,000 $366,000 12 Deferred income taxes-net $294.000 $168,000 $222,000 13 Goodwill $303,000 $1,307,000 $1,082,000 14 Other assets $995,000 $915,000 $789,000 15 Total assets $34,508,000 $35,291,000 $34,408,000 $405,000 $307,000 $9.458,000 $391,000 $10,561,000 $19,577,000 $222,000 $241,000 SO $665,000 $31,266,000 $722,000 $1,110,000 $8,279,000 $662,000 $1.299,000 $2,425,000 $14,497,000 $14,391,000 $827,000 $1,149,000 $30,864,000 $1,137,000 $294,000 $6,590,000 $747,000 $1,378,000 $1,950,000 $12,096,000 $15,564,000 $803,000 5955,000 $29,418,000 $510,000 $795,000 $6,651,000 $790,000 $1,253,000 $1,975,000 $11,974,000 $14,394,000 $763,000 $843,000 $27,974,000 $43,000 $1,061,000 $5,633,000 $820,000 $1,078,000 $1,857,000 $10,492,000 $11,545,000 $729,000 $846,000 $23,612,000 18 Liabilities 19 Short-term borrowings 20 Current maturities of long-term debt 21 Accounts payable 22 Accrued compensation & employee benefits 23 Deferred revenue 24 Other current liabilities 25 Total current liabilities 26 Long-term debt, excluding current maturities 27 Deferred revenue - extended protection plans 28 Other liabilities EF 29 Total liabilities 30 31 Stockholders' Equity 32 Common stock 33 Capital in excess of par value 34 Retained earnings 35 Accumulated other comprehensive income (lc 36 Total shareholders' equity 37 Total liabilities and stockholders' equity 38 39 Additional Information 40 Property and Equipment, at cost 41 Land 42 Buildings & building improvements 43 Equipment 44 Construction in progress 45 Total cost 46 Less: accumulated depreciation $401,000 $0 $3,452,000 -$209,000 $3,644,000 $34,508,000 S 415,000 $22.000 $5,425,000 $11,000 $5,873,000 $35,291,000 $433,000 SO $6.241,000 $240.000 $6,434,000 $34,408,000 $455,000 SO $7,593,000 $7,654,000 $31,266,000 $7,196,000 $18,052,000 $10,090,000 $525.000 $35,863,000 $17,431,000 $7.414,000 $18,521.000 $10,475,000 $530,000 $36.940,000 $17.219,000 $7,329,000 $18,147,000 $10,978,000 $464.000 $36,918,000 $16,969,000 $7,086,000 $17.451,000 $10,863,000 $513,000 $35,913,000 $16,336,000 1 As Reported Annual Income Statement 2 Report Date 3 Scale 4 Net sales 5 Cost of sales 6 Gross margin 7 Selling, general & administrative expense 8 Depreciation & amortization 9 Operating income (loss) 10 Interest income (expense), net 11 Loss on extinguishment of debt 12 Pre-tax earnings (losses) 13 Income tax provision 14 Net earnings 15 16 Additional Information 17 Weighted average shares outstanding - basic 18 Cash dividends per share 19 Net cash flows from operating activities 2018 2017 2016 Thousands Thousands Thousands $71,309,000 $68,619,000 $65,017,000 $48,401,000 $45,210,000 $42,553,000 $22,908,000 $23,409,000 $22,464,000 $17,413,000 $15,376,000 $15, 129,000 $ 1,477,000 5 1,447,000 $ 1,489,000 $ 4,018,000 $ 6,586,000 $ 5,846,000 $ (624,000) $ (633,000) $ (645,000) $ (464,000) S $ 3,394,000 $5,489,000 $ 5,201,000 $ 1,080,000 $ 2,042,000 $ 2,108,000 $ 2,314,000 $ 3,447,000 $ 3,093,000 811,000 1.85 $ 6,193,000 8 39,000 880,000 1.58 $5,065,000 $ 5,617,000 1.33 Project The required tasks are detailed below: (1) Prepare vertical common-size income statements and balance sheets for both companies. Note: Use "Total Sales" and "Total Assets" as the denominators for income statement and balance sheet, respectively. Compute for 2018, 2017, and 2016. (2) Prepare horizontal analysis on income statements and balance sheets for both companies. (3) Prepare ratio analyses (for 2018, 2017, and 2016) for both companies. You should include the following ratios in your computations: Profitability ratios o Gross Profit margin o Profit margin o Return on assets o Return on equity Productivity o Accounts Receivable Turnover o Average Collection Period 0 Inventory Turnover o days inventory outstanding OPPE Turnover o Asset Turnover Solvency O Debt-to-equity o Times interest earned o Return on Financial leverage Liquidity o Current Ratio o Quick Ratio o Working capital (3) Comment on the analytical results of the two companies based on your work in excel. In addition to contrasting the ratios between the companies, you should interpret the numbers and make suggestions as to why the ratio of one company might be higher/lower than the other Note: General discussions of the ratios are given in our textbook, which will help you structure your comments. AutoSave Om H Home Depot (2juds - Protected View - Excel File Home Insert Page Layout Formulas Data Review View Help O PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected f NOTE:2015 BALANCE SHEET INFORMATION SHOULD BE USED ONLY FOR RA NOTE: 2015 BALANCE SHEET INFORMATION SHOULD BE USED ONLY FOR RATIOS 2018 2017 2016 2 Report Date 2015 $1.778 $1.936 $13,925 $890 $18,529 $22 375 $2.252 $847 $44,003 $3,595 $1,952 $12.748 $638 $18,933 $22.075 $2.275 $1.246 $44,529 52538 $2,029 $12549 5600 $17,724 $21,914 $2,093 $1.235 $42,966 $2216 $1,890 $11,809 $569 $16,484 $22,191 $2,102 $1,196 $41,973 4 Sin Millions 5 Current assets 6 Cash and cash equivalents 7 Receivables, net 8 Merchandise inventories 9 Other current assets 10 Total current assets 11 Net property and equipment 12 Goodwill 13 Other assets 14 Total assets 15 Current Liabilities: 16 Short-term debt 17 Accounts payable 18 Accrued salaries and related expenses 19 Sales taxes payable 20 Deferred revenue 21 Income taxes payable 22 Current installments of long-term debt 23 Other accrued expenses 24 Total current liabilities 25 Long-term debt, excluding current installments 26 Deferred income taxes 27 Other long-term liabilities 28 Total liabilities 29 Common stock, par value $0.05, authorized 10,000 shares; iss 30 Paid-in capital 31 Retained earnings 32 Accumulated other comprehensive loss 33 Treasury stock, at cost, 677 shares at February 3, 2019 and 62: 34 Total stockholders' (deficit) equity 35 Total liabilities and stockholders' equity $350 $6,565 $1,515 5476 $1,566 534 $1,339 $7.755 $1,506 $656 $1,782 $11 $1,056 $2,611 $16.716 $26.807 $491 $1,867 $45.881 SB9 $10.578 546 423 -$772 -558.196 $1,878 $44,003 $1,559 $7244 $1,640 $520 $1,805 554 $1,202 $2,170 $16,194 $24 267 $440 $2,174 $43,075 $89 $10.192 $39,935 -$566 -$48.196 $1,454 $44,529 $710 $7.000 $1.484 $508 $1,669 $25 S542 $2.195 $14,133 $22349 $296 $1,855 $38,633 $88 $77 $1.941 $12,524 $20,789 $379 $1,965 $35,657 $88 59 347 $30.973 -$898 -$33.194 $6,316 $41,973 $9,787 $35,519 $867 $40.194 $4,333 $42,966 39 Additional Information 40 No allowance account is created for receivables, i.e. Netreceivables is equal to gross receivables 42 **Property and Equipment at cost 43 Land 44 Buildings 45 Furniture, Fixtures and Equipment 46 Leasehold Improvements 47 Construction in Progress 48 Capital Leases 49 Less Accumulated Depreciation and Amortization 50 Net Property and Equipment 58,363 18199 12460 1705 820 1392 20564 22375 8352 $18.073 11506 1637 538 1308 19339 22075 $8.207 $17.772 11,020 1519 739 1.169 18.512 21.914 8.149 17,667 10,279 1,481 670 1,020 17.075 22.191 AutoSave Of H Home Depot (2).xls - Protected View - Excel File O Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protec C22 x fa 1178 D 2018 2017 2016 $108,203 71,043 37,160 $100,904 66,548 34,356 $94,595 62,282 32,313 17,864 1,811 17,132 1,754 1 shares in Millions, $ in Millions 2 Income Statement 3 Net Sales 4 Cost of sales 5 Gross profit 6 Operating expenses: 7 Selling, general and administrative 8 Depreciation and amortization 9 Impairment loss 10 Total operating expenses 11 Operating income 12 Interest and other (income) expense: 13 Interest and investment income 14 Interest expense 15 Other 16 Interest and other, net 17 Earnings before provision for income taxes 18 Provision for income taxes 19 Net earnings 19,513 1,870 247 21,630 15,530 19,675 14,681 18,886 13,427 -93 1,051 1-74 1,057 -36 972 16 974 14,556 3,435 $11,121 983 13,698 5,068 $8,630 936 12,491 4,534 $7,957 L 21 Additional Information 22 Basic weighted average common shares 23 Cash dividend per share 24 Operating cash flow for the year 1,137 4.12 13,038 1.178 3.56 12,031 1,229 2.76 9.783 17 AutoSave on H Lowes (lxlsx - Protected View - Excel File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Pro 038 x for CL DEF NOTE: 2015 BALANCE SHEET INFORMATION SHOULD BE USED ONLY FOR RATIOS 2 Report Date 2018 2017 2016 2015 3 $ in Thousands 4 Cash & cash equivalents $511,000 $588,000 $558,000 S405,000 5 Short-term investments $218,000 $102,000 $100,000 $307,000 6 Merchandise inventory, net $12,561,000 $11,393,000 $10,458,000 $9.458,000 7 Other current assets $938,000 $689,000 $884,000 $391,000 8 Total current assets $14,228,000 $12,772,000 $12,000,000 $10,561,000 9 Property, less accumulated depreciation $18,432,000 $19,721,000 $19,577,000 10 Long-term investments $256,000 $408,000 $366,000 $222,000 11 Deferred income taxes - net $294.000 $168,000 $222,000 $241,000 12 Goodwill $303,000 $1,307,000 $1,082,000 13 Other assets $995,000 $915,000 $789,000 5665,000 14 Total assets $34,508,000 $35,291,000 $34,408,000 $31,266,000 15 16 17 Liabilities 18 Short-term borrowings $722,000 $1,137,000 $510,000 $43,000 19 Current maturities of long-term debt $1,110,000 $294,000 $795,000 $1,061,000 20 Accounts payable $8,279,000 $6,590,000 $6,651,000 $5,633,000 21 Accrued compensation & employee benefits $662,000 $747,000 $790,000 $820,000 22 Deferred revenue $1,299,000 $1,378,000 $1,253,000 $1,078,000 23 Other current liabilities $2,425,000 $1,950,000 $1.975,000 $1,857,000 24 Total current liabilities $14,497,000 $12,096,000 $11,974,000 $10,492,000 25 Long-term debt, excluding current maturities $14,391.000 $15.564,000 $14.394.000 $11.545.000 26 Deferred revenue - extended protection plans 5 827,000 5803,000 $763,000 27 Other liabilities $1,149,000 5955,000 $843,000 $846,000 28 Total liabilities $30,864,000 $29,418,000 $27,974,000 $23,612,000 29 30 Stockholders' Equity 31 Common stock $401,000 $415,000 $433,000 $455,000 32 Capital in excess of par value $0 $22.000 $0 33 Retained earnings $3,452,000 $5,425.000 $6,241,000 57,593,000 34 Accumulated other comprehensive income (c -$209,000 $11,000 -$240,000 $394.000 35 Total shareholders' equity $3,644,000 $5,873,000 $6,434,000 $7,654,000 36 Total liabilities and stockholders' equity $34.508,000 $35,291,000 $34,408,000 $31,266,000 SO 38 Additional Information 39 Property and Equipment, at cost 40 Land 41 Buildings & building improvements 42 Equipment 43 Construction in progress 44 Total cost 45 Less: accumulated depreciation $7.196.000 $18,052.000 $10.090.000 $525.000 $35,863,000 $17,431,000 $7.414,000 $18,521,000 $10,475,000 $530,000 $36,940,000 $17.219,000 $7.329,000 $18,147,000 $10.978.000 $464.000 $36.918,000 $16.969.000 $7,086,000 $17.451,000 $10,863.000 $513,000 $35,913.000 $16,336,000 AutoSave Off H Lowes (1).xlsx - Protected View - Excel File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay D22 : * fx B C D 1 As Reported Annual Income Statement 2 Report Date 3 Scale 4 Net sales 5 Cost of sales 6 Gross margin 7 Selling, general & administrative expense 8 Depreciation & amortization 9 Operating income (loss) 10 Interest income (expense), net 11 Loss on extinguishment of debt 12 Pre-tax earnings (losses) 13 Income tax provision 14 Net earnings 15 16 Additional Information 17 Weighted average shares outstanding - basic 18 Cash dividends per share 19 Net cash flows from operating activities 2018 2017 2016 Thousands Thousands Thousands $71,309,000 $68,619,000 $65,017,000 $48,401,000 $45,210,000 $42,553,000 $22,908,000 $23,409,000 $22,464,000 $17.413,000 $15,376,000 $15,129,000 $ 1.477 000 $ 1,447,000 $ 1,489,000 $ 4,018,000 $ 6,586,000 $ 5,846.000 S (624,000) $ (633,000) $ (645,000) $ (464,000) $ $ 3,394,000 $ 5,489,000 $5,201,000 $ 1,080.000 $ 2,042,000 $ 2.108,000 $ 2,314,000 $3,447,000 $ 3,093,000 811,000 1.85 $ 6,193,000 839,000 880,000 1.58 1.33 $ 5,065,000 $ 5,617,000