Using the Worksheet

  • Creating an adjusted trial balance using a worksheet

 

Step 6. Prepare adjusted trial balance.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
Trial Balance (unadjusted)
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:

NeatNiks
Trial Balance
For the Month ended October 31, 20XX
Unadjusted Trial Balance Adjustments Adjusted Trial Balance
Reference No. Accounts DR CR DR CR DR CR
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.

NeatNiks
Trial Balance
For the Month ended October 31, 20XX
Unadjusted Trial Balance Reference Adjustments Adjusted Trial Balance
Reference Accounts DR CR DR Reference CR DR CR
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.

NeatNiks
Trial Balance
For the Month ended October 31, 20XX
Unadjusted Trial Balance Reference Adjustments Adjusted Trial Balance
Reference No. Accounts DR CR DR Reference CR DR CR
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.