case study.

all ratio calculations must be done using excel formula. please include the formula.

Arial Wrap Text Accounting A AY UPO Paste B I Merge & Center $ % 2 0 .00 00 . Cell Conditional Format Formatting as Table Styles E30 4 xfx =+H11/1.11 , A D E F G H 1 J K L M 1 2 3 4 5 6 7 8 9 10 11 12 Statement of Comprehensive Income For the year ended Dec 31, 20xx ($ in millions) Sales $ 33,693 COGS 24,773 Gross profit $ 8,920 SG&A expenses 5,615 Depreciation 1,293 EBIT Interest 230 EBT 1,782 Taxes 63 Net income $ $ 1,719 Assets Current assets Cash Accounts receivable Inventory Total Statement of Financial Position As at Dec 31, 20XX ($ in millions) Liabilities and Owner's Equity Current liabilities $ 137,424 Accounts payable $ 17,300 25,176 Notes payable 116,918 13,912 Other 9,596 $ 176,512 Total $ 143,814 2,012 Long-term debt $ 131,428 13 14 15 Fixed assets $ 116,918 Total equity $ 18,188 16 17 18 Total liabilities and owner's equity Total assets S 293,430 $ 293,430 19 20 21 Shares outstanding 1,054 (in millions) 22 Market Price per share: $102.00 23 Instruction: 24 Under the Formula column (column C), put in the formula as specified in the textbook for each ratio 25 Under the Calculation column (column E), use Excel formula to do the calculation according to the formula. Any typed-in numbers will not receive mark 26 Please see the example provided in Current Ratio. Each answer must specify "times" or %, etc. Missing description will miss marks 27 28 Formula: Calculation: 29 Tinuidity Ratine Case 1 + la Select destination and press ENTER or choose Paste % Arial v 12 A- A Wrap Text Accounting Paste B 1 UPDA- E E Merge & Center $ $ - % % ) 2.9 .00 .00 Conditional Format Formatting as Table Ce Styl E30 X fc =+H11/L11 D E F G H I J K L A B 45 46 Solvency Ratios 47 48 Total Debt Ratio 49 50 51 Debt-equity ratio: 52 53 54 Equity multiplier: 55 56 57 Long-term debt ratio: 58 59 60 Times interest earned: 61 62 63 Cash coverage: 64 65 66 Assets Turnovr Ratios 67 68 Inventory turnover: 69 70 71 Days' sales in inventory: 72 73 Case 1 + Seleot destination and press ENTER or choose Paste H i Case 1 Assignment (4) (1) Home Insert Page Layout Formulas Data Review View Tell me Arial 12 A A Wrap Text Paste B Accounting $ %) I U- E E Merge & Center +0 .00 .00 Conditional Format Formatting as Table Cell Styles E30 1 x fx =+H11/L11 M A B D E F G H 1 J K L 23 Instruction: 24 Under the Formula column (column C), put in the formula as specified in the textbook for each ratio 25 Under the Calculation column (column E), use Excel formula to do the calculation according to the formula. Any typed-in numbers will not receive mark 26 Please see the example provided in Current Ratio. Each answer must specify "times" or %, etc. Missing description will miss marks 27 28 Formula: Calculation: 29 Liquidity Ratios 30 Current Assets $ 1.23 times 31 Current Ratio: Current Liabilities 32 33 34 Quick Ratio: 35 36 37 Cash Ratio: 38 39 40 Net working capital ratio: 41 42 43 Interval Measure 44 45 46 Solvency Ratios 47 48 Total Debt Ratio 49 50 51 Debt-equity ratio: Case 1 + Select destination and press ENTER or chaOSA Paste Case 1 Assignment (4) (1) Home Formulas Data Review View Insert Page Layout X Arial 12 L B I ... A- A Wrap Text Accounting Paste A AY Merge & Center $ - % %) 4.0 00 .00 0 Conditional Format Formatting as Table E30 A Xfx =+H11/L11 D E F G H 1 J K L A B 91 92 Profit margin: 93 94 95 Return on assets (ROA): 96 97 98 Return on equity (ROE): 99 100 101 Du Point Identity (ROE): 102 103 104 105 Earnings per share (EPS): 106 107 108 Price-earnings ratio (PE): 109 110 111 Book Value per share: 112 113 114 Market-to-Book 115 116 117 118 119 Case 1 Assignment (4) (1) Home Insert Page Layout Formulas Data Review View 8 Tell me Arial 12 A A Wrap Text Accounting Paste B 1 U - A .00 E E Merge & Center $ % ) .00 Cell Conditional Format Formatting as Table Styles 1 E30 X7 f =+H11/L11 D E F G H 1 K L M A B 65 66 Assets Turnovr Ratios 67 68 Inventory turnover: 69 70 71 Days' sales in inventory: 72 73 74 Receivables turnover: 75 76 77 78 Days' sales in receivables: 79 80 81 NWC turnover: 82 83 84 Fixed asset turnover: 85 86 87 Total asset turnover: 88 89 90 91 92 Profit margin: 93 Case 1 + Select destination and press ENTER or choose Paste