PLEASE PROVIDE RATIO FORMULAS!

thank you!

YOU MUST USE ALL FORMULAS AND CELL REFERENCES ON THE RATIOS PAGE FOR THE NUMBERS TO BE CONSIDERED FOR FULL CREDIT ON THE PROJECTS. You may type in the names of the items included in the ratios but you must use cell references and formulas for all of the computations and numbers. Failure to do so will result in up to a 100% point deduction. PROJECT INFORMATION You have been given financial statements for Amazon and Walmart (Income Statement, Balance Sheet, and Statement of Cash Flows). For this project, you will use the information in those statements to compute the ratios requested on the Ratios Tab. Current year information is Fiscal or Calendar year for both companies. SUGGESTED STEPS FOR COMPLETION OF THE PROJECT: Determine what amounts are needed to calculate the ratios requested. For example you need Current Assets and Current Liabilities to compute the Working Capital. Enter the names of the items needed in the boxes in column A for each of the ratios. See Ratios Tab, column A for Working Capital. Next, use the cell reference function and any other necessary formulas to enter the numbers for each of the items for each of the companies in the boxes under the requested ratios. Again see Ratios Tab Columns C and E for the Current Ratio for Walmart and for Amazon. Next use the numbers you have entered to calculate the ratios requested. Again see the Ratios Tab Columns and E for Current Ratio for Walmart and for Amazon ROUND ALL RATIO AMOUNTS TO 3 DECIMAL PLACES Repeat the steps for each of the ratios listed on the Ratio Tab HINTS Assume all sales were on credit where necessary. Show all numbers as positives not negatives COGS can also be called Cost of Sales On Walmart's financial statements, interest expense is with interest revenue so you will need to adjust for this to get just interest expense Income Tax Expense can also be called the Provision for Income Taxes Remember to consider the Statement of Cash Flows when trying to determine cash spent for items. Use Consolidated Net Income for Net Income Use Total Equity when asked for Equity Final step - So that we can see your use of formulas you must use the following steps to display your formulas for the ratios on the Ratios Formula Tab. Copy all of the Ratios information from the Ratios Tab to the Ratios formulas tab. Highlight the entire ratios information area and press the "etrl" key and the "-"key. This will cause the formulas used to display instead of the numbers. Save your file with the formulas displayed on the Ratio Formulas Tab page and the numbers displayed on the Ratios Tab. E17 fx B D 1 Walmart Financial Statements 2 Consolidated Statements of Income - USD ($) shares in Millions, $ in Millions 3 Consolidated Statements of income - USD ($) shares in Millions, S in Millions 12 Months Ended Jan 31, 2020 Jan. 31, 2019 Jan. 31, 2018 5 Revenues: Net Sales 519,926 510,329 495,761 4,038 523,964 4,076 514,405 4,582 500,343 394,605 108,791 20,568 385,301 107,147 21,957 373,396 106,510 20,437 7 Membership and other income 8 Total revenues 9 Costs and expenses: 10 Cost of sales 11 Operating selling general and administrative expenses Operating income 13 Interest: 14 Debt 15 Finance, capital lease and financing obligations 16 Interest income 17 Interest, net 18 Loss on extinguishment of debt 19 Other (gains) and losses 20 Income before income taxes 21 Provision for income taxes 22 Consolidated net income 23 Consolidated net income attributable to noncontrolling interest 24 Consolidated net income attributable to Walmart 25 Net income per common share: 26 Basic net income per common share attributable to Walmart 27 Diluted net income per common share attributable to Walmart 28 Weighted average common shares outstanding 29 Basic in shares) 30 Diluted On shares) 31 Dividends declared per common share 32 33 34 2,262 337 (189) 2,410 0 (1,958) 20,116 4,915 15,201 $ (320) $14,881.00 1,975 371 (217) 2,129 0 8,368 11,460 4,281 7,179 S (509) $6,670.00 1,978 352 (152) 2,178 3,136 0 15,123 4,600 10,523 $ (661) $9,862.00 $3.29 $5.22 5 $ 2.28 2 3 2.850.00 $ 2,868.00 $2.12 2.929.00 $ 2,945.00 $2.08 2,995.00 $ 3,010 $2.04 Jan. 31, 2020 Jan. 31, 2019 Consolidated Balance Sheets - USD ($) $ in Millions Current assets: Cash and cash equivalents Receivables, net Inventories 9,465 6,284 44,435 7,722 6,283 44,269 3,623 61,897 104,317 0 1,622 61,806 105,208 17,424 4,417 0 31,073 16,567 236,495 0 7,078 31,181 14,822 219,295 Prepaid expenses and other Total current assets Property and equipment, net Operating lease right-of-use assets Finance lease right-of-use assets, net Property under capital lease and financing obligations, net Goodwill Other long-term assets Total assets Current liabilities: Short-term borrowings Accounts payable Accrued liabilities Accrued income taxes Long-term debt due within one year Operating lease obligations due within one year Finance lease obligations due within one year Capital lease and financing obligations due within one year Total current liabilities Long-term debt Long-term operating lease obligations Long-term finance lease obligations Long-term capital lease and financing obligations Deferred income taxes and other Commitments and contingencies Equity Common stock Capital in excess of par value Retained earnings 575 46,973 22,296 280 5,362 1,793 511 0 77,790 43,714 16,171 4,307 0 12,961 5,225 47,060 22,159 428 1,876 0 0 729 77,477 43,520 0 0 6,683 11,981 284 3,247 83,943 288 2,965 80.785 Current liabilities: Short-term borrowings Accounts payable Accrued liabilities Accrued income taxes Long-term debt due within one year Operating lease obligations due within one year Finance lease obligations due within one year Capital lease and financing obligations due within one year Total current liabilities Long-term debt Long-term operating lease obligations Long-term finance lease obligations Long-term capital lease and financing obligations Deferred income taxes and other Commitments and contingencies Equity: Common stock Capital in excess of par value Retained earnings Accumulated other comprehensive loss Total Walmart shareholders' equity Noncontrolling interest Total equity Total liabilities and equity 575 46,973 22,296 280 5,362 1,793 511 0 77,790 43,714 16,171 4,307 0 12,961 5,225 47,060 22,159 428 1,876 0 0 729 77,477 43,520 0 0 6,683 11,981 284 3,247 83,943 (12,805) 74,669 6,883 $ 81,552 $236,495 288 2,965 80,785 (11,542) 72,496 7,138 $ 79,634 $219,295 Consolidated Statements of Cash Flows - USD ($) $ in Millions 12 Months Ended Jan. 31, 2020 Jan 31, 2019 Jan 31, 2018 $ 15,201 $7,179 $ 10,523 10.987 (1,886) 15 (1,036) 320 0 1,981 10,678 3,516 4,850 0 (499) 0 1,734 10,529 0 0 0 (304) 3,136 1.210 Cash flows from operating activities Consolidated net income Adjustments to reconelle income from continuing operations to net cash provided by operating activities Depreciation and amortization Unrealised gain and losses Gains and losses for disposal of business operations Asda pension contribution Deferred income taxes Loss on extinguishment of debt Other operating activities Changes in certain assets and abilities, net of effects of acquisitions Receivables.net Inventories Accounts payable Accrued liabilities Accrued income taxes Net cash provided by operating activities Cash flows from investing activities Payments for property and equipment Proceeds from the disposal of property and equipment Proceeds from the disposal of certain operations Payments for business acquisitions net of cash acquired Other investing activities Net cashed in investing activities Cash flows from financing title Net change in short term borrowings Proceeds from issuance of long term debt Repayments of long-term debt Premium paid to extingulthebt Dividends pald Purchase of Company Dividends paid to noncontrolling interest Purchase of noncontro 154 (300) (274) 386 193) 25.255 (368) 11,311) 1,831 183 (40) 27,753 (1.074) (140) 4,086 928 1557) 28,337 (10,705) 321 833 (56) 479 19.128) (10.344) 519 876 (14,656) (431) 124,036) (10,051) 378 1,046 375) 771 09,079) (6,656) 5,492 (1,907) 0 15,048) 15.717) 15551 0 153) 15,872 0,7841 0 16,102) 17,410) (431) D 4,148 7.476 (13,0611 13,059) 16,124) 18,296) 1690) (8) 274) 186 (93) 25,255 1,831 183 (40) 27,753 4,085 928 (5571 28,337 (10,705) 321 833 (56) 479 19.128) (10,344) 519 876 (14,656) (431) (26,036) 110,051) 375 1,046 (375) 1771 (9,079) Accounts payable Accrued liabilities Accrued income taxes Net cash provided by operating activities Cashflows from investing activities Payments for property and equipment Proceeds from the disposal of property and equipment Proceeds from the disposal of certain operations Payments for business acquisitions, net of cash acquired Other Investing activities Net cash used in investing activities Cash flows from financing activities Net change in short-term borrowing Proceeds from issuanceoftong-term debt Kepayments of long-term debt Premiums paid to extinguish debt Dividends paid Purchase of Company stock Dividends paid to noncontrolling interest Purchase of noncontrolling interest Other financing activities Net cash used in financing activities Effect of Exchange Rate on Canh, Cash Equivalents, and Restricted Cash Net increase (decrease in cash, cash equivalents and restricted cash Cath, cash equivalents and restricted cash at beginning of year Cash Cash equivalents and restricted cathat end of you Supplemental disclosure of cash flow information Income taxes paid Interest Pd, Excluding Capitalized interest, Operating Activities (4,656) 5,492 (1,907) 0 16,048) (5,717) 1555) (53) 15,872 13,784) 0 (6.102) (7.4101 (431) 0 (629) (2.537 (438) 742 7,014 7.256 4,148 7,476 (13,061) 3,059) (6,124) (8,296) (690) (8) 1261) (19,575) 487 (130) 7,144 7,014 (908) (14,299) (59) 1,759 7,756 9,515 3,616 52,464 3,982 $ 2,348 6,179 $ 2,450 1 Amazon Financial Statement 2 Consolidated Statements of Operations - USD ($) shares in Millions, $ in Millions 12 Months Ended Dec 31, 2019 $ 280,522 Dec 31, 2018 $ 232,887 Dec 31, 2017 $177,866 165,536 40,232 35,931 18,878 5,203 201 265,981 14,541 111,934 25,249 22,620 10,069 3,674 214 173,760 4,106 202 832 4 Total net sales 5 Operating expenses 6 Cost of sales 7 Fulfillment 8 Technology and content 9 Marketing 10 General and administrative 11 Other operating expense (income), net 12 Total operating expenses 13 Operating income 14 Interest income 15 Interest expense 16 Other income (expense), net 17 Total non-operating income (expense) 18 Income before income taxes 19 Provision for income taxes 20 Equity method investment activity, net of tax 21 Net income 22 Basic earnings per share 23 Diluted earnings per share 24 Weighted average shares used in computation of earnings per share: 25 Basic in shares) 26 Diluted in shares) 27 Net product sales 28 Total net sales 29 Net service sales 30 Total net sales 31 32 33 24 139,156 34,027 28,837 13,814 4,336 296 220,466 12,421 440 (1,417) (183) (1.160) 11,261 (1,197) 9 $ 10,073 $20.68 $ 20.14 (848) (1,600 203 (565) 13,976 (2,374) (14) $11,588 $ 23,46 $ 23.01 346 (300) 3,806 1769) (4) $ 3,033 $6.32 $6.15 494 504 487 500 480 493 $ 160,408 $ 141,915 $ 118,573 + $ 120,114 $ 90,972 559,293 Dec 31, 2019 Dec 31, 2018 $36,092 18,929 20,497 20,816 96,334 72,705 25,141 14,754 16,314 225,248 $31,750 9,500 17.174 16,677 75,101 61,797 0 14,548 11,202 162,648 Consolidated Balance Sheets - USD ($)S in Millions Current assets: Cash and cash equivalents Marketable securities Inventories Accounts receivable, net and other Total current assets Property and equipment, net Operating leases Goodwill Other assets Total assets Current liabilities: Accounts payable Accrued expenses and other Unearned revenue Total current liabilities Long-term lease liabilities Long-term debt Other long-term liabilities Commitments and contingencies (Note 7) Stockholders' equity! Preferred stock. $0.01 par value: Authorized shares-500 Issued and outstanding shares-none Common stock, $0.01 par value: Authorized shares - 5,000 Issued shares - 507 and 514 Outstanding shares Treasury stock, at cost Additional paid in capital Accumulated other comprehensive income (loss) Retained earnings Total stockholders' equity Total liabilities and stockholders' equity 47,183 32,439 8,190 87,812 39,791 23,414 12,171 38,192 23,663 6,536 68,391 9,650 23,495 17,563 O 5 (1,837) 33,658 (986) $ 31,220 62,050 $225,248 0 5 (1,837) 26,791 (1,035) $ 19,625 43,549 $162,648 Consolidated Statements of Cash Flows - USD ($)S in Millions 12 Months Ended Dec 31, 2019 Dec. 31, Dec 31, $32,173 $21,856 $19,934 11,588 10,073 3,033 21,789 6,864 164 (249) 796 15,341 5,418 274 219 441 11,478 4,215 202 (292) (29) Statement of Cash Flows (Abstract) CASH, CASH EQUIVALENTS, AND RESTRICTED CASH, BEGINNING OF PERIOD OPERATING ACTIVITIES Net income Adjustments to reconcile net income to net cash from operating activities: Depreciation and amortization of property and equipment and capitalized content costs, operating lease Stock-based compensation Other operating expense (income), net Other expense income), net Deferred income taxes Changes in operating assets and liabilities: Inventories Accounts receivable, net and other Accounts payable Accrued expenses and other Unearned revenue Net cash provided by used in operating activities INVESTING ACTIVITIES! Purchases of property and equipment Proceeds from property and equipment sales and incentives Acquisitions, net of cash acquired, and other Sales and maturities of marketable securities Purchases of marketable securities Net cash provided by used in investing activities FINANCING ACTIVITIES: Proceeds from long-term debt and other Repayments of long-term debt and other Principal repayments of financeleases Principal repayments of financing obligations Net cash provided by used in financing activities Foreign currency effect on cash, cash equivalents, and restricted cash Net increase decrease in cash, casheguivalents, and restricted cash CASH, CASH EQUIVALENTS, AND RESNUCTED CASH, END OF PERIOD SUPPLEMENTAL CASH FLOW INFORMATION: (3,278) (7,681) 8,193 (1,383) 1,711 38,514 (1,314) 14,615) 3,263 472 1,151 30,723 (3,583) (4,780) 7,100 283 738 18,365 (16,861) (13,427) (11,955) 4,172 2,104 1,897 12,461) (2,186) (13.972) 22,681 8,240 9,677 (31,812) (7,100) (12.731) (24,281) (12,369) (27.084) 2,273 (2,684) 19,628) (27) (10,066) 70 4,237 36,410 768 (668) 17,449) (337) 17,686) (351) 10,317 32,173 16,228 (1,301) (4,799) (200) 9,928 713 1,922 21,856 3,263 8,193 (1,383) 1,711 38,514 472 1,151 30,723 7,100 283 738 18,365 (16,861) (13,427) (11,955) 4,172 2,104 1,897 (2,461) (2,186) (13,972) 22,681 8,240 9,677 (31,812) (7,100) (12.731) (24,281) (12,369) (27,084) Accounts payable Accrued expenses and other Unearned revenue Net cash provided by (used in) operating activities INVESTING ACTIVITIES: Purchases of property and equipment Proceeds from property and equipment sales and incentives Acquisitions, net of cash acquired, and other Sales and maturities of marketable securities Purchases of marketable securities Net cash provided by (used in) investing activities FINANCING ACTIVITIES: Proceeds from long-term debt and other Repayments of long-term debt and other Principal repayments of finance leases Principal repayments of financing obligations Net cash provided by (used in)financing activities Foreign currency effect on cash, cash equivalents, and restricted cash Net increase (decrease) in cash, cash equivalents, and restricted cash CASH, CASH EQUIVALENTS, AND RESTRICTED CASH, END OF PERIOD SUPPLEMENTAL CASH FLOW INFORMATION: Cash paid for interest on long-term debt Cash paid for operating leases Cash paid for interest on capital leases Cash paid for interest on finance leases Cash paid for interest on financing obligations Cash paid for income taxes, net of refunds Assets acquired under operating leases Property and equipment acquired under capital leases Property and equipment acquired under finance leases Property and equipment acquired under build to suit arrangements 2,273 (2,684) (9,628) (27) (10,066) 70 4,237 36,410 768 (668) (7,449) (337) (7,686) (351) 10,317 32,173 16,228 (1,301) (4,799) (200) 9,928 713 1,922 21,856 875 3,361 854 0 381 328 0 200 647 39 881 7,870 194 -1,184 119 -957 0 9,637 10,615 13,723 $1,362 $3,641 $3,54 Ratios WALMART AMAZON Working Capital (15,984) 8,522 Example Current Assets 61,806 96,334 Example Current Liabilities 77,790 87,812 Example Current Ratio Debt to Asset Ratio Return on Equity 2 3 Return on Assets 40 Inventory Turnover 41 42 43 44 45 46 47 Days in Inventory 48 49 50 51 52 53 54 Accounts Receivable Turnover 55 56 57 58 59 60 61 Times Interest Earned 62 63 64 65 66 67 68 69 70 71 72 Free Cash Flow 73 74 75 76 77 Free Cash Flow Gross Profit Rate 5 6 7 8 Profit Margin 9 20 21 32 93 94 95 Asset Turnover 96 97 98 99 100 101 102 103 104 105 106 107