| Pivot
Tables: A Means to Quick, Accurate Trial Balances
By
Lucille G. Montondon and Treba L. Marsh
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. |