Question

You will use this Excel workbook with two worksheets to assist you in completing this part of the assignment. The first worksheet is the Balance Sheet and Income Statement for ABC Company. You will use the information to complete the assignment. The second worksheet contains a breakdown of categories of ratios where you will complete the calculations for each ratio. You will also enter the formula written out as to what information you have used to do the calculations. You are going to use the five classifications of financial ratios to assess the financial performance of ABC Company.

Please use the Income Statement and Balance Sheet, provide the calculation of the liquidity, activity, financing, market, and profitability ratios with the sub categories under each. Along with the proper assessment of outcomes with positive or negative trends when the ratio outcomes are factored as a group for the liquidity, activity, financing, market, and profitability ratios.

The excel being asked are the above screenshots. With the above Income statement and Balance sheet, the third screenshot needs to be filled out which is the excel sheet.

Answer 1

A B C D E F G H ! J 1 ABC Company Income Statement 2015 2014 2013 2 3 4 5 6 7 8 9 10 Period Ending Total Sales Cost of Goods Sold Gross Profit Selling General & Administrative Operating Profit Total Other Income!Expenses Net Earnings before Interest and Taxes Interest Expense Income before tax Income tax expense Net Income from Continuing Ops Discontinued Operations Net Income (Net Profit) 14,000,000 shares Outstanding Market Share Price per share 31-Dec-15 48,56,51,000 36,50,86,000 12,05,65,000 9.34.18.000 2,71.47.000 1.13.000 2,70,34,000 24.61.000 2,45,73,000 79,85,000 1,65,88,000 2.85.000 1,63,03,000 31-Dec-14 47,62.94.000 35,80,69,000 11,82,25,000 9.13.53,000 2,68.72.000 1,19,000 2,67,53,000 23,35,000 2,44,18,000 81,05,000 1,63,13,000 1,44,000 1,61,69,000 31-Dec-13 47.52.10.000 35,04,00,000 12,48,10,000 9.03 43,000 3,44,67,000 1,15,000 3,43,52,000 22,00,000 3,21,52,000 98,00,000 2,23,52,000 1,82,000 2,21,70,000 ABC Company Balance Sheet Period Ending Assets Current Assets Cash and Cash Equivalents Net Receivable Inventory Other Current Assets Total Current Assets PPE Goodwill Other Assets Total Assets 91,35,000 67,78,000 4,51,41,000 22,24,000 6,32,78,000 11,66,55,000 1,81,02,000 56,71,000 20,37,06,000 72,81,000 66,77,000 4.48.58.000 23.69,000 6,11,85,000 11,79,07,000 1.95 10,000 6149,000 20,47,51,000 67,89,000 65,25,000 4,39,89,000 21,99,000 5,95,02,000 12,03,00,000 1,79,00,000 45,00,000 20,22,02,000 11 12 13 14 15 16 17 5,85,83,000 10 9 9 Current Liabilities Accounts Payable Other Current Liabilities Short-term debt Total Current Liabilities Long-term debt Deferred Long-term Liability charges Monority Interest Total Liabilities 66,89,000 6,52,72.000 4,36,92,000 88,05,000 45,43,000 12,23,12,000 5,71,74,000 89,000 1,20,82,000 6.93.45,000 4,45,59,000 80,17,000 50.84.000 12.70.05,000 5,62, 10,000 55,000 1,40,50,000 7.03.15.000 4,53,24,000 1,35,53,000 68,75,000 13,60,67,000 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Miscellaneous Stock options warrants Common stock Retained Earnings Capital Surplus Other Stookholders' Equity Total Stockholders' Equity Total Liabilities & Stockholders' Equity #of shares Outstanding Market share price per share - 3,23,000 8,57,77,000 24,62,000 -71,68,000 8,13,94,000 20,37,06,000 1,40,00,000 10 3,23,000 7,65,66,000 23,62,000 -15,05,000 7,77,46,000 20,47,51,000 1,40,00,000 9 3,23,000 6,57,50,000 22.62.000 -22,00,000 6,61,35,000 20,22,02,000 1,40,00,000 9 9

Ratio Calculations 2015 2014 2013 Formula used Liquidity Ratios Current ratio Quick ratio 0.96945 0.88233 0.84622 0.28 0.24 0.22 Current Assets/Current Liabilities (Current Assets - Inventory)/Current Liabilities Activity ratios Inventory Turnover Accounts receivable turnover Total Asset Turnover Average Collection period 8.11311 8.060357.96563 72.1889 72.1548 72.8291 2.37798 2.34078 2.35017 5.06 5.06 5.01 COGS/Average Inventory Net Sales/Average Accounts receivable Net Sales/Average Total Assets (Average accounts receivable/Net sales) x 365 Financing ratios Debt ratio Debt to Equity ratio Times Interest Earned ratio 0.60043 0.620290.67293 1.50272 1.63359 2.05741 10.985 11.4574 | 15.6145 Total Debt/Total Assets Total Liabilities/Total Shareholders' Equity EBIT/Total Interest expense Market ratios Earning per Share Price Earnings 1.1645 8.59 1.15493 1.58357 7.79 5.37 Net Income/No of shares MPS/EPS Profitability ratios Return on Equity Return on Assets Net profit margin Operating profit margin 20.0% 8.0% 3.4% 5.6% 20.8% 7.9% 3.4% 5.6% 33.5% 11.0% 4.7% 7.3% Net Income/Shareholders' Equity Net Income/Average assets Net Income/Sales Operating profit/Sales

Formulas Used:

Ratio Calculations 2015 2014 2013 Formula used Liquidity Ratios Current ratio Quick ratio =H10/H20 =(H10-H8)/H20 =110/120 1=(110-18)/120 =J10/120 =(J10-18)/120 Current Assets/Current Liabilities (Current Assets - Inventory)/Current Liabilities Activity ratios Inventory Turnover =C6/((H8+18)/2) =D6/((18+J8)/2) =E6/18 Accounts receivable turnover =C5/((H7+17)/2) =D5/((17+17)/2) =E5/17 Total Asset Turnover =C5/((H14+114)/2) =D5/((114+J14)/2) =E5/J14 Average Collection period ={(H7+17)/2)/C5*365 =((17+37)/2)/D5*365 =(J7/E5*365) COGS/Average Inventory Net Sales/Average Accounts receivable Net Sales/Average Total Assets (Average accounts receivable/Net sales) x 365 Financing ratios Debt ratio Debt to Equity ratio Times Interest Earned ratio =H24/H14 =H24/H31 =C11/C12 =124/114 =124/131 =D11/D12 =J24/114 =J24/131 =E11/E12 Total Debt/Total Assets Total Liabilities/Total Shareholders' Equity EBIT/Total Interest expense Market ratios Earning per Share Price Earnings =C17/H33 =H34/M20 =D17/133 =134/N20 =E17/J33 =J34/020 Net Income/No of shares MPS/EPS Profitability ratios Return on Equity Return on Assets Net profit margin Operating profit margin =C17/H31 =D17/131 =C17/((H14+114)/2) =D17/((114+)14)/2) =C17/05 =D17/05 =C9/C5 =D9/D5 =E17/131 =E17/114 =E17/E5 =E9/E5 Net Income/Shareholders' Equity Net Income/Average assets Net Income/Sales Operating profit/Sales

.....