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{1bb28fb76c3d282be6cfd0391ccf1d9529baae691cd895e2d45215811b51644c} on cost.

(v) Allowance for bad debts 5{1bb28fb76c3d282be6cfd0391ccf1d9529baae691cd895e2d45215811b51644c} 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.

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

3 Allowance for Bad debts estimated @ 6{1bb28fb76c3d282be6cfd0391ccf1d9529baae691cd895e2d45215811b51644c} 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.

6. Interest accrued on loan term loan @ 6{1bb28fb76c3d282be6cfd0391ccf1d9529baae691cd895e2d45215811b51644c} 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{1bb28fb76c3d282be6cfd0391ccf1d9529baae691cd895e2d45215811b51644c} 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