Excel data file

Purpose: Use pivot table to analyze accounts receivable. Book page p.263
Data: Lab 1 data. (Canvas)
Analysis:
1. Put receivables in six 30-day buckets.
2. Detail the receivables from 61-90 days bucket.
Required:
Follow book example p263-269 (Lab1) to use Excel pivot table and conduct analysis mentioned above.
Screenshot what you have in excel after completing every step below and submit a WORD file.
Step 0: Understand the data
Step 1: Calculate “DAYS PAST DUE”
Step 2: turn the spreadsheet into a table and go to pivot table page by clicking “summarize with pivot
table
Step 3: drag “days past due’ to Rows and “invoice amount into ?Values”;
Step 4: Select groups to group receivables into six 30-day bucket
Step 5: Format the pivot table
Step 6: Pull out receivables in 61-90 day time period
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 each step listed above except for step 0.
– Include your name inside your screenshot for Step1, 5, and 6, meaning that you need to type
your name in the spreadsheet so that you can screenshot it. Points will be deducted if no name
is shown anywhere in the spreadsheet.

Customer
InvoiceAmount
Due Date
Column1
Home Depot
377.6 12/2/2021
Porsche
1259.97 9/28/2021
Burberry
1476.09 8/14/2021
AT&T
331.8 11/24/2021
Porsche
1122.46
9/5/2021
Porsche
838.18 10/3/2021
Beko
387.23
9/6/2021
BMW
1389.2 9/10/2021
United Parcel Service
685.1 10/31/2021
Deere & Company
1424.71 9/27/2021
Beko
772.14 12/8/2021
Citigroup
981.87 12/14/2021
Samsung Group
957.07 11/29/2021
General Electric
694.29 11/15/2021
Nokia
279.42 8/21/2021
Citigroup
994.35 10/30/2021
Xerox
230.98 10/16/2021
Starbucks
419.86 10/8/2021
Coca-Cola
1249.67 7/27/2021
3M
1469.06 11/4/2021
Ralph Lauren
541.26
9/6/2021
Porsche
1427.79 11/22/2021
The Walt Disney Company
893.4
9/8/2021
KFC
1093.72 10/8/2021
MTV
1031.88 11/15/2021
Vodafone
654.88
9/6/2021
Harley-Davidson
289.16
8/2/2021
Moet et Chandon
392.53 8/17/2021
Canon
1447.32 12/22/2021
Yahoo!
899.53 8/17/2021
Avon
717.65 10/12/2021
Gap Inc.
1385.16 11/18/2021
VISA
723.94 10/10/2021
Honda Motor Company
1220.03 11/3/2021
Kleenex
664.25 8/12/2021
Audi
481.84 8/10/2021
Credit Suisse
731.9 10/4/2021
Siemens AG
1162.88 12/11/2021
Intel Corporation
1210.11 9/29/2021
Nintendo
715.55 8/30/2021
Chase
1369.32 8/20/2021
Kellogg Company
982.75 8/20/2021
Microsoft
325.08 10/29/2021
Hermes
887.48 9/28/2021
Zara
751.44 8/16/2021
Xerox
471.33 7/25/2021
Today’s Date
Mastercard
Johnson & Johnson
Jack Daniel’s
Hyundai
Amazon.com
eBay
L’Oreal
Nissan Motor
Oracle Corporation
Morgan Stanley
Google Allianz
Toyota Motor Coporation
The Walt Disney Company
SAP
Jack Daniel’s
Kellogg Company
Samsung Group
IBM
Mercedes-benz
McDonald’s
Volkswagen Group
eBay
Google
Kia Motors
IBM
PepsiCo
Smirnoff
3M
Canon
Intel Corporation
H&M
American Express
Shell Oil Company
Johnson & Johnson
Intel Corporation
Blackberry
MasterCard
Nissan Motor
Adidas
Siemens AG
Mercedes-benz
Caterpillar Inc.
Pampers
General Electric
Toyota Motor Coporation
Apple Inc.
HSBC
1442.87
1303.01
770.13
1091.05
848.22
806.86
1110.77
1427.16
406.57
747.12
373.59
1415.76
308.63
526.42
1117.75
612.28
1348.39
1442.24
890.02
1158.98
439.83
1440.03
999.24
1183.68
812.62
461.81
1011.41
653.44
1204.74
1409.63
1478.8
656.28
1056.92
1180.65
249.81
768.3
810.28
1062.48
1434.44
1241.01
326.61
894.04
364.97
288.5
696.98
296.18
438.02
8/6/2021
11/19/2021
9/20/2021
9/8/2021
10/10/2021
8/13/2021
7/30/2021
8/15/2021
10/3/2021
12/15/2021
9/12/2021
11/27/2021
11/30/2021
9/29/2021
10/5/2021
12/3/2021
9/8/2021
12/20/2021
9/29/2021
9/11/2021
11/5/2021
8/18/2021
9/15/2021
12/8/2021
8/16/2021
9/23/2021
10/30/2021
8/5/2021
8/20/2021
12/12/2021
7/27/2021
12/3/2021
11/27/2021
8/24/2021
9/8/2021
8/8/2021
10/20/2021
11/21/2021
11/13/2021
11/6/2021
11/3/2021
8/21/2021
9/10/2021
8/17/2021
11/26/2021
7/28/2021
9/4/2021
Bucci
Bucci
eBay
Nintendo
Pizza Hut
Corona
Beko
Oracle Corporation
Morgan Stanley
Google Allianz
Toyota Motor Coporation
The Walt Disney Company
SAP
Jack Daniel’s
Kellogg Company
Samsung Group
IBM
Mercedes-benz
McDonald’s
Volkswagen Group
eBay
Google
Kia Motors
IBM
PepsiCo
Smirnoff
3M
Canon
Intel Corporation
H&M
American Express
Shell Oil Company
Home Depot
Porsche
Burberry
AT&T
Porsche
Porsche
Beko
BMW
United Parcel Service
Deere & Company
Beko
Citigroup
Samsung Group
General Electric
Nokia
1038.16
960.3
1004.74
1139.62
834.76
1054.12
262.04
440.72
781.68
273.55
1264.21
1020.57
824.93
311.53
868.49
1103.29
710.44
707.28
411.64
755.95
339.92
1309.85
336.31
1384.21
1234.88
506.43
993.83
411.27
546.41
1236.73
235.76
1101.52
1463.2
684.5
949
1117.61
383.24
1461.73
1368.48
592.69
1135.33
1053.65
808.94
740.98
784.24
1378.98
880.95
9/25/2021
8/6/2021
12/2/2021
10/12/2021
12/13/2021
10/24/2021
10/10/2021
9/4/2021
8/23/2021
12/6/2021
10/6/2021
10/9/2021
10/16/2021
12/15/2021
10/20/2021
11/9/2021
10/16/2021
10/3/2021
9/30/2021
9/5/2021
9/16/2021
12/6/2021
11/23/2021
12/16/2021
10/5/2021
8/11/2021
11/10/2021
10/23/2021
10/20/2021
11/15/2021
12/3/2021
10/23/2021
8/9/2021
10/22/2021
10/25/2021
11/25/2021
7/26/2021
11/28/2021
7/22/2021
10/18/2021
10/26/2021
11/11/2021
7/21/2021
10/29/2021
8/11/2021
12/2/2021
9/15/2021
Citigroup
Xerox
Starbucks
Coca-Cola
3M
Ralph Lauren
Porsche
The Walt Disney Company
KFC
MTV
Vodafone
Harley-Davidson
Moet et Chandon
Canon
Yahoo!
Avon
Gap Inc.
VISA
Honda Motor Company
Kleenex
Audi
Credit Suisse
Siemens AG
Intel Corporation
Nintendo
Chase
Kellogg Company
Microsoft
Hermes
Zara
Xerox
Mastercard
Johnson & Johnson
Jack Daniel’s
Hyundai
Amazon.com
eBay
L’Oreal
Nissan Motor
Johnson & Johnson
Intel Corporation
Blackberry
MasterCard
Nissan Motor
Adidas
Siemens AG
Mercedes-benz
623.89
1096.4
720.71
259.61
729.16
810.32
1505.35
317.05
412.91
660.57
1116.4
1225.94
658.05
979.4
959.38
1282.1
325.56
607.87
1065.24
510.38
522.79
800.47
1037.44
349.12
1351.7
1352.32
1444.58
631.99
619.12
258.13
707.38
468.63
796.25
1365.88
746.29
1271.11
293.66
528.29
700.7
406.6
493.27
1259.08
1400.18
365.46
302.34
847.28
912.97
8/28/2021
10/23/2021
9/1/2021
10/8/2021
7/25/2021
8/21/2021
11/3/2021
11/24/2021
10/12/2021
8/14/2021
8/2/2021
11/29/2021
12/14/2021
11/15/2021
11/16/2021
10/13/2021
11/22/2021
8/25/2021
11/27/2021
9/1/2021
11/17/2021
12/14/2021
12/18/2021
10/24/2021
11/11/2021
8/10/2021
10/6/2021
10/3/2021
8/15/2021
9/17/2021
8/1/2021
9/28/2021
9/4/2021
10/20/2021
8/23/2021
8/21/2021
8/29/2021
10/19/2021
10/25/2021
9/29/2021
8/28/2021
12/16/2021
9/30/2021
9/24/2021
8/4/2021
10/9/2021
10/19/2021
Caterpillar Inc.
Pampers
General Electric
Toyota Motor Coporation
Apple Inc.
HSBC
Bucci
Bucci
eBay
Nintendo
Pizza Hut
Corona
Beko
233.8
1021.21
411.54
1258.86
571.12
1306.32
262.1
1018.25
652.11
566.79
1036.24
1300.85
375.42
8/19/2021
12/13/2021
11/13/2021
8/31/2021
11/18/2021
9/11/2021
12/8/2021
12/12/2021
10/19/2021
8/20/2021
10/21/2021
11/18/2021
12/6/2021
12/31/2021
Excel HW#1
Valerie Li
Purpose: Use pivot table to analyze accounts receivable. Book page p.263
Data: Lab 1 data. (Canvas)
Analysis:
1. Put receivables in six 30-day buckets.
2. Detail the receivables from 61-90 days bucket.
Required:
Follow book example p263-269 (Lab1) to use Excel pivot table and conduct analysis mentioned above.
Screenshot what you have in excel after completing every step below and submit a WORD file.
Step 0: Understand the data
Step 1: Calculate “DAYS PAST DUE”
Step 2: turn the spreadsheet into a table and go to pivot table page by clicking “summarize with pivot
table
Step 3: drag “days past due’ to Rows and “invoice amount into ∑Values”;
Step 4: Select groups to group receivables into six 30-day bucket
Step 5: Format the pivot table
Step 6: Pull out receivables in 61-90 day time period
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 each step listed above except for step 0.
– Include your name inside your screenshot for Step1, 5, and 6, meaning that you need to type
your name in the spreadsheet so that you can screenshot it. Points will be deducted if no name
is shown anywhere in the spreadsheet.
*when you screenshot a large dataset, just screen shot the first 10 or more rows.
* Note that in the video, I misspoke and said that it is okay to miss Step 3 and 4 screenshots,
which was not what I meant, and it is hard to change. So you need to have step 3 and 4 screenshots.