Ten Column Worksheets

Construct ten-column worksheets

Contents

Key to resources

Introduction

Inventory control methods

Feedback to activities

This learning guide is based on the following resource:
Textbook
Duncan A (2006) Introductory Accounting, National Core Accounting Publications, Bondi
Note
A new edition of this textbook was being published at the same time as this resource.
Where possible, we have provided a second Key to resources to this new edition.

Key to resources

Resource / Textbook (2006 edition)
1 / Chapter 17 ‘Ten column worksheets’, example for Jackie Tse and Co
2 / Chapter 17 ‘Ten column worksheets’, income statement and balance sheet for Jackie Tse and Co, p524
Resource / Textbook (2007 edition)
1 / Chapter 15 ‘Ten column worksheets’, example for Jackie Tse and Co
2 / Chapter 15 ‘Ten column worksheets’, income statement and balance sheet for Jackie Tse and Co, pp 492–494

Introduction

Worksheets are prepared to assist a business with the preparation of their financial reports.

Inventory control methods

/ Go to Resource 1

This example uses the perpetual inventory method of inventory control. Note the following points:

Sales returns have been transferred to sales in the adjustment column.
The transfer of the general ledger accounts affecting gross profit to the trading account column.
Gross profit has been transferred to the profit and loss account at the bottom of the worksheet.
The transfer of the general ledger accounts affecting net profit to the profit and loss column.
The transfer of net profit(loss in this case) to the balance sheet column.

Financial reports can be prepared from the information in the worksheet.

/ Now go to Resource 2

Any balance day adjustments are recorded at the bottom of the worksheet underneath the trial balance.

Now check the following example for Norm Spence.

Norm Spence
Trial balance as at 30th June 20x2

Account / Debit
$ / Credit
$
Sales / 69 500
Inventory / 7 000
Cost of goods sold / 40 700
Discount received / 100
Advertising / 2 000
Salaries / 22 000
Office expenses / 1 700
Bad debts / 800
Rent received / 4 000
Capital / 53 100
Mortgage loan due 20x5 / 28 000
Bank overdraft / 2 500
GST clearing / 1 500
Land and buildings / 45 000
Accounts payable / 4 500
Motor vehicles / 35 000
Office furniture / 3 800
Accounts receivable / 5 200
163 200 / 163 200

Additional information

Salaries owing – $550
Advertising paid in advance – $200
Provide for doubtful debts at 1% of accounts receivable
Depreciate motor vehicles at 20% per annum straight-line; ie $7 000
Depreciate office furniture at 15% per annum straight-line; ie $570

Required

(a)A ten-column worksheet incorporating the balance day adjustments

(b)An income statement

(c)A balance sheet

Norm Spence 10-column worksheet
as at 30 June 20x2

Ledger account / Trial balance / Adjustments / Trading account / Profit and loss account / Balance sheet
Dr / Cr / Dr / Cr / Dr / Cr / Dr / Cr / Dr / Cr
$ / $ / $ / $ / $ / $ / $ / $ / $ / $
Sales / 69 500 / 69 500
Inventory / 7 000 / 7 000
Cost of goods sold / 40 700 / 40 700
Discount received / 100 / 100
Advertising / 2 000 / 200 / 1 800
Salaries / 22 000 / 550 / 22 550
Office expenses / 1 700 / 1 700
Bad debts / 800 / 800
Rent received / 4 000 / 4 000
Capital / 53 100 / 53 100
Mortgage loan due 20x5 / 28 000 / 28 000
Bank overdraft / 2 500 / 2 500
GST clearing / 1 500 / 1 500
Accounts payable / 4 500 / 4 500
Accounts receivable / 5 200 / 5 200
Motor vehicles / 35 000 / 35 000
Office furniture / 3 800 / 3 800
Land and buildings / 45 000 / 45 000
163 200 / 163 200
Accrued expenses / 550 / 550
Expenses prepaid / 200 / 200
Doubtful debts / 52 / 52
Allowance for doubtful debts / 52 / 52
Depn–motor vehicle / 7 000 / 7 000
Accum. depn–motor vehicle / 7 000 / 7 000
Depn–office furniture / 570 / 570
Accum. depn–office furniture / 570 / 570
40 700 / 69 500
Gross profit / 28 800 / 28 800
34 472 / 32 900
Net loss / 1 572 / 1 572
8 372 / 8 372 / 69 500 / 69 500 / 34 472 / 34 472 / 97 772 / 97 772

Norm Spence
Income statement
For period ended 30th June 20x2

$ / $ / $
Sales / 69 500
Less: Cost of goods sold / 40 700
Gross profit / 28 800
Add: Other operating income / 4 100
Discount received / 100
Rent received / 4 000
Total income / 32 900
Less: Operating expenses / 34 472
Marketing / 1 800
Advertising / 1 800
Administration / 31 820
Salaries / 22 550
Office expenses / 1 700
Depreciation – Motor vehicle / 7 000
Depreciation – Office furniture / 570
Financial / 852
Bad debts / 800
Doubtful debts / 52
Net loss / 1 572

Norm Spence
Balance sheet
As at 30th June 20x2

$ / $ / $
Current assets / 12 348
Inventory / 7 000
Accounts receivable / 5 200
Less: Allowance doubtful debts / 52 / 5 148
Expenses prepaid / 200
Total income
Non-current assets / 76 230
Land & buildings / 45 000
Motor vehicles / 35 000
Less: Accumulated depreciation / 7 000 / 28 000
Office furniture / 3 800
Less: Accumulated depreciation / 570 / 3 230
Total assets / 88 578
Current liabilities / 9 050
Accounts payable / 4 500
GST clearing account / 1 500
Bank (overdraft) / 2 500
Accrued expenses / 550
Non-current liabilities / 28 000
Mortgage loan / 28 000
Total liabilities / 37 050
Net assets / 51 528
Owner’s equity / 51 528
Capital / 53 100
Less: Loss / 1 572

Commentary

Note the balance day adjustments shown underneath the trial balance. The money amounts are shown in the adjustment column.
The totals of the adjustment column must agree.
Gross profit is the balance of the trading account column and is transferred to the profit and loss account.
Net loss is the balance of the profit and loss account and is transferred to the balance sheet column.
The income statement, if required, is prepared from the details in the trading account and profit and loss account columns.
The balance sheet, if required, is prepared from the details in the balance sheet column.

/ Activity 1

Note: The answer is at the end of this guide. From the following trial balance and additional information for Annie Best, you are required to prepare a ten-column worksheet. Use the template provided.

A Best
Trial balance as at 30 June 20x3

Account / Debit
$ / Credit
$
Sales / 18 500
Discount received / 200
Advertising / 350
Sales representatives’ salaries / 1 250
Motor vehicle expenses / 300
Cost of goods sold / 14 950
Inventory / 6 000
Cartage outwards / 400
Office expenses / 170
Telephone expenses / 30
Rent received / 250
Loan interest paid / 100
Bank / 1 000
GST clearing account / 1 000
Accounts receivable / 8 500
Motor vehicles / 18 000
Accounts payable / 5 000
Loan – Avcon / 20 000
Capital – ABest / 4 100
$50 050 / $50 050

continued overleaf …

Additional information

Prepaid advertising – $50
Sales representatives’ salaries owing – $200
Rent received in advance – $75
Depreciate motor vehicles 20% per annum on cost; ie, $3600

Ledger account / Trial balance / Adjustments / Trading account / Profit and loss account / Balance sheet
Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$
/ Activity 2

From the following information for Slyvester Crimmins prepare a ten-column worksheet. Use the template provided.

Information

Accrued office wages – $250
Insurance paid in advance – $450
Create an allowance for doubtful debts equal to 2% of accounts receivable (nearest $).
Depreciate delivery van – $3000
Depreciate office equipment – $1800

Slyvester Crimmins
Trial balance as at 30 June 20x2

Debit
$ / Credit
$
Capital – SCrimmins / 113 065
Inventory / 23 213
Cost of goods sold / 71 861
GST clearing account / 2 000
Sales / 204 434
Discount allowed / 1 479
Discount received / 1 233
Cash at bank / 15 431
Accounts payable / 17 365
Accounts receivable / 32 167
Bad debts / 3 500
Sales salaries and commissions / 42 236
Office wages / 29 150
Rates and taxes / 1 500
Insurance / 1 200
Rent expense / 12 000
Commission received / 8 140
Delivery van / 15 000
Office equipment / 12 000
Land and buildings / 160 000
Mortgage on land & buildings / 100 000
Shares in ABC Ltd / 15 000
Accumulated depreciation:
Delivery van / 5 500
Office equipment / 2 000
Drawings / 18 000
$453 737 / $453 737
Ledger account / Trial balance / Adjustments / Trading a/c / Profit & loss a/c / Balance sheet
Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$
/ Activity 3

From the following list of account balances you are required to complete a ten-column worksheet. Use the template provided.

Stay Tuned & Co
Ledger balances at 30 June 20x7
Sales / $25 250
Inventory / 3 700
Cost of goods sold / 19 340
Wages / 3 000
Rates / 100
Discounted allowed / 350
Rent expense / 600
Insurance / 80
Stationery / 50
Commission expense / 126
Interest expense / 120
General expenses / 60
Plant / 1 900
Bank overdraft / 250
Accounts receivable / 3 400
Allowance for doubtful debts / 100
Land & buildings / 12 800
Accounts payable / 1 020
Mortgage / 1 000
Drawings / 1 000
GST clearing (credit balance) / 200
Capital / ?
Additional information:

Write off bad debts $440 (including GST)
Prepaid insurance $15
Accrued wages $50
Depreciation plant $190
Allowance for doubtful debts is to be made equal to 5% of Accounts receivableafter bad debt write off.

Ledger account / Trial balance / Adjustments / Trading account / Profit and loss account / Balance sheet
Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$

Feedback to activities

Activity 1

Annie Best
10-column worksheet
as at 30 June 20x3

Ledger account / Trial balance / Adjustments / Trading a/c / Profit loss / Balance sheet
Dr $ / Cr $ / Dr $ / Cr $ / Dr $ / Cr $ / Dr $ / Cr $ / Dr $ / Cr $
Sales / 18 500 / 18 500
Discount received / 200 / 200
Advertising / 350 / 50 / 300
Sales reps’ salaries / 1 250 / 200 / 1 450
Motor vehicle expenses / 300 / 300
Cost of goods sold / 14 950 / 14 950
Inventory / 6 000 / 6 000
Cartage outwards / 400 / 400
Office expenses / 170 / 170
Telephone expenses / 30 / 30
Rent received / 250 / 75 / 175
Loan interest paid / 100 / 100
Bank / 1 000 / 1 000
GST clearing a/c / 1 000 / 1 000
Account receivable / 8 500 / 8 500
Motor vehicles / 18 000 / 18 000
Accounts payable / 5 000 / 5 000
Loan–Avcon / 20 000 / 20 000
Capital–A. Best / 4 100 / 4 100
50 050 / 50 050
Expenses prepaid / 50 / 50
Accrued expenses / 200 / 200
Income rec’d in advance / 75 / 75
Depn.–motor vehicles / 3 600 / 3 600
Accum.depn–m/vehicles / 3 600 / 3 600
14 950 / 18 500
Gross profit / 3 550 / 3 550
6 350 / 3 925
Net loss / 2 425 / 2 425
3 925 / 3 925 / 18 500 / 18 500 / 6 350 / 6 350 / 34 975 / 34 975

Activity2

Sylvester Crimmins
10-column worksheet
as at 30 June 20x2

Ledger account / Trial balance / Adjustments / Trading account / Profit loss a/c / Balance sheet
Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$
Capital–S Crimmins / 113 065 / 113 065
Inventory / 23 213 / 23 213
GST clearing / 2 000 / 2 000
Cost of goods sold / 71 861 / 71 861
Sales / 204 434 / 204 434
Discount allowed / 1 479 / 1 479
Discount received / 1 233 / 1 233
Cash at bank / 15 431 / 15 431
Accounts payable / 17 365 / 17 365
Accounts receivable / 32 167 / 32 167
Bad debts / 3 500 / 3 500
Sales salaries & commission / 42 236 / 42 236
Office wages / 29 150 / 250 / 29 400
Rates and taxes / 1 500 / 1 500
Insurance / 1 200 / 450 / 750
Rent expense / 12 000 / 12 000
Commission received / 8 140 / 8 140
Delivery van / 15 000 / 15 000
Office equipment / 12 000 / 12 000
Premises / 160000 / 160 000
Mortgage on premises / 100 000 / 100 000
Shares in ABC Ltd / 15 000 / 15 000
Accum. Depn.
Delivery van / 5 500 / 3 000 / 8 500
O/equipment / 2 000 / 1 800 / 3 800
Drawings / 18 000 / 18 000
453 737 / 453 737
Accrued expenses / 250 / 250
Expenses prepaid / 450 / 450
Allowance – doubtful debts / 643 / 643
Doubtful debts / 643 / 643
Depreciation:
Delivery van / 3 000 / 3 000
O/equipment / 1 800 / 1 800
71 861 / 204 434
Gross profit / 132 573 / 132 573
96 308 / 141 946
Net profit / 45 638 / 45 638
6 143 / 6 143 / 204 434 / 204 434 / 141 946 / 141 946 / 291261 / 291 261

Activity3

Stay Tuned and Co
10-column worksheet
as at 30 June 20x7

Ledger account / Trial balance / Adjustments / Trading account / Profit & loss a/c / Balance sheet
Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$ / Dr
$ / Cr
$
Sales / 25 250 / 25 250
Inventory / 3 700 / 3 700
Cost of goods sold / 19 340 / 19 340
Wages / 3 000 / 50 / 3 050
Rates / 100 / 100
Discount allowed / 350 / 350
Rent / 600 / 600
Insurance / 80 / 15 / 65
Stationery / 50 / 50
Commission expense / 126 / 126
Interest expense / 120 / 120
General expense / 60 / 60
Plant / 1 900 / 1 900
Bank overdraft / 250 / 250
Accounts receivable / 3 400 / 440 / 2 960
Allowance– doubtful debts / 100 / 48 / 148
Land & buildings / 12 800 / 12 800
Accounts payable / 1 020 / 1 020
Mortgage / 1 000 / 1 000
Drawings / 1 000 / 1 000
GST clearing / 200 / 40 / 160
Capital / 18 806 / 18 806
46 626 / 46 626
Bad debts / 400 / 400
Expenses prepaid / 15 / 15
Accrued expense / 50 / 50
Depreciation – Plant / 190 / 190
Accum.Depreciation –Plant / 190 / 190
Doubtful debts* / 48 / 48
19 340 / 25 250
Gross profit / 5 910 / 5 910
5 159 / 5 910
Net profit / 751 / 751
743 / 743 / 25 250 / 25 250 / 5 910 / 5 910 / 22 375 / 22 375

* $3 400 – 440 = 2 960 + 5% = 148 Less 100 = 48

Construct ten-column worksheets1