2017 $200 450 550 $1,200 $2,200 1,000 $1,200 $2.400 2018 $150 425 625 $1.200 $2,600 1.200 $1,400 $2,600 Knapp Inc. Balance Sheet: Assets Cash Accounts receivable Inventory Current assets Plant and equipment Accumulated depreciation Net plant and equipment Total assets Liabilities and Owners' Equity Accounts payable Notes payable-current (9%) Current liabilities Bonds Owners' equity Common stock Retained earnings Total owners' equity Total liabilities and owners' equity $200 0 $200 S600 $150 150 $300 S600 $900 700 $1,600 $2.400 $900 800 $1,700 $2.600 Knapp Inc. Income Statement: 2017 $1.200 700 $500 2018 $1.450 850 S600 $30 220 $40 200 Sales Cost of goods sold Gross profit Selling, general, and administrative expens Depreciation Operating income Interest expense Net income before taxes Taxes (40%) Net income before taxes 250 $250 50 $200 80 $120 240 S360 64 $296 118 $178 a. How much is the firm's net working capital and what is the debt ratio? 2017 2018 Net working capital Debt ratio 2018. Knapp Inc. Common-sized Income Statement for 2018: Sales Cost of goods sold Gross profit Operating expenses Depreciation expense Operating income Interest expense Net income before taxes Taxes (40%) Net income Knapp Inc. Common-sized Balance Sheet for 2018: Assets Cash Accounts Receivable Inventory Current assets Plant and equipment Accumulated depreciation Net plant and equipment Total assets Liabilities and Equity Accounts payable Notes payablecurrent (9%) Current liabilities Bonds Owners' equity Common stock Retained earnings Total owners' equity Total liabilities and owners' equity Knapp Inc. Statement of Cash Flows for 2018: Cash Flow from Operating Activities Net Income Depreciation expense' Decrease in accounts receivable Increase in inventory Decrease in account payable Net cash provided by operating activities Cash Flow from Investing Activities Increase in notes payable Dividends Net cash provided by financing activities Net decrease in cash Beginning cash, January 1, 2018 Ending cash, December 31, 2018 Requirements: 1. Start Excel. Download and open the workbook named: Keown_Martin_Petty_Problem_3-15_Start. Important note: All calculations must be shown using cell references. Do NOT enter numerical values in the cells. 2. In cell B44, calculate the net working capital for 2017. (1 point) 3. In cell B45, calculate the debt ratio for 2017. (1 point) 4. Copy cells B44:B45 and paste onto cells C44:C45. (1 point) 5. To prepare the common-sized income statement, find the ratio between each account in the income statement and the sales amount. In cell B49, divide the sales amount by the sales datum in cell E30 and make an absolute cell reference to the sales datum for copying and pasting purposes. (1 point) 6. Copy cell B49 and paste onto the cell ranges B50:B51 and B54:B58. (1 points) 7. In cell B52. divide the selling, general, and administrative expense by the sales datum in cell E30 and make an absolute cell reference to the sales datum for copying and pasting purposes. (1 points) 8. Copy cell B52 and paste onto the cell B53. (1 points) 9. To prepare the common-sized balance sheet, find the ratio between each account in the balance sheet and the total of the balance sheet. In cell B62. divide the cash amount by the total assets datum in cell C16 and make an absolute cell reference to the total assets datum for copying and pasting purposes. (1 point) 10. Copy cell B62 and paste onto the cells B63:B69. (1 points) and make an absolute cell reference to the total liabilities and owners' equity datum for copying and pasting purposes. (1 point) 12. Copy cell B71 and paste onto the cell ranges B72:B74 and B76:B79. (1 points) 13. In cell C83, input the net income amount. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 14. In cell B84, input the depreciation expense. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 15. In cell B85, calculate the decrease in accounts receivable. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 16. In cell B86, calculate the increase in inventory. 17. In cell B87, calculate the decrease in account payable. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 18. In cell C88. input the function SUM to calculate the net cash provided by operating activities. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 19. In cell C90, calculate the increase in plant and equipment. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 20. In cell B92. calculate the increase in notes payable. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 21. In cell B93, calculate the dividends amount. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 22. In cell C94. input the function SUM to calculate the net cash provided by financing activities. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 23. In cell C95, calculate the net decrease in cash. Note: The output of the expression or function you typed in this cell is expected as a positive number in case of cash inflow and as a negative number in case of cash outflow. (1 point) 24. In cell C96, input the beginning cash amount. (1 point) 25. In cell C97, calculate the ending cash amount. (1 point) 26. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed