Improving Decision Making: Using a Spreadsheet to select Suppliers (Detailed Instructions)

1. Read Improving Decision Making: Using a Spreadsheet (Excel) to select Suppliers
2. Download the files labeled with MIS13ch02-questionfile.xls
3. Perform the following activities:

a) Prepare a recommendation of how you can use the data in this spreadsheet database to improve your decisions about selecting suppliers.

b) Use your spreadsheet software to prepare the following reports to support your recommendations:

Report 1: Most On-time Suppliers

Report 2: Best Accounts Payable Terms

Report 3: Best Suppliers Price per Item

Orders and Suppliers m Order No. Item No. Item Description A0111 6489 O-Ring A0125 5319 Shielded Cable/ft A0123 4312 Bolt-nut package A0204 5319 Shielded Cable/ft A0205 5677 Side Panel A0207 4312 Bolt-nut package A0223 4224 Bolt-nut package A0433 5417 Control Panel A0443 1243 Airframe fasteners A0446 5417 Control Panel A0533 9752 Gasket A0555 6489 O-Ring A0622 9752 Gasket A0666 5125 Shielded Cable/ft. A0777 6489 O-Ring 0 A1222 4111 Bolt-nut package 1 A1234 9399 Gasket 2 A1235 9399 Gasket 23 A1344 5454 Control Panel 24 A1345 9399 Gasket 25 A1346 9399 Gasket 26 A1444 4111 Bolt-nut package 27 A1445 4111 Bolt-nut package 28 A1449 4111 Bolt-nut package 29 A1456 5454 Control Panel 30 A1457 4569 Bolt-nut package 31 A1567 1369 Airframe fasteners 32 A2345 6431 O-Ring 33 A2356 7258 Pressure Gauge 34 A2367 9977 Panel Decal Item Cost Quantity Cost per order A/P Terms Order Date Arrival Date $ 3.00 900 $ 2,700.00 25 10/10/14 10/18/14 $ 1.10 17,500 $ 19,250.00 30 08/20/14 08/31/14 $ 3.75 4,250 $ 15,937.50 30 08/25/14 09/01/14 $ 1.10 16,500 $ 18,150.00 30 09/15/14 10/05/14 $ 195.00 120 $ 23,400.00 30 12/02/14 12/13/14 $ 3.75 4,200 $ 15,750.00 30 09/01/14 09/10/14 $ 3.95 4,500 $ 17,775.00 30 10/15/14 10/20/14 $255.00 500 $ 127,500.00 30 10/20/14 10/27/14 $ 4.25 10,000 $ 42,500.00 30 08/08/14 08/14/14 $ 255.00 406 $ 103,530.00 30 09/01/14 09/10/14 $ 4.05 1,500 $ 6,075.00 25 09/20/14 09/25/14 $ 3.00 1,100 $ 3,300.00 25 10/05/14 10/10/14 $ 4.05 1,550 $ 6,277.50 25 09/25/14 10/05/14 $ 1.15 15,000 $ 17,250.00 25 10/01/14 10/15/14 $ 3.00 1,050 $ 3,150.00 25 10/29/14 12/10/14 $ 3.55 4,200 $ 14,910.00 25 09/15/14 10/15/14 $ 3.65 1,250 $ 4,562.50 45 10/01/14 10/06/14 $ 3.65 1,450 $ 5,292.50 45 10/03/14 10/08/14 $ 220.00 550 $ 121,000.00 45 10/09/14 10/14/14 $ 3.65 1,470 $ 5,365.50 45 10/07/14 10/12/14 $ 3.65 1,985 $ 7,245.25 45 10/05/14 10/11/14 $ 3.55 4,250 $ 15,087.50 25 09/20/14 10/10/14 $ 3.55 4,200 $ 14,910.00 25 09/25/14 10/25/14 $ 3.55 4,600 $ 16,330.00 25 10/05/14 10/19/14 $ 220.00 500 $ 110,000.00 45 10/15/14 10/20/14 $ 3.50 3,900 $ 13,650.00 45 10/05/14 10/10/14 $ 4.20 15,000 S 63,000.00 45 09/25/14 09/30/14 $ 2.85 1.250 $ 3,562.50 30 10/05/14 10/10/14 $ 100.50 95 $ 9,547.50 30 10/20/14 10/29/14 $ 1.00 525 $ 525.00 30 12/01/14 12/07/14 9 Format Painter Clipboard Font Alignment Number X & fx G C A2378 A3467 A5689 A9821 A9842 A9865 A9876 A9999 B0247 B0445 B0447 B0479 B0567 B1111 B1234 B1345 B1468 B1589 B1666 B2333 B2345 B2356 B2367 B2378 B2498 B2499 B2511 B2519 B2528 B2537 B2566 B3022 B3041 B3042 R2111 D 6431 4111 4111 6431 7258 9967 9955 6433 1243 4312 5634 5634 1243 9764 7258 7258 1369 5275 1369 6321 6321 6321 6321 6321 5689 7268 7268 5462 5689 5462 5462 5677 5234 5234 5224 E O-Ring Bolt-nut package Bolt-nut package O-Ring Pressure Gauge Hatch Decal Door Decal O-Ring Airframe fasteners Bolt-nut package Side Panel Side Panel Airframe fasteners Gasket Pressure Gauge Pressure Gauge Airframe fasteners Shielded Cable/ft. Airframe fasteners O-Ring O-Ring O-Ring O-Ring O-Ring Side Panel Pressure Gauge Pressure Gauge Shielded Cable/ft Side Panel Shielded Cable/ft Shielded Cable/ft. Side Panel Electrical Connector Electrical Connector Flortrical Connontor F $ 2.85 $ 3.55 $ 3.55 $ 2.85 $ 100.50 $ 0.85 0.55 $ 2.95 $ 4.25 $ 3.75 $ 185.00 $ 185.00 $ 4.25 $ 3.75 $ 90.00 $ 90.00 $ 4.20 $ 1.00 $ 4.20 $ 2.45 $ 2.45 $ 2.45 $ 2.45 $ 2.45 $ 175.00 $ 95.00 $ 95.00 1.05 $ 175.00 1.05 1.05 $ 195.00 $ 1.65 $ 1.65 165 1,350 $ 4,800 $ 4,585 $ 1,300 $ 100 $ 550 $ 150 $ 1,500 $ 9,000 $ 4,150 $ 150 $ 140 $ 10,500 $ 1,980 $ 100 $ 120 $ 14,000 $ 25,000 $ 10,000 $ 1,300 $ 1,200 $ 2,500 $ 1,250 $ 1,500 $ 150 $ 110 $ 105 $ 22,500 $ 175 $ 21,500 $ 23,000 $ 110 $ 4,500 $ 4,750 $ 1 850 3,847.50 17,040.00 16,276.75 3,705.00 10,050.00 467.50 82.50 4,425.00 38,250.00 15,562.50 27,750.00 25,900.00 44,625.00 7,425.00 9,000.00 10,800.00 58,800.00 25,000.00 42,000.00 3,185.00 2,940.00 6,125.00 3,062.50 3,675.00 26,250.00 10,450.00 9,975.00 23,625.00 30,625.00 22,575.00 24,150.00 21,450.00 7,425.00 7,837.50 RO2 50 30 25 25 30 30 30 30 15 30 30 30 30 30 15 45 45 45 45 45 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 an 10/01/14 09/05/14 09/10/14 09/25/14 10/15/14 12/05/14 12/01/14 10/01/14 09/05/14 09/03/14 10/25/14 10/29/14 10/10/14 09/20/14 08/25/14 09/05/14 09/27/14 10/25/14 09/29/14 08/25/14 09/12/14 09/25/14 10/12/14 10/25/14 12/01/14 12/01/14 12/05/14 08/20/14 12/05/14 08/15/14 08/10/14 12/05/14 08/28/14 09/05/14 nain2/14 K 10/07/14 09/20/14 09/30/14 10/01/14 10/24/14 12/12/14 12/06/14 10/10/14 09/12/14 09/12/14 12/03/14 12/04/14 10/17/14 09/29/14 08/28/14 09/09/14 10/03/14 10/30/14 10/04/14 09/04/14 09/23/14 10/04/14 10/21/14 12/02/14 12/09/14 12/12/14 12/16/14 08/26/14 12/15/14 08/22/14 08/15/14 12/17/14 09/05/14 09/13/14 na/12/14 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 B3041 B3042 B3111 B3222 B3333 C0234 C0423 C0433 C0456 C0467 C0589 C1111 C1212 C1313 C2211 C2222 C2323 C2929 C3232 C3333 C3434 C4545 C5656 C6765 C7875 C8854 C8989 D1111 D1212 D2121 D3232 D3333 5234 5234 5234 8008 8008 5166 5689 5462 5677 8008 5319 9955 1122 3166 9764 7258 1122 3166 1122 8148 1122 1122 1122 5066 3166 3166 9966 9764 5066 1122 1122 9764 Electrical Connector Electrical Connector Electrical Connector Machined Valve Machined Valve Electrical Connector Side Panel Shielded Cable/ft. Side Panel Machined Valve Shielded Cable/ft. Door Decal Airframe fasteners Electrical Connector Gasket Pressure Gauge Airframe fasteners Electrical Connector Airframe fasteners Machined Valve Airframe fasteners Airframe fasteners Airframe fasteners Shielded Cable/ft. Electrical Connector Electrical Connector Hatch Decal Gasket Shielded Cable/ft. Airframe fasteners Airframe fasteners Gasket 1.65 1.65 1.65 $ 645.00 $ 645.00 $ 1.25 $ 175.00 1.05 $ 195.00 $ 645.00 1.10 0.55 4.25 1.25 3.75 $ 100.50 4.25 1.25 4.25 $ 655.50 $ 4.25 $ 4.25 $ 4.25 $ 0.95 $ 1.25 $ 1.25 $ 0.75 $ 3.75 $ 0.95 $ 4.25 $ 4.25 3.75 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4,500 $ 4,750 $ 4,850 $ 150 $ 100 $ 5,650 $ 155 $ 22,500 $ 130 $ 120 $ 18,100 $ 125 $ 19,500 $ 5,600 $ 1,850 $ 90 $ 15,500 $ 5,500 $ 18,000 $ 125 $ 12,500 $ 15,000 $ 14,500 $ 25,000 $ 5,650 $ 5,425 $ 500 $ 1,800 $ 17,500 $ 17,500 $ 17,000 $ 1,750 $ 7,425.00 7,837.50 8,002.50 96,750.00 64,500.00 7,062.50 27,125.00 23,625.00 25,350.00 77,400.00 19,910.00 68.75 82,875.00 7,000.00 6,937.50 9,045.00 65,875.00 6,875.00 76,500.00 81,937.50 53,125.00 63,750.00 61,625.00 23,750.00 7,062.50 6,781.25 375.00 6,750.00 16,625.00 74,375.00 72,250.00 6,562.50 30 30 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 15 08/28/14 09/05/14 09/02/14 10/15/14 10/10/14 10/01/14 10/25/14 08/25/14 10/28/14 10/28/14 08/25/14 12/05/14 08/05/14 08/25/14 09/25/14 10/10/14 09/04/14 09/01/14 10/01/14 10/10/14 09/05/14 09/08/14 09/28/14 09/05/14 09/05/14 09/10/14 08/25/14 09/28/14 09/15/14 10/25/14 10/12/14 09/20/14 09/05/14 09/13/14 09/12/14 10/26/14 10/21/14 10/06/14 12/03/14 09/02/14 12/07/14 12/04/14 09/05/14 12/10/14 08/13/14 08/29/14 10/05/14 10/17/14 09/12/14 09/06/14 10/08/14 10/17/14 09/12/14 09/15/14 10/03/14 09/12/14 09/10/14 09/15/14 08/31/14 10/05/14 09/22/14 12/03/14 10/19/14 09/25/14 GA Orders and Suppliers m Order No. Item No. Item Description A0111 6489 O-Ring A0125 5319 Shielded Cable/ft A0123 4312 Bolt-nut package A0204 5319 Shielded Cable/ft A0205 5677 Side Panel A0207 4312 Bolt-nut package A0223 4224 Bolt-nut package A0433 5417 Control Panel A0443 1243 Airframe fasteners A0446 5417 Control Panel A0533 9752 Gasket A0555 6489 O-Ring A0622 9752 Gasket A0666 5125 Shielded Cable/ft. A0777 6489 O-Ring 0 A1222 4111 Bolt-nut package 1 A1234 9399 Gasket 2 A1235 9399 Gasket 23 A1344 5454 Control Panel 24 A1345 9399 Gasket 25 A1346 9399 Gasket 26 A1444 4111 Bolt-nut package 27 A1445 4111 Bolt-nut package 28 A1449 4111 Bolt-nut package 29 A1456 5454 Control Panel 30 A1457 4569 Bolt-nut package 31 A1567 1369 Airframe fasteners 32 A2345 6431 O-Ring 33 A2356 7258 Pressure Gauge 34 A2367 9977 Panel Decal Item Cost Quantity Cost per order A/P Terms Order Date Arrival Date $ 3.00 900 $ 2,700.00 25 10/10/14 10/18/14 $ 1.10 17,500 $ 19,250.00 30 08/20/14 08/31/14 $ 3.75 4,250 $ 15,937.50 30 08/25/14 09/01/14 $ 1.10 16,500 $ 18,150.00 30 09/15/14 10/05/14 $ 195.00 120 $ 23,400.00 30 12/02/14 12/13/14 $ 3.75 4,200 $ 15,750.00 30 09/01/14 09/10/14 $ 3.95 4,500 $ 17,775.00 30 10/15/14 10/20/14 $255.00 500 $ 127,500.00 30 10/20/14 10/27/14 $ 4.25 10,000 $ 42,500.00 30 08/08/14 08/14/14 $ 255.00 406 $ 103,530.00 30 09/01/14 09/10/14 $ 4.05 1,500 $ 6,075.00 25 09/20/14 09/25/14 $ 3.00 1,100 $ 3,300.00 25 10/05/14 10/10/14 $ 4.05 1,550 $ 6,277.50 25 09/25/14 10/05/14 $ 1.15 15,000 $ 17,250.00 25 10/01/14 10/15/14 $ 3.00 1,050 $ 3,150.00 25 10/29/14 12/10/14 $ 3.55 4,200 $ 14,910.00 25 09/15/14 10/15/14 $ 3.65 1,250 $ 4,562.50 45 10/01/14 10/06/14 $ 3.65 1,450 $ 5,292.50 45 10/03/14 10/08/14 $ 220.00 550 $ 121,000.00 45 10/09/14 10/14/14 $ 3.65 1,470 $ 5,365.50 45 10/07/14 10/12/14 $ 3.65 1,985 $ 7,245.25 45 10/05/14 10/11/14 $ 3.55 4,250 $ 15,087.50 25 09/20/14 10/10/14 $ 3.55 4,200 $ 14,910.00 25 09/25/14 10/25/14 $ 3.55 4,600 $ 16,330.00 25 10/05/14 10/19/14 $ 220.00 500 $ 110,000.00 45 10/15/14 10/20/14 $ 3.50 3,900 $ 13,650.00 45 10/05/14 10/10/14 $ 4.20 15,000 S 63,000.00 45 09/25/14 09/30/14 $ 2.85 1.250 $ 3,562.50 30 10/05/14 10/10/14 $ 100.50 95 $ 9,547.50 30 10/20/14 10/29/14 $ 1.00 525 $ 525.00 30 12/01/14 12/07/14 9 Format Painter Clipboard Font Alignment Number X & fx G C A2378 A3467 A5689 A9821 A9842 A9865 A9876 A9999 B0247 B0445 B0447 B0479 B0567 B1111 B1234 B1345 B1468 B1589 B1666 B2333 B2345 B2356 B2367 B2378 B2498 B2499 B2511 B2519 B2528 B2537 B2566 B3022 B3041 B3042 R2111 D 6431 4111 4111 6431 7258 9967 9955 6433 1243 4312 5634 5634 1243 9764 7258 7258 1369 5275 1369 6321 6321 6321 6321 6321 5689 7268 7268 5462 5689 5462 5462 5677 5234 5234 5224 E O-Ring Bolt-nut package Bolt-nut package O-Ring Pressure Gauge Hatch Decal Door Decal O-Ring Airframe fasteners Bolt-nut package Side Panel Side Panel Airframe fasteners Gasket Pressure Gauge Pressure Gauge Airframe fasteners Shielded Cable/ft. Airframe fasteners O-Ring O-Ring O-Ring O-Ring O-Ring Side Panel Pressure Gauge Pressure Gauge Shielded Cable/ft Side Panel Shielded Cable/ft Shielded Cable/ft. Side Panel Electrical Connector Electrical Connector Flortrical Connontor F $ 2.85 $ 3.55 $ 3.55 $ 2.85 $ 100.50 $ 0.85 0.55 $ 2.95 $ 4.25 $ 3.75 $ 185.00 $ 185.00 $ 4.25 $ 3.75 $ 90.00 $ 90.00 $ 4.20 $ 1.00 $ 4.20 $ 2.45 $ 2.45 $ 2.45 $ 2.45 $ 2.45 $ 175.00 $ 95.00 $ 95.00 1.05 $ 175.00 1.05 1.05 $ 195.00 $ 1.65 $ 1.65 165 1,350 $ 4,800 $ 4,585 $ 1,300 $ 100 $ 550 $ 150 $ 1,500 $ 9,000 $ 4,150 $ 150 $ 140 $ 10,500 $ 1,980 $ 100 $ 120 $ 14,000 $ 25,000 $ 10,000 $ 1,300 $ 1,200 $ 2,500 $ 1,250 $ 1,500 $ 150 $ 110 $ 105 $ 22,500 $ 175 $ 21,500 $ 23,000 $ 110 $ 4,500 $ 4,750 $ 1 850 3,847.50 17,040.00 16,276.75 3,705.00 10,050.00 467.50 82.50 4,425.00 38,250.00 15,562.50 27,750.00 25,900.00 44,625.00 7,425.00 9,000.00 10,800.00 58,800.00 25,000.00 42,000.00 3,185.00 2,940.00 6,125.00 3,062.50 3,675.00 26,250.00 10,450.00 9,975.00 23,625.00 30,625.00 22,575.00 24,150.00 21,450.00 7,425.00 7,837.50 RO2 50 30 25 25 30 30 30 30 15 30 30 30 30 30 15 45 45 45 45 45 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 an 10/01/14 09/05/14 09/10/14 09/25/14 10/15/14 12/05/14 12/01/14 10/01/14 09/05/14 09/03/14 10/25/14 10/29/14 10/10/14 09/20/14 08/25/14 09/05/14 09/27/14 10/25/14 09/29/14 08/25/14 09/12/14 09/25/14 10/12/14 10/25/14 12/01/14 12/01/14 12/05/14 08/20/14 12/05/14 08/15/14 08/10/14 12/05/14 08/28/14 09/05/14 nain2/14 K 10/07/14 09/20/14 09/30/14 10/01/14 10/24/14 12/12/14 12/06/14 10/10/14 09/12/14 09/12/14 12/03/14 12/04/14 10/17/14 09/29/14 08/28/14 09/09/14 10/03/14 10/30/14 10/04/14 09/04/14 09/23/14 10/04/14 10/21/14 12/02/14 12/09/14 12/12/14 12/16/14 08/26/14 12/15/14 08/22/14 08/15/14 12/17/14 09/05/14 09/13/14 na/12/14 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 B3041 B3042 B3111 B3222 B3333 C0234 C0423 C0433 C0456 C0467 C0589 C1111 C1212 C1313 C2211 C2222 C2323 C2929 C3232 C3333 C3434 C4545 C5656 C6765 C7875 C8854 C8989 D1111 D1212 D2121 D3232 D3333 5234 5234 5234 8008 8008 5166 5689 5462 5677 8008 5319 9955 1122 3166 9764 7258 1122 3166 1122 8148 1122 1122 1122 5066 3166 3166 9966 9764 5066 1122 1122 9764 Electrical Connector Electrical Connector Electrical Connector Machined Valve Machined Valve Electrical Connector Side Panel Shielded Cable/ft. Side Panel Machined Valve Shielded Cable/ft. Door Decal Airframe fasteners Electrical Connector Gasket Pressure Gauge Airframe fasteners Electrical Connector Airframe fasteners Machined Valve Airframe fasteners Airframe fasteners Airframe fasteners Shielded Cable/ft. Electrical Connector Electrical Connector Hatch Decal Gasket Shielded Cable/ft. Airframe fasteners Airframe fasteners Gasket 1.65 1.65 1.65 $ 645.00 $ 645.00 $ 1.25 $ 175.00 1.05 $ 195.00 $ 645.00 1.10 0.55 4.25 1.25 3.75 $ 100.50 4.25 1.25 4.25 $ 655.50 $ 4.25 $ 4.25 $ 4.25 $ 0.95 $ 1.25 $ 1.25 $ 0.75 $ 3.75 $ 0.95 $ 4.25 $ 4.25 3.75 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4,500 $ 4,750 $ 4,850 $ 150 $ 100 $ 5,650 $ 155 $ 22,500 $ 130 $ 120 $ 18,100 $ 125 $ 19,500 $ 5,600 $ 1,850 $ 90 $ 15,500 $ 5,500 $ 18,000 $ 125 $ 12,500 $ 15,000 $ 14,500 $ 25,000 $ 5,650 $ 5,425 $ 500 $ 1,800 $ 17,500 $ 17,500 $ 17,000 $ 1,750 $ 7,425.00 7,837.50 8,002.50 96,750.00 64,500.00 7,062.50 27,125.00 23,625.00 25,350.00 77,400.00 19,910.00 68.75 82,875.00 7,000.00 6,937.50 9,045.00 65,875.00 6,875.00 76,500.00 81,937.50 53,125.00 63,750.00 61,625.00 23,750.00 7,062.50 6,781.25 375.00 6,750.00 16,625.00 74,375.00 72,250.00 6,562.50 30 30 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 15 08/28/14 09/05/14 09/02/14 10/15/14 10/10/14 10/01/14 10/25/14 08/25/14 10/28/14 10/28/14 08/25/14 12/05/14 08/05/14 08/25/14 09/25/14 10/10/14 09/04/14 09/01/14 10/01/14 10/10/14 09/05/14 09/08/14 09/28/14 09/05/14 09/05/14 09/10/14 08/25/14 09/28/14 09/15/14 10/25/14 10/12/14 09/20/14 09/05/14 09/13/14 09/12/14 10/26/14 10/21/14 10/06/14 12/03/14 09/02/14 12/07/14 12/04/14 09/05/14 12/10/14 08/13/14 08/29/14 10/05/14 10/17/14 09/12/14 09/06/14 10/08/14 10/17/14 09/12/14 09/15/14 10/03/14 09/12/14 09/10/14 09/15/14 08/31/14 10/05/14 09/22/14 12/03/14 10/19/14 09/25/14 GA