Work Sheet - 10 Column Work Sheet

Work Sheet is an important tool to make adjustment in the unadjusted trial balamnce. As a result, adjusted trial balance, income statement and balance sheet can easily be prepared without any difficulty. 

Work Sheet is not a part of  journal and not a part of financial statement. It is just like a working paper used to do the work easily and in a timely manner. 

Components

Typically, following are the main components of the work sheet:

  • Trail balance column,
  • Adjustment column,
  • Adjusted trial balance,
  • Income statement Column,
  • Balance sheet column.

Jonna Brothers is a well-known retailer in the auto parts industry. Over the years, Jonna has evolved as the major market leader and has many successful years of running the business. Following are the balances taken from the Jonna Brothers. on Dec. 31, 2015.

DEBIT BALANCES:

Cash Rs.80,000; Account ReceivableRs.120,000; Allowance for bad debts Rs.2,000; Merchandise Inventory Rs.137,000; Prepaid Rent Rs.18,000; Equipment Rs.2,00,000; Drawing $72,000; Cost of Goods Sold Rs.3,60,000; Utilities expense Rs.48,000; Wages expense Rs.64,000; Office Supplies Expense Rs.30,000.

CREDIT BALANCES:

Sales Rs.439,000; Commission Rs.120,000; Bond Payable Rs.90,000, Capital Rs.?

Following additional information is available for adjustment:

(l) Commission earned Ra.108,000.

(ii) Rent expired Rs.12,000.

(Iii) Accrued Wages Rs.18,000.

(iv) Depreciation was estimated at 5% on cost.

(v) Allowance for bad debts 5% of Account

(vi) Of?ce supplies used Rs.26,000.

REQUIRED: Prepare ten column worksheet.

Solution

Particulars

Trial Balance

Adjustments

Adjusted T. B

Income Stat.

Balance Sheet

 

Debit

Credit Debit Credit

Debit

Credit Debit Credit

Debit

Credit

Cash

80,000

     

80,000

     

80,000

 

Accounts receivable

120,000

     

120,000

     

120,000

 

Allow Bad Debt

2,000

   

8,000

 

6,000

     

6,000

Inventory

137,000

     

137,000

     

137,000

 

Prepaid rent

18,000

   

12,000

6,000

     

6,000

 

Equipment

200,000

     

200,000

     

200,000

 

Drawing

50,000

     

50,000

     

50,000

 

Cogs

360,000

     

360,000

 

360,000

     

Utilities exp

48,000

     

48,000

 

48,000

     

Wages exp

64,000

 

18,000

 

82,000

 

82,000

     

Supplies exp

30,000

   

4,000

26,000

 

26,000

     

Sales revenue

 

439,000

     

439,000

 

439,000

   

Unearned commission

 

120,000

108,000

   

12,000

     

12,000

Bond payable

 

90,000

     

90,000

     

90,000

Capital

 

460,000

     

460,000

     

460,000

Comm income

     

108,000

 

108,000

 

108,000

   

Rent exp

   

12,000

 

12,000

 

12,000

     

Wages payable

     

18,000

 

18,000

     

18,000

Depreciation

   

10,000

 

10,000

 

10,000

     

Allow for depreciation

     

10,000

 

10,000

     

10,000

Bad debt exp

   

8,000

 

8,000

 

8,000

     

Office supplies

   

4,000

 

4,000

     

4,000

 

Net income

           

500

   

500

Total

1,109,000

1,109,000

160,000

160,000

1,143,000

1,143,000

547,000

547,000

597,000

597,000

 

Exercise:

Tow Plc is a large chain of retail outlets. The junior accountant is facing trouble in balancing out the trial balance and as a result is not able to produce financial statements.

You as the senior accountant knows the usefulness of work sheets, have decided to produce the financial statements using 10 column work sheets.

The following Trial balance data have been taken from the books of Tow Plc. The accounts are maintained on a July to June - year basis and are adjusted and closed annually to meet the requirements of accounting standards and generally accepted accounting policies.

Cash Rs.133,200; Accounts Receivable Rs.196,800; Merchandise inventory (Jan. 1. 2013) Rs.248,000; Unexpired Insurance Rs.7,200; Office supplies Rs.3,200; Building Rs.240,000; Accumulated Depreciation: Building Rs.9,600; Equipment Rs.64,000; Accumulated Depreciation: Equipment Rs.19,200;

Accounts payable Rs.191,600; Tow Capital Rs.?

Tow Drawing Rs.72,000; Sales Rs.1,304,000; sales Returns Rs.20,800; Purchases Rs.760,000; Purchase Return Rs.8,000; Transportation in Rs.19,200; Salaries expense Rs.161,600; Misc. Expenses Rs.4,400.

Data for Adjustments:

(i) Unexpired insurance on December 31, Rs.2,400

(ii) Supplies used Rs.2,000.

(iii) The buildings are being depreciated over a 25-year useful life. The equipment is being depreciated over a 10-year useful life (Use straight line method)

(iv) Salaries payable as of December 31, were Rs. 20,000

(v) Inventory of merchandise on Dec. 31, was Rs. 158,400.

REQUIRED:

Prepare 10-columns Work Sheet.

Solution

Particulars

Trial Balance

Adjustments

Adjusted T. B

Income Stat.

Balance Sheet

 

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Cash

133,200

     

133,200

     

133,200

 

Accounts receivable

196,800

     

196,800

     

196,800

 

Inventory

248,000

     

248,000

 

248,000

     

Unexpired insurance

7,200

   

4,800

2,400

     

2,400

 

Office supplies

3,200

   

2,000

1,200

     

1,200

 

Building

240,000

     

240,000

     

240,000

 

All. For dep – building

 

9,600

 

9,600

 

19,200

     

19,200

Equipment

64,000

     

64,000

     

64,000

 

All. For dep – equip

 

19,200

 

6,400

 

25,600

     

25,600

Accounts payable

 

191,600

     

191,600

     

191,600

Capital

 

398,000

     

398,000

     

398,000

Drawings

72,000

     

72,000

     

72,000

 

Sales

 

1,304,000

     

1,304,000

 

1,304,000

   

Sales return

20,800

     

20,800

 

20,800

     

Purchase

760,000

     

760,000

 

760,000

     

Purchase returns

 

8,000

     

8,000

 

8,000

   

Transportation

19,200

     

19,200

 

19,200

     

Salaries expenses

161,600

 

20,000

 

181,600

 

181,600

     

Misc. expenses

4,400

     

4,400

 

4,400

     

Insurance exp

   

4,800

 

4,800

 

4,800

     

Supplies exp

   

2,000

 

2,000

 

2,000

     

Dep. Exp – building

   

9,600

 

9,600

 

9,600

     

Dep. Exp – Equip.

   

6,400

 

6,400

 

6,400

     

Salaries payable

     

20,000

 

20,000

     

20,000

Inventory

             

158,400

158,400

 

Net income

                 

216,600

Total

1,930,400

1,930,400

42,800

42,800

1,966,400

1,966,400

1,470,400

1,470,400

868,000

868,000

Worksheet Accounting Problems

Purpose Ltd is a medium size company operating in the manufacture of comforter set mostly for the local market. It normally produces low price products targeting middle class of the country. The accounting staff employed in the office do not have much experience. As a result, you have been approached by the chief operating officer of the company to provide your outsourcing service as accounts experts. You are the management accountant and member of the professional organization representing the management accounting profession in the industry has the task to help them in the preparation of the financial statements for the year ended.

You have chosen an important tool of Financial Accounting, that is, Work Sheet, to do the task more smoothly and easily. You have obtained unadjusted trial balance from the company’s accounting staff and obtained the data for adjustment.

Following trial balance data has been taken from the books of Purpose Ltd. for the year ended December 31, 2009:

Cash $.440,000, Accounts Receivable $.150,000. Office Supplies Expense $.10,000, Unearned Commission $,100,000, Machine $.300,000. Accounts payable $.160,000, Allowance for Depreciation - machine $.10,000, Merchandise inventory $,100.000, Allowance for Bad debts (Dr) $.6,000, Cost of Goods Sold $ 400,000, Prepaid Rent $.7,200, salaries Expense $.12,000, Drawing $.10,000, Capital $.592,000. Sales Revenue $.560,000, Long term Loan?

DATA FOR ADJUSTMENT:

1 Used office supplies $.3,000.

  1. Rent was paid for 1 year on April 30, 2009,

3 Allowance for Bad debts estimated @ 6% of Accounts Receivable at end.

4 Unexpired salaries $,1,500.

5 The machine has a life of 80,000 hours and estimated salvage value is $ 50,000. Machine has operated 5,000 hours during 2009.

  1. Interest accrued on loan term loan @ 6% per annum. Loan acquired on September 1, 2009.

REQUIRED

As a qualified management accountant professional, you are required to prepare ten columns Work Sheet.

Solution

Particulars

Trial Balance

Adjustments

Adjusted T. B

Income Stat.

Balance Sheet

 

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Cash

440,000

     

440,000

     

440,000

 

Accounts receivable

150,000

     

150,000

     

150,000

 

Office supplies exp.

10,000

   

3,000

4,000

 

4,000

     

Unearned commission

 

100,000

     

100,000

     

100,000

Machine

300,000

     

300,000

     

300,000

 

Accounts payable

 

160,000

     

160,000

     

160,000

Accumulated dep.

 

10,000

 

12,500

 

22,500

     

22,500

Inventory

100,000

     

100,000

     

100,000

 

Allow. For bad debts

6,000

   

15,000

 

9,000

     

9,000

Cost of goods sold

400,000

     

400,000

 

400,000

     

Prepaid rent

7,200

   

5,400

1,800

     

1,800

 

Salaries exp

12,000

   

3,000

9,000

 

9,000

     

Drawing

10,000

     

10,000

     

10,000

 

Capital

 

592,000

     

592,000

     

592,000

Sales revenue

 

560,000

     

560,000

 

560,000

   

Long term loan

 

13,200

     

13,200

     

13,200

Office supplies

   

6,000

 

6,000

     

6,000

 

Rent exp

   

5,400

 

5,400

 

5,400

     

Bad debts exp

   

15,000

 

15,000

 

15,000

     

Prepaid salaries

   

3,000

 

3,000

     

3,000

 

Depreciation exp

   

12,500

 

12,500

 

12,500

     

Interest exp

   

264

 

264

 

264

     

Interest payable

     

264

 

264

     

264

Net income

           

113,836

   

113,836

Total

1,435,200

1,435,200

42,164

42,164

1,456,964

1,456,964

560,000

560,000

1,010,800

1,010,800

 

Key Point: Common problems in creating worksheets is that students make mistake in debit and credit. Sometimes, they forget to make the corresponding credit entry or debit entry or make the entry in wrong column. So, students need to be very careful while making the adjusting entries otherwise the end up with un-balance trial balance, balance sheet and profit & loss. Let’s practice another example to make your concept clearer and prepare better for the exam.

Manor is the well-known name in the education sector and runs a master level unive$ ity in the country. Recently, it has also opened a school to increase its student base for the unive$ ity level. Following is the pro-closing trial balance of Manor on 30 June, 2011.

Accounts Head

Debit

Credit

Cash

140,000

 

Accounts receivable

48,000

 

Heavy duty machines

24,00,000

 

Allowance for depreciation – heavy duty machines

 

24,000

Accounts payable

 

36,000

Bank loan

 

50,000

Capital

 

20,00,000

Revenue from university students

 

520,000

Revenue from school’s student

 

170,000

Repair & maintenance expense

66,000

 

Parking services expense

30,000

 

Fuel expenses

52,000

 

Salaries expenses

64,000

 
 

28,00,000

28,00,000

 

ADDITIONAL INFORMATION:

(i) Salaries accrued $ 6,000 and Prepaid salaries for 10,000.

(ii) Bad debts estimated at 10% 0f Accounts Receivable.

(iii) Interest on bank loan $ 10,000 outstanding.

(iv) Unearned revenue from cargo $ 20,000 and earned Receivable $ 14,000.

(v) Proprietor withdrew cash from the business $ 10,000 for private use.

(vi) Book value of heavy duty machinery was estimated at $ 23,52,000.

REQUIRED:

Prepare a Ten Column Work Sheet from the above data given above.

Solution

Particulars

Trial Balance

Adjustments

Adjusted T. B

Income Stat.

Balance Sheet

 

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Dr.

Cr.

Cash

140,000

   

10,000

130,000

     

130,000

 

Accounts receivable

48,000

     

48,000

     

48,000

 

Machinery

24,00,000

     

2400,000

     

2400,000

 

Allow. For dep –machinery

 

24,000

 

24,000

 

24,000

     

48,000

Accounts payable

 

36,000

     

36,000

     

36,000

Bank loan

 

50,000

     

50,000

     

50,000

Capital

 

20,00,000

     

2000,000

     

2000,000

Revenue from university

 

520,000

     

520,000

 

520,000

   

Revenue from school

 

170,000

20,000

14,000

 

164,000

 

164,000

   

Repair & maintenance

66,000

     

66,000

 

66,000

     

Parking service expenses

30,000

     

30,000

 

30,000

     

Fuel exp

52,000

     

52,000

 

52,000

     

Salaries exp

64,000

 

6,000

10,000

60,000

 

60,000

     

Salary payable

     

6,000

 

6,000

     

6,000

Prepaid salary

   

10,000

 

10,000

     

10,000

 

Bad debt exp.

   

4,800

 

4,800

 

4,800

     

Allow. For bad debts

     

4,800

 

4,800

     

4,800

Interest expense.

   

10,000

 

10,000

 

10,000

     

Interest payable

     

10,000

 

10,000

     

10,000

Unearned revenue

     

20,000

 

20,000

     

20,000

School revenue receivable

   

14,000

 

14,000

     

14,000

 

Drawings

   

10,000

 

10,000

     

10,000

 

Depreciation exp.

   

24,000

 

24,000

 

24,000

     

Net income

           

437,200

   

437,200

Total

2800,000

2800,000

98,800

98,800

2,858,800

2,858,800

684,000

684,000

2,612,000

2,612,000