Pivot Tables: A Means to Quick, Accurate Trial Balances

By Lucille G. Montondon and Treba L. Marsh

E-mail Story
Print Story
APRIL 2006 - Technology is valuable when it allows necessary tasks to be done more quickly and accurately. Accountants have benefited from spreadsheet software that has given them the ability to sum thousands of numbers in seconds, link cells, create macros, and perform other tasks. Schools of business have added courses to prepare graduates for this new workplace. They have also introduced computer assignments into almost every course, providing the opportunity for students to hone their skills. Unfortunately, in many cases, these assignments are nothing more than preparing journal entries in a spreadsheet. Spreadsheet functions are so varied that even experienced users seldom learn the shortcuts for simplifying a task.

One example of this is the pivot table function. This tool for managing large data files can, on a simpler scale and with a few adjustments to standard practice, be used to develop trial balances from journal entries almost instantly. The pivot table eliminates the need for T-accounts; it can be used for unadjusted trial balances, preclosing trial balances, and postclosing trial balances. It also provides a means of making corrections to journal entries and, with one click, having the trial balances adjusted to include the new information.

In classroom settings, pivot tables can be introduced within a week of teaching journal entries. By eliminating the tedium of obtaining the various trial balances, more time is available for analysis and discussion of the results.

The data require labeling two columns in a spreadsheet “Accounts” and “Amounts”; the account names are placed in one column and the amounts in a contingent column (see Exhibit 1). Use positive numbers for all debits and negative numbers for credits. There can be no open rows between the journal entries, but alternate journal entries may be highlighted to make distinguishing them easy. Other columns can be used for items such as dates and notes.

When all journal entries have been prepared, highlight the two columns, account names and amounts, including the column titles. Click on “data” and select “pivot tables” from the drop-down menu. The Pivot Table Wizard Step 1 box appears, indicating that you have chosen to use this spreadsheet to create a pivot table (see Exhibit 2). Click “next.” Because you have highlighted or selected the cells you wish to include in the pivot table, the Step 2 box will note the cell addresses that have just been selected. Again, click “next.” Step 3 of the Pivot Table Wizard allows you to choose where to place the table. It can be on a new worksheet or on the same worksheet as the journal entries. If you select “existing worksheet,” type in the top left cell address where
you’d like the table to begin, and click “finish.”

Two boxes appear. The smaller one on the left will have the names of the column headings, and the larger one is a table with spaces for row, column, and data items (see Exhibit 3). Drag and drop “Account” to the row part of the table. Drag and drop “Amount” to the “data” part of the table. The result is a trial balance with the accounts in alphabetical order. The balance is reported at the bottom (see Exhibit 4). To add dollar signs, highlight the numbers and click on the dollar sign. Adjust the column size as needed.

By right-clicking on the pivot table, another menu appears that allows features such as hiding, reordering, and refreshing. The refresh option allows for changes to the accounts and amounts in the journal entries to be immediately incorporated into the pivot table/trial balance. Additions of journal entries, however, will require the creation of a new pivot table.

By linking the pivot table results to the financial statements, a pivot table allows an easy demonstration of the effect of a single transaction on the results.

See the Sidebar for these instructions on using pivot tables.

The Classroom and Beyond

Technology can be a valuable ally by eliminating the tedium of T-accounts and subsequent trial balances. Students often dread the time-consuming nature of journal entry assignments. Pivot tables require them to input the journal entries, but the remaining steps can be done quickly and accurately. In the classroom, this leaves more time for explanations of the effects of the transactions and for analyses of how a transaction affects the financial statements. Beyond the classroom, professionals working with large databases are likely to find pivot tables useful for partitioning and analyzing multiple columns of data.


Lucille G. Montondon, PhD, is a professor in the college of business at Texas State University San Marcos, San Marcos, Texas, and
Treba L. Marsh, PhD, CPA, is an associate professor in the department of Accounting at Stephen F. Austin State University, Nacogdoches, Texas.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.

©2009 The New York State Society of CPAs. Legal Notices