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

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 29, 2019

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)

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

Step by Step Solution

★★★★★

3.46 Rating (153 Votes )

There are 3 Steps involved in it

Step: 1

1 Invoices just below the 1000 approval threshold There are in all 14 invoices below 1000 approval threshold 2 Suppliers who submit sequentially numbe... blur-text-image