I just want to know what you need because this is the full question or maybe be you can give me similar solutions you have thank you.

Financial Statement Analysis case O @ E Attached Files: AC603- Financial statement analysis ratios case study.xlsx (22.444 KB) Attached is an excel file with both Pepsi's Balance sheet and income statement (tab 2) and Coca-Cola's Balance sheet and Income Statement (tab 3). In tab 1 are the requirements of this case 1) In tab 1 are the ratios (with excel formulas) calculated for Pepsi-Co, Inc. for you - all you need to do is calculate the Coca-Cola Company ratios and then answer the questions below. 1. Using the financial data for the three years for The Coca-Cola Company discuss the company's: a. Ability to pay current liabilities; b. Ability to sell inventory and collect receivables; c. Ability to pay long-term debt; and d. Profitability 2. Using the financial data for the three years for PepsiCo, Inc., discuss the company's a. Ability to pay current liabilities; b. Ability to sell inventory and collect receivables; c. Ability to pay long-term debt; and d. Profitability. 3. Compare Coca-Cola's financial position to PepsiCo's financial position throughout the three years given. How do the two companies compare in the following areas? a. Ability to pay current liabilities; b. Ability to sell inventory and collect receivables; c. Ability to pay long-term debt; and d. Profitability 4. What conclusions can you draw from your analysis of the two companies? Which company do you think is in a stronger financial position? X1 + W FLE HOME INSERT PAGE LAYOUT AC603- Financial statement analysis ratios case study(1) (2) - Microsoft Excel FORMULAS DATA REVIEW VIEW H2S fir 0.86 B D E F G H Below are ve ratios (with excel formulas) calculated for Pepsi-Co, Inc. for you - all you need to do is calculate the Coca-Cola Company ratios 2 Pepsi-Co, Inc. The Coca-Cola Company 3 Selected Financial Data Selected Financial Data 4 For years ended December 31 For years ended December 31 5 2020 2019 2018 2020 2019 2018 6 Acid-test ratio (CA-inventery-prepaid)/CL 0.77 0.66 0.82 Current ratio 0.98 0.99 18 Debt ratio 0.85 0.81 0.81 9 Earnings per she 5.14 5.23 8.84 10 Inventory turnover 8.47 9.32 9.39 11 Net income ($ in millions) $ 7,120 12 Rate of return on jassets $7,314 $ 12,515 10.97% 13 Receivables turnover 13.05% 13.40% 8.67 14 Return on assets 8.98 9.05 15 Operating income percentage 9.34% 10.31% 17.69% 16 Revenues ($ in millions) 14.32% 15.32% 15.64% 17 Times interest earned ratio $ 70,372 $ 67,161 $ 64,661 18 Working capital ($ in millions) 9.04 10.96 8.54 19 (371) (2,816) (245) 20 21 Notes: 22 Debt ratio = TL/TA 23 Inv. TO=CGS/Avg. Inv. 24 Rate of Return on Assets EBIT/TA 25 Acciounts receivable TO-SALES/Avg. AR 26 Return on Assets=NI + {Interest Expense * (1-Tax)} / Avg TA 27 Operating Income%=operatinig income /SALES REVENUE 28 TIE Ratio=EBIT/Interest Exp. 29 WC-CA-CL 30 Inventory Turnover, Receivables Turnover, Return on Assets figures for 2018 use ONLY 2018 values in the denominator as we don't have 2017 numbers to average 31 32 Requirements 33 1. Using the financial data for the three years for The Coca-Cola Company discuss the company's: 34 35_a. Ability to pay current liabilities: Ratio calculations Pepsi balancoch READY 63 AC603- Financial statement analysis ratios case study(1) (2) - Microsoft Exc FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW X V fx Consolidated Balance Sheet - USD ($) shares in Millions, $ in Millions E D Dec. 28, 2019 Dec. 29, 2018 3 $ 5,509 229 0 0 A B Consolidated Balance Sheet - USD ($) shares in Millions, $ in Millions Dec. 26, 2020 2 ASSETS 3 Cash and cash equivalents $ 8,185 4 Short-term Investments 1,366 5 Restricted Cash 0 0 6 Accounts and notes receivable, net 8,404 7 Inventories 4,172 8 Prepaid expenses and other current assets 874 Total current a ats 23,001 10 Property, Plant and Equipment, net 21,369 11 Ansortizable Intangible Assets, net 1,703 12 Goodwill 18,757 13 Other Indefinite-lived Intangible Assets 17,612 14 Indefinite-Lived Intangible Assets 36,369 15 Investments in Noncontrolled Affiliates 2,792 16 Deferred Income Taxes 4,372 17 Other Assets 3,312 18 Total Assets 92,918 19 LIABILITIES AND EQUITY 20 Short-term debt obligations 3,780 21 Accounts payable and other current liabilities 19,592 22 Total Current Liabiities 23,372 23 Long-Term Debt Obligations 40,370 24 Deferred income Taxes 4,284 25 Other Liabilities 11,340 26 Total Liabilities 79,366 27 28 PepsiCo Common Shareholders' Equity Common stock, par value 12/30 per share (authorized 3,600 shares, issued, net of repurchased $ 23 29 common stock at par value: 1,380 and 1,391 shares, respectively) 30 Additional Paid in Capital, Common Stock $ 3,910 31 Retained Earnings (Accumulated Deficit) 63,443 32 Accumulated Other Comprehensive Income (Loss), Net of Tax $ (15,476) 33 Treasury Stock, Value (38,446) 34 TOTAL PepsiCo common shareholders' equity $ 13.454 Ratio calculations Pepsi balance sheet & income CocaCola balance sheet & Income + READY 7,822 3,338 747 17,645 19,305 1,433 15,501 14,610 30,111 2,683 4,359 3,011 78,547 $8,721 272 1,997 7,142 3,128 633 21,893 17,589 1,644 14,808 14,181 28,989 2,409 4,364 760 77,648 2,920 17,541 20,461 29,148 4,091 9,979 63,679 4,026 18,112 22,138 28,295 3,499 9,114 63,046 $ 23 $ 3,886 61,946 $(14,300) (36,769) $ 14.786 23 3,953 59,947 $ (15,119) (34,286) $ 14.518 2 FILE HOME INSERT PAGE LAYOUT AC603- Financial statement analysis ratios case study(1) (2) - Microso FORMULAS DATA REVIEW VIEW IX fx Consolidated Balance Sheet - USD ($) shares in Millions, $ in Millions B D E 79,366 63,679 3 63,046 $ 23 A 26 Total Liables 27 28 PepsiCo Common Shareholders' Equity Common stock, par value 12/30 per share (authorized 3,600 shares, issued, net of repurchased 29 common stock at par value: 1,380 and 1,391 shares, respectively) 30 Additional Paid in Capital, Common Stock 31 Retained Earnings (Accumulated Deficit) B2: Accumulated Other Comprehensive Income (Loss), Net of Tax 33 Treasury Stock salue 34 NTOTAL PepsiCo common shareholders' equity 35 Noncontrolling interests 36 Total equity 37 Total Habilities and Equity 38 $ 23 23 $ 3,910 63,443 $ (15,476) (38,446) $ 13,454 98 $ 13,552 92,918 $ 3,886 61,946 $ (14,300) (36,769) $ 14,786 82 $ 14,868 78,547 3,953 59,947 $ (15,119) (34,286) $ 14,518 84 $ 14,602 77,648 39 40 41 42 43 44 45 FORMULAS DATA statement analysis ratios case study(1) (2) - Microsoft Excel REVIEW VIEW X Consolidated Balance Sheet - USD ($) shares in Millions, $ in Millions K L M Dec. 29, 2018 G . 1 Consolidated Statement of Income - USD ($) shares in Millions, $ in Mil 12 Months Ended Dec. 26, 2020 Dec. 28, 2019 Income Statement [Abstract] Net Revenue 70,372 67,161 Cost of sales 31,797 30,132 Gross profit 38,575 37,029 Selling, general and administrative expenses 28,495 26,738 Operating Profit 10,080 10,291 Other pension and retiree medical benefits income/(expense) 117 (44) Net interest expense and other (1,128) (935) Income before income taxes 9,069 9,312 Provision for/(benefit from) income taxes (See Note 5) 1,894 1,959 Net income 7,175 7,353 Less: Net income attributable to noncontrolling interests 55 39 Net Income Attributable to PepsiCo 7,120 7,314 Net Income Attributable to PepsiCo per Common Share Basic 5.14 Diluted 5.23 5.12 5.20 Weighted average common shares outstanding Basic Diluted 1,385 1,399 NOTES: 1,392 1,407 64,661 29,381 35,280 25,170 10,110 298 (1,219) 9,189 (3,370) 12,559 44 12,515 8.84 8.78 Tax rate/Provision for Income Taxes/ Income before taxes 1,415 1,425 0.2088 0.2104 0 AC603- Financial statement analysis ratios case study(1) (2) - Microsoft Exc HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW X fir =H8+H9 H10+H11+H12 E Dec. 31, 2019 D Dec. 31, 2018 A B Dec. 31, 2020 CURRENT ASSETS Cash and cash equivalents $ 6,795 Short-term investments 1,771 TOTAL CASH, CASH EQUIVALENTS AND SHORT-TERM INVESTMENTS 8,566 Marketable securities 2,348 Trade accounts receivable, less allowances of $524 and $501, respectively 3,144 3 Inventories 3,266 9 Prepaid expens and other assets 1,916 10 TOTAL CURREN) ASSETS 19,240 71 EQUITY METHOO, NVESTMENTS 19,273 72 OTHER INVESTMENTS 812 13 OTHER ASSETS 6,184 14 Deferred Tax Assets, Net, Noncurrent 2,460 15 PROPERTY, PLANT AND EQUIPMENT - net 10,777 16 TRADEMARKS WITH INDEFINITE LIVES 10,395 17 BOTTLERS' FRANCHISE RIGHTS WITH INDEFINITE LIVES 0 18 GOODWILL 17,506 19 OTHER INTANGIBLE ASSETS 649 20 TOTAL ASSETS 87,296 21 CURRENT LIABILITIES 22 Accounts payable and accrued expenses 11,145 23 Notes and Loans Payable, Current 2,183 24 Current maturities of long-term debt 485 25 Accrued income taxes 788 26 TOTAL CURRENT LIABILITIES 14,601 27 LONG-TERM DEBT 40,125 28 OTHER LIABILITIES 9,453 29 DEFERRED INCOME TAXES 30 THE COCA-COLA COMPANY SHAREOWNERS' EQUITY 1,833 31 Common stock, $0.25 par value; authorized 11,200 shares; issued 7,040 shares 32 Capital surplus 1,760 17,601 33 Reinvested earnings 66,555 34 Accumulated other comprehensive income (loss) 35 Treasury stock, at cost - 2.760 and 2.772 shares, respectively (14,601) Ratio calculations Pepsi balance sheet & income CocaCola balance sheet & Income (52.016) READY $ 6,480 1,467 7,947 3,228 3,971 3,379 1,886 20,411 19,025 854 6,075 2,412 10,838 9,266 109 16,764 627 86,381 $9,077 2,025 11,102 5,013 3,685 3,071 2,059 24,930 19,412 867 4,148 2,674 9,598 6,682 51 14,109 745 83,216 11,312 10,994 4,253 414 26,973 27,516 8,510 2,284 9,533 13,835 5,003 411 28,782 25,376 7,646 2,354 1,760 17,154 65,855 (13,544) (52.244) 1,760 16,520 63,234 (12,814) (51.719) ALE HOME INSERT PAGE LAYOUT AC603- Financial statement analysis ratios case study(1) (2) - Microsoft Excel FORMULAS DATA REVIEW VIEW His X =H8+H9-H10+H11+H12 A B. C D E F 14,601 40,125 9,453 1,833 26,973 27,516 8,510 2,284 28,782 25,376 7,646 2,354 25 TOTAL CUENT LIABILITIES 27 LONG-TERM DEBT 28 QTHER LIABILITIES 29 DEFERRED INCOME TAXES 30 THE COCA-COLA COMPANY SHAREOWNERS' EQUITY 31 Common stock, $0.25 par value; authorized 11,200 shares; issued 7,040 shares 32 Capital surplus B3 Reinvested earnings SA Accumulated offer comprehensive income (loss) 35 Treasury stock at cost - 2,760 and 2,772 shares, respectively 36 EQUITY ATTRIBE ABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY 37. EQUITY ATTRIBU ABLE TO NONCONTROLLING INTERESTS 38 TOTAL EQUITY 39 TOTAL LIABILITIES AND EQUITY 40 41 42 1,760 17,601 66,555 (14,601) (52,016) 19,299 1,985 $ 21,284 $87,296 1,760 17,154 65,855 (13,544) (52,244) 18,981 2,117 $ 21,098 $ 86,381 1,760 16,520 63,234 (12,814) (51,719) 16,981 2,077 $ 19,058 $ 83,216 43 44 45 PAGE LAYOUT FORMULAS AC603- Financial statement analysis ratios case study(1) (2) - Microsoft Excel DATA REVIEW VIEW : X E F G CONSOLIDATED STATEMENTS OF INCOME - USD ($) shares in Millions, $ in Millions H L M . Revenues Cost of Goods and Services Sold GROSS PROFIT Selling, General and Administrative Expense Other Cost and Expense, Operating OPERATING INCOME Interest income Interest expense Equity income (loss) - net Other income (loss) - net INCOME BEFORE INCOME TAXES Income taxes CONSOLIDATED NET INCOME Net Income (Loss) Attributable to Noncontrolling Interest NET INCOME ATTRIBUTABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY Dec. 31, 2020 Dec. 31, 2019 Dec. 31, 2018 33,014 37,266 34,300 13,433 14,619 13,067 19,581 22,647 21,233 9,731 12,103 11,002 853 458 1,079 8,997 10,086 9,152 370 563 689 1,437 946 950 978 1,049 1,008 841 34 (1,674) 9,749 10,786 8,225 1,981 1,801 1,749 7,768 8,985 6,476 21 65 42 7,747 8,920 6,434 BASIC NET INCOME PER SHARE (in dollars per share) DILUTED NET INCOME PER SHARE (in dollars per share) AVERAGE SHARES OUTSTANDING (in shares) Effect of dilutive securities (in shares) AVERAGE SHARES OUTSTANDING ASSUMING DILUTION (in shares) 1.8 1.51 1.79 2.09 2.07 4276 4295 1.5 4259 28 38 4314 40 4323 NOTES: Tax rate= Provision for Income Taxes/ Income before taxes 4299 0.2032 0.1670 0.2126