Excel problem for accounting information systems 13th, chapter 13.13

Date	Invoice#	Supplier (Payee)	Amount	Address	City	State	Zip
2-Jan	7455	AAA Hardware	3,965.89	1422 Third Ave.	Tucson	AZ	85110
2-Jan	1744	Z Best Office Supplies	2,984.05	678 Quinto St.	Tucson	AZ	85116
2-Jan	1933	DFR, Inc.	950.00	PO Box 174583	Tucson	AZ	85123
3-Jan	2043	City Electric	1,144.97	PO Box 876543	Tucson	AZ	85111
3-Jan	7688	AAA Hardware	2,757.95	1422 Third Ave.	Tucson	AZ	85110
4-Jan	7701	AAA Hardware	1,499.00	1422 Third Ave.	Tucson	AZ	85110
4-Jan	1921	Office Supply Headquarters	2,255.63	936 First Ave.	Tucson	AZ	85110
4-Jan	21999	True Blue Supplies	1,984.48	PO Box 3345	Tucson	AZ	85120
4-Jan	45218	Quick Supply, Inc	2,001.04	666 Scary Lane	Tucson	AZ	85116
5-Jan	33456	Jones Office Supplies	2,015.25	77 Sunset Strip	Tucson	AZ	85110
5-Jan	1288	L&J	995.99	PO Box 174583	Tucson	AZ	85123
8-Jan	1455	Easy Green Supplies	888.45	444 My Way	Tucson	AZ	85114
8-Jan	23976	True Blue Supplies	599.75	PO Box 3345	Tucson	AZ	85111
8-Jan	18357	Great Western Supplies	1,825.12	PO Box 22445	Tucson	AZ	85111
8-Jan	1288	Midwest Copiers	1,944.50	246 Eighth Street	Tucson	AZ	85120
9-Jan	1945	Z Best Office Supplies	4,733.55	678 Quinto St.	Tucson	AZ	85116
9-Jan	7899	AAA Hardware	2,184.37	1422 Third Ave.	Tucson	AZ	85110
9-Jan	33852	Jones Office Supplies	1,789.22	77 Sunset Strip	Tucson	AZ	85110
9-Jan	45892	Quick Supply, Inc	3,145.69	666 Scary Lane	Tucson	AZ	85116
10-Jan	6429	Xanadu Supplies	845.77	8080 Fourth St.	Tucson	AZ	85110
10-Jan	2378	Office Supply Headquarters	1,035.67	936 First Ave.	Tucson	AZ	85110
10-Jan	22846	Quality Paper	2,000.00	PO Box 99334	Tucson	AZ	85111
10-Jan	1573	Easy Green Supplies	4,275.14	444 My Way	Tucson	AZ	85114
10-Jan	18933	Great Western Supplies	3,358.95	PO Box 22445	Tucson	AZ	85111
11-Jan	1934	DFR, Inc.	984.96	PO Box 174583	Tucson	AZ	85123
11-Jan	47001	Quick Supply, Inc	2,345.82	666 Scary Lane	Tucson	AZ	85116
12-Jan	27456	True Blue Supplies	1,862.65	PO Box 3345	Tucson	AZ	85111
12-Jan	47035	Quick Supply, Inc	1,914.18	666 Scary Lane	Tucson	AZ	85116
12-Jan	1935	D.F.R. Inc	979.95	PO Box 174583	Tucson	AZ	85123
12-Jan	1622	Easy Green Supplies	566.50	444 My Way	Tucson	AZ	85114
12-Jan	6803	Xanadu Supplies	759.95	8080 Fourth St.	Tucson	AZ	85110
12-Jan	975	Orange Cleaning	1,833.55	111 West Apple Rd.	Tucson	AZ	85114
16-Jan	1414	Midwest Copiers	2,468.95	246 Eighth Street	Tucson	AZ	85120
16-Jan	1234	Hard copy masters	1,776.83	1010 Heard St.	Tucson	AZ	85122
16-Jan		E Z Does It	1,583.95	55 Easy Street	Tucson	AZ	85122
16-Jan	2110	Z Best Office Supplies	2,488.95	678 Quinto St.	Tucson	AZ	85116
17-Jan	34468	Jones Office Supplies	1,635.95	77 Sunset Strip	Tucson	AZ	85110
18-Jan	47987	Quick Supply, Inc	1,718.95	666 Scary Lane	Tucson	AZ	85116
18-Jan	47987	Quick Supply, Inc	1,718.95	666 Scary Lane	Tucson	AZ	85116
18-Jan	1984	Fred's Toner	3,250.95	55 S. Van Buren	Tucson	AZ	85122
19-Jan	1847	Easy Green Supplies	2,846.50	444 My Way	Tucson	AZ	85114
19-Jan	31002	True Blue Supplies	4,193.77	PO Box 3345	Tucson	AZ	85111
19-Jan	1936	White shipping	2,255.50	PO Box 85287	Tucson	AZ	85111
19-Jan	1357	Hard copy masters	1,156.76	1010 Heard St.	Tucson	AZ	85122
19-Jan	1936	D.F.R.	999.95	PO Box 174583	Tucson	AZ	85123
22-Jan	3188	Office Supply Headquarters	4,321.55	936 First Ave.	Tucson	AZ	85110
22-Jan	49246	Quick Supply, Inc	1,234.56	666 Scary Lane	Tucson	AZ	85116
22-Jan	6999	Xanadu Supplies	2,844.77	8080 Fourth St.	Tucson	AZ	85110
22-Jan	34765	True Blue Supplies	3,328.44	PO Box 3345	Tucson	AZ	85111
23-Jan	2255	Z Best Office Supplies	4,172.65	678 Quinto St.	Tucson	AZ	85116
23-Jan	1975	White shipping	1,357.95	PO Box 85287	Tucson	AZ	85111
23-Jan	35859	True Blue Supplies	2,064.55	PO Box 3345	Tucson	AZ	85111
23-Jan	49842	Quick Supply, Inc	3,175.33	666 Scary Lane	Tucson	AZ	85116
23-Jan	1583	Hard copy masters	4,442.95	1010 Heard St.	Tucson	AZ	85122
24-Jan	1977	Easy Green Supplies	2,367.41	444 My Way	Tucson	AZ	85114
24-Jan	7135	Xanadu Supplies	1,963.55	8080 Fourth St.	Tucson	AZ	85110
25-Jan	37132	Jones Office Supplies	3,799.50	77 Sunset Strip	Tucson	AZ	85110
25-Jan	23591	Quality Paper	2,244.56	PO Box 99334	Tucson	AZ	85111
25-Jan	1699	Hard copy masters	3,847.66	1010 Heard St.	Tucson	AZ	85122
26-Jan	51022	Quick Supply, Inc	3,122.85	666 Scary Lane	Tucson	AZ	85116
26-Jan	37899	True Blue Supplies	1,499.55	PO Box 3345	Tucson	AZ	85111
26-Jan	1025	Orange Cleaning	1,833.55	111 West Apple Rd.	Tucson	AZ	85114
26-Jan	19955	Great Western Supplies	2,863.55	PO Box 22445	Tucson	AZ	85111
26-Jan	38809	True Blue Supplies	3,172.99	PO Box 3345	Tucson	AZ	85111
29-Jan	2084	White shipping	4,295.25	PO Box 85287	Tucson	AZ	85111
29-Jan	8422	AAA Hardware	1,933.50	1422 Third Ave.	Tucson	AZ	85110
29-Jan	2133	Hard copy masters	4,125.77	1010 Heard St.	Tucson	AZ	85122
30-Jan	2198	Hard copy masters	190.95	1010 Heard St.	Tucson	AZ	85122
30-Jan	2788	Z Best Office Supplies	3,524.68	678 Quinto St.	Tucson	AZ	85116
30-Jan	7403	Xanadu Supplies	1,965.29	8080 Fourth St.	Tucson	AZ	85110
31-Jan	1289	L&J	997.55	PO Box 174583	Tucson	AZ	85123
31-Jan	20066	Great Western Supplies	2,504.55	PO Box 22445	Tucson	AZ	85111
31-Jan	2055	Easy Green Supplies	1,155.75	444 My Way	Tucson	AZ	85114
31-Jan	38994	Jones Office Supplies	955.00	77 Sunset Strip	Tucson	AZ	85110
31-Jan	54599	Quick Supply, Inc	1,433.85	666 Scary Lane	Tucson	AZ	85116
31-Jan	38809	True Blue Supplies	3,172.99	PO Box 3345	Tucson	AZ	85120
31-Jan	1937	DFR	989.99	PO Box 174583	Tucson	AZ	85123

Download the spreadsheet for this problem from the course website and perform the following tasks: Explore Excel's AutoFilter function. At the bottom of the table, in cell C79 enter the words "Total (sum)" and in cell D79 enter the SUM function to total all amounts in column D. At the bottom of the table, in cell C80 enter the words "Total (subtotal)"and in cell D80 enter "SUBTOTAL" function to sum all the amounts in column D. Use AutoFilter to display all purchases from suppliers in Zipcode 85110. What is displayed in cells D79 and D80? Why? Repeat step 3 for Zipcodes 85111 and 85116. What is the value of using the AutoFilter function? Use the AutoFilter and Sort functions to identify the following warning signs of potentially fraudulent supplier invoices: Suppliers that have only initials for the name Different suppliers with the same PO Box for an address Suppliers who submit sequentially numbered invoices Invoices just below a threshold requiring approval; for example, invoices below $1,000 Duplicate invoice numbers from the same supplier