Pivot Table and Trial Balances Function and Example
You can perform a similar procedure to hide the Grand Total in column E. Right click on cell E4 and click Hide. The resulting pivot table will look like this:
That's it. You created a comparative trial balance using 12/31/05 and 12/31/06 trial balances. Note that the pivot table automatically aligns the accounts with respective balances for each year. For example, account 8011 does not have a balance as of 12/31/06 which is shown as a blank cell (because account 8011 did not exist in 2006). On the other hand, account 8022 does not have a balance as of 12/31/05 which is also shown as a blank cell (because account 8022 did not exist in 2005). Thus, even though there were changes in the chart of accounts, all balances are presented against their respective accounts:
5) Check to ensure pulled debits and credits balance
One thing any data manipulation requires is final checks to ensure there were no omissions or errors. Such checks are more valuable when the data manipulation is done via formulas and not automated tools like pivot tables. However, if you want to have a final check that the comparative trial balance we created has no errors, then checks for zero net balance of balance sheet accounts and net income of income statement accounts may be performed (see also Excel Lecture #1, step 5):
The balance sheet check returns zero for both periods; thus, we can conclude that the list of balance sheet accounts in our comparative trial balance is complete. The income statement check returns net results for respective periods and equals the balances in account 5003 "Current year retained earnings." So we can conclude that the list of income statement accounts is also complete. Both checks did not identify any errors and we can safely move on with any further procedures on the comparative trial balance.
Expanding the Trial Balances File
The two trial balances are used as an example. If you need to build a comparative trial balance for more periods, just add them to the same database in Step 3 and enter appropriate dates in column F. Pivot table will automatically arrange all such TB Periods horizontally on tab Step 4-Pivot for you.
Once again, after some practice, the above procedure should take 5-7 minutes to complete depending on how clean the initial trial balances are.
Page 7 of 7
Location