Homework Sourse Homework Sourse

the following exercises use the Purchase Orders database Use

the following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following:

a) the row numbers corresponding to the first and last instance of item number 1369 in column c

b) the order cost associated with teh first instance of item 1369 that you identified in part (a)

c) The total cost of all orders for item 1369. Use the answers to parts (a) and (b) along wth the SUM function to do this. In other words, you should use the appropriate INDEX and MATCH functions within the SUM function to find the answer. Validate your results by applying the SUM function directly to the data in column G.

Supplier Order No. Item No. Item Description Item Cost Quantity Cost per order A/P Terms (Months) Order Date Arrival Date
Hulkey Fasteners Aug11001 1122 Airframe fasteners $     4.25    19,500 $    82,875.00 30 08/05/11 08/13/11
Alum Sheeting Aug11002 1243 Airframe fasteners $      4.25     10,000 $    42,500.00 30 08/08/11 08/14/11
Fast-Tie Aerospace Aug11003 5462 Shielded Cable/ft. $      1.05     23,000 $      24,150.00 30 08/10/11 08/15/11
Fast-Tie Aerospace Aug11004 5462 Shielded Cable/ft. $      1.05     21,500 $      22,575.00 30 08/15/11 08/22/11
Steelpin Inc. Aug11005 5319 Shielded Cable/ft. $      1.10     17,500 $      19,250.00 30 08/20/11 08/31/11
Fast-Tie Aerospace Aug11006 5462 Shielded Cable/ft. $      1.05     22,500 $      23,625.00 30 08/20/11 08/26/11
Steelpin Inc. Aug11007 4312 Bolt-nut package $      3.75        4,250 $      15,937.50 30 08/25/11 09/01/11
Durrable Products Aug11008 7258 Pressure Gauge $    90.00           100 $        9,000.00 45 08/25/11 08/28/11
Fast-Tie Aerospace Aug11009 6321 O-Ring $     2.45      1,300 $      3,185.00 30 08/25/11 09/04/11
Fast-Tie Aerospace Aug11010 5462 Shielded Cable/ft. $      1.05     22,500 $      23,625.00 30 08/25/11 09/02/11
Steelpin Inc. Aug11011 5319 Shielded Cable/ft. $      1.10     18,100 $      19,910.00 30 08/25/11 09/05/11
Hulkey Fasteners Aug11012 3166 Electrical Connector $      1.25        5,600 $        7,000.00 30 08/25/11 08/29/11
Hulkey Fasteners Aug11013 9966 Hatch Decal $      0.75           500 $           375.00 30 08/25/11 08/31/11
Steelpin Inc. Aug11014 5234 Electrical Connector $      1.65        4,500 $        7,425.00 30 08/28/11 09/05/11
Steelpin Inc. Sep11001 4312 Bolt-nut package $      3.75        4,200 $      15,750.00 30 09/01/11 09/10/11
Alum Sheeting Sep11002 5417 Control Panel $ 255.00           406 $    103,530.00 30 09/01/11 09/10/11
Hulkey Fasteners Sep11003 3166 Electrical Connector $      1.25        5,500 $        6,875.00 30 09/01/11 09/06/11
Steelpin Inc. Sep11004 5234 Electrical Connector $      1.65        4,850 $        8,002.50 30 09/02/11 09/11/11
Steelpin Inc. Sep11005 4312 Bolt-nut package $      3.75        4,150 $      15,562.50 30 09/03/11 09/11/11
Hulkey Fasteners Sep11006 1122 Airframe fasteners $     4.25    15,500 $    65,875.00 30 09/04/11 09/12/11
Spacetime Technologies Sep11007 4111 Bolt-nut package $     3.55      4,800 $    17,040.00 25 09/05/11 09/20/11
Alum Sheeting Sep11008 1243 Airframe fasteners $      4.25        9,000 $    38,250.00 30 09/05/11 09/12/11
Durrable Products Sep11009 7258 Pressure Gauge $    90.00           120 $      10,800.00 45 09/05/11 09/09/11
Steelpin Inc. Sep11010 5234 Electrical Connector $      1.65        4,750 $        7,837.50 30 09/05/11 09/13/11
Hulkey Fasteners Sep11011 1122 Airframe fasteners $     4.25    12,500 $    53,125.00 30 09/05/11 09/11/11
Hulkey Fasteners Sep11012 5066 Shielded Cable/ft. $      0.95     25,000 $      23,750.00 30 09/05/11 09/12/11
Hulkey Fasteners Sep11013 3166 Electrical Connector $      1.25        5,650 $        7,062.50 30 09/05/11 09/10/11
Hulkey Fasteners Sep11014 1122 Airframe fasteners $     4.25    15,000 $    63,750.00 30 09/08/11 09/15/11
Spacetime Technologies Sep11015 4111 Bolt-nut package $     3.55      4,585 $    16,276.75 25 09/10/11 09/30/11
Hulkey Fasteners Sep11016 3166 Electrical Connector $      1.25        5,425 $        6,781.25 30 09/10/11 09/15/11
Fast-Tie Aerospace Sep11017 6321 O-Ring $     2.45      1,200 $      2,940.00 30 09/12/11 09/23/11
Steelpin Inc. Sep11018 5319 Shielded Cable/ft. $      1.10     16,500 $      18,150.00 30 09/15/11 10/05/11
Spacetime Technologies Sep11019 4111 Bolt-nut package $     3.55      4,200 $    14,910.00 25 09/15/11 10/15/11
Hulkey Fasteners Sep11020 5066 Shielded Cable/ft. $      0.95     17,500 $      16,625.00 30 09/15/11 09/22/11
Spacetime Technologies Sep11021 9752 Gasket $      4.05        1,500 $        6,075.00 25 09/20/11 09/25/11
Spacetime Technologies Sep11022 4111 Bolt-nut package $     3.55      4,250 $    15,087.50 25 09/20/11 10/10/11
Pylon Accessories Sep11023 9764 Gasket $      3.75        1,980 $        7,425.00 15 09/20/11 09/29/11
Pylon Accessories Sep11024 9764 Gasket $      3.75        1,750 $        6,562.50 15 09/20/11 09/25/11
Spacetime Technologies Sep11025 9752 Gasket $      4.05        1,550 $        6,277.50 25 09/25/11 10/05/11
Spacetime Technologies Sep11026 4111 Bolt-nut package $     3.55      4,200 $    14,910.00 25 09/25/11 10/25/11
Durrable Products Sep11027 1369 Airframe fasteners $      4.20     15,000 $    63,000.00 45 09/25/11 09/30/11
Manley Valve Sep11028 6431 O-Ring $      2.85        1,300 $        3,705.00 30 09/25/11 10/01/11
Fast-Tie Aerospace Sep11029 6321 O-Ring $     2.45      2,500 $      6,125.00 30 09/25/11 10/04/11
Pylon Accessories Sep11030 9764 Gasket $      3.75        1,850 $        6,937.50 15 09/25/11 10/05/11
Durrable Products Sep11031 1369 Airframe fasteners $      4.20     14,000 $    58,800.00 45 09/27/11 10/03/11
Hulkey Fasteners Sep11032 1122 Airframe fasteners $     4.25    14,500 $    61,625.00 30 09/28/11 10/03/11
Pylon Accessories Sep11033 9764 Gasket $      3.75        1,800 $        6,750.00 15 09/28/11 10/05/11
Durrable Products Sep11034 1369 Airframe fasteners $      4.20     10,000 $    42,000.00 45 09/29/11 10/04/11
Spacetime Technologies Oct11001 5125 Shielded Cable/ft. $      1.15     15,000 $      17,250.00 25 10/01/11 10/15/11
Durrable Products Oct11002 9399 Gasket $     3.65      1,250 $      4,562.50 45 10/01/11 10/06/11
Manley Valve Oct11003 6431 O-Ring $      2.85        1,350 $        3,847.50 30 10/01/11 10/07/11
Pylon Accessories Oct11004 6433 O-Ring $      2.95        1,500 $        4,425.00 15 10/01/11 10/10/11
Fast-Tie Aerospace Oct11005 5166 Electrical Connector $      1.25        5,650 $        7,062.50 30 10/01/11 10/06/11
Hulkey Fasteners Oct11006 1122 Airframe fasteners $     4.25    18,000 $    76,500.00 30 10/01/11 10/08/11
Durrable Products Oct11007 9399 Gasket $     3.65      1,450 $      5,292.50 45 10/03/11 10/08/11
Spacetime Technologies Oct11008 6489 O-Ring $      3.00        1,100 $        3,300.00 25 10/05/11 10/10/11
Durrable Products Oct11009 9399 Gasket $     3.65      1,985 $      7,245.25 45 10/05/11 10/11/11
Spacetime Technologies Oct11010 4111 Bolt-nut package $     3.55      4,600 $    16,330.00 25 10/05/11 10/19/11
Durrable Products Oct11011 4569 Bolt-nut package $      3.50        3,900 $      13,650.00 45 10/05/11 10/10/11
Manley Valve Oct11012 6431 O-Ring $      2.85        1,250 $        3,562.50 30 10/05/11 10/10/11
Durrable Products Oct11013 9399 Gasket $     3.65      1,470 $      5,365.50 45 10/07/11 10/12/11
Durrable Products Oct11014 5454 Control Panel $ 220.00           550 $    121,000.00 45 10/09/11 10/14/11
Spacetime Technologies Oct11015 6489 O-Ring $      3.00           900 $        2,700.00 25 10/10/11 10/18/11
Alum Sheeting Oct11016 1243 Airframe fasteners $      4.25     10,500 $    44,625.00 30 10/10/11 10/17/11
Steelpin Inc. Oct11017 8008 Machined Valve $ 645.00           100 $      64,500.00 30 10/10/11 10/21/11
Manley Valve Oct11018 7258 Pressure Gauge $ 100.50             90 $        9,045.00 30 10/10/11 10/17/11
Manley Valve Oct11019 8148 Machined Valve $ 655.50           125 $      81,937.50 30 10/10/11 10/17/11
Hulkey Fasteners Oct11020 1122 Airframe fasteners $     4.25    17,000 $    72,250.00 30 10/11/11 10/19/11
Fast-Tie Aerospace Oct11021 6321 O-Ring $     2.45      1,250 $      3,062.50 30 10/12/11 10/21/11
Alum Sheeting Oct11022 4224 Bolt-nut package $      3.95        4,500 $      17,775.00 30 10/15/11 10/20/11
Durrable Products Oct11023 5454 Control Panel $ 220.00           500 $    110,000.00 45 10/15/11 10/20/11
Manley Valve Oct11024 7258 Pressure Gauge $ 100.50           100 $      10,050.00 30 10/15/11 10/24/11
Steelpin Inc. Oct11025 8008 Machined Valve $ 645.00           150 $      96,750.00 30 10/15/11 10/26/11
Alum Sheeting Oct11026 5417 Control Panel $ 255.00           500 $    127,500.00 30 10/20/11 10/27/11
Manley Valve Oct11027 7258 Pressure Gauge $ 100.50             95 $        9,547.50 30 10/20/11 10/29/11
Alum Sheeting Oct11028 5634 Side Panel $ 185.00           150 $      27,750.00 30 10/25/11 11/03/11
Durrable Products Oct11029 5275 Shielded Cable/ft. $      1.00     25,000 $      25,000.00 45 10/25/11 10/30/11
Fast-Tie Aerospace Oct11030 6321 O-Ring $     2.45      1,500 $      3,675.00 30 10/25/11 11/02/11
Fast-Tie Aerospace Oct11031 5689 Side Panel $ 175.00           155 $      27,125.00 30 10/25/11 11/03/11
Hulkey Fasteners Oct11032 1122 Airframe fasteners $     4.25    17,500 $    74,375.00 30 10/25/11 11/03/11
Steelpin Inc. Oct11033 5677 Side Panel $ 195.00           130 $      25,350.00 30 10/28/11 11/07/11
Steelpin Inc. Oct11034 8008 Machined Valve $ 645.00           120 $      77,400.00 30 10/28/11 11/04/11
Spacetime Technologies Oct11035 6489 O-Ring $      3.00        1,050 $        3,150.00 25 10/29/11 11/10/11
Alum Sheeting Oct11036 5634 Side Panel $ 185.00           140 $      25,900.00 30 10/29/11 11/04/11
Manley Valve Nov11001 9977 Panel Decal $      1.00           525 $           525.00 30 11/01/11 11/07/11
Manley Valve Nov11002 9955 Door Decal $      0.55           150 $              82.50 30 11/01/11 11/06/11
Fast-Tie Aerospace Nov11003 5689 Side Panel $ 175.00           150 $      26,250.00 30 11/01/11 11/09/11
Fast-Tie Aerospace Nov11004 7268 Pressure Gauge $    95.00           110 $      10,450.00 30 11/01/11 11/12/11
Steelpin Inc. Nov11005 5677 Side Panel $ 195.00           120 $      23,400.00 30 11/02/11 11/13/11
Manley Valve Nov11006 9967 Hatch Decal $      0.85           550 $           467.50 30 11/05/11 11/11/11
Fast-Tie Aerospace Nov11007 7268 Pressure Gauge $    95.00           105 $        9,975.00 30 11/05/11 11/16/11
Fast-Tie Aerospace Nov11008 5689 Side Panel $ 175.00           175 $      30,625.00 30 11/05/11 11/15/11
Steelpin Inc. Nov11009 5677 Side Panel $ 195.00           110 $      21,450.00 30 11/05/11 11/17/11
Manley Valve Nov11010 9955 Door Decal $      0.55           125 $              68.75 30 11/05/11 11/10/11

Solution

(a) Item is present in Row No. 41, 45 and 48. First is 41 and last is 48.

(b) $63000.

(c) The three values of orders for item no. 1369 are 42000, 58800 and 63000. Using SUM function we get total value of $163800,

the following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following: a) the row numbers corresponding to the first
the following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following: a) the row numbers corresponding to the first
the following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following: a) the row numbers corresponding to the first
the following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following: a) the row numbers corresponding to the first
the following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following: a) the row numbers corresponding to the first

Get Help Now

Submit a Take Down Notice
Tutor
Tutor: Dr Jack
Most rated tutor on our site