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.
- 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.
- 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 |