Using the AutoFilter, Sort, and other Excel functions develop a test for each of the following:

  

    1) Invoices just below the $1,000 approval threshold

     2) Suppliers who submit sequentially numbered invoices

     3) Duplicate invoices from the same supplier

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

    

1. Which of the following suppliers submitted a duplicate invoice?

                                                                                   

		Fred's Toner
		Orange Cleaning
		Quick Supply, Inc
		Z Best Office Supplies

    

2. Of the following suppliers, which has submitted the most invoices that are close to the $1,000 approval threshold?

                                                                                 

		True Blood Supplies
		DFR
		Office Supply Headquarters
		Quick Supply, Inc

    

3. How many duplicate invoices exist in the file (i.e. the same invoice number from a single supplier)?

                                                                                    

		2
		0
		6
		4

    

4. Which supplier below submitted a sequential invoice (i.e. the same supplier submitted two or more sequentially numbered invoices)?

                                                                                 

		L&J
		Quality Paper
		City Electric
		True Blue Supplies

    

5. How many invoices are within $20 of the required approval threshold of $1,000 (i.e. just below the threshold)?

                                                                                    

		0
		5
		20
		8

Answer rating: 100% (QA)

1 Invoices just below the 1000 approval threshold There are in all 14 invoices below 1000 approval threshold 2 Suppliers who submit sequentially numbe... View the full answer