ACC 3010 PROJECT 3 PART 3 Fall 2020 Due December 15th before 5pm - You must submit your completed excel file to the submission link on Canvas. Your file must be named correctly - "Your name (first and last) Project 3 part 3. Failure to name your file correctly will result in a 1 point deduction. BE SURE TO CHECK AFTER THE LAST SUGGESTED "STEP TO COMPLETE" FOR INFORMATION REGARDING POTENTIAL EXTRA CREDIT ON THIS PROJECT. 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. You are encouraged to get help from the tutors in the Accounting Lab. You can also get help by email. You can email your professor and you can email the accounting lab (accountinglab@wayne.edu). If you email both please do so in the same email. Replies will be a reply all so whoever replies the other will know. You will likely get a faster response if you email both. Response time for emails sent to the accounting lab should be within 7-8 hours. Also, there will be no timely response to project emails sent after 2p on the project due dates. Don't wait until the last minute. There will be no response to accounting lab emails on Sundays. 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 C 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. 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 "ctrl" 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. POSSIBLE 5 POINTS EXTRA CREDIT For an opportunity to earn up to 5 points extra credit, please read the posted article "WalMart has already lost its battle with Amazon". Prepare a 1-1 page (max) double spaced paper with your opinion on the statements in the article. For the potential to earn the full 5 points you must refer to the ratios that you calculated in the excel portion of the project. You must mention specific ratios and how they support or do not support your position/opinion on the article. Your submission will also be graded for correct spelling, sentence structure, and grammar. Your paper must be prepared in Word (no other programs accepted). You must submit your Word document to the correct submission link on the Canvas site before 5pm on the due date. Each student must submit an ORIGINAL excel file that is their own work. While students are encouraged to work together, each student must process and format his/her own set of excel worksheet. Duplicate submissions (format, not numbers) will result in the students receiving a 0 for the assignment. And possibly other consequesces as stated inthe University Integrity policies. Submissions will NOT be accepted via email attachment. Submission to other than the correct link on Canvas will result in a 10% point deduction. NO LATE SUBMISSIONS WILL BE ACCEPTED Walmart Financial Statements Consolidated Statements of Income - USD ($) shares in Millions, $ in Millions Consolidated Statements of Income-USD ($) shares in Millions, $ in Millions Revenues: Net Sales Membership and other income 12 Months Ended 12 Months Ended Jan. 31, 2020 Consolidated Balance Sheets - USD ($) $ in Millions Current assets: Jan. 31, 2020 Jan. 31, 2019 Jan. 31, 2019 Jan. 31, 2018 Cash and cash equivalents 9,465 7,722 519,926 510,329 495,761 Receivables, net Inventories 6,284 6,283 44,435 44,269 4,038 Total revenues 523,964 4,076 514,405 4,582 500,343 Prepaid expenses and other 1,622 3,623 Total current assets 61,806 Costs and expenses: Property and equipment, net 105,208 61,897 104,317 Cost of sales 394,605 385,301 373,396 Operating lease right-of-use assets 17,424 0 Operating, selling, general and administrative expenses 108,791 107,147 106,510 Finance lease right-of-use assets, net 4,417 0 Operating income 20,568 21,957 20,437 Property under capital lease and financing obligations, net 0 7,078 Interest: Debt Finance, capital lease and financing obligations Interest income Interest, net Loss on extinguishment of debt Other (gains) and losses Income before income taxes Goodwill 31,073 31,181 2,262 337 1,975 1,978 Other long-term assets 16,567 14,822 371 352 Total assets 236,495 219,295 (189) (217) (152) Current liabilities: 2,410 2,129 2,178 0 0 3,136 Short-term borrowings Accounts payable 575 5,225 46,973 47,060 (1,958) 8,368 0 Accrued liabilities 22,296 22,159 20,116 11,460 15,123 Accrued income taxes 280 428 Provision for income taxes 4,915 4,281 4,600 Long-term debt due within one year 5,362 1,876 Consolidated net income 15,201 7,179 10,523 Operating lease obligations due within one year 1,793 0 Consolidated net income attributable to noncontrolling interest $ (320) $ (509) Consolidated net income attributable to Walmart Net income per common share: $14,881.00 $6,670.00 $ (661) $9,862.00 Finance lease obligations due within one year 511 0 Capital lease and financing obligations due within one year Total current liabilities 0 729 77,790 77,477 Basic net income per common share attributable to Walmart Diluted net income per common share attributable to Walmart Weighted-average common shares outstanding: Basic (in shares) $5.22 5 $2.28 $3.29 Long-term debt 43,714 43,520 2 3 Long-term operating lease obligations 16,171 0 Long-term finance lease obligations 4,307 0 Diluted (in shares) Dividends declared per common share 2,850.00 $2,868.00 $2.12 2,929.00 $2,945.00 $2.08 Long-term capital lease and financing obligations 0 6,683 12,961 11,981 2,995.00 $3,010 $2.04 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 284 288 3,247 2,965 83,943 80,785 (12,805) (11,542) 74,669 72,496 6,883 7,138 $ 81,552 $79,634 $236,495 $219,295 Consolidated Statements of Cash Flows - USD ($) $ in Millions 12 Months Ended Jan. 31, 2020 Jan. 31, 2019 Jan. 31, 2018 Cash flows from operating activities: Consolidated net income Adjustments to reconcile income from continuing operations to net cash provided by operating activities: Depreciation and amortization $ 15,201 $ 7,179 $ 10,523 10,987 10,678 10,529 Unrealized (gains) and losses (1,886) 3,516 0 (Gains) and losses for disposal of business operations 15 4,850 Asda pension contribution Deferred income taxes Loss on extinguishment of debt (1,036) 0 0 320 (499) (304) 3,136 Other operating activities Changes in certain assets and liabilities, net of effects of acquisitions: 1,981 1,734 1,210 Receivables, net Inventories Accounts payable 154 (368) (1,074) (300) (1,311) (140) (274) 1,831 4,086 Accrued liabilities 186 183 928 Accrued income taxes (93) (40) (557) Net cash provided by operating activities 25,255 27,753 28,337 Cash flows from investing activities: Payments for property and equipment (10,705) (10,344) (10,051) Proceeds from the disposal of property and equipment 321 519 378 Proceeds from the disposal of certain operations 833 876 1,046 Payments for business acquisitions, net of cash acquired (56) (14,656) (375) Other investing activities 479 (431) (77) Net cash used in investing activities (9,128) (24,036) (9,079) Cash flows from financing activities: Net change in short-term borrowings (4,656) (53) 4,148 Proceeds from issuance of long-term debt 5,492 15,872 7,476 Repayments of long-term debt (1,907) (3,784) (13,061) Premiums paid to extinguish debt 0 0 (3,059) Dividends paid (6,048) (6,102) (6,124) Purchase of Company stock (5,717) (7,410) (8,296) Dividends paid to noncontrolling interest (555) (431) (690) Purchase of noncontrolling interest Other financing activities 0 (8) (908) (629) (261) Net cash used in financing activities (14,299) (2,537) (19,875) Effect of Exchange Rate on Cash, Cash Equivalents, and Restricted Cash (69) (438) 487 Net increase (decrease) in cash, cash equivalents and restricted cash 1,759 742 (130) Cash, cash equivalents and restricted cash at beginning of year Cash, cash equivalents and restricted cash at end of year 7,756 7,014 7,144 9,515 7,756 7,014 Supplemental disclosure of cash flow information: Income taxes paid Interest Paid, Excluding Capitalized Interest, Operating Activities 3,616 3,982 6,179 $ 2,464 $2,348 $ 2,450 Amazon Financial Statement Consolidated Statements Of Operations - USD ($) shares in Millions, $ in Millions Total net sales Operating expenses: Cost of sales Fulfillment Technology and content Marketing General and administrative Other operating expense (income), net Total operating expenses Operating income Interest income Interest expense Other income (expense), net Total non-operating income (expense) Income before income taxes Provision for income taxes Equity-method investment activity, net of tax Net income Basic earnings per share Diluted earnings per share Weighted-average shares used in computation of earnings per share: Basic (in shares) Diluted (in shares) Net product sales Total net sales Net service sales Total net sales 12 Months Ended Dec. 31, 2019 $ 280,522 Dec. 31, 2018 $ 232,887 Dec. 31, 2017 $177,866 165,536 139,156 111,934 40,232 34,027 25,249 35,931 28,837 22,620 18,878 13,814 10,069 5,203 4,336 3,674 201 296 265,981 220,466 14,541 832 12,421 440 (1,600) (1,417) (848) 203 (183) 346 (565) (1,160) (300) 13,976 11,261 3,806 (2,374) (1,197) (769) (14) 9 (4) $ 11,588 $ 10,073 $3,033 $23.46 $ 20.68 $6.32 $ 23.01 $ 20.14 $6.15 Current assets: Cash and cash equivalents Marketable securities Consolidated Balance Sheets - USD ($) $ in Millions Dec. 31, 2019 Dec. 31, 2018 $ 36,092 18,929 $31,750 9,500 20,497 17,174 20,816 16,677 96,334 75,101 72,705 61,797 25,141 0 14,754 14,548 16,314 11,202 225,248 162,648 214 173,760 4,106 202 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: 47,183 38,192 32,439 23,663 8,190 6,536 87,812 68,391 39,791 9,650 23,414 23,495 12,171 17,563 494 487 480 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 0 0 5 5 504 500 493 Treasury stock, at cost (1,837) (1,837) Additional paid-in capital 33,658 26,791 $ 160,408 $141,915 $118,573 Accumulated other comprehensive income (loss) (986) (1,035) Retained earnings $31,220 $ 19,625 $ 120,114 $ 90,972 $ 59,293 Total stockholders' equity 62,060 43,549 Total liabilities and stockholders' equity $225,248 $162,648 12 Months Ended Consolidated Statements of Cash Flows - USD ($) $ in Millions Statement of Cash Flows [Abstract] CASH, CASH EQUIVALENTS, AND RESTRICTED CASH, BEGINNING OF PERIOD OPERATING ACTIVITIES: Dec. 31, 2019 Dec. 31, Dec. 31, $32,173 $21,856 $19,934 Net income 11,588 10,073 3,033 Adjustments to reconcile net income to net cash from operating activities: Depreciation and amortization of property and equipment and capitalized content costs, operating lease 21,789 15,341 11,478 Stock-based compensation 6,864 5,418 4,215 Other operating expense (income), net 164 274 202 Other expense (income), net (249) 219 (292) 796 441 (29) (3,278) (1,314) (3,583) (7,681) (4,615) (4,780) 8,193 3,263 7,100 (1,383) 472 283 1,711 1,151 738 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 38,514 30,723 18,365 (16,861) (13,427) (11,955) 4,172 (2,461) 2,104 (2,186) (13,972) 1,897 22,681 8,240 9,677 (31,812) (7,100) (12,731) (24,281) (12,369) (27,084) 2,273 768 16,228 (2,684) (668) (1,301) Principal repayments of finance leases (9,628) (7,449) (4,799) Principal repayments of financing obligations (27) (337) (200) Net cash provided by (used in) financing activities (10,066) (7,686) 9,928 Foreign currency effect on cash, cash equivalents, and restricted cash 70 (351) 713 Net increase (decrease) in cash, cash equivalents, and restricted cash 4,237 10,317 1,922 CASH, CASH EQUIVALENTS, AND RESTRICTED CASH, END OF PERIOD 36,410 32,173 21,856 SUPPLEMENTAL CASH FLOW INFORMATION: Cash paid for interest on long-term debt 875 854 328 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 3,361 381 200 647 39 194 119 881 -1,184 -957 Assets acquired under operating leases 7,870 0 Property and equipment acquired under capital leases 10,615 9,637 Property and equipment acquired under finance leases 13,723 Property and equipment acquired under build-to-suit arrangements $1,362 $3,641 $3,541 Ratios WALMART AMAZON Working Capital Current Assets (15,984) 8,522 Example 61,806 96,334 Current Liabilities 77,790 87,812 Current Ratio Debt to Asset Ratio Return on Equity Return on Assets Inventory Turnover Days in Inventory Accounts Receivable Turnover Times Interest Earned Free Cash Flow Gross Profit Rate Profit Margin Asset Turnover