46 Requirements: 47 1. Start Excel. Download and open the workbook named: Keown Martin_Petty_Problem 3-4_Start. Important note: All calculations must be shown using cell references. Do NOT enter numerical values in the cells. 48 2. In cell B22, input the cash amount. (1 point) 49 3. In cell B23, input the accounts receivable. (1 point) 50 4. In cell B24, input the inventories. (1 point) 51 5. In cell B25, input the other current assets. (1 point) 6. In cell B26, input the function SUM to calculate the total current assets. (1 52 point) 53 7. In cell B27, input the gross fixed assets. (1 point) 8. In cell B28, input the accumulated depreciation. Note: The output of the expression or function you typed in this cell is expected as 54 a positive number. (1 point) 55 9. In cell B29, calculate the net fixed assets. (1 point) 56 10. In cell B30, input the other assets. (1 point) 57 11. In cell B31, calculate the total assets. (1 point) 58 12. In cell B33, input the notes payable amount. (1 point) 59 13. In cell B34, input the accounts payable amount. (1 point) 60 14. In cell B35, calculate the total current liabilities. (1 point) 61 15. In cell B36, input the long-term debt amount. (1 point) 62 16. In cell B37, calculate the total liabilities. (1 point) 63 17. In cell B38, input the common stock amount. (1 point) 18. Since the balance sheet must balance, input the total liabilities and equity 64 amount in cell B41 by making a cell reference to the total assets. (1 point) 65 19. In cell B40, calculate the total equity amount. (1 point) 66 20. In cell B39, calculate the retained earnings amount. (1 point) 67 21. In cell B43, calculate the net working capital. (1 point) 68 22. In cell B44, calculate the debt ratio. (1 point) 23. Save the workbook. Close the workbook and then exit Excel. Submit the 69 workbook as directed. 1 PROBLEM 3-4 2 PREPARING A BALANCE SHEET 3 Prepare a balance sheet from the following information. What is the 4 net working capital and debt ratio? Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your 5 formulas, usually the Given Data section. 6 DATA 7 Cash 8 Account receivables $50,000 42,700 9 Accounts payable 23,000 10 Short-term notes payable 10,500 11 Inventories 40,000 12 Gross fixed assets 1,280,000 13 Other current assets 14 Long-term debt 5,000 200,000 15 Common stock 490,000 16 Other assets 15,000 17 Accumulated depreciation 312,000 18 Retained earnings ? 19 20 SOLUTION 21 Balance Sheet: 22 Cash 23 Accounts receivable 24 Inventories 25 Other current assets 26 Total current assets 27 Gross fixed assets 28 Accumulated depreciation