1. Compute and interpret financial ratios that managers use to assess liquidity. 2. Compute and interpret financial ratios that managers use for asset management purposes 3. Compute and interpret financial ratios that managers use for debt management purposes Selected Financial Ratios - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW De X Colibri - 10 A A Paste BIU- BE- Cells Alignment Number Conditional Format as Cell Formatting" Table Styles Styles Clipboard Font X Recent financial statements for Zimmer, Inc. are as follows: F G This Year 54980.000 SR 1.99 C E 1 Recent financial warments for Zamor, Ing are follow 2 Zimmer Company Zimmer Company 3 Comparative Balance Sheet Income Statement 4 This Year Las Yew 5 Aucts 6 Carreta 7 Cash 5 360.000 5 310.00 Sales (alle $ Marketable securities 220.000 30.000 Cost of de old 9 Acetas civable et 775.000 700.000 Gross margin 10 Inventory 925.000 750,000 Selling and ministrative pe 11 Other current 1956 Operinge 12 Total curre 2.635.000 2035.60 Depreciation and more 13 Plantad quipment 1.975.000 1.NOODOO Geen und ministrative expenses 14 Other 7500 100, Tal ciling and ministrative expe 15 Total A LLIS. Net operating lice 16 Lahalles and Stockholders Wally Instepe 17 Current Lisbilities Netime before tres 18 Acts payable 5 250,000 $225,00 Income taxe 19 1 Shorthank loans 750.00 O Net Income 20 550.000 395.000 Acred payables 21 Other reliabili 275.000 23.400 Additional information 1,825 DOO 1443.400 Dividend pasure paid in 2 sonde payable 105 575.000 LOGO Market price of share at yed 24.RO 1841.46 Purval of common stock per here 985.500 410.00 100 1.545.2009 446 RO 385.CO 135.100 SOLO 5 100 000 20.00 26 1.150.000 1.135.00 25 1.150.200 Nmber of shares standing 01. 2011 Tolkspuity 31 Messi Sheet1 . READY 31 Required: 32 Compute the following ratios for the current year only: 33 Gross margin percentage 34 Current ratio frounded to two decimal places) 35 Acid-test ratio (rounded to two decimal places) 36 Accounts receivable turnover (rounded to two decimal places) 37 Average collection period (rounded to the nearest whole day) 38 Inventory turnover ratio (rounded to two decimal places) 39. Average salos period (rounded to the nearest whole day) 40 Debt-to-equity ratio (rounded to two decimal places) 41 Times interest earned (rounded to two decimal places) 42 Book value por share (rounded to two decimal places) 43 Karting per share (rounded to two decimal places) 44 Dividend payout ratio (rounded to three decimal places) 45 Dividend yield ratio crounded to three decimal places) 46 Price earnings ratio (rounded to two decimal places) 47 48 49 50 Sheet1 READY Mc 1. Use ratios to analyze a company's liquidity and solvency. KU Calculate Select Ratios - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW X Calibri - 11 - A Paste BIU- Alignment Number Cells Conditional Format as Cell Formatting Table Styles Styles Clipboard Font A1 1 X fo Condensed financial statements for Games Galore are summa: B D $ 2016 185.000 $ 80.000 104.000 11.900 380,900 514.500 895.400 $ 2015 143.000 59.000 134,000 5880 341 880 407.000 748,880 $ $ 1 Condensed financial statements for Games Galore are summarized below: 2 3 Balance Sheet 4 Cash 5 Accounts Receivable, Net 6 Inventory 7 Prepaid Insurance 8 Total Current Assets 9 Property and Equipment, Net 10 Total Assets 11 12 Current Liabilities 13 Long-term Liabilities 14 Total Liabilities 15 Contributed Capital 16 Retained Earnings 17 Total Stockholders' Equity 18 Total Liabilities and Stockholders' Equity 19 20 Income Statement 21 Net Sales (all on account 22 Cost of Goods Sold 23 Gross Profit 24 Operating Expenses 25 Income from Operations 26 Interest Expense 27 Income before Income Tax Expense 28 Income Tax Expense 29 Net Income 30 31 Required: Sheet1 85,300 $ 284.000 369 300 299.000 227.100 526,100 895.400$ 96,800 224.000 320.800 299.000 129,080 428.080 748.880 $ $ 3,031.000 1.949.000 1,082,000 891400 190.600 14.500 176,100 47.000 $ 129,100 KB 5 Calculate Select Ratios - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW 86 Calibri -11 ' ' Jole Paste BIU- % Alignment Number Conditional Format as Formatting Table S Styles Clipboard Font A1 X fo Condensed financial statements for Games B 891,400 190.600 14,500 176,100 47.000 129,100 $ 24 Operating Expenses 25 Income from Operations 26 Interest Expense 27 Income before Income Tax Expense 28 Income Tax Expense 29 Net Income 30 31 Required: 32 Compute the following liquidity ratios for 2016. Round to the 33 required number of decimal places USING THE EXCEL. 34 ROUND FUNCTION (fx). 35 Current ratio (rounded to one decimal place) 36 Receivables tumover (rounded to two decimal places) 37 Days to collect (rounded to the nearest whole day) 38 Inventory turnover (rounded to two decimal places) 39 Days' sales in inventory (rounded to the nearest day) to 1 times days times days 40 43 44 to 1 times 41 Compute the following Solvency Ratios for 2016. Again, USE 42 THE EXCEL ROUND FUNCTION (fx). Debt to assets (rounded to two decimal places) Times interest earned (rounded to one decimal place) 45 46 Compute the following Profitability Ratios for 2016. Again, USE 47 THE EXCEL ROUND FUNCTION (fx). Profit margin ratio (rounded to one decimal place) Gross margin ratio (rounded to one decimal place) 50 48 49 percent percent 51 52 53 1. Use vertical analysis to compare profitability. Perform Vertical Analysis - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW IT Calibri - 11 A A Paste BIU- BH - % Alignment Number Conditional Format as Cell Formatting Table Styles Styles Cel Clipboard Font A1 X R Lake Athletics sells two distinct product lines: Apparel B F 4 D E 1 Lake Athletics sells two distinct product lines: Apparel and Equipment 2 The income statement for each product line appears below. 3 LAKE ATHLETICS 5 Income Statement 6 For the Year Ended December 31, 20X1 7 Apparel Equipment 8 Amount Percent Amount Percent 9 Net sales $3.150.000 $5.550,000 10 Cost of goods sold T.400,000 2,720,000 11 Gross profit 1.750.800 2.850.00 12 Operating expenses 800000 87500 13 Operating income 950TURIO 1955TAR 14 Other income 20.000 TOTAKO 15 income before tax 97000 1.965300U 16 Income tax capense 241.250 491.000 17 Net income $728.750 S1474/50 18 19 Required: 20 1. Use appropriate Excel formulas to perform vertical analysis and complete the "Percent" columns 21 for both product lines above. Express each amount as a percentage of net sales. 22 Note: You should use ahiplute references in the divisors of all formulas 23 2. Use the Excel IF function and the completed vertical analysis above to answer each of the 24 questions below: 25 26 Which product line has the highest gross profit %? 27 Which product line has the highest net income? 28 29 30 31