need help on this

Hands-on MIS Project: Use a spreadsheet to select suppliers

Software skills: spreadsheet datefunctions: data sorting, data filtering, AVERAGE functions. Business skills: Analyzing supplier performance and pricing

In this exercise, you will learn how to use spreadsheet software to improve management decisions about selecting suppliers. You will filter transactional data about suppliers based on several criteria to select the best suppliers for your company. The transactional data are given in the file:Assignment1-data-2.xlsx.Download Assignment1-data-2.xlsx.

Your job is to turn the data into meaningful information. In particular, your boss needs to compare the suppliers by their delivery time, AP terms, and item cost so that they can choose the better suppliers for future orders.

Deliverable: A solution Excel spreadsheet that includes 4 worksheets:

1. Data (original plus added column: Delivery days)

2. Delivery Time ?Table: Vendor No., Name, Average delivery time)

3. AP Terms (Table: Vendor No., Name, Accounts payable terms)

4. Supplier item cost (Table: Item Description, Cost, Vendor Name, filtered by item description)

Orders and Suppliers Vendor Name Vendor No. Order No. Item No. Item Description Item Cost Quantity Cost per order A/P Terms Order Date Arrival Date Spacetime Technologies 2 A0111 6489 O-Ring 3.00 900 2,700.00 25 10/10/15 10/18/15 Steelpin Inc. 6 A0115 5319 Shielded Cable/ft 1.10 19,250.00 08/20/15 08/31/15 Steelpin Inc. A0123 4312 Bolt-nut package 3.75 4,250 15,937.50 08/25/15 09/01/15 Steelpin Inc. A0204 5319 Shielded Cable/ft 1.10 16,500 $ 18, 150.00 09/15/15 10/05/15 Steelpin Inc. A0205 5677 Side Panel 195.00 120 23,400.00 11/02/15 11/13/15 Steelpin Inc. A0207 4312 Bolt-nut package 3.75 4,290 15,750.00 09/01/15 09/10/15 Alum Sheeting A0223 4224 Bolt-nut package 3.95 .500 17,775.00 10/15/15 10/20/15 Alum Sheeting A0433 5417 Control Panel $ 2 255.00 500 $ 127,500.00 10/20/15 10/27/15 Alum Sheeting A0443 1243 Airframe fasteners $ 4.25 10,000 42,500.00 08/08/15 08/14/15 Alum Sheeting A0446 5417 Control Panel 255.00 406 103,530.00 09/01/15 09/10/15 Spacetime Technologies A0533 9752 Gasket 4.05 1,500 6,075.00 09/20/15 09/25/15 Spacetime Technologies A0555 6489 O-Ring 3.00 1.100 3,300.00 10/05/15 10/10/15 Spacetime Technologies WWNNNNNNCICI U A0622 9752 Gasket 4.05 1,550 6,277.50 09/25/15 10/05/15 Spacetime Technologies 4066 5125 Shielded Cable/ft. 1,15 5,000 7,250.00 10/01/15 10/15/15 Spacetime Technologies A0777 6489 O-Ring 3.00 1,050 3,150.00 10/29/15 11/10/15 Spacetime Technologies A1222 4111 Bolt-nut package 3.55 4,200 $ 14,910.00 09/15/15 10/15/15 Durrable Products A1234 9399 Gasket 3.65 1,250 4,562.50 10/01/15 10/06/15 Durrable Products A1235 9399 Gasket 3.65 1.450 5,292.50 45 10/03/15 10/08/15 Durrable Products A1344 5454 Control Panel 220.00 550 121,000.00 45 10/09/15 10/14/15 Durrable Products A1345 9399 Gaske 3.65 1,470 5,365.50 45 10/07/15 10/13/15 Durrable Products A134 9399 Gasket 3.65 1,985 7,245.25 10/05/15 10/1 1/15 Spacetime Technologies A1444 4111 Bolt-nut package 3.55 4.250 15,087.50 09/20/15 10/10/15 Spacetime Technologies A144 4111 Bolt-nut package 3.55 4,200 $ 14,910.00 09/25/15 10/25/15 Spacetime Technologies INWWWNNNWWC A144 4111 Bolt-nut package 3.55 4.600 16,330.00 10/05/15 10/19/15 Durrable Product A145 5454 Control Panel 220.00 500 10,000.00 10/15/15 10/20/15 Durrable Products A1457 4569 Bolt-nut package 350 3,900 13,650.00 10/05/15 10/10/15 Durrable Products A1567 1369 Airframe fasteners 4.20 15,000 63,000.00 09/25/15 09/30/15 Manley Valve A234 6431 O-Ring 2.85 ,250 3,562.50 10/05/15 10/10/15 Manley Valve A2356 7258 Pressure Gauge 100.50 95 9,547.50 10/20/15 10/29/15 Manley Valve A2367 9977 Panel Decal 1.00 525 525.00 11/01/15 11/07/15 Manley Valve 2378 6431 O-Ring 2.85 1.350 3,847.50 10/01/15 10/07/15 Spacetime Technologies A3467 4111 Bolt-nut package 3.55 4,800 17,040.00 09/05/15 09/20/15 Spacetime Technologies A5689 4111 Bolt-nut package 3.55 4,585 6,276.75 09/10/15 09/30/15 Manley Valve A9821 6431 O-Ring 2.85 1,300 3,705.00 09/25/15 10/01/15 Manley Valve A984 7258 Pressure Gauge 100.50 100 10,050.00 10/15/15 10/24/15 Manley Valve A9865 9967 Hatch Decal 0.85 550 467.50 11/05/15 11/11/15 Manley Valve A9876 9955 Door Decal 0.55 150 82.50 11/01/15 11/06/15 Pylon Accessories A9999 6433 O-Ring 2.95 1,500 4,425.00 10/01/15 10/10/15 WWOUUUTQUID VV VV NN - Alum Sheeting B0247 1243 Airframe fasteners 4.25 9,000 38,250.00 09/05/15 09/13/15 Steelpin Inc. B044 4312 Bolt-nut package 3.75 1.150 15,562.50 09/03/15 09/1 1/15 Alum Sheeting B0447 5634 Side Panel 185.00 150 27,750.00 10/25/15 11/03/15 Alum Sheeting B047 5634 Side Panel 185.00 140 25,900.00 10/29/15 11/04/15 Alum Sheeting B0567 1243 Airframe fasteners 4.25 10,500 44,625.00 10/10/15 10/17/15 Pylon Accessories B1111 9764 Gaske 3.75 1.980 7,425.00 09/20/15 09/29/15 Durrable Products B1234 7258 Pressure Gauge 90.00 100 9,000.00 08/25/15 08/28/15 Durrable Products B1345 7258 Pressure Gauge 90.00 120 10,800.00 09/05/15 09/09/15 Durrable Products B146 1369 Airframe fasteners 4.20 14,000 58,800.00 09/27/15 10/03/15 Durrable Products B1589 5275 Shielded Cable/f 1.00 25,000 25,000.00 45 10/25/15 10/30/15 Durrable Products B166 1369 Airframe fasteners 4.20 10,000 42,000.00 45 09/29/15 10/04/15 Fast-Tie Aerospace B2333 6321 O-Ring 2.45 1,300 3,185.00 08/25/15 09/04/15 Fast-Tie Aerospace B2345 6321 O-Ring 2.45 1,200 2,940.00 09/13/15 09/23/15 Fast-Tie Aerospace B235 6321 O-Ring 2.45 2,500 o $ 6,125.00 09/25/15 10/04/15 Fast-Tie Aerospace B2367 6321 O-Ring 2.45 1.250 3,062.50 10/13/15 10/21/15 Fast-Tie Aerospace B237 6321 O-Ring 2.45 1,500 3,675.00 10/25/15 11/02/15 Fast-Tie Aerospace APP B2498 5689 Side Panel 175.00 150 26,250.00 11/01/15 11/09/15 Fast-Tie Aerospace B249 7268 Pressure Gauge 95.00 10 10,450.00 11/01/15 11/13/15 Fast-Tie Aerospace B2511 7268 Pressure Gauge 95.00 105 9,975.00 11/05/15 11/16/15 Fast-Tie Aerospace B2519 5462 Shielded Cable/ft 1.05 22,500 23,625.00 08/20/15 08/26/15 Fast-Tie Aerospace B252 5689 Side Panel 175.0 175 30,625.00 1 1/05/15 11/15/15 Fast-Tie Aerospace B253 5462 Shielded Cable/ft 1.05 21,500 22,575.00 08/15/15 08/22/15 Fast-Tie Aerospace B256 5462 Shielded Cable/ft 1.05 23,000 $ 24,150.00 08/10/15 08/15/15 Steelpin Inc B302 5677 Side Panel 195.00 110 21,450.00 11/05/15 11/17/15 Steelpin Inc B3041 5234 Electrical Connector 1.65 4,500 7,425.00 08/28/15 09/05/15 Steelpin Inc. B3042 5234 Electrical Connector 1.65 4,750 7,837.50 09/05/15 09/13/15 Steelpin Inc. B311 5234 Electrical Connector 1.65 4.850 8,002.50 09/02/15 09/11/15 Steelpin Inc B322 8008 Machined Valve $ 6 150 96,750.00 10/15/15 10/26/15 Steelpin Inc. B3333 8008 Machined Valve $6 100 64,500.00 10/10/15 10/21/15 Fast-Tie Aerospace C023 5166 Electrical Connector 1.25 ,650 A At 7,062.50 10/01/15 10/06/15 Fast-Tie Aerospace C0423 5689 Side Panel 175.00 155 27,125.00 10/25/15 11/03/15 Fast-Tie Aerospace C0433 5462 Shielded Cable/ft. 1.05 22,500 23,625.00 08/25/15 09/02/15 Steelpin Inc. C0456 5677 Side Panel 95.00 130 25,350.00 10/28/15 11/07/15 Steelpin Inc. C0467 8008 Machined Valve $ 6 120 77,400.00 10/28/15 11/04/15 Steelpin Inc. C0589 5319 Shielded Cable/ft 1.10 8.100 19,910.00 08/25/15 09/05/15 Manley Valve C1111 9955 Door Decal 0.55 125 68.75 11/05/15 11/10/15 Hulkey Fasteners C1212 1122 Airframe fasteners 4.25 19,500 82,875.00 08/05/15 08/13/15 Hulkey Fasteners C1313 3166 Electrical Connector 1.25 5,600 7,000.00 08/25/15 08/29/15 Pylon Accessories C2211 9764 Gasket 3.75 1.850 6,937.50 09/25/15 10/05/15 Manley Valve C2222 7258 Pressure Gauge 100.50 90 9,045.00 10/10/15 10/17/15 Hulkey Fasteners C2323 1122 Airframe fasteners 4.25 15,500 65,875.00 09/04/15 09/13/15 Hulkey Fasteners C2929 3166 Electrical Connector 1.25 5,500 6,875.00 09/01/15 09/06/15 Hulkey Fasteners C3232 1122 Airframe fasteners 4.25 18,000 76,500.00 10/01/15 10/08/15 Manley Valve C3333 8148 Machined Valve 655.50 125 81,937.50 10/10/15 10/17/15 Hulkey Fasteners C3434 1122 Airframe fasteners 4.25 12,500 53, 125.00 6868868 09/05/15 09/1 1/15 Hulkey Fasteners C4545 1122 Airframe fasteners 4.25 15,000 63,750.00 09/08/15 09/15/15 Hulkey Fasteners C5656 1122 Airframe fasteners 4.25 14,500 61,625.00 09/28/15 10/03/15 Hulkey Fasteners C6765 5066 Shielded Cable/ft. 0.95 25,000 23,750.00 30 09/05/15 09/13/15 Hulkey Fasteners C7875 3166 Electrical Connector 1.25 5,650 7,062.50 09/05/15 09/10/15 Hulkey Fasteners C8854 3166 Electrical Connector 1.25 5.425 6,781.25 09/10/15 09/15/15 Hulkey Fasteners C8989 9966 Hatch Decal 0.75 500 375.00 08/25/15 08/31/15 Pylon Accessories D1111 9764 Gasket 3.75 1,800 6,750.00 09/28/15 10/05/15 Hulkey Fasteners D1212 5066 Shielded Cable/ft 0.95 17,500 16,625.00 09/15/15 09/22/15 Hulkey Fasteners D2121 1122 Airframe fasteners 4.25 17,500 $ 74,375.00 10/25/15 11/03/15 Hulkey Fasteners D3232 1122 Airframe fasteners 4.25 17,000 72,250.00 30 10/1 1/15 10/19/15 Pylon Accessories D3333 9764 Gasket 3.75 1,750 6,562.50 15 09/20/15 09/25/15