Date
JE#
1/3/2022
1/3/2022
1/3/2022
1/3/2022
1/3/2022
1/3/2022
1/5/2022
1/5/2022
1/6/2022
1/6/2022
1/7/2022
1/7/2022
1/7/2022
1/7/2022
1/8/2022
1/8/2022
1/8/2022
1/8/2022
1/9/2022
1/9/2022
1/9/2022
1/9/2022
1/9/2022
1/9/2022
1/10/2022
1/10/2022
1/10/2022
1/10/2022
1/11/2022
1/11/2022
1/11/2022
1/11/2022
1/12/2022
1/12/2022
1/13/2022
1/13/2022
1/13/2022
1/14/2022
1/14/2022
1/14/2022
1/14/2022
1/15/2022
1/15/2022
1/15/2022
1/15/2022
1/16/2022
Account Name
Debit
Credit
Entered
0 Cash
50,000
VR
0 Common Stock
50,000 VR
1 Travel Expense
250
VR
1 Cash
250 VR
2 Building
32,000
VR
2 Cash
32,000 VR
3 Utilities Expense
1,600
VR
3 Cash
1,600 VR
4 Equipment
19,300
VR
4 Notes Payable
19,300 VR
5 Salaries Expense
5,200
VR
5 Cash
5,200 VR
6 Payroll Tax Expense
1,100
VR
6 Cash
1,100 VR
7 Accounts Receivable
10,000
VR
7 Service Revenue
10,000 VR
8 Cash
12,000
VR
8 Notes Payable
12,000 VR
9 Repairs Expense
7,000
VR
9 Cash
7,000 VR
10 Accounts Receivable
20,000
VR
10 Service Revenue
20,000 VR
11 Cash
10,000
VR
11 Accounts Receivable
10,000 VR
12 Utilities Expense
1,300
MW
12 Cash
1,300 MW
13 Rent Expense
7,000
MW
13 Cash
7,000 MW
14 Notes Payable
1,000
MW
14 Cash
1,000 MW
15 Accounts Receivable
15,000
MW
15 Service Revenue
15,000 MW
16 Notes Payable
3,000
MW
16 Cash
3,000 MW
17 Supplies
400
MW
17 Computer Supplies
600
MW
17 Cash
1,000 MW
18 Salaries Expense
5,200
MW
18 Cash
5,200 MW
19 Payroll Tax Expense
1,100
MW
19 Cash
1,100 MW
20 Travel Expense
2,025
VR
20 Cash
2,025 VR
21 Insurance Expense
1,200
VR
21 Cash
1,200 VR
22 Cash
23,000
VR
Approved
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
DH
1/16/2022
1/16/2022
1/16/2022
1/16/2022
1/16/2022
1/16/2022
1/16/2022
1/17/2022
1/17/2022
1/17/2022
1/17/2022
1/19/2022
1/19/2022
1/21/2022
1/21/2022
1/21/2022
1/21/2022
1/22/2022
1/22/2022
1/22/2022
1/22/2022
1/22/2022
1/22/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/23/2022
1/24/2022
1/24/2022
1/24/2022
1/24/2022
1/24/2022
1/24/2022
1/25/2022
1/25/2022
1/25/2022
1/25/2022
1/25/2022
1/25/2022
1/28/2022
22 Service Revenue
23 Training Expense
23 Cash
24 Misc. Expense
24 Cash
25 Travel Expense
25 Cash
26 Travel Expense
26 Cash
27 Travel Expense
27 Cash
28 Cash
28 Accounts Receivable
29 Salaries Expense
29 Cash
30 Payroll Tax Expense
30 Cash
31 Accounts Receivable
31 Service Revenue
32 Cash
32 Service Revenue
33 Travel Expense
33 Cash
34 Training Expense
34 Cash
35 Misc. Expense
35 Cash
36 Travel Expense
36 Cash
37 Travel Expense
37 Cash
38 Travel Expense
38 Cash
39 Training Expense
39 Cash
40 Cash
40 Service Revenue
41 Travel Expense
41 Cash
42 Cash
42 Accounts Receivable
43 Computer Supplies
43 Cash
44 Supplies Expense
44 Supplies
45 Salaries Expense
500
50
200
2,025
250
2,000
5,200
1,100
1,200
2,400
272
500
125
350
2,025
250
400
2,200
250
12,000
200
255
5,200
23,000 VR
VR
500 VR
VR
50 VR
VR
200 VR
MW
2,025 MW
VR
250 VR
VR
2,000 VR
MW
5,200 MW
MW
1,100 MW
MW
1,200 MW
MW
2,400 MW
MW
272 MW
VR
500 VR
VR
125 VR
VR
350 VR
VR
2,025 VR
VR
250 VR
MW
400 MW
MW
2,200 MW
MW
250 MW
MW
12,000 MW
MW
200 MW
MW
255 MW
MW
DH
DH
DH
DH
DH
DH
DH
DH
DH
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
VR
VR
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
1/28/2022
1/28/2022
1/28/2022
1/29/2022
1/29/2022
1/29/2022
1/29/2022
1/29/2022
1/29/2022
1/30/2022
1/30/2022
1/31/2022
1/31/2022
45 Cash
46 Payroll Tax Expense
1,100
46 Cash
47 Bad Debt Expense
6,000
47 Allowance for Doubtful Accounts
48 Depreciation Expense
1,000
48 Accumulated Depreciation
49 Training Expense
400
49 Cash
50 Insurance Expense
200
50 Prepaid Insurance
51 Cash
21,000
51 Service Revenue
5,200 MW
MW
1,100 MW
VR
6,000 VR
VR
1,000 VR
VR
400 VR
VR
200 VR
VR
21,000 VR
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
AC
DH
DH
Excel HW#5
Valerie Li
Purpose: Use pivot table to analyze accounts receivable. Book page p.263
Data: Lab 2 data. (posted on Canvas)
Analysis:
1. Compare the names entered and approved journal entries to see if adequate separation of duties
are maintained.
Required:
Follow book example p270-273 (Lab2) to use Excel pivot table and conduct analysis mentioned above.
Excel5-lab2 video on Canvas walks you through every step listed below.
Step 0: Understand the data
“Entered”: Authorized personnel for this function are “VR” and “MW”.
“Approved”: Authorized personnel for this function are “AC” and “DH”.
Step 1: Insert pivot table
Step 2: drag [approved] to Columns and [Entered] into Rows. And [Debit] into ∑Values.
To be submitted:
Please submit your assignment in WORD file. Put your screen shot in a WORD file, clearly label your
screenshot such as “Step1”. Put the homework title, your section, and your name in the Title of the file
and name the file the same way “HWTitle_01-JamesBond”. Use your name and section number as the
header in the Word file, such as “01_JamesBond”. Insert page numbers. Points will be deducted if not
following the instructions and you will receive a 0 if not submitted in a WORD file.
1. Screenshot after you finish Step 2. Please show both the left side of the pivot table and the right
side which shows how you arranged your pivot table.
2. Screenshot the entries that violate the segregation of duties
Include your name somewhere in your screenshot
*when you screenshot a large dataset, just screen shot the first 10 or more rows.