ysis Project-2021 Spring.pdf 1 / 2 114% [ Within the ratio tab, you need to show...

Transcribed Image Text:

ysis Project-2021 Spring.pdf 1 / 2 114% [ Within the ratio tab, you need to show 3 things for each of the ratios: 1. Written formula 2. Detailed numbers for the calculation 3. Result of the calculation When showing all of these items you will receive as many points as possible. For example, if the current ratio is = $10,296 / $8,876 1.16. When the work is submitted in this manner, I can see where you may have made an error, but still provide some points. An answer showing of just 1.24, for example, you receive no points (it's incorrect and I can't see what numbers you used). An answer of $11,006/ $8,876 = 1.24 would receive partial credit (the denominator is correct, but an incorrect numerator was used). Calculations to Perform (on the most recent year): 1. Prepare a horizontal (trend) analysis for the balance sheet - 1 point 2. Prepare a horizontal (trend) analysis for the income statement - 1 point 3. Prepare a vertical (common size) analysis for the balance sheet - 1 point 4. Prepare a vertical (common size) analysis for the income statement - 1 point 5. Net profit margin - 2 points Page 112 File Home Insert Draw Page Layout Formulas Data Review View Help ChemOffice19 Tell me what you want Cut Calibri 11 Copy Paste BIU. Format Painter A A A Wrap Text General Merge & Center- - %' Clipboard Font Alignment Number 000 Conditional Format as Cell 000 Formatting Table Styles Styles N4 A B D E F G H 20 Income tax provision (benefit) 265 21 Net income 3,630 22 Net income attributable to non (24) 23 Net income attributable to Me 24 Basic earnings per share 25 Diluted earnings per share SSS $ 3,606 $ 2.68 $ 2.66 26 Basic weighted average shares 27 Diluted weighted average shar 1,344.90 1,354.00 28 29 30 31 32 33 34 35 36 37 38 Ready consolidated balance sheets income Ratios K (751) 4,806 (17) SSS $ 4,789 $ 3.57 $ 3.54 1,340.70 1,351.10 Format Painter Clipboard N4 A B C Font 2 Consolidated Statements of Income 3 4 Alignment D E F G H 5 6 (in millions, except per share di 2021 7 Net sales 8 Costs and expenses: 9 Cost of products sold 10 Research and development exp 11 Selling, general, and administra 12 Amortization of intangible asset 13 Restructuring charges, net $ Fiscal Year 30,117 10,483 2,493 10,148 1,783 293 14 Certain litigation charges 118 15 Other operating expense, net 315 16 Operating profit 4,484 17 Other non-operating income, ne (336) 925 18 Interest expense 19 Income before income taxes 20 Income tax provision (benefit) consolidated balance sheets Ready Type here to search 3,895 265 income Ratios O Number Formatting Table Styles Styles 2020 $ 28,913 9,424 2,331 10,109 1,756 118 313 71 4,791 (356) 1,092 4,055 (751) Template FS Project - Excel Fe Home Insert Dran Page Layout Formul Data Review View Help ChemOffice19 Tell me what you want to do Cut Calbri AA Wrap Text General Copy Paste B/U- A. Merge & Center $ % Format Painter Clipboard Font Alignment Conditional Format as Call Formatting Insert De Number Table Styles Styles P14 C D E F G H 51,428 40 Total shareholders' equity 41 Noncontrolling interests 42 Total equity 43 Total liabilities and equity 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 Ready consolidated balance sheets Type here to search $ income Ratios O 174 51,602 93,083 M N 50,737 135 50,872 $ 90,689 5F Fle Home Insert Page Layout Draw Formulas Data Review View Help ChemOffice19 Tell me what you want to do M Cut Copy Calibri 11 AA Wrap Text General Paste Format Painter BIUS A Merge & Center $ % 00 4.0 + Conditional Format as Cell Formatting Table Styles Insert De Clyboed Font Alignment Number Styles P14 X fi B C D E F G H M N 20 LIABILITIES AND EQUITY 21 Current liabilities: 22 Current debt obligations 23 Accounts payable 24 Accrued compensation 25 Accrued income taxes i 26 Other accrued expenses 27 Total current liabilities $ 11 $ 2,776 2,106 1,996 2,482 2,099 435 502 3,475 2,993 8,509 10,366 28 Long-term debt 26,378 22,021 29 Accrued compensation and retirement benefits 1,557 1,910 30 Accrued income taxes 2,251 2,682 31 Deferred tax liabilities 1,028 1,174 32 Other liabilities 1,756 1,664 33 Total liabilities 41,481 39,817 34 Commitments and contingencies (Notes 3, 16, and 18) 35 Shareholders' equity: 36 Ordinary shares-par value $0.0001, 2.6 billion shares authoriz 37 Additional paid-in capital 38 Retained earnings 39 Accumulated other comprehensive loss consolidated balance sheets income Ratios Ready Type here to search O TY 26,319 28,594 (3,485) 5F 26,165 28,132 (3,560) P14 Cipboard A B C 1 2 Consolidated Balance Sheets Font fe D E Alignment G H Number Styles J K M 3 4 5 (in millions) April 30, 2021 April 24, 2020 6 ASSETS 7 Current assets: 8 Cash and cash equivalents $ 3,593 $ 4,140 9 Investments 7,224 6,808 10 Accounts receivable, less allowances and credit losses of $241 a 5,462 4,645 11 Inventories, net 4,313 4,229 12 Other current assets 1,955 2,209 13 Total current assets 22,548 22,031 14 Property, plant, and equipment, net 5,221 4,828 15 Goodwill 41,961 39,841 16 Other intangible assets, net 17 Tax assets 18 Other assets 17,740 3,169 2,443 19,063 2,832 2,094 19 Total assets $ 93,083 20 LIABILITIES AND EQUITY consolidated balance sheets income Ratios $ 90,689 Ready Type here to search E 5F ysis Project-2021 Spring.pdf 1 / 2 114% [ Within the ratio tab, you need to show 3 things for each of the ratios: 1. Written formula 2. Detailed numbers for the calculation 3. Result of the calculation When showing all of these items you will receive as many points as possible. For example, if the current ratio is = $10,296 / $8,876 1.16. When the work is submitted in this manner, I can see where you may have made an error, but still provide some points. An answer showing of just 1.24, for example, you receive no points (it's incorrect and I can't see what numbers you used). An answer of $11,006/ $8,876 = 1.24 would receive partial credit (the denominator is correct, but an incorrect numerator was used). Calculations to Perform (on the most recent year): 1. Prepare a horizontal (trend) analysis for the balance sheet - 1 point 2. Prepare a horizontal (trend) analysis for the income statement - 1 point 3. Prepare a vertical (common size) analysis for the balance sheet - 1 point 4. Prepare a vertical (common size) analysis for the income statement - 1 point 5. Net profit margin - 2 points Page 112 File Home Insert Draw Page Layout Formulas Data Review View Help ChemOffice19 Tell me what you want Cut Calibri 11 Copy Paste BIU. Format Painter A A A Wrap Text General Merge & Center- - %' Clipboard Font Alignment Number 000 Conditional Format as Cell 000 Formatting Table Styles Styles N4 A B D E F G H 20 Income tax provision (benefit) 265 21 Net income 3,630 22 Net income attributable to non (24) 23 Net income attributable to Me 24 Basic earnings per share 25 Diluted earnings per share SSS $ 3,606 $ 2.68 $ 2.66 26 Basic weighted average shares 27 Diluted weighted average shar 1,344.90 1,354.00 28 29 30 31 32 33 34 35 36 37 38 Ready consolidated balance sheets income Ratios K (751) 4,806 (17) SSS $ 4,789 $ 3.57 $ 3.54 1,340.70 1,351.10 Format Painter Clipboard N4 A B C Font 2 Consolidated Statements of Income 3 4 Alignment D E F G H 5 6 (in millions, except per share di 2021 7 Net sales 8 Costs and expenses: 9 Cost of products sold 10 Research and development exp 11 Selling, general, and administra 12 Amortization of intangible asset 13 Restructuring charges, net $ Fiscal Year 30,117 10,483 2,493 10,148 1,783 293 14 Certain litigation charges 118 15 Other operating expense, net 315 16 Operating profit 4,484 17 Other non-operating income, ne (336) 925 18 Interest expense 19 Income before income taxes 20 Income tax provision (benefit) consolidated balance sheets Ready Type here to search 3,895 265 income Ratios O Number Formatting Table Styles Styles 2020 $ 28,913 9,424 2,331 10,109 1,756 118 313 71 4,791 (356) 1,092 4,055 (751) Template FS Project - Excel Fe Home Insert Dran Page Layout Formul Data Review View Help ChemOffice19 Tell me what you want to do Cut Calbri AA Wrap Text General Copy Paste B/U- A. Merge & Center $ % Format Painter Clipboard Font Alignment Conditional Format as Call Formatting Insert De Number Table Styles Styles P14 C D E F G H 51,428 40 Total shareholders' equity 41 Noncontrolling interests 42 Total equity 43 Total liabilities and equity 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 Ready consolidated balance sheets Type here to search $ income Ratios O 174 51,602 93,083 M N 50,737 135 50,872 $ 90,689 5F Fle Home Insert Page Layout Draw Formulas Data Review View Help ChemOffice19 Tell me what you want to do M Cut Copy Calibri 11 AA Wrap Text General Paste Format Painter BIUS A Merge & Center $ % 00 4.0 + Conditional Format as Cell Formatting Table Styles Insert De Clyboed Font Alignment Number Styles P14 X fi B C D E F G H M N 20 LIABILITIES AND EQUITY 21 Current liabilities: 22 Current debt obligations 23 Accounts payable 24 Accrued compensation 25 Accrued income taxes i 26 Other accrued expenses 27 Total current liabilities $ 11 $ 2,776 2,106 1,996 2,482 2,099 435 502 3,475 2,993 8,509 10,366 28 Long-term debt 26,378 22,021 29 Accrued compensation and retirement benefits 1,557 1,910 30 Accrued income taxes 2,251 2,682 31 Deferred tax liabilities 1,028 1,174 32 Other liabilities 1,756 1,664 33 Total liabilities 41,481 39,817 34 Commitments and contingencies (Notes 3, 16, and 18) 35 Shareholders' equity: 36 Ordinary shares-par value $0.0001, 2.6 billion shares authoriz 37 Additional paid-in capital 38 Retained earnings 39 Accumulated other comprehensive loss consolidated balance sheets income Ratios Ready Type here to search O TY 26,319 28,594 (3,485) 5F 26,165 28,132 (3,560) P14 Cipboard A B C 1 2 Consolidated Balance Sheets Font fe D E Alignment G H Number Styles J K M 3 4 5 (in millions) April 30, 2021 April 24, 2020 6 ASSETS 7 Current assets: 8 Cash and cash equivalents $ 3,593 $ 4,140 9 Investments 7,224 6,808 10 Accounts receivable, less allowances and credit losses of $241 a 5,462 4,645 11 Inventories, net 4,313 4,229 12 Other current assets 1,955 2,209 13 Total current assets 22,548 22,031 14 Property, plant, and equipment, net 5,221 4,828 15 Goodwill 41,961 39,841 16 Other intangible assets, net 17 Tax assets 18 Other assets 17,740 3,169 2,443 19,063 2,832 2,094 19 Total assets $ 93,083 20 LIABILITIES AND EQUITY consolidated balance sheets income Ratios $ 90,689 Ready Type here to search E 5F