Expectation on your MGMT4205-Assignment #1 (EXCEL) Dear students, My expectation on your Business Software Assignment#1 are listed below: for Q1-Identify the Suppliers who provide On-Time Delivery . for Q2- Identify suppliers who provide The Best Accounts Payable Terms a Pivot table named as Accts-Payable-Terms in which contains Vendor Names and Maximum A/P Terms and is sorted descending by Maximum A/P Terms. Identify the best supplier who provides the best Accounts Payable Terms. . . a Pivot table named as On-Time Delivery in which contains Vendor Names and Average Delivery Days and is sorted ascending by Average Delivery Time. (Hint: you must find out the "delivery days" from the data provided before creating the pivot table to solve this problem) Identify the best supplier who provides on-time delivery. for Q3- Identify suppliers who offer lower pricing items when the sam item can be provided by multiple suppliers . . a Pivot table named as Low-Pricing-Items in which contains Item description(column label). Vendor Names (row lable) and Minimum Item Cost. For each item, identify the supplier who provides the lowest price for the item. The above Objects of your answers for Q1-Q3 should be included in your Business Software Assignment ! EXCEL file. Your answer for Q4 a summary of Q1-3--can be posted on a separate worksheet of the same EXCEL file of Q1-3. 1 2 3 4 Vendor Name 5 Durable Products 6 Hulkey Fasteners 7 Alum Sheeting 8 Fast-Tie Aerospace 9 Durable Products 10 Fast-Tie Aerospace 11 Fast-Tie Aerospace 12 Steelpin Inc 13 Durrable Products 14 Fast-Tie Aerospace 15 Fast-Tie Aerospace 16 Hulkey Fasteners 17 Hulkey Fasteners 18 Steelpin Inc 19 Steelpin Inc. 20 Steelpin Inc. 21 Durrable Products 22 Alum Sheeting 23 Hulkey Fasteners 24 Steelpin Inc. 25 Steelpin Inc 26 Steelpin Inc 27 Hulkey Fasteners data Vendor No. Order No. Item No. B1234 7258 C1212 1122 A0443 1243 B2566 5462 B1345 7258 B2537 5462 B2519 5462 5319 1369 A0145 A1567 B2333 6321 O-Ring C0433 C1313 5462 Shielded Cable/t 3166 Electrical Connector C8989 9966 Hatch Decal A0123 4312 Bolt-nut package C0589 5319 Shielded Cable/t B3041 5234 Electrical Connector B1666 1369 Airframe fasteners A0446 5417 Control Panel 3166 C2929 A0207 4312 Sheet1 3 1 5 4 3 4 4 6 3 4 4 1 1 6 6 6 3 5 1 6 6 6 1 B3111 B0445 C2323 Orders and Suppliers Item Description Pressure Gauge Airframe fasteners Airframe fasteners Shielded Cable/ft Pressure Gauge Shielded Cable/ft Shielded Cable/t Shielded Cable/ft Airframe fasteners Electrical Connector Bolt-nut package Electrical Connector Bolt-nut package 1122 Auframe fasteners. 5234 4312 100 $ 45 08/05/09 08/13/09 Item Cost Quantity Cost per order A/P Terms Order Date Arrival Date $ 90.00 07/25/09 08/28/09 $ 4.25 $ 4.25 $ 1.05 $90.00 $ 1.05 $ 1.05 $ 1.10 $ 4.20 $ 2.45 S 1.05 9,000.00 19,500 $ 82,875.00 10,000 $ 42,500.00 23,000 $ 24,150.00 120 $ 10,800.00 08/08/09 08/14/09 08/10/09 08/15/09 08/15/09 09/09/09 08/22/09 08/15/09 21,500 S 22,575.00 22,500 $ 17,500 $ 08/20/09 08/26/09 08/20/09 23,625.00 19,250.00 63,000.00 3,185.00 23,625.00 7,000.00 08/25/09 08/25/09 08/25/09 08/25/09 $ 1.25 $ 0.75 $ 3.75 $ 1.10 $1.65 375.00 15,937.50 19,910.00 $ 4.20 $255.00 $1.25 $ 3.75 $1.65 $ 3.75 $ 425 15,000 S 1,300 $ 22,500 $ 5,600 $ 500 $ 4,250 $ 18,100 S 4,500 $ 10,000 $ 406 $ 5,500 $ 4,200 $ 4,850 $ 4,150 $ 15.500 S 7,425.00 42,000.00 103,530.00 6,875.00 15,750.00 8,002 50 15,562 50 65.875.00 388888888888888888886 30 30 30 45 30 30 30 45 30 30 30 30 30 30 30 45 30 30 30 30 30 30 08/31/09 09/30/09 09/04/09 09/02/09 08/29/09 08/25/09 08/31/09 08/25/09 09/01/09 08/25/09 08/28/09 08/29/09 09/01/09 09/01/09 A 09/05/09 09/05/09 10/04/09 09/10/09 09/06/09 09/01/09 09/10/09 09/11/09 09/02/09 09/03/09 09/11/09 09/04/09 09/12/09 A 27 Hulkey Fasteners 28 Alum Sheeting 29 Durrable Products 30 Durrable Products 31 Hulkey Fasteners 32 Hulkey Fasteners 33 Hulkey Fasteners 34 Spacetime Technologies 35 Steelpin Inc. 36 Durrable Products 37 Hulkey Fasteners 38 Hulkey Fasteners 39 Spacetime Technologies 40 Fast-Tie Aerospace 41 Durrable Products 42 Durrable Products 43 Hulkey Fasteners 44 Spacetime Technologies 45 Steelpin Inc 46 Durable Products 47 Pylon Accessones 48 Pylon Accessories 49 Spacetime Technologies 50 Spacetime Technologies 51 Durable Products 52 Fast-Tie Aerospace 53 Manley Valve Ready data Sheet1 Accessibility: Good to go B 1 5 3 3 1 1 1 2 6 3 1 1 2 4 3 3 1 2 6 3 8 8 2 2 3 4 7 C C2323 B0247 A1346 A1457 C3434 C6765 C7875 A3467 B3042 A1345 C4545 C8854 A5689 B2345 A1234 A1456 D1212 A1222 A0204 $119 A1235 $199 B1111 9764 D3333 9764 A0533 9752 4111 A1444 A1344 5454 B2356 6321 A9821 E Airframe fasteners Airframe fasteners Gasket Bolt-nut package Airframe fasteners Shielded Cable/t Electrical Connector D 1122 1243 9399 4569 1122 5066 3166 4111 5234 9399 1122 3166 4111 6321 Bolt-nut package O-Ring 9399 Gasket 5454 Control Panel 5066 4111 Bolt-nut package Electrical Connector Gasket Airframe fasteners Electrical Connector Shielded Cable/ Bolt-nut package Shielded Cable/h Gasket Gasket Gasket Gasket Bolt-nut package Control Panel O-Ring 6431 O-Ring ANDROM F $ 4.25 $ 4.25 $ 3.65 $3.50 $ 4.25 $0.95 $1.25 $ 3.55 $1.65 $ 3.65 $ 4.25 $1.25 $3.55 $2.45 $ 3.65 $220.00 $0.95 $ 3.55 $1.10 $ 3.65 $ 3.75 $ 3.75 $ 4.05 $ 3.55 $220.00 $ 245 $ 2.85 G H 65,875.00 15,500 $ 9,000 $ 38,250.00 7,245 25 1,985 $ 13,650.00 53,125.00 23,750.00 7,062.50 17,040.00 3,900 $ 12,500 $ 25,000 $ 7,837 50 5,650 $ 4,800 $ 4,750 S 1,470 S 15,000 $ 5,425 $ 5,365.50 63,750.00 6,781.25 4,585 $ 16,276.75 1,200 S 1,250 $ 2,940.00 4,562 50 500 $ 110,000.00 16,625.00 14,910 00 18,150.00 5,292 50 7,425.00 6,562 50 6,075.00 15,087 50 121,000.00 6,125 00 550 $ 2,500 $ 1,300 $ 3,705.00 17,500 $ 4,200 $ 16,500 $ 1,450 $ 1,980 $ 1,750 $ 1,500 $ 4,250 $ I 30 586588 30 45 45 30 30 30 25 30 45 30 30 25 30 SSARRARASNSAAS 45 45 30 25 30 45 15 15 25 25 45 30 50 = 09/04/09 09/05/09 09/05/09 09/05/09 09/05/09 09/05/09 09/05/09 09/05/09 09/05/09 09/07/09 09/08/09 09/10/09 09/10/09 K 09/12/09 09/12/09 10/11/09 10/10/09 09/11/09 09/12/09 09/10/09 09/20/09 09/18/09 09/20/09 09/20/09 09/13/09 10/12/09 09/15/09 09/15/09 09/30/09 09/23/09 10/06/09 09/12/09 09/15/09 09/15/09 10/20/09 09/15/09 09/22/09 09/15/09 10/15/09 09/15/09 10/05/09 10/08/09 09/29/09 09/25/09 09/20/09 09/25/09 09/20/09 10/10/09 09/24/09 10/14/09 09/25/09 10/04/09 09/25/09 10/01/09 3 A 53 Manley Valve 54 Pylon Accessories 55 Spacetime Technologies 56 Spacetime Technologies 57 Durrable Products 58 Hulkey Fasteners 59 Pylon Accessories 60 Fast-Tie Aerospace 61 Hulkey Fasteners 62 Manley Valve 63 Pylon Accessones 64 Spacetime Technologies 65 Manley Valve 66 Spacetime Technologies 67 Spacetime Technologies 68 Alum Sheeting 69 Manley Valve 70 Manley Valve 71 Spacetime Technologies 72 Steelpin Inc 73 Hulkey Fasteners 74 Fast-Tie Aerospace 75 Alum Sheeting 76 Manley Valve 77 Steelpin Inc. 78 Alum Sheeting. 79 Manley Valve Ready data Sheet1 Accessibilite Good to on B 7 8 2 2 3 1 8 4 1 7 8 2 7 2 2 5 7 7 2 6 1 4 5 7 6 5 7 O D A9821 6431 O-Ring A0622 A1445 C2211 9764 Gasket 9752 Gasket 4111 Bolt-nut package B1468 Airframe fasteners Airframe fasteners C5656 D11 1369 1122 9764 Gasket 5166 1122 6431 6433 5125 6431 6489 4111 1243 7258 8148 C0234 C3232 A2378 A9999 A0666 A2345 A0555 A1449 B0567 C2222 C3333 A0111 5489 B3333 dos D3232 1122 B2367 6321 A0223 4224 A9842 7258 B3222 8008 A0433 5417 A2356 7258 E Electrical Connector Airframe fasteners O-Ring O-Ring Shielded Cable/t O-Ring O-Ring Bolt-nut package Airframe fasteners Pressure Gauge Machined Valve O-Ring Machined Valve Airframe fasteners O-Ring Bolt-nut package Pressure Gauge Machined Valve Control Panel Pressure Gauge G 1,300 $ 1,850 $ 1,550 $ 4,200 $ H 3,705.00 6,937.50 6,277.50 14,910.00 58,800.00 61,625.00 6,750.00 7,062.50 18,000 $ 76,500.00 14,000 $ 14,500 $ 1,800 $ 5,650 $ 1,350 $ 1,500 $ 15,000 S 3,847.50 4,425.00 17,250.00 3,562.50 3,300.00 16,330.00 $ 4.25 10,500 $ 44,625.00 $100.50 $655.50 $ 3.55 4,600 $ 90 $ 125 $ 900 $ $ 3.00 $ 645.00 100 $ $ 4.25 17,000 S $ 2.45 $3.95 $100.50 1,250 $ 4,500 $ 9,045.00 81,937.50 2,700.00 64,500.00 72,250.00 3,062.50 17,775.00 10,050.00 96,750.00 127,500.00 9,547.50 100 $ 150 S 500 $ 95 $ F $ $ $ 2.85 3.75 4.05 $ 3.55 $ 4.20 $ 4.25 $ 3,75 $1.25 $ 4.25 $ 2.85 $ 2.95 $ 1.15 $ 2.85 $3.00 $ 645.00 $255.00 $ 100.50 1,250 $ 1,100 $ 1 50 888888888888CCSHGS88-8ANCES 15 25 25 45 30 15 30 30 50 15 25 50 25 25 30 50 50 25 30 30 30 30 50 30 30 50 09/25/09 09/25/09 09/25/09 10/05/09 09/25/09 10/25/09 09/27/09 09/28/09 09/28/09 K 10/01/09 10/05/09 10/03/09 10/03/09 10/05/09 10/01/09 10/06/09 10/01/09 10/01/09 10/01/09 10/01/09 10/10/09 10/10/09 10/11/09 10/12/09 10/15/09 10/08/09 10/07/09 10/10/09 10/15/09 10/05/09 10/10/09 10/05/09 10/10/09 10/05/09 10/19/09 10/10/09 10/17/09 10/10/09 10/17/09 10/10/09 10/17/09 10/18/09 10/21/09 10/19/09 10/21/09 10/20/09 10/15/09 10/24/09 10/15/09 10/26/09 10/20/09 10/27/09 10/20/09 10/29/09 72 Steelpin Inc. 73 Hulkey Fasteners 74 Fast-Tie Aerospace 75 Alum Sheeting 76 Manley Valve 77 Steelpin Inc 78 Alum Sheeting 79 Manley Valve 80 Alum Sheeting 81 Durrable Products 82 Fast-Tie Aerospace 83 Fast-Tie Aerospace 84 Hulkey Fasteners 85 Steelpin Inc 86 Steelpin Inc 87 Alum Sheeting 88 Spacetime Technologies 89 Fast-Tie Aerospace 90 Fast-Tie Aerospace 91 Manley Valve 92 Manley Valve 93 Steelpin Inc 94 Fast-Tie Aerospace 95 Fast-Tie Aerospace 96 Manley Valve 97 Manley Valve 98 Steelpin Inc. data Sheet1 B 6 1 55 4 7 6 5 7 5344 1 665244TSATI 7 7 7 6 C D E B3333 8008 Machined Valve Airframe fasteners D3232 1122 B2367 6321 O-Ring Bolt-nut package A0223 4224 A9842 7258 8008 Pressure Gauge B3222 Machined Valve A0433 5417 Control Panel A2356 7258 Pressure Gauge B0447 5634 Side Panel B1589 5275 Shielded Cable/tt B2378 6321 O-Ring C0423 5689 Side Panel D2121 C0456 C0467 B0479 A0777 B2498 B2499 A2367 A9876 1122 Airframe fasteners 5677 Side Panel 8008 Machined Valve 5634 6489 Side Panel O-Ring Side Panel 5689 7268 Pressure Gauge 9977 Panel Decal 9955 Door Decal A0205 5677 Side Panel B2511 B2528 7268 Pressure Gauge 5689 Side Panel 9967 Hatch Decal C1111 9955 Door Decal B3022 5677 Side Panel A9865 F $ 645.00 $ 4.25 $ 245 $ 3.95 $ 100.50 $ 645.00 $255.00 $100.50 $185.00 $1.00 $ 2.45 $175.00 $ 4.25 $195.00 $ 645.00 $185.00 $ 3.00 $ 175,00 $95.00 $ 1.00 $ 0.55 $ 195.00 $95.00 $ 175.00 $ 0.85 $ 0.55 $195.00 G H 100 S 64,500.00 72,250.00 3,062.50 17,000 $ 1,250 $ 4,500 $ 17,775.00 10,050.00 100 $ 150 $ 96,750.00 500 $ 127,500.00 95 $ 9,547.50 150 $ 27,750.00 25,000.00 3,675.00 25,000 $ 1,500 $ 155 $ 27,125.00 17,500 $ 74,375.00 130 S 25,350.00 120 $ 77,400.00 140 S 25,900.00 1,050 $ 150 $ 3,150.00 26,250.00 10,450.00 110 S 525 $ 150 $ 120 $ 105 $ 175 $ 550 $ 125 $ 110 $ 21,450.00 525.00 82.50 23,400.00 9,975.00 30,625.00 467.50 68.75 1 30 30 30 22288 30 50 30 30 8888888888 50 30 45 30 30 30 30 30 30 88888888888 25 30 30 50 50 30 30 30 50 50 30 10/10/09 10/21/09 10/19/09 10/21/09 10/11/09 10/12/09 10/15/09 10/20/09 10/24/09 10/15/09 10/15/09 10/26/09 10/20/09 10/27/09 10/20/09 10/29/09 10/25/09 11/03/09 10/25/09 10/30/09 10/25/09 11/02/09 10/25/09 11/03/09 10/25/09 11/03/09 10/28/09 11/07/09 11/04/09 10/28/09 10/29/09 10/29/09 11/01/09 11/01/09 11/01/09 11/01/09 11/02/09 11/05/09 11/05/09 11/05/09 11/05/09 11/05/09 11/04/09 11/10/09 11/09/09 11/12/09 11/07/09 11/06/09 11/13/09 11/16/09 11/15/09 11/11/09 11/10/09 11/17/09