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,127,159
Income from operations		1,276,141
Other income
Gain on sale of equip	18,000
Other expenses
Interest expense	(12,187)
Net other income and expenses	5,813
Income before income tax		1,281,954
Income tax expenses		384,586
Net income			$897,368

Northern Illinois Manufacturing
Balance Sheet
December 31
Assets			This Year	Last Year
Current assets
Cash			$786,797	$746,681
Accounts receivable		680,750	542,685
Work in process		702,159	0
Inventory			16,766	7,500
Prepaid expenses		76,550	42,590
Total current assets		2,263,022	1,339,456
Property, plant, and equipment
Furnishings		40,416	40,416
Equipment		929,400	800,200
Buildings			450,000	450,000
Land			300,000	300,000
Accumulated depreciation		(482,523)	(485,204)
Total property, plant, and equipment	1,237,293	1,105,412
Total Assets			$3,500,315	$2,444,868
Liabilities and Stockholders' Equity
Current liabilities
Accounts payable		$157,095	$128,360
Income taxes payable		101,344	79,989
Wages payable		4,517	1,984
Interest payable		1,187	0
Other current liabilities		14,515	15,246
Revolving bank loan payable	15,000	0
Total current liabilities		293,658	225,579
Long-term liabilities
Notes payable		140,000	0
Total liabilities		433,658	225,579
Stockholders' equity
Common stock		1,250,000	1,250,000
Retained earnings		1,816,657	969,289
Total stockholders' equity	3,066,657	2,219,289
Total liabilities and stockholders' equity	$3,500,315	$2,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.