First Accounting Cycle, 1 -- Classifying Accounting Data Using Excel
This is part one of a two-part module. My goal was to develop a small general ledger system that would allow students to use Excel to journalize and post elementary accounting transactions–without resorting to the use of programming techniques. The teaching moment for these modules is in chapter 1 of any beginning accounting text–in which students use a transaction table, with accounts across the top, and focus on the increases and decreases of individual accounts.
This module, Part 1, focuses on the data entry aspect of solving a transaction table problem. Two sheets are used: In Sheet1, a Chart of Accounts is set up, consisting of account number, account name and account type. Sheet2 is the Journal, in which the increases and decreases are entered, in date order.
My goal was to automate the data entry process by allowing the user to refer to each account by account number. For best results, each student should have a written record of the account numbers in the chart of accounts. I recommend that a handout be distributed to the students for ease of data entry.
Using Excel, the student can simply enter the date, the account number affected, and the dollar amount of the increase or decrease. The account number is then used as a VLOOKUP argument, to retrieve the account name and account type from the Chart sheet.
There are several worthy teaching goals in Part 1. First, I wanted to demonstrate the nature of organizing data for the sake of efficient data entry–suggesting that a lookup strategy can provide quicker and more accurate results than simply typing them. Second, I wanted to provide students with practice using one of the most time-saving functions in Excel–the VLOOKUP. An instructor could come up with many worthy examples of how a lookup function could be devised–whether in accounting, or mathematics, or the sciences–to automatically reference related data.
Finally, I wanted to demonstrate how a pivot table can be used to summarize a set of table data, by using the column headers as variable names. The pivot table summarization is the topic of Part 2 of this module.
Using a pivot table, the student can quickly create a list of account balances, and then selectively manipulate account categories to create an income statement, owner's equity statement, and balance sheet.
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 transactions, along with construction of rudimentary financial statements. The recording would take place in Part 1; the financial statement summarization is the subject of Part 2.
Beyond the accounting related aspects, I'm hoping that instructors and students will imagine the use of efficient data entry techniques involving lookups, in other disciplines. The idea of placing a code in the data record that can be used to automatically access related data is a fundamental data processing concept. And, owing to the row and column makeup of Excel, data can be efficiently and accurately accessed.
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 for purposes of reducing data entry and increasing data accuracy.
3. (in Part 2), to become familiar with the basic usage of a pivot table to sort, summarize and manipulate data.
4. to practice incidental Excel tasks such as copy, paste and summing.
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–to show how Excel can automate the accounting process.
For part 1, students will need to understand account membership in the categories of assets, liabilities and owner's equity.
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 (firstname.lastname@example.org) 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. Ideally, a classroom with computer, overhead projector and screen would allow the instructor to conduct a short demonstration of the process.
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, for example. This might be vexing the first time around.
In designing the assessment, I considered two competing objectives: 1) the need to make the assessment relevant to the goals of chapter 1 of an accounting text; and 2) relating the module to computational literacy.
Of course, the fundamental truth in accounting is that assets must at all times equal liabilities + owner's equity. A student who successfully completes the module will have entered transactions that prove this formula.
Additionally, the accounting cycle must allow for transactions to be entered for the temporary accounts–revenues, expenses and drawing. However, at the end of the accounting period (in this context, one month) the effects of the temporary accounts must be combined with the capital account.
From the larger standpoint, the illustration provided for the accounting application can be extended to other applications–in fact, any application in which a lookup can advantageously obviate data entry.
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.