MGMT 701 Information Systems Business Management Technology Lab Assignment 1 Tech Lab 1 You can work on your own or in a group of up to 5 students. If you choose group work, you will be responsible to form the groups. In this exercise, you will learn how to use spreadsheet software to improve management decisions about selecting suppliers. You will filter transactional data on suppliers based on several different criteria to select the best suppliers for your company. You run a company that manufactures aircraft components. You have many competitors who are trying to offer lower prices and better service to customers, and you are trying to determine whether you can benefit from better supply chain management. The spreadsheet file MIS13ch02_questionfile.xls contains a list of all of the items that your firm has ordered from its suppliers during the past three months. The fields in the spreadsheet file include vendor name, vendor identification number, purchaser's order number, item identification number and item description (for each item ordered from the vendor), cost per item, number of units of the item ordered (quantity), total cost of each order, vendor's accounts payable terms, order date, and actual arrival date for each order. Prepare a recommendation of how you can use the data in this spreadsheet database to improve your decisions about selecting suppliers. Some criteria to consider for identifying preferred suppliers include the supplier's track record for on-time deliveries, suppliers offering the best accounts payable terms, and suppliers offering lower pricing when the same item can be provided by multiple suppliers. Use your spreadsheet software to prepare reports to support your recommendations. Vendor No. Order No. Item No. Item Description 2 A0111 6489 O-Ring Orders and Suppliers Item Cost Quantity Cost per order A/P Terms Order Date Arrival Date $ 3.00 900 $ 2,700.00 25 10-10-12 10-18-12 6 A0125 5319 Shielded Cable/ft. $ 1.10 17,500 $ 19,250.00 30 08-20-12 08-31-12 6 A0123 4312 Bolt-nut package $ 3.75 4,250 $ 15,937.50 30 08-25-12 09-01-12 6 A0204 5319 Shielded Cable/ft. $ 1.10 16,500 $ 18,150.00 30 09-15-12 10-05-12 6 A0205 5677 Side Panel $195.00 120 $ 23,400.00 30 12-02-12 12-13-12 6 A0207 4312 Bolt nut package $ 3.75 4,200 $ 15,750.00 30 09-01-12 09-10-12 5 A0223 4224 Bolt-nut package $ 3.95 4,500 $ 17,775.00 30 10-15-12 10-20-12 5 A0433 5417 Control Panel 5 A0443 1243 Airframe fasteners $255.00 $ 4.25 500 $ 127,500.00 30 10-20-12 10-27-12 10,000 $ 42,500.00 30 08-08-12 08-14-12 5 A0446 5417 22222223 Control Panel $255.00 406 $ 103,530.00 30 09-01-12 09-10-12 A0533 9752 Gasket $ 4.05 1,500 $ 6,075.00 25 09-20-12 09-25-12 A0555 6489 O-Ring $ 3.00 1,100 $ 3,300.00 25 10-05-12 10-10-12 A0622 9752 Gasket $ 4.05 1,550 $ 6,277.50 25 09-25-12 10-05-12 A0666 5125 Shielded Cable/ft $ 1.15 15,000 $ 17,250.00 25 10-01-12 10-15-12 A0777 6489 O-Ring $ 3.00 1.050 $ 3,150.00 25 10-29-12 12-10-12 A1222 4111 Bolt-nut package $ 3.55 4,200 $ 14,910.00 25 09-15-12 10-15-12 A1234 9399 Gasket $ 3.65 1,250 $ 4,562.50 45 10-01-12 10-06-12 3 A1235 9399 Gasket $ 3 A1344 5454 Control Panel 3.65 $220.00 1,450 $ 5.292.50 45 10-03-12 10-08-12 550 $ 121,000.00 45 10-09-12 10-14-12 3 A1345 9399 Gasket $ 3.65 1,470 $ 5,365.50 45 10-07-12 10-12-12 3 A1346 9399 Gasket $ 3.65 1,985 $ 7,245.25 45 10-05-12 10-11-12 2 A1444 4111 Bolt-nut package $ 3.55 4,250 $ 15,087.50 25 09-20-12 10-10-12 2 A1445 4111 Bolt-nut package $ 3.55 4,200 $ 14,910.00 25 09-25-12 10-25-12 2 A1449 4111 Bolt-nut package $ 3.55 4,600 $ 16,330.00 25 10-05-12 10-19-12 3 A1456 5454 Control Panel $220.00 500 $ 110,000.00 45 10-15-12 10-20-12 3 A1457 4569 Bolt-nut package $ 3.50 3,900 $ 13,650.00 45 10-05-12 10-10-12 3 A1567 1369 Airframe fasteners $ 4.20 15,000 $ 63,000.00 45 09-25-12 09-30-12 7 A2345 6431 O-Ring $ 2.85 1,250 $ 3,562.50 30 10-05-12 10-10-12 7 A2356 7258 Pressure Gauge $100.50 95 $ 9,547.50 30 10-20-12 10-29-12 7 A2367 9977 Panel Decal $ 1.00 525 $ 525.00 30 12-01-12 12-07-12 7 A2378 6431 O-Ring $ 2.85 1,350 $ 3,847.50 30 10-01-12 10-07-12 4.000 nc an or 10 Data On-Time-Suppliers Accts-Payable-Terms Supplier Costs M B C D E F G H I J K M 35 7 A2378 6431 O-Ring $ 2.85 1,350 $ 3,847.50 30 10-01-12 10-07-12 36 2 A3467 4111 Bolt-nut package $ 3.55 4,800 $ 17,040.00 25 09-05-12 09-20-12 37 2 A5689 4111 Bolt-nut package $ 3.55 4,585 $ 16,276.75 25 09-10-12 09-30-12 38 7 A9821 6431 O-Ring $ 2.85 1,300 $ 3,705.00 30 09-25-12 10-01-12 39 7 A9842 7258 Pressure Gauge $100.50 100 $ 10,050.00 30 10-15-12 10-24-12 40 7 A9865 9967 Hatch Decal $ 0.85 550 $ 467.50 30 12-05-12 12-12-12 41 7 A9876 9955 Door Decal $ 0.55 42 8 A9999 6433 O-Ring $ 2.95 150 $ 1,500 $ 82.50 30 12-01-12 12-06-12 43 5 B0247 1243 Airframe fasteners $ 4.25 9,000 $ 595 4,425.00 15 10-01-12 10-10-12 38,250.00 30 09-05-12 09-12-12 44 6 B0445 4312 Bolt-nut package $ 3.75 4,150 $ 15,562.50 30 09-03-12 09-12-12 45 5 B0447 5634 Side Panel $185.00 150 $ 27,750.00 30 10-25-12 12-03-12 46 5 B0479 5634 Side Panel $185.00 140 $ 25,900.00 30 10-29-12 12-04-12 47 5 B0567 1243 Airframe fasteners $ 4.25 10,500 $ 44,625.00 30 10-10-12 10-17-12 48 8 B1111 9764 Gasket $ 3.75 1,980 $ 7,425.00 15 09-20-12 09-29-12 49 3 B1234 7258 Pressure Gauge $ 90.00 100 $ 9,000.00 45 08-25-12 08-28-12 50 3 B1345 7258 Pressure Gauge $ 90.00 120 $ 10,800.00 45 09-05-12 09-09-12 51 3 B1468 1369 Airframe fasteners $ 4.20 14,000 $ 58,800.00 45 09-27-12 10-03-12 52 3 B1589 5275 Shielded Cable/ft $ 1.00 25,000 $ 25.000.00 45 10-25-12 10-30-12 53 3 B1666 1369 Airframe fasteners $ 4.20 10,000 $ 42,000.00 45 09-29-12 10-04-12 54 4 B2333 6321 O-Ring $ 2.45 1,300 $ 3,185.00 30 08-25-12 09-04-12 55 4 B2345 6321 O-Ring $ 2.45 1,200 $ 2.940.00 30 09-12-12 09-23-12 56 4 B2356 6321 O-Ring $ 2.45 2,500 $ 6,125.00 30 09-25-12 10-04-12 57 4 B2367 6321 O-Ring $ 2.45 1,250 $ 3,062 50 30 10-12-12 10-21-12 58 4 B2378 6321 O-Ring $ 2.45 1,500 $ 3.675.00 30 10-25-12 12-02-12 59 4 B2498 5689 Side Panel $175.00 150 $ 26,250.00 30 12-01-12 12-09-12 60 4 B2499 7268 Pressure Gauge $ 95.00 110 $ 10,450.00 30 12-01-12 12-12-12 61 4 B2511 7268 Pressure Gauge $ 95.00 105 $ 9,975.00 30 12-05-12 12-16-12 62 4 B2519 5462 Shielded Cable/ft. $ 1.05 22,500 $ 23,625.00 30 08-20-12 08-26-12 63 4 B2528 5689 Side Panel $175.00 175 $ 30,625.00 30 12-05-12 12-15-12 64 4 B2537 5462 Shielded Cable/ft. $ 1.05 21,500 $ 22,575.00 30 08-15-12 08-22-12 65 4 B2566 5462 Shielded Cable/ft. $ 1.05 23,000 $ 24,150.00 30 08-10-12 08-15-12 66 6 B3022 5677 Side Panel $195.00 110 $ 21,450.00 30 12-05-12 12-17-12 67 6 B3041 5234 Electrical Connector $ 69 1.65 4,500 $ 7,425.00 30 08-28-12 09-05-12 68 6 B3042 5234 60 R2111 Electrical Connector $ 1.65 5234 Electrical Connector @ 1.65 4,750 $ 7,837.50 30 09-05-12 09-13-12 1.860 8.002.50 00.02.12 na 12 12 Data On-Time-Suppliers Accts-Payable-Terms Supplier Costs Ready B C D E F G H 1 J 65 K 4 B2566 M 5462 N Shielded Cable/ft. $ 1.05 23,000 $ 66 6 B3022 5677 Side Panel $195.00 110 $ 67 6 B3041 5234 Electrical Connector $ 1.65 4,500 $ 68 6 B3042 5234 Electrical Connector $ 1.65 4,750 $ 69 6 B3111 5234 Electrical Connector $ 1.65 4,850 70 6 B3222 8008 Machined Valve $645.00 150 71 6 B3333 8008 Machined Valve $645.00 100 72 4 C0234 5166 Electrical Connector $ 1:25 5,650 73 4 C0423 5689 Side Panel $175.00 74 4 C0433 5462 Shielded Cable/ft. $ 1.05 155 $ 22,500 $ 75 6 C0456 5677 Side Panel $195.00 76 6 C0467 8008 Machined Valve $645.00 130 $ 120 77 6 C0589 5319 Shielded Cable/ft. $ 1.10 18,100 78 7 C1111 9955 Door Decal $ 0.55 125 79 1 C1212 1122 Airframe fasteners $ 4.25 19,500 80 1 C1313 3166 Electrical Connector $ 1.25 5.600 81 8 C2211 9764 Gasket $ 3.75 1,850 82 7 C2222 7258 Pressure Gauge $100.50 90 83 1 C2323 1122 Airframe fasteners $ 4.25 15,500 34 1 C2929 3166 Electrical Connector $ 1.25 5,500 35 1 C3232 1122 Airframe fasteners $ 4.25 18,000 36 7 C3333 8148 Machined Valve $655.50 125 EA EA EA SA CA SA SA SA SA SA SA SA SA SASAAASSSSSS 24,150.00 30 08-10-12 08-15-12 21,450.00 30 12-05-12 12-17-12 7,425.00 30 08-28-12 09-05-12 7,837.50 30 09-05-12 09-13-12 $ 8,002.50 30 09-02-12 09-12-12 96,750.00 30 10-15-12 10-26-12 64,500.00 30 10-10-12 10-21-12 7,062.50 30 10-01-12 10-06-12 27,125.00 30 10-25-12 12-03-12 23,625.00 30 08-25-12 09-02-12 25,350.00 30 10-28-12 12-07-12 77,400.00 30 10-28-12 12-04-12 19,910.00 30 08-25-12 09-05-12 68.75 30 12-05-12 12-10-12 82,875.00 30 08-05-12 08-13-12 $ 7,000.00 30 08-25-12 08-29-12 $ 6,937.50 15 09-25-12 10-05-12 $ 9,045.00 30 10-10-12 10-17-12 65,875.00 30 09-04-12 09-12-12 6,875.00 30 09-01-12 09-06-12 76,500.00 30 10-01-12 10-08-12 81,937.50 30 10-10-12 10-17-12 37 1 C3434 1122 Airframe fasteners $ 4.25 12,500 $ 38 1 C4545 1122 Airframe fasteners 39 1 C5656 1122 Airframe fasteners $ 0 1 C6765 5066 Shielded Cable/ft. AAAAA $ 4.25 15,000 $ 4.25 14,500 $ $ 0.95 25,000 $ 1 1 C7875 3166 Electrical Connector FA $ 1.25 5,650 2 1 C8854 3166 Electrical Connector 3 1 C8989 9966 Hatch Decal 4 8 D1111 9764 Gasket 5 1 D1212 5066 Shielded Cable/ft. 6 1 D2121 1122 Airframe fasteners $ 4.25 7 1 D3232 1122 3 8 D3333 9764 Airframe fasteners Gasket EA SA SAA AAAA AA $ 1.25 5,425 $ 0.75 500 $ 3.75 $ 0.95 1,800 17,500 $ 17,500 $ 5 59 59 59 59 A A A A S 53,125.00 30 09-05-12 09-12-12 63,750.00 30 09-08-12 09-15-12 61,625.00 30 09-28-12 10-03-12 23,750.00 30 09-05-12 09-12-12 $ 7,062.50 30 09-05-12 09-10-12 $ 6,781.25 30 09-10-12 09-15-12 375.00 30 08-25-12 08-31-12 $ 6,750.00 15 09-28-12 10-05-12 16,625.00 30 09-15-12 09-22-12 74,375.00 30 10-25-12 12-03-12 $ 4.25 17,000 $ 72,250.00 30 10-12-12 10-19-12 $ 3.75 1,750 $ 6,562.50 15 09-20-12 09-25-12 Data On-Time-Suppliers Accts-Payable-Terms Supplier Costs