Assignment instructions are as follows: 1. Use the information in the provided Income Statement and Balance Sheet to create the 2020 Statement of Cash Flows for H&K Inc. This should be created on the appropriate Tab of the Excel Workbook Template provided on Canvas. 3. Use Excel to setup the Statement of Cash Flows by linking the values from the Income Statement and the Balance Sheet b. Use Excel formulas to calculate all totals and sub-totals. 2. Formulas: 3. Complete the ratio calculations for 2020 on the appropriate tab using the income Statement and Balance Sheet. All ratios should be linked back to your financial statements, 4. Save your Excel workbook Project3-your last name (For example: Project3-Smith.xls) 5. Upload the completed assignment to Canvas using the link provided. Home Insert Draw Page Layout Formulas Data Review View Tell me X Calibri (Body) = 2 Wrap Text General V 11 A A a. Av Paste BIU 3 IM Merge & Center $ %) 85 AS x fx B C D E F NH H & KINC. Income Statement For the Years Ended December 31, 2020 and 2019 $ in millions 3 4 2020 2019 N00 $ 44,452 $ 24,969 19,483 37,043 20,807 16,236 6 7 Revenue 8 Net sales 9 Cost of merchandise sold 10 Gross Profit 11 12 Operating Expenses 13 Selling, general and administrative 14 Depreciation 15 Amortization 16 Operating income 17 18 Interest expense, net 19 Gain/(Loss) on sale of property & equip 20 Income before income taxes 21 22 Provision for income taxes 23 24 Net Income 7,182 2,550 45 9,706 5,985 2,499 45 7,707 235 350 9,821 171 115 7,651 2,455 1,913 $ 7,366 $ 5,738 26 27 Income Staten ont Balance Sheet Cash Flow Statement Ratios + Ready A1 x fe H & KINC. A B D 4 2020 2019 9,839 $ 2,010 4,029 326 16,204 3,242 1,600 3,814 359 9,015 1 H&K INC. 2 Balance Sheet 3 December 31, 2020 and 2019 $ in millions 5 6 7 Current Assets 8 Cash and cash equivalents $ 9 Accounts Receivable, net 10 Merchandise inventories 11 Other current assets 12 Total Current Assets 13 14 Long-Term Assets 15 Property and equipment, net of accumulated depreciation 16 Intangible assets, net of accumulated amortization 17 Total Long-Term Assets 18 Total Assets $ 19 20 Current Liabilities 21 Accounts payable $ 22 Accrued liabilities 23 Income taxes payable 24 Current portion of long-term debt 25 Total Current Liabilitles 26 27 Non-current Liabilities 28 Long-term debt 29 Total Liabilities 30 31 Shareholders' Equity 8,775 193 8,968 25,172 $ 8.515 238 8,753 17,768 1,276 $ 1,200 2.455 99 5,030 1,511 1,160 1,913 110 4,694 5,825 10,855 5,483 10,177 Income Statement Balance Sheet Cash Flow Statement Ratios + Ready home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) 11 A A w Wrap Text MTB Paste General B IU IM Merge & Cente $ % * Al Xfx H & KINC Conditional Format Formatting as Table SE 4 4 D E 23 Income taxes payable F 2,455 1,913 24 Current portion of long-term debt 99 110 25 Total Current Liabilities 5,030 4,694 26 27 Non-current Liabilities 28 Long-term debt 5,825 5,483 29 Total Liabilities 10,855 10,177 30 31 Shareholders' Equity Common stock, 01 par value; 9,000 shares authorized: 380 and 370 32 shares issued & outstanding respectively 33 Pald-in capital 3,018 2,723 34 Treasury stock, at cost 19,649) (8.744) 35 Retained earnings 20,944 13,608 36 Total Shareholders' Equity 14,317 7,591 37 Total Liabilities and Shareholders' Equity $ 25,172 $ 17.768 38 39 Additional Information: 40 41 The company paid dividends of $30 in 2020, 42 The company purchased treasury stock of $905 during 2020, and sold common stock for $20 during 2020. No other stock transactions took place 43 The company purchased $3,010 in equipment during the year. In addition, equipment was sold for $550 in cash the equipment had a book value at the time of sale of $200 44 There were no purchases or sales of intangible assets. 45 The company made payments of $189 on its debt obligations, and took an additional $520 in debt during the year. 46 47 48 49 50 51 52 Income Statement Balance Sheet CastiFlow Statement Ratios + Ready Home Insert Draw Page Layout Formulas Data Review View Tell me X Calibri (Body) v 11 ' v 2 Wrap Text General Paste BIU Merge & Center $ % ? A1 4 Xfx H & KINC. D E F G 2 3 4 5 H&K INC. Statement of Cash Flows For the Year Ended December 31, 2020 USD ($) in Millions 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 22 28 29 30 31 32 33 34 35 Income Statement Balance Shoot Cash Flow Statement Ratios + Ready Home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) 11 ' = V Wrap Text General LG 3 HI Paste BT a. Ar 53Merge & Center $ % ) 38 Cand Form G6 f A B D E F G H 1 Current Ratio 1 2 3 4 5 6 Current assets/Current abilities 16204/ 5030 Quick Assets/Current Liabilities Add-Test Ratio = 11894/ 5030 AR Turnover = Days Sales Uncollected Inventory turnover = Days sales in Inventory Total Asset Turnover - 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 15 BE Debt to Equity Times Interest Carned Net Profit Margin- Return on Total Assets Income Statement Balance Shoot Cash Flow Statement Ration Ready