Question

Accounting excel

Northern Illinois Manufacturing prepared the balance sheet and income statement for this year. Now the company needs to prepare its statement of cash flows. 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. Norther Illinois Manufacturing sold a piece of company equipment for $30,000. The equipment had been used for six years. It had cost $100,000 when purchases and had a 10-years life and a $10,000 salvage value. Straight-line depreciation was used.

2. Northern Illinois manufacturing purchased new equipment costing $200,000.

3. The company paid $100,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

$52,000 


  Insurance


382,000 


  Salaries and wages

594,650 


  Depreciation

72,500 


  Other op. expenses

30,500 


Total op. expenses


1,131,650 

Income from operations


1,271,650 

Other income



  Loss on sale of equip

(16,000)


Other expenses



  Interest expense

(12,200)


Net other income and expenses

(28,200)

Income before income tax


1,243,450 

Income tax expenses


373,035 

Net income



$870,415 

 

 

 

 

 

 

 

 

Northern Illinois Manufacturing

Balance Sheet

December 31






Assets



This Year

Last Year

Current assets




  Cash



$792,306 

$746,681 

  Accounts receivable


680,750 

542,685 

  Work in process


680,450 

0

  Inventory



16,750 

7,500 

  Prepaid expenses


71,500 

42,590 

     Total current assets


2,241,756 

1,339,456 

Property, plant, and equipment



  Furnishings


40,416 

40,416 

  Equipment


900,200 

800,200 

  Buildings



450,000 

450,000 

  Land



300,000 

300,000 

  Accumulated depreciation


(503,704)

(485,204)

     Total property, plant, and

    equipment

1,186,912 

1,105,412 

Total Assets



$3,428,668 

$2,444,868 






Liabilities and Stockholders' Equity



Current liabilities




  Accounts payable


$156,300 

$128,360 

  Income taxes payable


100,344 

79,989 

  Wages payable


600 

1,984 

  Interest payable


1,200 

0

  Other current liabilities


14,520 

15,246 

  Revolving bank loan payable

16,000 

     Total current liabilities


288,964 

225,579 

Long-term liabilities




  Notes payable


150,000 

     Total liabilities


438,964 

225,579 

Stockholders' equity




  Common stock


1,250,000 

1,250,000 

  Retained earnings


1,739,704 

969,289 

     Total stockholders' equity

2,989,704 

2,219,289 

Total liabilities and stockholders' equity

$3,428,668 

$2,444,868 

 

Instructions:

 

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

 

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

b. Determine free cash flow.


0 0
Add a comment Improve this question Transcribed image text
Request Professional Answer

Request Answer!

We need at least 10 more requests to produce the answer.

0 / 10 have requested this problem solution

The more requests, the faster the answer.

Request! (Login Required)


All students who have requested the answer will be notified once they are available.
Know the answer?
Add Answer to:
Accounting excel
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Similar Homework Help Questions
  • Prepare a statement of cash flow using the indirect method for this year and determine the...

    Prepare a statement of cash flow using the indirect method for this year and determine the free cash flow. I have completed this this but I want to make sure my numbers add up correctly and not just a coincidence. 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...

  • Please complete in excel The Statement of Cash Flows 989 P16-34A Preparing the statement of cash...

    Please complete in excel The Statement of Cash Flows 989 P16-34A Preparing the statement of cash flows-indirect method with Learning Objective 2 non-cash transactions The 2015 income statement and comparaive balance sheet of Rolling Hills, Inc. follow 1. Net Cash Prov. by Op. Act. $125,700 ROLLING HILLS, INC. Income Statement Year Ended December 31, 2015 Sales Revenue $436,000 Cost of Goods Sold 202,200 Gross Profit 233,800 Operating Expenses Salaries Expense $78,400 Depreciation Expense-Plant Assets 14,400 Other Operating Expenses 10,200 Total...

  • Managerial Accounting: Prepare a statement of cash flows for this year. The following information applies to...

    Managerial Accounting: Prepare a statement of cash flows for this year. The following information applies to the questions displayed below) Comparative financial statements for Weaver Company follow: Weaver Company Comparative Balance Sheet at December 31 This $ 10 300 159 བློ་འཆཎ པas an Assets Cash and cash equivalents Accounts receivable Inventory Prepaid expenses Total current assets Property, plant, and equipment Less accumulated depreciation Net property, plant, and equipment Long-term investments Total assets Liabilities and Stockholders' Equity Accounts payable Accrued liabilities...

  • Prepare a statement of cash flows for the Crosby Corporation

    27. Prepare a statement of cash flows for the Crosby Corporation. Follow the general procedures indicated in Table 2–10 on page 38 .Statement of cash flows(L04)Current Assets LiabilitiesCash . . . . . . . . . . . . . . . . . . . . . . . . . $ 15,000 Accounts payable . . . . . . . . $ 20,000Accounts receivable . . . . . . . . . . . ....

  • Problem 12-4B Calculate risk ratios (L012-3) The following income statement and balance sheets for The Athletic...

    Problem 12-4B Calculate risk ratios (L012-3) The following income statement and balance sheets for The Athletic Attic are provided. THE ATHLETIC ATTIC Income Statement For the year ended December 31, 2021 Net sales $8,780,000 Cost of goods sold 5,390,000 Gross profit 3,390,000 Expenses: Operating expenses $1,540,000 Depreciation expense 198.000 Interest expense 38. Bee Income tax expense 348,000 Total expenses 2,124,000 Net income 31.266,000 THE ATHLETIC ATTIC Balance Sheets December 31 2021 2020 Assets Current assets: $ $ 152,000 730,000 1,345,000...

  • Multiple-Step Income Statement and Report Form of Balance Sheet The following selected accounts and their current...

    Multiple-Step Income Statement and Report Form of Balance Sheet The following selected accounts and their current balances appear in the ledger of Kanpur Co. for the fiscal year ended June 30, 2018 Cash Accounts Receivable Inventory Estimated Returns Inventory Office Supplies Prepaid Insurance Office Equipment Accumulated Depreciation-Office Equipment 58,000 Miscellaneous Selling Expense Store Equipment Accumulated Depreciation-Store Equipment 87,500 Rent Expense Accounts Payable Customers Refunds Payable $92,000 Retained Earnings 450,000 Dividends 370,000 Sales $381,000 300,000 8,925,000 5,000 Cost of Goods Sold...

  • A comparative balance sheet for “Alpha” Company containing data in € for the last recent years...

    A comparative balance sheet for “Alpha” Company containing data in € for the last recent years is as follows: Required: 1. Prepare the cash flow statement for “Alpha” Company for this year 20X2, using the indirect method. 2. Prepare the cash flow statement for “Alpha” Company for this year 20X2, using the direct method. . 20X2 20X2 20X1 Assets Fixed assets: Land Buildings Less accumulated depreciation Equipment Less accumulated depreciation Total fixed assets 170,000 450.000 (110,000) Comparative Balance Sheet Liabilities...

  • Multiple-step income statement and balance sheet The following selected accounts and their current balances appear in t...

    Multiple-step income statement and balance sheet The following selected accounts and their current balances appear in the ledger of Kanpur Co. for the fiscal year ended June 30, 2017: Cash $118,000 Retained Earnings $520,000 70,600 Accounts Receivable Dividends 320,200 358,600 4,321,100 Inventory Estimated Returns Inventory Office Supplies 2,499,400 5,000 11,100 Sales Cost of Goods Sold Sales Salaries Expense Advertising Expense Depreciation Expense- Store Equipment 702,900 193,300 Prepaid Insurance 8,600 Office Equipment 259,600 37,700 Miscellaneous Selling Expense 16,500 176,400 Accumulated Depreciation-...

  • Veesperna Company Balance Sheet As at December 31 2018 2017 Assets Current Assets Cash Accounts R...

    need to find the sale of equipment Veesperna Company Balance Sheet As at December 31 2018 2017 Assets Current Assets Cash Accounts Receivable Merchandise Inventory Total Current Assets Land Equipment Less: Accumulated Depreciation $-55,000 $-69,000 Total Assets $109,915 $75,100 $47,600 $29,900 $78,600 $46,500 $236,115 $151,500 $96,000 $103,000 $106,000 $139,000 $383,115 $324,500 Liabilities and Equity Liabilities Current Liabilities Accounts Payable Taxes Payable Total Current Liabilities Bonds Payable Total Liabilities Stockholders' Equity Common Stock Retained Earnings Total Stockholders' Equity Total Liabilities and...

  • The comparative balance sheets of Waterways Corporation's trrigation Installation Division for the years 2019 and 2020...

    The comparative balance sheets of Waterways Corporation's trrigation Installation Division for the years 2019 and 2020 and the income statements for the year 2019 and 2020 are presented below. Additional information: 85% of the sales for Waterways were credit sales. There are 5,000 shares outstanding for both years. This is a private corporation whose shares are not available to the public. WATERWAYS CORPORATION-INSTALLATION DIVISION Balance Sheets December 31 Assets 2020 2019 Current assets Cash $839,900 $752,800 Accounts receivable 684,400 545,200...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT