Room rates are the same for one or two guests; however, additional guests must pay an additional $20 per person per day for meals. Guests staying for seven days or more receive a 10% discount. Modify the spreadsheet to calculate the number of days that each party stayed at the inn and the total revenue for the length of stay for each party.

what would excel formula be

?

Assignment 2 Due: Thursday Feb 2nd At 11pm (evening) All assignments must be completed individually (Please follow all the listed instructions to receive full credit) Instructions Sheet: This sheet IF Sheet: 30 points Room Costs Sheet: 30 points Vlookup Sheet: 10 points Match Index Sheet: 30 points IMPORTANT Assignment has to be submitted on BlackBoard. File should be named as: Lastname_Firstname_Assignment2.xlsx POINT DEDUCTIONS 1. For each 24 hours (or part thereof) the assignment is late, there will be a 10 point deduction. 2. Failure to follow the file naming convention when submitting assignments in BlackBoard = 10 point deducti = 10 point deduction. Employees in a local company is required to work no more This sheet has two problems: Hourly Rate and Disounts. than 40 hours a week by law. Their regular hourly rate is $16. If an employee works overtime, the hourly rate is $24 for each Remember to do both problems. extra hour. The left table shows the working hours of some Typically, in atotal local company are employees of this company.employees Calculate the wage using IF Question 1a: Hourly Rate required to work no more than 40.00 hours a Regular Hourly Rate Overtime Hours Threshold Overtime Rate Employee Kira Susan Amelie Sandy Tom Pat Jan function. $17.89 40.00 $37.89 Weekly Working Hours Weekly Wage 24 37.5 35 48 52.5 37.5 47 17.89 17.89 17.89 37.89 37.89 17.89 37.89 250 150 $14.99 $20.99 $26.99 week. Their regular hourly rate is $17.89. If an employee works overtime (more than 40.00 hours a week), the hourly rate is $37.89 for each extra hour. The table shows the working hours of employees of this company. Calculate the WEEKLY wage using the IF function. Do NOT hard code values; instead use cell references to refer to fixed values such as $17.89. Use absolute and relative referencing appropriately. Question 1b: Discounts Deep Discount Rate (q > 250) Regular Discount Rate (250 >= q > 150) Regular Rate (otherwise) Customer Avila Enterprises New Salem Enterprises Timson and Brothers French Brothers, Inc. Zaragosa, Inc. Orleans and Sons Thyme and Sons, Inc. Oswego Trading Company Quantity (q) Sales 219 166 250 139 150 299 323 133 4596.81 3484.34 5247.5 3751.61 4048.5 4482.01 4841.77 3589.67 A particular company offers two types of discounts based on the quantity (q) bought; regular discount and deep discount (see the table to the left). For each of its customers, calculate sales using the nested IF function. Do NOT hard code values; instead use cell references to refer to fixed values such as $14.99. Use absolute and relative referencing appropriately. company are 40.00 hours a is $17.89. If an e than 40.00 hours 9 for each extra ing hours of ing the IF lues; instead use values such as ve referencing o types of y (q) bought; ount (see the table ulate sales using hard code values; efer to fixed values nd relative Question 2: Room Costs The data below shows the information for a small bed and breakfast duri rates are the same for one or two guests; however, additional guests mu per person per day for meals. Guests staying for seven days or more rec Modify the spreadsheet to calculate the number of days that each party the total revenue for the length of stay for each party. ID Guest First Name Guest Last Name 1 Barry 2 Michael 3 Kim 4 Edward 5 Thomas 6 Paul 7 Randall 8 Calvin 9 Homer 10 David 11 Buster 12 Julia 13 Samuel 14 Arthur 15 Darlene 16 Carlyle 17 Albert 18 Charlene 19 Everett 20 Gerald 21 Jamal 22 Louis 23 Nigel 24 Peter 25 Ronald 26 Trista 27 Valerie 28 Walter 29 Xavier 30 Zachary Room Room Type Arrival Date Departure Date Lloyd Hayes Bay-window 12/1/2014 12/4/2014 Lunsford Kyuong Holt Collins Bodkin Battenburg Nowotney Gonzalez Sanchez Whisler Martines Kim Gottfried Shore Charleston Goldstone Tilson Chad Pittsfield Smith Paris Stratford Willington Cartier Leven Snell Acton Trezza Miller Cleveland Coolidge Washington Lincoln Coolidge Washington Lincoln Lincoln Jefferson Jackson Reagan Truman Garfield Arthur Quincy Adams Johnson Van Buren Madison Roosevelt Tyler Jackson Eisenhower Grant Jefferson Eisenhower Adams Polk McKinley Washington Ocean Bay-window Ocean Ocean Bay-window Ocean Ocean Ocean Bay-window Ocean Bay-window Side Side Ocean Bay-window Ocean Bay-window Ocean Ocean Bay-window Ocean Ocean Ocean Bay-window Ocean Bay-window Side Side Ocean 12/1/2014 12/4/2014 12/1/2014 12/9/2014 12/1/2014 12/4/2014 12/2/2014 12/5/2014 12/5/2014 12/5/2014 12/10/2014 12/20/2014 12/13/2014 12/24/2014 12/3/2014 12/5/2014 12/5/2014 12/10/2014 12/5/2014 12/20/2014 12/10/2014 12/14/2014 12/19/2014 12/24/2014 12/17/2014 12/3/2014 12/24/2014 12/14/2014 12/13/2014 12/9/2014 12/7/2014 12/3/2014 12/13/2014 12/3/2014 12/12/2014 12/4/2014 12/7/2014 12/7/2014 12/8/2014 12/15/2014 12/30/2014 12/15/2014 12/31/2014 12/6/2014 12/7/2014 12/7/2014 12/14/2014 12/7/2014 12/23/2014 12/14/2014 12/16/2014 12/21/2014 12/28/2014 12/20/2014 12/6/2014 12/31/2014 12/17/2014 12/16/2014 nd breakfast during one month. Room tional guests must pay an additional $20 days or more receive a 10% discount. that each party stayed at the inn and No of Guests Daily Rate # of days New Rate 2 $150.00 3 1 4 2 2 2 1 5 2 2 1 3 2 5 2 3 1 2 2 2 1 2 1 4 1 1 6 2 2 $112.50 $150.00 $325.00 $300.00 $150.00 $292.50 $300.00 $320.00 $175.00 $250.00 $150.00 $112.50 $125.00 $198.00 $150.00 $250.00 $150.00 $275.00 $275.00 $150.00 $250.00 $200.00 $200.00 $175.00 $200.00 $150.00 $148.50 $125.00 $325.00 3 8 3 2 4 2 8 2 2 2 3 5 10 2 7 3 2 2 4 2 3 4 2 2 4 3 3 7 3 3 $150.00 $121.25 $130.00 $365.00 $300.00 $150.00 $263.25 $280.00 $380.00 $175.00 $250.00 $130.00 $121.25 $125.00 $238.20 $150.00 $270.00 $130.00 $275.00 $275.00 $150.00 $230.00 $200.00 $180.00 $215.00 $180.00 $130.00 $213.65 $125.00 $325.00 Question 3: Vlookup A manager neds to identify some information from the data below, bu Description and Cost Per Order for the requested Order Numbers. Purchase Orders Supplier Hulkey Fasteners Alum Sheeting Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Fast-Tie Aerospace Steelpin Inc. Order No. Aug11001 Aug11002 Aug11003 Aug11004 Aug11005 Aug11006 Aug11007 Item No. 1122 1243 5462 5462 5319 5462 4312 Durrable Products Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Hulkey Fasteners Hulkey Fasteners Steelpin Inc. Steelpin Inc. Alum Sheeting Hulkey Fasteners Steelpin Inc. Steelpin Inc. Hulkey Fasteners Spacetime Technologies Alum Sheeting Durrable Products Steelpin Inc. Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Spacetime Technologies Hulkey Fasteners Fast-Tie Aerospace Steelpin Inc. Spacetime Technologies Hulkey Fasteners Spacetime Technologies Spacetime Technologies Pylon Accessories Aug11008 Aug11009 Aug11010 Aug11011 Aug11012 Aug11013 Aug11014 Sep11001 Sep11002 Sep11003 Sep11004 Sep11005 Sep11006 Sep11007 Sep11008 Sep11009 Sep11010 Sep11011 Sep11012 Sep11013 Sep11014 Sep11015 Sep11016 Sep11017 Sep11018 Sep11019 Sep11020 Sep11021 Sep11022 Sep11023 7258 6321 5462 5319 3166 9966 5234 4312 5417 3166 5234 4312 1122 4111 1243 7258 5234 1122 5066 3166 1122 4111 3166 6321 5319 4111 5066 9752 4111 9764 Pylon Accessories Spacetime Technologies Spacetime Technologies Durrable Products Manley Valve Fast-Tie Aerospace Pylon Accessories Durrable Products Hulkey Fasteners Pylon Accessories Durrable Products Spacetime Technologies Durrable Products Manley Valve Pylon Accessories Fast-Tie Aerospace Hulkey Fasteners Durrable Products Sep11024 Sep11025 Sep11026 Sep11027 Sep11028 Sep11029 Sep11030 Sep11031 Sep11032 Sep11033 Sep11034 Oct11001 Oct11002 Oct11003 Oct11004 Oct11005 Oct11006 Oct11007 9764 9752 4111 1369 6431 6321 9764 1369 1122 9764 1369 5125 9399 6431 6433 5166 1122 9399 Spacetime Technologies Durrable Products Spacetime Technologies Durrable Products Manley Valve Durrable Products Durrable Products Spacetime Technologies Alum Sheeting Steelpin Inc. Manley Valve Manley Valve Hulkey Fasteners Fast-Tie Aerospace Alum Sheeting Durrable Products Manley Valve Steelpin Inc. Alum Sheeting Manley Valve Alum Sheeting Durrable Products Fast-Tie Aerospace Fast-Tie Aerospace Hulkey Fasteners Steelpin Inc. Steelpin Inc. Spacetime Technologies Alum Sheeting Manley Valve Oct11008 Oct11009 Oct11010 Oct11011 Oct11012 Oct11013 Oct11014 Oct11015 Oct11016 Oct11017 Oct11018 Oct11019 Oct11020 Oct11021 Oct11022 Oct11023 Oct11024 Oct11025 Oct11026 Oct11027 Oct11028 Oct11029 Oct11030 Oct11031 Oct11032 Oct11033 Oct11034 Oct11035 Oct11036 Nov11001 6489 9399 4111 4569 6431 9399 5454 6489 1243 8008 7258 8148 1122 6321 4224 5454 7258 8008 5417 7258 5634 5275 6321 5689 1122 5677 8008 6489 5634 9977 Manley Valve Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Manley Valve Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Nov11002 Nov11003 Nov11004 Nov11005 Nov11006 Nov11007 Nov11008 Nov11009 9955 5689 7268 5677 9967 7268 5689 5677 Manley Valve Nov11010 9955 QUESTION Order Numbers Sep11001 Oct11024 Nov11006 Item Description Bolt-nut package Pressure Gauge Hatch Decal Cost Per Order $ 15,750.00 $ 10,050.00 $ 467.50 n from the data below, but has only the order number. Use VLOOKUP to find the Ite sted Order Numbers. Item Description Airframe fasteners Airframe fasteners Shielded Cable/ft. Shielded Cable/ft. Shielded Cable/ft. Shielded Cable/ft. Bolt-nut package Pressure Gauge O-Ring Shielded Cable/ft. Shielded Cable/ft. Electrical Connector Hatch Decal Electrical Connector Bolt-nut package Control Panel Electrical Connector Electrical Connector Bolt-nut package Airframe fasteners Bolt-nut package Airframe fasteners Pressure Gauge Electrical Connector Airframe fasteners Shielded Cable/ft. Electrical Connector Airframe fasteners Bolt-nut package Electrical Connector O-Ring Shielded Cable/ft. Bolt-nut package Shielded Cable/ft. Gasket Bolt-nut package Gasket Item Cost Quantity Cost per order A/P Terms (Months) $ 4.25 19,500 $ 82,875.00 30 $ 4.25 10,000 $ 42,500.00 30 $ 1.05 23,000 $ 24,150.00 30 $ 1.05 21,500 $ 22,575.00 30 $ 1.10 17,500 $ 19,250.00 30 $ 1.05 22,500 $ 23,625.00 30 $ 3.75 4,250 $ 15,937.50 30 $ 90.00 $ 2.45 100 $ 1,300 $ 9,000.00 3,185.00 $ 1.05 $ 1.10 $ 1.25 $ 0.75 $ 1.65 $ 3.75 $ 255.00 $ 1.25 $ 1.65 $ 3.75 22,500 18,100 5,600 500 4,500 4,200 406 5,500 4,850 4,150 $ $ $ $ $ $ $ $ $ $ 23,625.00 19,910.00 7,000.00 375.00 7,425.00 15,750.00 103,530.00 6,875.00 8,002.50 15,562.50 $ $ 4.25 3.55 $ $ $ 4.25 90.00 1.65 15,500 $ 4,800 $ 9,000 $ 65,875.00 17,040.00 38,250.00 120 $ 4,750 $ 10,800.00 7,837.50 $ 4.25 12,500 $ 53,125.00 $ $ 0.95 1.25 25,000 $ 5,650 $ 23,750.00 7,062.50 $ $ 4.25 3.55 15,000 $ 4,585 $ 63,750.00 16,276.75 $ 1.25 5,425 $ 6,781.25 $ 2.45 1,200 $ 2,940.00 $ 1.10 16,500 $ 18,150.00 $ 3.55 4,200 $ 14,910.00 $ $ 0.95 4.05 17,500 $ 1,500 $ 16,625.00 6,075.00 $ 3.55 4,250 $ 15,087.50 $ 3.75 1,980 $ 7,425.00 45 30 30 30 30 30 30 30 30 30 30 30 30 25 30 45 30 30 30 30 30 25 30 30 30 25 30 25 25 15 Order Date 08/05/11 08/08/11 08/10/11 08/15/11 08/20/11 08/20/11 08/25/11 08/25/11 08/25/11 08/25/11 08/25/11 08/25/11 08/25/11 08/28/11 09/01/11 09/01/11 09/01/11 09/02/11 09/03/11 09/04/11 09/05/11 09/05/11 09/05/11 09/05/11 09/05/11 09/05/11 09/05/11 09/08/11 09/10/11 09/10/11 09/12/11 09/15/11 09/15/11 09/15/11 09/20/11 09/20/11 09/20/11 Gasket Gasket Bolt-nut package Airframe fasteners O-Ring O-Ring Gasket Airframe fasteners Airframe fasteners Gasket Airframe fasteners Shielded Cable/ft. Gasket O-Ring O-Ring Electrical Connector Airframe fasteners Gasket $ $ 3.75 4.05 1,750 $ 1,550 $ 6,562.50 6,277.50 $ 3.55 $ $ 4.20 2.85 4,200 $ 15,000 $ 14,910.00 63,000.00 1,300 $ 3,705.00 O-Ring Gasket Bolt-nut package Bolt-nut package O-Ring Gasket Control Panel O-Ring Airframe fasteners Machined Valve Pressure Gauge Machined Valve Airframe fasteners O-Ring Bolt-nut package Control Panel Pressure Gauge Machined Valve Control Panel Pressure Gauge Side Panel Shielded Cable/ft. O-Ring Side Panel Airframe fasteners Side Panel Machined Valve O-Ring Side Panel Panel Decal $ 2.45 2,500 $ 6,125.00 $ $ 3.75 4.20 1,850 $ 14,000 $ 6,937.50 $ 4.25 14,500 $ $ $ $ 3.75 4.20 1.15 1,800 $ 10,000 $ 15,000 $ 42,000.00 $ 3.65 1,250 $ 4,562.50 $ $ $ 2.85 2.95 1.25 1,350 $ 1,500 $ 5,650 $ 3,847.50 4,425.00 7,062.50 $ $ 4.25 3.65 18,000 $ 1,450 $ 76,500.00 5,292.50 $ 3.00 1,100 $ 3,300.00 $ $ 3.65 3.55 1,985 $ 4,600 $ 7,245.25 16,330.00 $ $ 3.50 2.85 3,900 $ 1,250 $ 13,650.00 3,562.50 $ 3.65 1,470 $ 5,365.50 $ $ $ $ $ $ 220.00 3.00 4.25 645.00 100.50 655.50 $ $ 4.25 2.45 $ $ $ $ $ $ $ $ 3.95 220.00 100.50 645.00 255.00 100.50 185.00 1.00 $ 2.45 $ 175.00 $ 4.25 $ 195.00 $ 645.00 $ 3.00 $ 185.00 $ 1.00 550 900 10,500 100 90 125 58,800.00 61,625.00 6,750.00 17,250.00 $ $ 121,000.00 2,700.00 $ 44,625.00 $ $ $ 64,500.00 9,045.00 81,937.50 17,000 $ 1,250 $ 72,250.00 3,062.50 4,500 500 100 150 500 95 150 25,000 $ $ $ $ $ $ $ $ 17,775.00 110,000.00 10,050.00 96,750.00 127,500.00 9,547.50 27,750.00 25,000.00 1,500 $ 3,675.00 155 $ 27,125.00 17,500 $ 74,375.00 130 120 1,050 140 525 $ $ $ $ $ 25,350.00 77,400.00 3,150.00 25,900.00 525.00 15 25 25 45 30 30 15 45 30 15 45 25 45 30 15 30 30 45 09/20/11 09/25/11 09/25/11 09/25/11 09/25/11 09/25/11 09/25/11 09/27/11 09/28/11 09/28/11 09/29/11 10/01/11 10/01/11 10/01/11 10/01/11 10/01/11 10/01/11 10/03/11 25 45 25 45 30 45 45 25 30 30 30 30 30 30 30 45 30 30 30 30 30 45 30 30 30 30 30 25 30 30 10/05/11 10/05/11 10/05/11 10/05/11 10/05/11 10/07/11 10/09/11 10/10/11 10/10/11 10/10/11 10/10/11 10/10/11 10/11/11 10/12/11 10/15/11 10/15/11 10/15/11 10/15/11 10/20/11 10/20/11 10/25/11 10/25/11 10/25/11 10/25/11 10/25/11 10/28/11 10/28/11 10/29/11 10/29/11 11/01/11 Door Decal Side Panel Pressure Gauge Side Panel Hatch Decal Pressure Gauge Side Panel Side Panel Door Decal $ $ $ $ $ $ $ $ $ 0.55 175.00 95.00 195.00 0.85 95.00 175.00 195.00 0.55 150 150 110 120 550 105 175 110 125 $ $ $ $ $ $ $ $ $ 82.50 26,250.00 10,450.00 23,400.00 467.50 9,975.00 30,625.00 21,450.00 68.75 30 30 30 30 30 30 30 30 11/01/11 11/01/11 11/01/11 11/02/11 11/05/11 11/05/11 11/05/11 11/05/11 30 11/05/11 OOKUP to find the Item Arrival Date 08/13/11 08/14/11 08/15/11 08/22/11 08/31/11 08/26/11 09/01/11 08/28/11 09/04/11 09/02/11 09/05/11 08/29/11 08/31/11 09/05/11 09/10/11 09/10/11 09/06/11 09/11/11 09/11/11 09/12/11 09/20/11 09/12/11 09/09/11 09/13/11 09/11/11 09/12/11 09/10/11 09/15/11 09/30/11 09/15/11 09/23/11 10/05/11 10/15/11 09/22/11 09/25/11 10/10/11 09/29/11 09/25/11 10/05/11 10/25/11 09/30/11 10/01/11 10/04/11 10/05/11 10/03/11 10/03/11 10/05/11 10/04/11 10/15/11 10/06/11 10/07/11 10/10/11 10/06/11 10/08/11 10/08/11 10/10/11 10/11/11 10/19/11 10/10/11 10/10/11 10/12/11 10/14/11 10/18/11 10/17/11 10/21/11 10/17/11 10/17/11 10/19/11 10/21/11 10/20/11 10/20/11 10/24/11 10/26/11 10/27/11 10/29/11 11/03/11 10/30/11 11/02/11 11/03/11 11/03/11 11/07/11 11/04/11 11/10/11 11/04/11 11/07/11 11/06/11 11/09/11 11/12/11 11/13/11 11/11/11 11/16/11 11/15/11 11/17/11 11/10/11 Question 4: Match Index Use MATCH and/or INDEX functions to find the following: a) The row numbers corresponding to the first and last instance of item item number. b) The order cost associated with the first instance of item 1369 that you c) The total cost of all orders for item 1369. Use the answers to parts (a) words, you should use the appropriate INDEX and MATCH functions with Purchase Orders Supplier Order No. Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Alum Sheeting Alum Sheeting Alum Sheeting Durrable Products Durrable Products Durrable Products Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Alum Sheeting Steelpin Inc. Steelpin Inc. Steelpin Inc. Durrable Products Hulkey Fasteners Aug11001 Sep11006 Sep11011 Sep11014 Sep11032 Oct11006 Oct11020 Oct11032 Aug11002 Sep11008 Oct11016 Sep11027 Sep11031 Sep11034 Aug11012 Sep11003 Sep11013 Sep11016 Sep11007 Sep11015 Sep11019 Sep11022 Sep11026 Oct11010 Oct11022 Aug11007 Sep11001 Sep11005 Oct11011 Sep11012 Item No. 1122 1122 1122 1122 1122 1122 1122 1122 1243 1243 1243 1369 1369 1369 3166 3166 3166 3166 4111 4111 4111 4111 4111 4111 4224 4312 4312 4312 4569 5066 Hulkey Fasteners Spacetime Technologies Fast-Tie Aerospace Steelpin Inc. Steelpin Inc. Steelpin Inc. Durrable Products Steelpin Inc. Steelpin Inc. Steelpin Inc. Alum Sheeting Alum Sheeting Durrable Products Durrable Products Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Sep11020 Oct11001 Oct11005 Aug11014 Sep11004 Sep11010 Oct11029 Aug11005 Aug11011 Sep11018 Sep11002 Oct11026 Oct11014 Oct11023 Aug11003 Aug11004 Aug11006 Aug11010 5066 5125 5166 5234 5234 5234 5275 5319 5319 5319 5417 5417 5454 5454 5462 5462 5462 5462 Alum Sheeting Alum Sheeting Steelpin Inc. Steelpin Inc. Steelpin Inc. Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Manley Valve Manley Valve Manley Valve Pylon Accessories Spacetime Technologies Spacetime Technologies Spacetime Technologies Durrable Products Durrable Products Manley Valve Manley Valve Manley Valve Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Steelpin Inc. Steelpin Inc. Oct11028 Oct11036 Oct11033 Nov11005 Nov11009 Oct11031 Nov11003 Nov11008 Aug11009 Sep11017 Sep11029 Oct11021 Oct11030 Sep11028 Oct11003 Oct11012 Oct11004 Oct11008 Oct11015 Oct11035 Aug11008 Sep11009 Oct11018 Oct11024 Oct11027 Nov11004 Nov11007 Oct11017 Oct11025 Oct11034 5634 5634 5677 5677 5677 5689 5689 5689 6321 6321 6321 6321 6321 6431 6431 6431 6433 6489 6489 6489 7258 7258 7258 7258 7258 7268 7268 8008 8008 8008 Manley Valve Durrable Products Durrable Products Durrable Products Durrable Products Spacetime Technologies Spacetime Technologies Pylon Accessories Pylon Accessories Pylon Accessories Pylon Accessories Manley Valve Manley Valve Hulkey Fasteners Manley Valve Oct11019 Oct11002 Oct11007 Oct11009 Oct11013 Sep11021 Sep11025 Sep11023 Sep11024 Sep11030 Sep11033 Nov11002 Nov11010 Aug11013 Nov11006 8148 9399 9399 9399 9399 9752 9752 9764 9764 9764 9764 9955 9955 9966 9967 Manley Valve Nov11001 9977 following: nd last instance of item number 1369 in Column C (be sure Column C is sorted by nce of item 1369 that you identified in part (a) the answers to parts (a) and (b) along with the SUM function to do this. In other nd MATCH functions within the SUM function to find the answer. Item Description Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Airframe fasteners Electrical Connector Electrical Connector Electrical Connector Electrical Connector Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Bolt-nut package Shielded Cable/ft. Item Cost Quantity Cost per order $ $ $ $ $ $ $ $ 4.25 4.25 4.25 4.25 4.25 4.25 4.25 4.25 19,500 15,500 12,500 15,000 14,500 18,000 17,000 17,500 10,000 9,000 10,500 15,000 14,000 10,000 5,600 5,500 5,650 5,425 $ $ $ $ $ $ $ $ $ $ $ $ $ $ 82,875.00 65,875.00 53,125.00 63,750.00 61,625.00 76,500.00 72,250.00 74,375.00 42,500.00 38,250.00 44,625.00 63,000.00 58,800.00 42,000.00 $ $ $ $ $ $ $ $ $ $ 4.25 4.25 4.25 4.20 4.20 4.20 1.25 1.25 1.25 1.25 $ $ $ $ 7,000.00 6,875.00 7,062.50 6,781.25 $ $ $ $ $ $ 3.55 3.55 3.55 3.55 3.55 3.55 4,800 4,585 4,200 4,250 4,200 4,600 $ $ $ $ $ $ 17,040.00 16,276.75 14,910.00 15,087.50 14,910.00 16,330.00 $ $ $ $ $ $ 3.95 3.75 3.75 3.75 3.50 0.95 4,500 4,250 4,200 4,150 3,900 25,000 $ $ $ $ $ $ 17,775.00 15,937.50 15,750.00 15,562.50 13,650.00 23,750.00 A/P Terms (Months) 30 30 30 30 30 30 30 30 30 30 30 45 45 45 30 30 30 30 25 25 25 25 25 25 30 30 30 30 45 30 Order Date 08/05/11 09/04/11 09/05/11 09/08/11 09/28/11 10/01/11 10/11/11 10/25/11 08/08/11 09/05/11 10/10/11 09/25/11 09/27/11 09/29/11 08/25/11 09/01/11 09/05/11 09/10/11 09/05/11 09/10/11 09/15/11 09/20/11 09/25/11 10/05/11 10/15/11 08/25/11 09/01/11 09/03/11 10/05/11 09/05/11 Shielded Cable/ft. Shielded Cable/ft. Electrical Connector Electrical Connector Electrical Connector Electrical Connector Shielded Cable/ft. Shielded Cable/ft. Shielded Cable/ft. Shielded Cable/ft. Control Panel Control Panel Control Panel Control Panel Shielded Cable/ft. Shielded Cable/ft. Shielded Cable/ft. Shielded Cable/ft. Side Panel Side Panel Side Panel Side Panel Side Panel Side Panel Side Panel Side Panel O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring O-Ring Pressure Gauge Pressure Gauge Pressure Gauge Pressure Gauge Pressure Gauge Pressure Gauge Pressure Gauge Machined Valve Machined Valve Machined Valve $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0.95 1.15 1.25 1.65 1.65 1.65 1.00 1.10 1.10 1.10 255.00 255.00 220.00 220.00 1.05 1.05 1.05 1.05 185.00 185.00 195.00 195.00 195.00 175.00 175.00 175.00 17,500 15,000 5,650 4,500 4,850 4,750 25,000 17,500 18,100 16,500 406 500 550 500 23,000 21,500 22,500 22,500 150 140 130 120 110 155 150 175 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 16,625.00 17,250.00 7,062.50 7,425.00 8,002.50 7,837.50 25,000.00 19,250.00 19,910.00 18,150.00 103,530.00 127,500.00 121,000.00 110,000.00 24,150.00 22,575.00 23,625.00 23,625.00 27,750.00 25,900.00 25,350.00 23,400.00 21,450.00 27,125.00 26,250.00 30,625.00 $ $ $ $ $ 2.45 2.45 2.45 2.45 2.45 1,300 1,200 2,500 1,250 1,500 $ $ $ $ $ 3,185.00 2,940.00 6,125.00 3,062.50 3,675.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2.85 2.85 2.85 2.95 3.00 3.00 3.00 90.00 90.00 100.50 100.50 100.50 95.00 95.00 645.00 645.00 645.00 1,300 1,350 1,250 1,500 1,100 900 1,050 100 120 90 100 95 110 105 100 150 120 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 3,705.00 3,847.50 3,562.50 4,425.00 3,300.00 2,700.00 3,150.00 9,000.00 10,800.00 9,045.00 10,050.00 9,547.50 10,450.00 9,975.00 64,500.00 96,750.00 77,400.00 30 25 30 30 30 30 45 30 30 30 30 30 45 45 30 30 30 30 09/15/11 10/01/11 10/01/11 08/28/11 09/02/11 09/05/11 10/25/11 08/20/11 08/25/11 09/15/11 09/01/11 10/20/11 10/09/11 10/15/11 08/10/11 08/15/11 08/20/11 08/25/11 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 15 25 25 25 45 45 30 30 30 30 30 30 30 30 10/25/11 10/29/11 10/28/11 11/02/11 11/05/11 10/25/11 11/01/11 11/05/11 08/25/11 09/12/11 09/25/11 10/12/11 10/25/11 09/25/11 10/01/11 10/05/11 10/01/11 10/05/11 10/10/11 10/29/11 08/25/11 09/05/11 10/10/11 10/15/11 10/20/11 11/01/11 11/05/11 10/10/11 10/15/11 10/28/11 Machined Valve Gasket Gasket Gasket Gasket Gasket Gasket Gasket Gasket Gasket Gasket Door Decal Door Decal Hatch Decal Hatch Decal Panel Decal $ 655.50 125 $ 81,937.50 $ $ $ $ 3.65 3.65 3.65 3.65 1,250 1,450 1,985 1,470 $ $ $ $ 4,562.50 5,292.50 7,245.25 5,365.50 $ $ $ $ $ $ $ $ $ $ $ 4.05 4.05 3.75 3.75 3.75 3.75 0.55 0.55 0.75 0.85 1.00 1,500 1,550 1,980 1,750 1,850 1,800 150 125 500 550 525 $ $ $ $ $ $ $ $ $ $ $ 6,075.00 6,277.50 7,425.00 6,562.50 6,937.50 6,750.00 82.50 68.75 375.00 467.50 525.00 30 45 45 45 45 25 25 15 15 15 15 30 30 30 30 10/10/11 10/01/11 10/03/11 10/05/11 10/07/11 09/20/11 09/25/11 09/20/11 09/20/11 09/25/11 09/28/11 11/01/11 11/05/11 08/25/11 11/05/11 30 11/01/11 lumn C is sorted by to do this. In other er. Arrival Date 08/13/11 09/12/11 09/11/11 09/15/11 10/03/11 10/08/11 10/19/11 11/03/11 08/14/11 09/12/11 10/17/11 09/30/11 10/03/11 10/04/11 08/29/11 09/06/11 09/10/11 09/15/11 09/20/11 09/30/11 10/15/11 10/10/11 10/25/11 10/19/11 10/20/11 09/01/11 09/10/11 09/11/11 10/10/11 09/12/11 Supplier item No. Row # Durrable P 1396 Column # 12 09/22/11 10/15/11 10/06/11 09/05/11 09/11/11 09/13/11 10/30/11 08/31/11 09/05/11 10/05/11 09/10/11 10/27/11 10/14/11 10/20/11 08/15/11 08/22/11 08/26/11 09/02/11 11/03/11 11/04/11 11/07/11 11/13/11 11/17/11 11/03/11 11/09/11 11/15/11 09/04/11 09/23/11 10/04/11 10/21/11 11/02/11 10/01/11 10/07/11 10/10/11 10/10/11 10/10/11 10/18/11 11/10/11 08/28/11 09/09/11 10/17/11 10/24/11 10/29/11 11/12/11 11/16/11 10/21/11 10/26/11 11/04/11 10/17/11 10/06/11 10/08/11 10/11/11 10/12/11 09/25/11 10/05/11 09/29/11 09/25/11 10/05/11 10/05/11 11/06/11 11/10/11 08/31/11 11/11/11 11/07/11