Question

1. Use ratios to analyze a company's liquidity and solvency Calculate Select Ratios Excel 2 points FILE HOME INSERTP DATA PAGE LAYOUT FORMULAS REVIEW VIEW 6Calibri ED A Alignment Number Conditional Format as Cel l Editing Paste B 1 u· B-2-. Clipboard A1 N Formatting Table Styles eBook Styles Font VXfCondensed financial statements for Games Galore are summarized below: Ask 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 2016 2015 $185,000 $143,000 9,000 134,000 5,880 341,880 407,000 $895,400748,880 80,000 104,000 11,900 380,900 514,500 12 Current Liabilities 13 Long-term Liabilities 14 Total Liabilities 15 Contributed Capital 16 Retained Earnings 17 Total Stockholders' Equit 18 Total Liabilities and Stockholders' Equity 19 20 Income Statement 21 Net Sales (al on account) 22 Cost of Goods Sold 23 Gross Pr 24 Operating Expenses 25 Income from Operations 26 Interest Exnensp $85,300 96,800 224,000 320,800 299,000 129,080 428,080 $ 895,400 $ 748,880 284,000 369,300 299,000 227,100 526,100 3,031,000 1,949,000 1,082,000 891,400 190,600 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 turnover (rounded to two decimal places) 37 Days to collect (rounded to the nearest whole day) 38 nventory turnover (rounded to two decimal places) 39 Days' sales in inventory (rounded to the nearest day) 40 41 Compute the following Solvency Ratios for 2016. Again, USE 42 THE EXCEL ROUND FUNCTION (fx) 43Debt to assets (rounded to two decimal places) 44Times interest earned (rounded to one decimal place) 176,100 47,000 $129,100 to 1 times days times days to 1 times 46 Compute the following Profitability Ratios for 2016. Again, USE 47 THE EXCEL ROUND FUNCTION (fx) 48 Profit margin ratio (rounded to one decimal place) 49Gross margin ratio (rounded to one decimal place) 50 percent percent Sheet1 1. Use vertical analysis to compare profitability Perform Vertical Analysis - Excel HOME INSERT PAGE LAYOUTFORMULAS DATA REVIEWVIEW Calibri 驛H AAlignment Number Conditional Format as Cell Cells Editing Paste Formatting Table" Styles Clipboard Font es C9 LAKE ATHLETICS Income Statement For the Year Ended December 31, 20X1 4 Apparel Equipment Amount Percent Amount Percent 9 Net sales 10 Cost of goods sold 11 Gross profit 12 Operating expenses 13 Operating income 14 Other income 15 Income before tax 16 Income tax expense 17 Net income 18 19 Required: 201. 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 absolute 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 3,150,000 1,400,000 1,750,000 800,000 950,000 20,000 970,000 241,250 728,750 5,550,000 2,720,000 2,830,000 875,000 1,955,000 10,000 1,965,000 491,000 1,474,750 which product line has the highest gross profit %? which product line has the highest net income %? Sheet1

Answer 1

As per rules I am answering the first 4 subparts of the question

1: Current ratio = current assets/Current liabilities

= 380900/85300

=4.5

(Excel formula =ROUND(B8/B12,1))

2: Receivables turnover = Sales / Average receivables

=3031000/ ((80000+59000)/2)

= 43.61

(Excel formula = ROUND( B21/((B6+C6)/2),2)

3: Days to collect = 365/Receivables turnover = 365/43.61 =8 days

(Excel formula= ROUND(365/B36),0)

4: Inventory turnover= Cost of goods sold/ Average inventory

= 1949000/ ((104000+134000)/2)

=16.38

(Excel formula= ROUND(B22/ ((B6+C6)/2),2)