Complete "Problem 15-18 Common Size Statements and Financial Ratios for a Loan Application" in chapter 15 of Managerial Accounting and present your responses in an Excel spreadsheet.

Please review attached with notes and correct errors which are notated

Week 2 Assignment Template Name: Date: Class: Professor: ALL YELLOW HIGHLIGHTED CELLS R DO NOT KEY OVER FORMULAS CORNER OF A CELL, I HAVE INSERT CELL TO 5/2/2017 Number/Problem 1a Description Current Assets (a) Current Liabilities (b) Working Capital (a - b) 1b Current Assets (a) Current Liabilities (b) Current Ratio (a/b) 1c Quick Assets (a) Current Liabilities (b) Acid-test Ratio (a/b) 1d jbrynsaas: CURRENT YEAR + PRIOR Sales on Account YEAR (a) DIVIDED BY 2. Average Receivables (b) AR Turnover (a/b) This Year 1,520,000.00 $ 800,000.00 $ 720,000.00 $ Last Year 1,090,000.00 430,000.00 660,000.00 $ $ 1,520,000.00 $ 800,000.00 $ 1.90 1,090,000.00 430,000.00 2.534884 $ $ 550,000.00 $ 800,000.00 $ 0.69 468,000.00 430,000.00 1.09 $ $ 5,000,000.00 $ 390,000.00 $ 12.82 4,350,000.00 275,000.00 15.82 $ $ $ Average Collection Period: 365 days divided by AR Turnover 28.47 23.0747126437 jbrynsaas: 1e CURRENT Cost of Goods Sold (a) YEAR + PRIOR$ 3,875,000.00 $ YEAR DIVIDED BY 2. Average Inventory (b) $ 950,000.00 $ Inventory Turnover Ratio (a/b) 4.0789473684 Average Sale Period: 365 days divided by Inventory Turnover 1f 1g Average Sale Period Average Collection Period Operating Cycle 89.4838709677 $ $ jbrynsaas: CURRENT YEAR + PRIOR Sales (a) YEAR DIVIDED BY 2. $ Average total Assets (b) Total Asset Turnover (a/b) $ 3,450,000.00 600,000.00 5.75 63.4782608696 89.48 $ 28.47 $ 63.48 23.07 117.95 86.55 5,000,000.00 $ 3,000,000.00 $ 1.67 4,350,000.00 2,460,000.00 1.77 1h Total Liabilities (a) Stockholders Equity (b) Debt-to-equity ratio (a/b) $ $ 1,400,000.00 $ 1,600,000.00 $ 0.875 1,030,000.00 1,430,000.00 0.72 1i Net income BEFORE interest AND taxes (a) Interest expense (b) Time interest earned (a/b) $ $ 400,000.00 $ 72,000.00 $ 5.56 280,000.00 72,000.00 3.89 Average Stockholders equity (b) Equity multiplier (a/b) $ 3,000,000.00 $ 1,600,000.00 $ 1.88 2,460,000.00 1,430,000.00 1.72 jbrynsaas: CURRENT YEAR + PRIOR YEAR(a) DIVIDED BY 2. Average total assets $ 1j 2a Saban Electronics Common-Size Balance Sheets Description (Percentages Not Dollars) This Year Last Year Current Assets: Cash Marketable Securities Accounts Receivable, Net Inventory Prepaid Expenses Total Current Assets Plant and Equipment, Net Total Assets 2.3% 0.0% 16.0% 31.7% 0.7% 50.7% 49.3% 100.0% 6.1% 0.7% 12.2% 24.4% 0.9% 44.3% 55.7% 100.0% Liabilities: Current Liabilities Bonds Payable, 12% Total Liabilites 26.7% 20.0% 46.7% 17.5% 24.4% 41.9% Stockholders' Equity: Common Stock, $15 par Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 25.0% 28.3% 53.3% 100.0% 30.5% 27.6% 58.1% 100.0% Note: Columns may not total down due to rounding of percentages 2b Saban Electronics Common-Size Income Statement Description Sales (Percentages Not Dollars) This Year Last Year 100.0% 100.0% Cost of goods sold Gross margin Selling and administrative expenses Net operating income Interest expense Net income before taxes Income taxes Net income 77.0% 23.0% 58.0% -35.0% 15.0% -50.0% 30.0% -80.0% 79.0% 21.0% 56.0% -35.0% 20.0% -55.0% 30.0% -85.0% 3a One weakness I identified is that liabilities have increased from the prior year. It is positive that collection activity is not reducing the aquiree's receivables for the company 3b One weakness I identified is that total net income is negative. After reviewing it including income taxes it still shows that total income is not doing well 3c One strength found is the cash & equivalents for the company have generated & increased over time 3d One strength found is the average receivables turnover is less this year than the prior year which means that the company is not in financial distress and is not liquidating their assets 3e My recommendation on the loan is to add short-term and long-term debt together and compare this amount to total cash on hand in the current assets section. It allows the investor to understand how much of a cash cushion is available or if a firm is dependent on the market to refinance debt when it is coming due OW HIGHLIGHTED CELLS REQUIRE DATA INPUT T KEY OVER FORMULAS IF YOU SEE A RED TRIANGLE IN THE OF A CELL, I HAVE INSERTED A COMMENT TO HELP YOU. HOVER OVER THE CELL TO SEE THE COMMENT. $ $ $ Last Year 1,090,000.00 430,000.00 660,000.00 $ $ 1,090,000.00 430,000.00 2.534884 $ $ 468,000.00 430,000.00 1.09 $ $ 4,350,000.00 275,000.00 15.82 23.0747126437 $ $ 3,450,000.00 600,000.00 5.75 jbrynsaas: Same process as above only using the Inventory figures from the comparative balance sheet in the text and the prior year total given in 1e. 63.4782608696 $ $ 63.48 23.07 86.55 $ $ 4,350,000.00 2,460,000.00 1.77 jbrynsaas: Same process as above only using the Total Assets figures from the comparative balance sheet in the text and the prior year total given in 1g. $ $ $ $ $ $ 1,030,000.00 1,430,000.00 0.72 280,000.00 72,000.00 3.89 2,460,000.00 1,430,000.00 1.72 jbrynsaas: These should be the Net Income BEFORE the interest and BEFORE the taxes. jbrynsaas: Same process as above only using the Total Stockholders figures from the comparative balance sheet in the text and the prior year total given in 1j. s Not Dollars) Last Year 6.1% 0.7% 12.2% 24.4% 0.9% 44.3% 55.7% 100.0%