a. How much is the firm's net working capital and what is the debt ratio? b. Complete a common-sized income statement, a common-sized balunce sheet, and a statement of cash flows for Complete the slops below using cell references fo given data or previous calculations. In some cases, a simple cell reference is all you need. To copylpaste a formula across a row or down a cohmm, an absohde cell reference or a mixed cell reference moy be preferred. If a specific Excel function is to be used, the directions will specify the nce of that finction Do not by in mumerical dala into a cell or function. listead, make a reference lo the cell in which the dala is found. Make your computations onb in the bhe cells highighed below, In all cases, inless othenwice directed, sise the earliest appearance of the data in jour formulas, uswally the Given Data section. Knapp Inc. Income Statement: Sales Cost of goods sold Gross profit Selling general, and administrative expense Depreciation Operating income Interest expense Net income before taxes Taxes (40%) Net income before taxes b. Complete a common-sized income statement, a common-sized balance sheet, and a statement of cash flows for Knapp Ine Common sized lecome Statement for 2018 . Knapp Inc Common-atzed Balance Sheet for 2018 : Curtent labilities Bonde Owners' equity Common stock Retained earnings Total owners' equity Total bablities and owners' eguity Krapp ine Statement of Cash Flows for 2018 Cash Flow from Operating Activities Net incoroe Deprecation expensed Decrease in accounts receivable Increase in inventory Decrease in accouat payable Net cash provided by operating activities Casb Flow from Investing Activities Increase in plant and equiprent Cash Flow from Financing Activities Increase in notes payable Dividends Net cash provided by financing activities Net decrease in cash Beginning cast, Jamuary 1,2018 Fnding cash, December 31,2018 Requircments: 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 . (I point) 3. In cell B45, calculate the debt ntio for 2017. (1 point) 4. Copy cells B44 B45 and paste onto cells C4tCes. (1 point) 3. 10 prepare the common-sized income statement, wid 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 BSO BS1 and BS4 BS8 (1 points) 7. In cell B52, divide the selling, general, and administrative expense by the sales datum in cell B30 and make an absolute cell reference to the sales datum for copying and pssting purposes: (1 points) 8. Copy cell B52 and paste onto the cell B53. ( 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 B 82 , divide the cash amount by the total assets darum in cell C16 and make an absolute cell teference to the total assets datum for copying and pasting purposes. (1 point) 10. Copy cell B62 and paste onto the cells B63869. (1 points) 11. In cell Bit, divide the accounts payable amount by the total thabilities and owners' equity datum in cell C26 and make an absolute cell reference to the total labbilities and omners' oquity datum for copying and pasting purposes. 12 Copy cell B7t and paste onto the cell ranget B72:B74 and B76879 (1 points) 13. In cell C83, input the net iacome amount. Note- The output of the expression or function you typed in this cell is expected as a positive number nn case of cach inflow and as a negative number in case of cash outhlow. ( 1 point) 14. In cell BM, input the deprecistion expense. Note The output of the exprestion or function you lyped in this cell is expected as a positive number in case of 3.15 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 B99, ealeuthte the docrease in accounts receivible 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 eell 806 , calculate the increase in inventory. Note The output of the expression or function you typed in this cell is expected an a positive number in case of cash inflow and as a negative number in case of cash outflow. ( 1 poini) 17. In cell B87, caleulate the decrease in account payable Note The output of the expression or function you typed in thas 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 actrivities. Note: The outpot of the expression of function you typed in this cell is expected as a positive mumber 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 eash outflow, (1 point) 20. In cell 892 , calculate the increase in notes payable. Note: The cutput of the expretsion or fuaction 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 poiat) 21. In cell B93, calculate the dividends amoun! Note: The output of the expression or function you typed in thas cell is expected as a positive number in case of 120 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 expresision 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 begining cash amount. (1 poiat) 25. In cell C9?, calculate the ending casb amount. ( P point) 26. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed