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