Instructions:

The assignment must be completed in an Excel spreadsheet and include the following:

a. Prepare a statement of cash flow using the indirect method for this year.

b. Determine free cash flow.

Excel Assignment # 1 - Statement of Cash Flow Spring Semester Northern Illinois Manufacturing prepared the balance sheet and income statement for this year. Now the company needs to prepare its statement of cash flow. The comparative balance sheets for Northern Illinois Manufacturing for this year and last year and the income statement for this year are presented below. (Note: Work in Process is a current asset and should be treated as any other current asset.) Additional information: 1. Northern Illinois Manufacturing sold a piece of company equipment for $24,000. The equipment had been used for ten years. It had cost $80,000 when purchased and had a 10-years life and a $6,000 salvage value. Straight-line depreciation was used. 2. Northern Illinois Manufacturing purchased new equipment costing $209,200. 3. The company paid $50,000 in dividends. Northern Illinois Manufacturing Income Statement For the year ending December 31 Sales $5,536,077 Less: Cost of goods sold 3.132.777 Gross Profit 2,403,300 Operating expenses Advertising $50,000 Insurance 400,000 Salaries and wages 584,640 Depreciation 71,319 Other op expenses 21.200 Total operating expenses 1.121159 Income from operations 1,276,141 Other income Gain on sale of equip 18,000 Other expenses Interest expense (12.1871 Net other income and expenses 5.813 Income before income taxe 1,281.954 Income tax expenses 394586 income 97,368 0.59 Back Excel1Spring.docx Balance Sheet December 31 This Year Last Year 0 $786,797 $746,681 680,750 542,685 702,159 16,766 7,500 76.550 42.590 2.263,022 1.339.456 Assets Current assets Cash Accounts receivable Work in process Inventory Prepaid expenses Total current assets Property, plant, and equipment Furnishings Equipment Buildings Land Accumulated depreciation Total property, plant, and equipment Total Assets 40,416 40,416 929,4001 900,200 450,000 450,000 300,000 300,000 1482.523) 485,204) 1.237,293 1,105,412 $3,500,315 $2,444,868 Liabilities and Stockholders' Equity Current liabilities Accounts payable Income taxes payable Wages payable Interest payable Other current liabilities Revolving bank loan payable Total current liabilities Long-term liabilities Notes payable Total liabilities Stockholders' equity Common stock Retained earnings Total stockholders' equity Total liabilities and stockholders' equity $157,095 $128,360 101,344 79,989 4,517 1,984 1,187 0 14,5151 15,246 15.000 0 293,658 225,579 140,000 433,658 225,579 1.250,000 1,250,000 1.816.657 969.289 3.066.657 2.219.289 $3,500,315 32,444,868 Instructions: The assignment must be completed in an Excel spreadsheet and include the following: a. Prepare a statement of cash flow using the indirect method for this year. b. Determine free cash flow