Vlookup and Trial Balances Function and Example
5) Check to ensure pulled debits and credits balance
The final step is to ensure that we pulled amounts to the combined tab (tab Step 4) without errors. The best way is to check that debits and credits for the balance sheet accounts net out to zero and that income statement accounts in total give the net result for a respective period. We will follow the same procedure as we did in step 2.
In cell H59, we sum up all balance sheet account by entering formula: =SUM(H2:H30). In cell H60, we sum up all income statement accounts by entering formula: =SUM(H31:H57). We then copy these two formulas to column I:
You can see that balance sheet checks provide zeros, which means all debits and credits for the balance sheet accounts were pulled to the combined tab completely and accurately. You can then notice that the result of adding all income statement accounts equals balances in account 5003, which means that all income statement accounts were pulled to the combined tab completely and accurately as well.
Expanding the Trial Balances File
As mentioned, two trial balances are not a limit. If you need to build comparable trial balances for more than two periods, just follow the above procedures with such points in mind:
- When you create the complete list of accounts (step 3), you need to copy accounts and their descriptions from all trial balances which you want to compare.
- When you pull the data from trial balances to the combined trial balances tab, you will need to adjust the second argument of VLOOKUP function for each additional period so that it pulls data from respective trial balance tabs.
Once again, after some practice, the above procedure should take 5-7 minutes to complete depending on how clean the initial trial balances are.
Finally, regarding efficiency. If your trial balances have 20 accounts in them, performing accounts matching manually may be faster. However, if you run into trial balances with hundreds or even thousands of accounts, then the above approach may save time.
Page 7 of 7
Location