The instructions are on the file.Besides that she reminded usYour excel worksheets must have formulas to be considered correct. There will be a loss of 5 points on the ratio worksheet if formulas are not used, even if the answer is correct. There will be a loss of 10 points on both the vertical analysis and the horizontal analysis if formulas are not used.She didn't give any other information then that.
Data Tim Kennney started and operates Kenney Home Electronics. He has several stores where he sells and repairs televisions and stereo equipment. A balance sheet, statement of equity and income statement are provided. Calculate the following ratios being sure to show the formula in written form and followed by the numeric calculation. If you show the answer without the calculation, it will be marked wrong. Please carry out to 2 decimal places and if the ratio is expressed as a percentage, you must write it so in order to earn credit. Presently the market price of the stock is $8.75. Income StatementBalance Sheet 201920182,0192,018 Revenue from sales:ASSETS Sales315,788267,120 Current Assets: Less sales returns and allowances-1,890-1,260 Cash12,3447,600 Net Sales313,898265,860 Accounts Receivable15,62513,823 Merchandise Inventory47,25027,450 Cost of Goods Sold:156,713158,891 Supplies1,430500 Gross profit157,185106,969 Prepaid Insurance954450 Total Current Assets77,60349,823 Operating Expenses: Wages expense45,85937,485 Property, plant and equipment: Advertising expense3,9385,250 Land15,00015,000 Supplies expense2,2051,575 Building135,000135,000 Telephone expense5,5134,935 Less: accumulated depreciation-12,600-6,300 Utilities expense18,90012,600 Store equipment47,90047,900 Insurance expense2,8352,835 Less: accumulated depreciation-9,450-4,725 Depreciation expense-building6,3006,300 Total Property, plant and equipment175,850186,875 Depreciation expense-store equipment4,7254,725TOTAL ASSETS253,453236,698 Miscellaneous expense3,5442,531 Total operating expenses93,81878,236LIABILITIES Current liabilities: Income from operations63,36828,733 Notes payable7,87513,000 Accounts payable15,75017,500 Other Revenues: Wages payable709550 Repair fees15,7502,100 Sales tax payable2,3631,966 Interest revenue1,418289 Unearned repair fees2,4502,200 Total other revenues17,1682,389 Mortgage payable (current portion)1,0501,000 Income before Interest and Taxes80,53531,122 Total current liabilities30,19736,216 Interest expense4,9615,500 Long term liabilities" Income Tax Expense21,00010,140 Mortgage payable45,00046,000 Net Income54,57415,482 Less current portion-1,000-1,000 Total Long term Liabilities TOTAL LIABILITIES74,19781,216 STOCKHOLDER'S EQUITY Common Stock, $2 par140,000140,000 Retained Earnings StatementRetained Earnings39,25615,482 for the Year Ended December 31, 2019Total Stockholder's Equity179,256155,482 Beginning Retained Earnings15,482TOTAL LIABILITIES & STOCKHOLDER'S EQUITY253,453236,698 Add: Net Income for the Year54,574 Less dividends-30,800 Increase in retained earnings23,774 Retained Earnings, December 31, 201339,256 Ratios Ratios should be for the current year and be carried out 2 decimal places. You must show formulas in cells or lose 10% of points. Express formula in termsExpress formula in numbersAnswer Liquidity 1) Current ratio 2) Quick (acid test) ratio 3) Working capital 4) Accounts receivable turnover 5) Inventory turnover Solvency 6) Debt to equity ratio 7) Debt to total assets 8) Times interest earned Profitability 9) Return on assets 10) Asset turnover ratio 11) Return on equity 12) Profit margin ratio 13) Gross profit ratio Market indicators 14) Earnings per share 15) Dividend yield 16) Price earnings ratio 17) Payout ratio Vertical Analysis Prepare vertical analysis for 2013 on the income statement and balance sheet. You must carry out to 2 decimal places and show formulas in your cells or lose 10% of the points. Income StatementBalance Sheet 2019Percentage2018Percentage2019Percentage2018Percentage Revenue from sales:ASSETS Sales315,788267,120 Current Assets: Less sales returns and allowances-1,890-1,260 Cash12,3447,600 Net Sales313,898265,860 Accounts Receivable15,62513,823 Merchandise Inventory47,25027,450 Cost of Goods Sold:156,713158,891 Supplies1,430500 Gross profit157,185106,969 Prepaid Insurance954450 Total Current Assets77,60349,823 Operating Expenses: Wages expense45,85937,485 Property, plant and equipment: Advertising expense3,9385,250 Land15,00015,000 Supplies expense2,2051,575 Building135,000135,000 Telephone expense5,5134,935 Less: accumulated depreciation-12,600-6,300 Utilities expense18,90012,600 Store equipment47,90047,900 Insurance expense2,8352,835 Less: accumulated depreciation-9,450-4,725 Depreciation expense-building6,3006,300 Total Property, plant and equipment175,850186,875 Depreciation expense-store equipment4,7254,725TOTAL ASSETS253,453236,698 Miscellaneous expense3,5442,531LIABILITIES Total operating expenses93,81878,236 Current liabilities: Notes payable7,87513,000 Income from operations63,36828,733 Accounts payable15,75017,500 Wages payable709550 Other Revenues: Sales tax payable2,3631,966 Repair fees15,7502,100 Unearned repair fees2,4502,200 Interest revenue1,418289 Mortgage payable (current portion)1,0501,000 Total other revenues17,1682,389 Total current liabilities30,19736,216 Income before Interest and Taxes80,53531,122 Long term liabilities Interest expense4,9615,500 Mortgage payable45,00046,000 Income Tax Expense21,00010,140 Less current portion-1,000-1,000 Net Income54,57415,482Total Long term Liabilities44,00045,000 TOTAL LIABILITIES74,19781,216 STOCKHOLDER'S EQUITY Common Stock, $2 par140,000140,000 Retained Earnings39,25615,482 Total Stockholder's Equity179,256155,482 TOTAL LIABILITIES & STOCKHOLDER'S EQUITY253,453236,698 Horizontal Analysis Prepare horizontal analysis on the income statement and balance sheet. You must carry out to 2 decimal places and show formulas in your cells or lose 10% of the points. Income StatementBalance Sheet 20192018Percentage20192018Percentage Revenue from sales:ASSETS Sales315,788267,120 Current Assets: Less sales returns and allowances-1,890-1,260 Cash12,3447,600 Net Sales313,898265,860 Accounts Receivable15,62513,823 Merchandise Inventory47,25027,450 Cost of Goods Sold:156,713158,891 Supplies1,430500 Gross profit157,185106,969 Prepaid Insurance954450 Total Current Assets77,60349,823 Operating Expenses: Wages expense45,85937,485 Property, plant and equipment: Advertising expense3,9385,250 Land15,00015,000 Supplies expense2,2051,575 Building135,000135,000 Telephone expense5,5134,935 Less: accumulated depreciation-12,600-6,300 Utilities expense18,90012,600 Store equipment47,90047,900 Insurance expense2,8352,835 Less: accumulated depreciation-9,450-4,725 Depreciation expense-building6,3006,300 Total Property, plant and equipment175,850186,875 Depreciation expense-store equipment4,7254,725TOTAL ASSETS253,453236,698 Miscellaneous expense3,5442,531LIABILITIES Total operating expenses93,81878,236 Current liabilities: Notes payable7,87513,000 Income from operations63,36828,733 Accounts payable15,75017,500 Wages payable709550 Other Revenues: Sales tax payable2,3631,966 Repair fees15,7502,100 Unearned repair fees2,4502,200 Interest revenue1,418289 Mortgage payable (current portion)1,0501,000 Total other revenues17,1682,389 Total current liabilities30,19736,216 Income before Interest and Taxes80,53531,122 Long term liabilities: Interest expense4,9615,500 Mortgage payable45,00046,000 Income Tax Expense21,00010,140 Less current portion-1,000-1,000 Net Income54,57415,482Total Long term Liabilities44,00045,000 TOTAL LIABILITIES74,19781,216 STOCKHOLDER'S EQUITY Common Stock, $2 par140,000140,000 Retained Earnings39,25615,482 Total Stockholder's Equity179,256155,482 TOTAL LIABILITIES & STOCKHOLDER'S EQUITY253,453236,698 1 2 3 4 5 6 7 8 9 A B C D E Tim Kennney started and operates Kenney Home Electronics. He has several stores where he sells and repairs televisions and stereo equipment. A balance sheet, statement of equity and income statement are provided. Calculate the following ratios being sure to show the formula in written form and followed by the numeric calculation. If you show the answer without the calculation, it will be marked wrong. Please carry out to 2 decimal places and if the ratio is expressed as a percentage, you must write it so in order to earn credit. Presently the market price of the stock is $8.75.