First Accounting Cycle, 2 -- Sorting and Grouping Data Using Excel
Summary
This two-part module is designed to go along with accounting cycle problems in the first chapter of any accounting text. Typically, these problems are complete accounting cycles, but are set up in a table format with all accounts listed at the top--which is great for processing transactions, but resistant to efficiently solving on the computer.
In Part 1, my goal was to automate the data entry process by allowing the user to refer to each account by account number. This necessitates having a written record of the account numbers in the chart of accounts, which could be delivered to students in the form of a handout, or just printed out from the Powerpoint presentation.
The more dramatic aspect of the module is covered in Part 2, and shows one way in which the transaction balances can be summarized and manipulated--using Excel pivot tables. After data entry in the journal sheet, the student can quickly create a list of account balances, and then selectively manipulate account categories to create an income statement, OE statement, and balance sheet.
The key element in sorting and grouping the accounts and their balances is the use of account codes. Each account in a general ledger is identifiable based on its membership in one of five categories--asset, liability, owner's equity, revenue or expense. By using a one-letter code for each account, the pivot table can easily sort, group and total the amounts for that group.
Although this example is from the realm of accounting, the basic idea of classifying data has numerous applications in business, the social sciences and mathematics.
Collapse
Learning Goals
The accounting-related goals of this module are:
To get the student to perform a complete accounting cycle, from analysis, to recording, to completion of the month's transaction, along with construction of rudimentary income statement, owner's equity statement, and balance sheet.
In a more general sense, this module demonstrates an approach to organizing data using a classification strategy, and then using the pivot table tool to flexibly summarize a numeric variable based on the desired classification. Moreover, classifications can be combined if desired.
The Excel-related goals of this module are:
1. to gain experience working with multiple worksheets that are related to one another.
2. to use a lookup function (Part 1) for purposes of reducing data entry.
3. to practice incidental Excel tasks such as copy, paste and summing.
4. to become familiar with the basic usage of a pivot table to sort, summarize and manipulate data.
Context for Use
This module would be most suitable in chapter 1 of any financial accounting course. Most beginning accounting texts conclude chapter 1 with a set of transaction tables, and the module is built for this type of exercise.
I suggest that students be assigned one or two of these problems to do by hand, in order to practice increases and decreases in assets, liabilities and owner's equity accounts. Ideally, this module would be used in week two of the course--perhaps in a computer lab situation.
Students will need to know the formulas for the income statement, owner's equity statement and balance sheet in order to create the pivot table reports.
I suggest that the module be one of the last assignments in chapter 1, just prior to the discussion of debits and credits--if debits and credits are to be covered. Some instructors might continue to use the table approach in subsequent chapters, if the instructor is not planning to cover debits and credits.
Description and Teaching Materials
This module is a PowerPoint presentation with embedded spreadsheets. Students work though the presentation, answering questions and developing the spreadsheet. If the embedded spreadsheets are not visible, save the PowerPoint file to a disk and open it from there.
This PowerPoint file is the student version of the module. An instructor version is available by request. The instructor version includes the completed spreadsheet. Send your request to Len Vacher (vacher@usf.edu) by filling out and submitting the Instructor Module Request Form.
Teaching Notes and Tips
I encourage instructors to provide advance information to students prior to assigning this module. A handout which lists the transactions to be entered, along with suggested account numbers will give students the required information to analyze and enter the transactions. I also suggest showing students a little of the lab on the day before they are to perform the module.
A word about the VLOOKUP--it is useful to think about the ways in which the VLOOKUP can fail--like if the account number is incorrectly typed or doesn't exist on the account list. This might be vexing the first time around.
Having taught pivot tables in a lab situation on many occasions, I suggest that the instructor will learn far more than the students about pivot tables, after witnessing the creative ways in which students move the data around(!). One characteristic of pivot tables that is not immediately obvious is that if you place the cursor outside the pivot table, the field list disappears. Click on the pivot table area and it will return to the screen.
Remember that the pivot table can be undone quite easily by dragging the variables from the pivot table back to the field list. This is a useful tactic for trying to reconstruct how the table got built in the first place. Also, it is always ok to tell a student you'd like to look at their file a little bit back at the office before recommending a course of action--in cases where something's not working right, or gives strange results.Assessment
The end-of-module questions can be used for assessment.
The instructor version contains a pre-test.
References and Resources
Much has been written on the topic of pivot tables. Two books that explore the subject are Pivot Table Data Crunching, by Bill Jelen, and A Complete Guide to Pivot Tables, by Paul Cornell.
Additionally, I have written a short course on the subject of pivot tables, which can be found at Pivot Table Presentation and contains elementary examples of pivot table operations.