Using the Worksheet

Learning Outcomes

• Creating an adjusted trial balance using a worksheet

Before we create the adjusted trial balance from the ledgers, letâ€™s take a look at one of the common ways that accountants prepare and track the adjustments they propose to make. It can be a bit inefficient to make each adjustment independently, the way we did in the prior section, because things can be interconnected and will most likely be way more complicated than our simple NeatNiks example. For that reason, experienced accountants use a worksheet.

The worksheet starts with the unadjusted trial balance:

NeatNiks
For the month ended October 31, 20XX
Reference No. Accounts Debits Credits
110 Checking 3,500.00
120 Accounts Receivable 5,650.00
125 Supplies 2,600.00
130 Prepaid Rent 12,000.00
210 Account Payable 1,600.00
220 Contractor Payable
310 Nick Frank, Capital Contributions 20,000.00
330 Nick Frank, Withdrawals 4,000.00
410 Service Revenue 8,750.00
510 Insurance Expense 1,500.00
520 Rent Expense
530 Supplies Expense
540 Contractor Expense 1,100.00
Totals Single line 30,350.00
Double line
Single line 30,350.00
Double line

And then we expand it to accommodate our proposed adjustments:

Unadjusted Trial Balance Adjustments Adjusted Trial Balance Reference No. Accounts DR 110 Checking 3,500.00 3,500.00 120 Accounts Receivable 5,650.00 5,650.00 125 Supplies 2,600.00 2,600.00 130 Prepaid Rent 12,000.00 12,000.00 210 Accounts Payable 1,600.00 1,600.00 220 Contractor Payable – – 310 Nick Frank, Capital Contributions 20,000.00 20,000.00 330 Nick Frank, Withdrawals 4,000.00 4,000.00 410 Service Revenue 8,750.00 8,750.00 510 Insurance Expense 1,500.00 1,500.00 520 Rent Expense – – 530 Supplies Expense – – 540 Contractor Expense 1,100.00 1,100.00 Single line 30,350 Double line Single line 30,350 Double line Single line 30,350 Double line Single line 30,350 Double line

Instead of using T accounts as we analyze accounts, we track everything on the spreadsheet, making journal entries and labeling them as we go, so we can see the effect they will have on the accounts AND so we can see the whole picture as we build it.

Unadjusted Trial Balance Reference Adjustments Adjusted Trial Balance Reference Accounts DR CR DR Reference 110 Checking 3,500.00 3,500.00 120 Accounts Receivable 5,650.00 5,650.00 125 Supplies 2,600.00 AJE1 1,600.00 1,000.00 130 Prepaid Rent 12,000.00 12,000.00 210 Accounts Payable 1,600.00 1,600.00 220 Contractor Payable – – 310 Nick Frank, Capital Contributions 20,000.00 20,000.00 330 Nick Frank, Withdrawals 4,000.00 4,000.00 410 Service Revenue 8,750.00 8,750.00 510 Insurance Expense 1,500.00 1,500.00 520 Rent Expense – – 530 Supplies Expense – AJE1 1,600.00 1,600.00 540 Contractor Expense 1,100.00 1,100.00 Single line 30,350 Double line Single line 30,350 Double line Single line 1,600 Double line Single line 1,600 Double line Single line 30,350 Double line Single line 30,350 Double line

When we are done with our analysis and all the journal entries are written, we post them and then we compare the trial balance from our adjusted general ledger accounts to the worksheet. If we did everything right, they should match.

Unadjusted Trial Balance Reference Adjustments Adjusted Trial Balance Reference No. Accounts DR CR DR Reference 110 Checking 3,500.00 3,500.00 120 Accounts Receivable 5,650.00 5,650.00 125 Supplies 2,600.00 AJE1 1,600.00 1,000.00 130 Prepaid Rent 12,000.00 AJE2 2,000.00 10,000.00 210 Accounts Payable 1,600.00 1,600.00 220 Contractor Payable – AJE3 1,200.00 1,200.00 310 Nick Frank, Capital Contributions 20,000.00 20,000.00 330 Nick Frank, Withdrawals 4,000.00 4,000.00 410 Service Revenue 8,750.00 8,750.00 510 Insurance Expense 1,500.00 1,500.00 520 Rent Expense – AJE2 2,000.00 2,000.00 530 Supplies Expense – AJE1 1,600.00 1,600.00 540 Contractor Expense 1,100.00 AJE3 1,200.00 2,300.00 Single line 30,350 Double line Single line 30,350 Double line Single line 4,800.00 Double line Single line 4,800.00 Double line Single line 31,550.00 Double line Single line 31,550.00 Double line

Once we have made all the adjustments to the ledger accounts and we have run the adjusted trial balance and feel confident that all the assets, liabilities, capital accounts, revenues, and expenses are recorded and are being reported according to GAAP (or, if we are a small business, as close to GAAP as we want to be), then we are ready for the final step: to create the financial statements.