Transcript SSAC2007.HF5635.MPO1.1

SSAC2007.HF5635.MPO1.1
First Accounting Cycle—1, Classifying
Accounting Data Using Excel
In this module, students will use a simple
Excel model to enter financial
transactions. A table lookup function is
employed to allow classification of
entries by account type. The module is
designed to work with Chapter 1 of any
beginning accounting text.
Core Quantitative Concept
Manipulating Tabular Data
Specifically –
Organizing and classifying data
Visual display of data
Lookup Function (Excel)
Prepared for SSAC by
Michael P. O’Neill and Yilin Sun, Seattle Central Community College
© The Washington Center for Improving the Quality of Undergraduate Education. All rights reserved. 2007
1
Overview of Module
The fundamental accounting cycle consists of 1. recording
transactions in a journal; 2. classifying transactions by account; and
3. summarizing effects of the transactions in the form of an income
statement, owner’s equity statement, and balance sheet. These tasks
can easily be accomplished using Excel. Using predefined account
types, journal entries can be classified as to whether they affect an
Asset, a Liability, an Owner’s Equity account, a Revenue, or an
Expense.
Slide 3 The Problem Statement
Slide 4 Chart of Accounts
Slides 5 Account Codes
Slides 6,7 Chart of Accounts
Slides 8-13 Creating the Journal in Excel
Slides 14-21 Assessments and References
2
Problem
You invest money in a business and
operate it for one month. Can you create
a journal-entry table that classifies each
transaction by its account type—asset,
liability, owner’s equity, revenue, or
expense?
Can you create a data-entry system that indicates, for each
entry, the accounting element affected?
3
The Accounts We Will Use—The Chart of Accounts
•
Assets (Account numbers 100-199)
–
–
–
–
•
101 Cash: Cash on hand or in a checking account
104 Accounts Receivable: amounts owed to you, the owner
106 Supplies: Items that are used up in the production of revenue
110 Equipment: Vehicles, computers, and other tools for producing revenue
Liabilities (Accounts 200-299)
– 201 Accounts Payable: Short term debts you owe
– 202 Notes Payable: a bank loan for which you sign a promissory note
•
Owner’s Equity: what the owner owns after the debts have been paid—
includes four separate items:
–
–
–
–
301 Owner’s Capital—the owner’s claim in the business
302 Owner’s Drawing—the owner’s withdrawals from the business
401 Service Revenue—used when the owner provides a service or a product
501-599: Expenses: costs incurred in the process of earning revenue. Examples
include rent, advertising, salaries, utilities, travel expenses.
4
Classifying the Accounts Using a Code
In addition to the account numbering system, we will need to add a
classification CODE so that each entry can be categorized so that we can
create financial reports—income statement, balance sheet and owner’s equity
statement.
ASSETS
Account numbers between 100 and 199 CODE = “A”
LIABILITIES
Account numbers between 200 and 299 CODE = “L”
OWNER’S EQUITY:
The Capital account: 301
CODE = “OE”
The Drawing account 302
CODE = “OE”
Revenue accounts
401-499
CODE = “R”
Expense accounts
501-599
CODE = “E”
NOTE: Although the revenues and expenses are owner’s equity accounts,
we must code them separately in order to create an income statement. The
income statement provides information to show how the owner of the business
increased the owner’s equity by providing a service or product to customers.
5
The Complete Chart of Accounts
Note: Print out this chart of accounts for the next step.
Number
Account
Type
101
Cash
A
104
Accounts Receivable
A
106
Supplies
A
110
Equipment
A
201
Accounts Payable
L
202
Note Payable
L
301
Johnson Capital
OE
302
Johnson Drawing
OE
401
Service Revenue
R
501
Rent Expense
E
502
Advertising Expense
E
503
Utilities Expense
E
6
Create the Chart of Accounts in Sheet1
Open a new Excel worksheet, and in Sheet1, type in the following Chart of
Account data. Create three columns—Number, Account and Type. Use the
account numbering scheme on the previous slide. At the bottom of your sheet,
rename the sheet tab from Sheet1 to Chart. To do so, double-click the tab
“Sheet1”, and type in “Chart”. Note that the account data (not including the
headers) occupies Block B3:D14 of Chart and occupies three columns.
A
B
C
2 Number Account
101 Cash
3
104 Accounts Receivable
4
106 Supplies
5
110 Equipment
6
201 Accounts Payable
7
202 Note Payable
8
301 Johnson Capital
9
302 Johnson Drawing
10
401 Service Revenue
11
501 Rent Expense
12
502 Advertising Expense
13
503 Utilities Expense
14
D
Type
A
A
A
A
L
L
OE
OE
R
E
E
E
E
Type in the account data, with
headers at the top. Observe
that the Number, Account, and
Type are located in Block
B3:D14. Rename Sheet1 to
Chart by double-clicking on it.
Type this data into Excel
Boldface headers
7
The Transactions for January
Now that we have a Chart of Accounts, our next task is to
enter the transactions for the month of January. Here are
the transactions that occurred during the month.
Print out this list of transactions so that you can enter them into the worksheet.
1/1/07: Johnson invested $10,000 cash in the business, Johnson Consulting.
1/2/07: Johnson borrowed $20,000 from Bank of America and issued a note
payable in that amount.
1/3/07: Johnson purchased equipment for $5,000; the account must be paid in
30 days.
1/4/07: Johnson paid rent for January in cash, $1,000.
1/5/07: Johnson performed consulting services for a client in the amount of
$2,000. Cash of $1,000 was received; the rest is due in 30 days.
1/6/07: Johnson placed an advertisement in the Seattle Times. The cost was
$300, which is payable in 30 days.
1/7/07: Johnson withdrew $500 for personal use, in cash.
8
Create The Journal in Sheet2
Double-click on Sheet2, and rename it “Journal”. We will create a Journal here to
record the transactions. The first transaction is: on 1/1/07, Johnson invested
$10,000 cash in the business, Johnson Consulting. Type in date and account
number, as shown below.
A
B
C
2 Date
Number Account
3
1/1/2007
101 Cash
4
5
6
7
8
9
10
11
12
13
14
D
E
Type
F
Amount
10000
Type this in
Use the VLOOKUP formula
A
B
C
2 Number Account
3
101 Cash
4
104 Accounts Receivable
5
106 Supplies
6
110 Equipment
7
201 Accounts Payable
8
202 Note Payable
9
301 Johnson Capital
10
302 Johnson Drawing
11
401 Service Revenue
12
501 Rent Expense
13
502 Advertising Expense
14
503 Utilities Expense
D
Type
A
A
A
A
L
L
OE
OE
R
E
E
E
Here’s your account list for
reference.
In Cell D3, type: =VLOOKUP(C3,CHART!$B$3:$D$14,2,FALSE)
Use the “Chart” list to the right for reference.
9
A Few Words About the VLOOKUP function
A
B
C
2 Number Account
3
101 Cash
4
104 Accounts Receivable
5
106 Supplies
6
110 Equipment
7
201 Accounts Payable
8
202 Note Payable
9
301 Johnson Capital
10
302 Johnson Drawing
11
401 Service Revenue
12
501 Rent Expense
13
502 Advertising Expense
14
503 Utilities Expense
D
Type
A
A
A
A
L
L
OE
OE
R
E
E
E
The Chart contains a two
dimensional array of data,
from B3 to D14.
Note: we have put the account
numbers in numeric order in the
Chart, though the VLOOKUP
function will work even if they are
out of order…as long as you insert
FALSE in the rightmost position of
the VLOOKUP function.
A
B
C
2 Date
Number Account
3
1/1/2007
101 Cash
4
D
E
Type
F
Amount
10000
In the Journal, C3 contains the account number,
101.
In Journal, Cell D3, type:
=VLOOKUP(C3,CHART!$B$3:$D$14,2,FALSE)
Translation: Using the account number in C3,
(“101”), go to the Chart sheet, Block B3:D14;
look for a match in the leftmost column.
(Find “101”, in other words.)
If you find a match, bring back the item on the
same row as the match, but in the Column 2.
In this case, a match on “101” in Column 1
will bring back a value of “Cash”, the item in
Column 2.
10
Composing One More VLOOKUP for the Account Type
A
B
C
2 Number Account
3
101 Cash
4
104 Accounts Receivable
5
106 Supplies
6
110 Equipment
7
201 Accounts Payable
8
202 Note Payable
9
301 Johnson Capital
10
302 Johnson Drawing
11
401 Service Revenue
12
501 Rent Expense
13
502 Advertising Expense
14
503 Utilities Expense
D
Type
A
A
A
A
L
L
OE
OE
R
E
E
E
A
B
C
2 Date
Number Account
3
1/1/2007
101 Cash
D
E
Type
A
F
Amount
10000
We need one more VLOOKUP formula, to look up
the Account type (A, L, OE, R, or E).
The general formula for the VLOOKUP function is:
=VLOOKUP(searchvalue, array, column, FALSE)
Can you compose this formula? Your result for
To review, this is the array in the account 101 should result in a type of “A” as
Chart sheet, Cells B3:D14.
demonstrated above.
To look up Account, we used:
=VLOOKUP(C3,CHART!$B$3:$D$14,2,FALSE)
11
Copy the VLOOKUP formulas down the sheet.
A
B
C
D
2 Date
Number Account
3
1/1/2007
101 Cash
4
#N/A
5
#N/A
6
#N/A
7
#N/A
8
#N/A
9
#N/A
10
#N/A
11
#N/A
12
#N/A
13
#N/A
14
#N/A
E
Type
A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
F
Amount
10000
Type this in
Use the VLOOKUP formula
To automate the remainder of the data entry, we need to copy
the VLOOKUP formulas down the sheet. Place your cursor on
Cell D3. Click Edit | Copy; then select Block D4:D18 and click
Edit | Paste. Copy Cell E3 down the sheet in similar fashion.
Because there is no account number in column C, Excel will
report #N/A for the copied cells. The formula will execute
however, when you type in each account number.
12
The Journal, Complete
A
B
C
2 Date
Number
3
1/1/2007
101
4
1/1/2007
301
5
1/2/2007
101
6
1/2/2007
202
7
1/3/2007
110
8
1/3/2007
201
9
1/4/2007
501
10 1/4/2007
101
11 1/5/2007
101
12 1/5/2007
104
13 1/5/2007
401
14 1/6/2007
502
15 1/6/2007
201
16 1/7/2007
302
17 1/7/2007
101
18
D
Account
Cash
Johnson Capital
Cash
Note Payable
Equipment
Accounts Payable
Rent Expense
Cash
Cash
Accounts Receivable
Service Revenue
Advertising Expense
Accounts Payable
Johnson Drawing
Cash
E
Type
A
OE
A
L
A
L
E
A
A
A
R
E
L
OE
A
F
Amount
10000
10000
20000
20000
5000
5000
-1000
-1000
1000
1000
2000
-300
300
-500
-500
Type this in
Use the VLOOKUP formula
When complete, the journal should appear as shown above.
13
Assignment
Assignment
1. Describe the format of the VLOOKUP function in Excel.
2. What convenience does the VLOOKUP function provide—why not just
type in all the data needed, rather than using a VLOOKUP function?
3. Using the final set of journal entries, by what process would you
calculate the total Cash balance? What is the final Cash balance?
4. What is the net income for January? (Use the formula, Revenues
minus Expenses = Net Income)
5. Can you compute the final owner’s equity balance? Include the
beginning Capital, add Net Income and subtract Drawings.
6. Calculate the total assets figure, total liabilities, and owner’s equity.
Do Assets = Liabilities + Owner’s Equity?
14
Assignment, Continued
7. Go to a new sheet in your workbook, or use INSERT | WORKSHEET to
get a new worksheet. Enter the following transactions for February.
2/1/07 Johnson paid the account payable owed to the Seattle Times,
$300.
2/2/07 Johnson paid the rent for February, $1,000 cash.
2/3/07 Johnson paid $1,000 owed to the equipment dealer, in cash.
2/4/07 Johnson provided consulting services to a client in the amount
of $5,000. The client paid $3,000 immediately, in cash; the rest will be
received in one month.
2/25/07 Johnson paid an assistant $1,500 cash for work done in
February. You will need to add an account titled 511 Wages Expense to
your chart of accounts.
2/28/07 Johnson earned revenues of $3500 on account.
15
Assignment, Continued
8. Donna wishes to create a household budget, with costs generally
classified as follows:
Housing Expenses (H): Rent, Gas, Electricity, Water
Food Expenses (F): Meat, Vegetables, Dairy Products, Snacks
Transportation Expenses (T): Auto, Fuel, Bus Fares
Design a data entry form in Excel that will allow Donna to enter each
expense individually, but also categorize each expense into the its
general classification. The expense types and classification
information will go in Sheet1 and the cost data in Sheet 2.
For March, Donna’s estimates for the budget are: Rent=$2500,
Gas=$200, Electricity=$75, Water=$50. Food Expenses are
Meat=$100, Vegetables=$50, Dairy=$50. Transportation Expenses are:
Auto=$100, Fuel=$300, Bus=$40.
16
References
More information about the Excel VLOOKUP function
can be found in the Excel Help files.
17