"Case Study 4
Using the financial information in the following tables, compute the required ratios list on the ratio analysis sheet and then complete the horizontal and vertical analyses worksheets. indicating whether or not if fraud may be occurring and which, if any indicators. and if you could please show with excel formulas, thank you.
ABC COMPANY BALANCE SHEET AS OF DECEMBER 31, 2018 ASSETS 2018 2017 2016 Current assets Cash $501,992 $434,215 $375,141 Accounts receivable 335,272 302,514 241,764 Inventory 515,174 505.321 310.885 Prepaid expenses 251,874 231,100 136,388 Total current assets $1,604,312 $1,473,150 $1,064,178 Property, plant, and equipment 765,215 735.531 705,132 Accumulated depreciation (218,284) (196,842) (175.400) TOTAL ASSETS $2,151,243 $2,011,839 $1,593.910 LIABILITIES Current liabilities Accounts payable $248,494 $366,864 $322.156 Accrued liabilities 122.192 216,533 215,474 Income taxes payable 10,645 25.698 22.349 Current portion of long-term debt 42 200 42,200 42,200 Total current liabilities $423.531 $651.295 $602,179 Long-term liabilities Long-term debt 425,311 400.311 375,100 TOTAL LIABILITIES $848,842 $1,051,606 $977.279 STOCKHOLDERS' EQUITY Common stock $370,124 $356,758 $320.841 Additional paid-in capital 29.546 24,881 21,910 Retained earnings 902.731 578,594 273,880 Total stockholders' equity $1,302,401 $960,233 $616.631 TOTAL LIABILITIES AND STOCKHOLDERS EQUITY $2.151,243 $2,011,839 $1,593,910ABC COMPANY INCOME STATEMENT FOR THE PERIOD ENDED DECEMBER 31, 2018 2018 2017 2016 Sales $1,572,134 $1,413,581 $1,158,417 Cost of goods sold $601,215 556,721 500,702 Gross profit $970.919 $856,860 $657,715 EXPENSES Advertising $555,153 $50,531 $42,150 Depreciation 21,442 21,442 21,4-42 Bad debts 20,151 18,934 17,943 Legal 17,261 10,207 9,701 Miscellaneous 91,014 31,214 29,104 Rent 148,321 142,078 141,143 Repairs and maintenance 14,315 13,642 11,932 Salaries and wages 47,121 45,312 39,142 Utilities 15,912 15,643 14,217 Total expenses $430,690 $349,003 $326,774 Net income before income tax $540,229 $507.857 $330,941 Income tax expense 216,092 203,143 132,376 NET INCOME $324,137 $304,714 $198,565 Number of shares of stock outstanding 35,913 26,786 23,712ABC COMPANY RATIO ANALYSIS DECEMBER 31, 2018 LIQUIDITY RATIOS: 12/31/18 12/31/17 Change % Change Current ratio Current assets/Current liabilities Quick ratio (Current assets - Inventory)/Current liabilities Accounts receivable turnover Sales/Average accounts receivable Days' sales in accounts receivable 365/Accounts receivable turnover Inventory turnover Cost of goods sold/ Average inventory PROFITABILITY/PERFORMANCE RATIOS: Profit margin Net income/Net sales Gross profit margin (96) Gross profit/ Sales Earnings per share Net income/Number of shares of stock Sales/Total assets Sales/Total assets Sales/Working capital Sales/(Current assets - Current liabilities) EQUITY POSITION RATIOS: Owners' equity/Total assets Total stockholders' equity/Total assets Current liabilities/Owners' equity Current liabilities/Total stockholders' equity Total liabilities/Owners' equity Total liabilities/Total stockholders' equityABC COMPANY INCOME STATEMENT FOR THE PERIOD ENDED DECEMBER 31, 2018 2018 2017 $ CHANGE % CHANGE Sales $1,572,134 $1,413,581 Cost of goods sold 601,215 556,721 Gross profit $970,919 $856,860 EXPENSES Advertising $55,153 $50,531 Depreciation 21,442 21,4-42 Bad debts 20,151 18,934 Legal 17,261 10,207 Miscellaneous 91,014 31,214 Rent 148,321 142,078 Repairs and maintenance 14,315 13,642 Salaries and wages 47,121 45.312 Utilities 15,912 15,643 Total expenses $430,690 $349,003 Net income before income tax $540,229 $507,857 Income tax expense 216,092 203,143 NET INCOME $324,137 $304,714ABC COMPANY BALANCE SHEET AS OF DECEMBER 31, 2018 % TOTAL % TOTAL % TOTAL ASSETS 2018 ASSETS 2017 ASSETS 2016 ASSETS Current assets Cash $501,992 $434,215 $375,141 Accounts receivable 335,272 302,514 241,764 Inventory 515,174 505,321 310,885 Prepaid expenses 251,874 231,100 136,388 Total current assets $1,604,312 $1,473,150 $1,064,178 Property, plant, and equipment 765,215 735,531 705,132 Accumulated depreciation (218,280) (196,842) (175,400) TOTAL ASSETS $2,151,243 $2,011,839 $1,593,910 LIABILITIES Current liabilities Accounts payable $248,494 $366,864 $322,156 Accrued liabilities 122,192 216,533 215,474 Income taxes payable 10,645 25,698 22.349 Current portion of long-term debt -42,200 -42,200 42,200 Total current liabilities $423,531 $651,295 $602,179 Long-term liabilities Long-term debt 425,311 400,311 375,100 TOTAL LIABILITIES $848,842 $1.051,606 $977.279 STOCKHOLDERS' EQUITY Common stock $370, 124 $356,758 $320,841 Additional paid-in capital 29,546 24,881 21,910 Retained earnings 902,731 578,594 273,880 Total stockholders' equity $1,302,401 $960,233 $616,631 TOTAL LIABILITIES AND STOCK- $2,151,243 $2,011,839 $1,593,910 HOLDERS' EQUITY
