Spreadsheet: 147330d1332740857-excel-2007-supply-chart-formulation-supplychain1
Sheet: Sheet1

 |  |  |  | Orders and [NAME]
[NAME] | Vendor No. | Order No. | Item No. | Item Description | Item Cost | Quantity | Cost per order | A/P Terms | Ship Date | Transit Time | [NAME]
Spacetime Technologies | 2 | A0111 | 6489 | O-Ring | 3 | 900 | 2700 | 25 | 2004-10-10 00:00:00 | 5 | 2004-10-18 00:00:00
Steelpin Inc. | 6 | A0115 | 5319 | Shielded Cable/ft. | 1.1 | 17500 | 19250 | 30 | 2004-08-20 00:00:00 | 11 | 2004-08-31 00:00:00
Steelpin Inc. | 6 | A0123 | 4312 | Bolt-nut package | 3.75 | 4250 | 15937.5 | 30 | 2004-08-25 00:00:00 | 5 | 2004-09-01 00:00:00
Steelpin Inc. | 6 | A0204 | 5319 | Shielded Cable/ft. | 1.1 | 16500 | 18150 | 30 | 2004-09-15 00:00:00 | 15 | 2004-10-05 00:00:00
Steelpin Inc. | 6 | A0205 | 5677 | Side Panel | 195 | 120 | 23400 | 30 | 2004-11-02 00:00:00 | 8 | 2004-11-13 00:00:00
Steelpin Inc. | 6 | A0207 | 4312 | Bolt-nut package | 3.75 | 4200 | 15750 | 30 | 2004-09-01 00:00:00 | 6 | 2004-09-10 00:00:00
Alum Sheeting | 5 | A0223 | 4224 | Bolt-nut package | 3.95 | 4500 | 17775 | 30 | 2004-10-15 00:00:00 | 5 | 2004-10-20 00:00:00
Alum Sheeting | 5 | A0433 | 5417 | Control Panel | 255 | 500 | 127500 | 30 | 2004-10-20 00:00:00 | 5 | 2004-10-27 00:00:00
Alum Sheeting | 5 | A0443 | 1243 | Airframe fasteners | 4.25 | 10000 | 42500 | 30 | 2004-08-08 00:00:00 | 5 | 2004-08-14 00:00:00
Alum Sheeting | 5 | A0446 | 5417 | Control Panel | 255 | 405 | 103275 | 30 | 2004-09-01 00:00:00 | 6 | 2004-09-10 00:00:00
Spacetime Technologies | 2 | A0533 | 9752 | Gasket | 4.05 | 1500 | 6075 | 25 | 2004-09-20 00:00:00 | 5 | 2004-09-25 00:00:00
Spacetime Technologies | 2 | A0555 | 6489 | O-Ring | 3 | 1100 | 3300 | 25 | 2004-10-05 00:00:00 | 5 | 2004-10-10 00:00:00
Spacetime Technologies | 2 | A0622 | 9752 | Gasket | 4.05 | 1550 | 6277.5 | 25 | 2004-09-25 00:00:00 | 5 | 2004-10-05 00:00:00
Spacetime Technologies | 2 | A0666 | 5125 | Shielded Cable/ft. | 1.15 | 15000 | 17250 | 25 | 2004-10-01 00:00:00 | 10 | 2004-10-15 00:00:00
Spacetime Technologies | 2 | A0777 | 6489 | O-Ring | 3 | 1050 | 3150 | 25 | 2004-10-29 00:00:00 | 5 | 2004-11-10 00:00:00
Spacetime Technologies | 2 | A1222 | 4111 | Bolt-nut package | 3.55 | 4200 | 14910 | 25 | 2004-09-15 00:00:00 | 20 | 2004-10-15 00:00:00
Durrable Products | 3 | A1234 | 9399 | Gasket | 3.65 | 1250 | 4562.5 | 45 | 2004-10-01 00:00:00 | 5 | 2004-10-06 00:00:00
Durrable Products | 3 | A1235 | 9399 | Gasket | 3.65 | 1450 | 5292.5 | 45 | 2004-10-03 00:00:00 | 5 | 2004-10-08 00:00:00
Durrable Products | 3 | A1344 | 5454 | Control Panel | 220 | 550 | 121000 | 45 | 2004-10-09 00:00:00 | 6 | 2004-10-14 00:00:00
Durrable Products | 3 | A1345 | 9399 | Gasket | 3.65 | 1470 | 5365.5 | 45 | 2004-10-07 00:00:00 | 5 | 2004-10-12 00:00:00
Durrable Products | 3 | A1346 | 9399 | Gasket | 3.65 | 1985 | 7245.25 | 45 | 2004-10-05 00:00:00 | 5 | 2004-10-11 00:00:00
Spacetime Technologies | 2 | A1444 | 4111 | Bolt-nut package | 3.55 | 4250 | 15087.5 | 25 | 2004-09-20 00:00:00 | 15 | 2004-10-10 00:00:00
Spacetime Technologies | 2 | A1445 | 4111 | Bolt-nut package | 3.55 | 4200 | 14910 | 25 | 2004-09-25 00:00:00 | 25 | 2004-10-25 00:00:00
Spacetime Technologies | 2 | A1449 | 4111 | Bolt-nut package | 3.55 | 4600 | 16330 | 25 | 2004-10-05 00:00:00 | 15 | 2004-10-19 00:00:00
Durrable Products | 3 | A1456 | 5454 | Control Panel | 220 | 500 | 110000 | 45 | 2004-10-15 00:00:00 | 5 | 2004-10-20 00:00:00
Durrable Products | 3 | A1457 | 4569 | Bolt-nut package | 3.5 | 3900 | 13650 | 45 | 2004-10-05 00:00:00 | 5 | 2004-10-10 00:00:00
Durrable Products | 3 | A1567 | 1369 | Airframe fasteners | 4.2 | 15000 | 63000 | 45 | 2004-09-25 00:00:00 | 5 | 2004-09-30 00:00:00
[NAME] | 7 | A2345 | 6431 | O-Ring | 2.85 | 1250 | 3562.5 | 30 | 2004-10-05 00:00:00 | 5 | 2004-10-10 00:00:00
[NAME] | 7 | A2356 | 7258 | Pressure Gauge | 100.5 | 95 | 9547.5 | 30 | 2004-10-20 00:00:00 | 8 | 2004-10-29 00:00:00
[NAME] | 7 | A2367 | 9977 | Panel Decal | 1 | 525 | 525 | 30 | 2004-11-01 00:00:00 | 5 | 2004-11-07 00:00:00
[NAME] | 7 | A2378 | 6431 | O-Ring | 2.85 | 1350 | 3847.5 | 30 | 2004-10-01 00:00:00 | 5 | 2004-10-07 00:00:00
Spacetime Technologies | 2 | A3467 | 4111 | Bolt-nut package | 3.55 | 4800 | 17040 | 25 | 2004-09-05 00:00:00 | 10 | 2004-09-20 00:00:00
Spacetime Technologies | 2 | A5689 | 4111 | Bolt-nut package | 3.55 | 4585 | 16276.75 | 25 | 2004-09-10 00:00:00 | 15 | 2004-09-30 00:00:00
[NAME] | 7 | A9821 | 6431 | O-Ring | 2.85 | 1300 | 3705 | 30 | 2004-09-25 00:00:00 | 5 | 2004-10-01 00:00:00
[NAME] | 7 | A9842 | 7258 | Pressure Gauge | 100.5 | 100 | 10050 | 30 | 2004-10-15 00:00:00 | 8 | 2004-10-24 00:00:00
[NAME] | 7 | A9865 | 9967 | [NAME] | 0.85 | 550 | 467.5 | 30 | 2004-11-05 00:00:00 | 6 | 2004-11-11 00:00:00
[NAME] | 7 | A9876 | 9955 | Door Decal | 0.55 | 150 | 82.5 | 30 | 2004-11-01 00:00:00 | 5 | 2004-11-06 00:00:00
Pylon Accessories | 8 | A9999 | 6433 | O-Ring | 2.95 | 1500 | 4425 | 15 | 2004-10-01 00:00:00 | 5 | 2004-10-10 00:00:00
Alum Sheeting | 5 | B0447 | 5634 | Side Panel | 185 | 150 | 27750 | 30 | 2004-10-25 00:00:00 | 5 | 2004-11-03 00:00:00
Alum Sheeting | 5 | B0247 | 1243 | Airframe fasteners | 4.25 | 9000 | 38250 | 30 | 2004-09-05 00:00:00 | 5 | 2004-09-12 00:00:00
Steelpin Inc. | 6 | B0445 | 4312 | Bolt-nut package | 3.75 | 4150 | 15562.5 | 30 | 2004-09-03 00:00:00 | 5 | 2004-09-11 00:00:00
Alum Sheeting | 5 | B0479 | 5634 | Side Panel | 185 | 140 | 25900 | 30 | 2004-10-29 00:00:00 | 5 | 2004-11-04 00:00:00
Alum Sheeting | 5 | B0567 | 1243 | Airframe fasteners | 4.25 | 10500 | 44625 | 30 | 2004-10-10 00:00:00 | 6 | 2004-10-17 00:00:00
Pylon Accessories | 8 | B1111 | 9764 | Gasket | 3.75 | 1980 | 7425 | 15 | 2004-09-20 00:00:00 | 6 | 2004-09-29 00:00:00
Durrable Products | 3 | B1234 | 7258 | Pressure Gauge | 90 | 100 | 9000 | 45 | 2004-08-25 00:00:00 | 4 | 2004-08-28 00:00:00
Durrable Products | 3 | B1345 | 7258 | Pressure Gauge | 90 | 120 | 10800 | 45 | 2004-09-05 00:00:00 | 4 | 2004-09-09 00:00:00
Durrable Products | 3 | B1468 | 1369 | Airframe fasteners | 4.2 | 14000 | 58800 | 45 | 2004-09-27 00:00:00 | 6 | 2004-10-03 00:00:00
Durrable Products | 3 | B1589 | 5275 | Shielded Cable/ft. | 1 | 25000 | 25000 | 45 | 2004-10-25 00:00:00 | 4 | 2004-10-30 00:00:00
Durrable Products | 3 | B1666 | 1369 | Airframe fasteners | 4.2 | 10000 | 42000 | 45 | 2004-09-29 00:00:00 | 5 | 2004-10-04 00:00:00
Fast-Tie Aerospace | 4 | B2333 | 6321 | O-Ring | 2.45 | 1300 | 3185 | 30 | 2004-08-25 00:00:00 | 10 | 2004-09-04 00:00:00
Fast-Tie Aerospace | 4 | B2345 | 6321 | O-Ring | 2.45 | 1200 | 2940 | 30 | 2004-09-12 00:00:00 | 9 | 2004-09-23 00:00:00
Fast-Tie Aerospace | 4 | B2356 | 6321 | O-Ring | 2.45 | 2500 | 6125 | 30 | 2004-09-25 00:00:00 | 9 | 2004-10-04 00:00:00
Fast-Tie Aerospace | 4 | B2367 | 6321 | O-Ring | 2.45 | 1250 | 3062.5 | 30 | 2004-10-12 00:00:00 | 8 | 2004-10-21 00:00:00
Fast-Tie Aerospace | 4 | B2378 | 6321 | O-Ring | 2.45 | 1500 | 3675 | 30 | 2004-10-25 00:00:00 | 8 | 2004-11-02 00:00:00
Fast-Tie Aerospace | 4 | B2498 | 5689 | Side Panel | 175 | 150 | 26250 | 30 | 2004-11-01 00:00:00 | 7 | 2004-11-09 00:00:00
Fast-Tie Aerospace | 4 | B2499 | 7268 | Pressure Gauge | 95 | 110 | 10450 | 30 | 2004-11-01 00:00:00 | 10 | 2004-11-12 00:00:00
Fast-Tie Aerospace | 4 | B2511 | 7268 | Pressure Gauge | 95 | 105 | 9975 | 30 | 2004-11-05 00:00:00 | 10 | 2004-11-16 00:00:00
Fast-Tie Aerospace | 4 | B2519 | 5462 | Shielded Cable/ft. | 1.05 | 22500 | 23625 | 30 | 2004-08-20 00:00:00 | 5 | 2004-08-26 00:00:00
Fast-Tie Aerospace | 4 | [NAME] | 5689 | Side Panel | 175 | 175 | 30625 | 30 | 2004-11-05 00:00:00 | 7 | 2004-11-15 00:00:00
Fast-Tie Aerospace | 4 | B2537 | 5462 | Shielded Cable/ft. | 1.05 | 21500 | 22575 | 30 | 2004-08-15 00:00:00 | 5 | 2004-08-22 00:00:00
Fast-Tie Aerospace | 4 | B2566 | 5462 | Shielded Cable/ft. | 1.05 | 23000 | 24150 | 30 | 2004-08-10 00:00:00 | 5 | 2004-08-15 00:00:00
Steelpin Inc. | 6 | B3041 | 5234 | Electrical Connector | 1.65 | 4500 | 7425 | 30 | 2004-08-28 00:00:00 | 5 | 2004-09-05 00:00:00
Steelpin Inc. | 6 | B3042 | 5234 | Electrical Connector | 1.65 | 4750 | 7837.5 | 30 | 2004-09-05 00:00:00 | 5 | 2004-09-13 00:00:00
Steelpin Inc. | 6 | B3022 | 5677 | Side Panel | 195 | 110 | 21450 | 30 | 2004-11-05 00:00:00 | 9 | 2004-11-17 00:00:00
Steelpin Inc. | 6 | B3111 | 5234 | Electrical Connector | 1.65 | 4850 | 8002.5 | 30 | 2004-09-02 00:00:00 | 6 | 2004-09-11 00:00:00
Steelpin Inc. | 6 | B3222 | 8008 | Machined Valve | 645 | 150 | 96750 | 30 | 2004-10-15 00:00:00 | 7 | 2004-10-26 00:00:00
Steelpin Inc. | 6 | B3333 | 8008 | Machined Valve | 645 | 100 | 64500 | 30 | 2004-10-10 00:00:00 | 8 | 2004-10-21 00:00:00
Fast-Tie Aerospace | 4 | C0423 | 5689 | Side Panel | 175 | 155 | 27125 | 30 | 2004-10-25 00:00:00 | 8 | 2004-11-03 00:00:00
Fast-Tie Aerospace | 4 | C0234 | 5166 | Electrical Connector | 1.25 | 5650 | 7062.5 | 30 | 2004-10-01 00:00:00 | 5 | 2004-10-06 00:00:00
Fast-Tie Aerospace | 4 | C0433 | 5462 | Shielded Cable/ft. | 1.05 | 22500 | 23625 | 30 | 2004-08-25 00:00:00 | 6 | 2004-09-02 00:00:00
Steelpin Inc. | 6 | C0456 | 5677 | Side Panel | 195 | 130 | 25350 | 30 | 2004-10-28 00:00:00 | 8 | 2004-11-07 00:00:00
Steelpin Inc. | 6 | C0467 | 8008 | Machined Valve | 645 | 120 | 77400 | 30 | 2004-10-28 00:00:00 | 7 | 2004-11-04 00:00:00
Steelpin Inc. | 6 | C0589 | 5319 | Shielded Cable/ft. | 1.1 | 18100 | 19910 | 30 | 2004-08-25 00:00:00 | 10 | 2004-09-05 00:00:00
[NAME] | 7 | C1111 | 9955 | Door Decal | 0.55 | 125 | 68.75 | 30 | 2004-11-05 00:00:00 | 5 | 2004-11-10 00:00:00
[NAME] | 1 | C1212 | 1122 | Airframe fasteners | 4.25 | 19500 | 82875 | 30 | 2004-08-05 00:00:00 | 7 | 2004-08-13 00:00:00
[NAME] | 1 | C1313 | 3166 | Electrical Connector | 1.25 | 5600 | 7000 | 30 | 2004-08-25 00:00:00 | 5 | 2004-08-29 00:00:00
Pylon Accessories | 8 | C2211 | 9764 | Gasket | 3.75 | 1850 | 6937.5 | 15 | 2004-09-25 00:00:00 | 6 | 2004-10-05 00:00:00
[NAME] | 7 | C2222 | 7258 | Pressure Gauge | 100.5 | 90 | 9045 | 30 | 2004-10-10 00:00:00 | 7 | 2004-10-17 00:00:00
[NAME] | 1 | C2323 | 1122 | Airframe fasteners | 4.25 | 15500 | 65875 | 30 | 2004-09-04 00:00:00 | 8 | 2004-09-12 00:00:00
[NAME] | 1 | C2929 | 3166 | Electrical Connector | 1.25 | 5500 | 6875 | 30 | 2004-09-01 00:00:00 | 5 | 2004-09-06 00:00:00
[NAME] | 1 | C3232 | 1122 | Airframe fasteners | 4.25 | 18000 | 76500 | 30 | 2004-10-01 00:00:00 | 7 | 2004-10-08 00:00:00
[NAME] | 7 | C3333 | 8148 | Machined Valve | 655.5 | 125 | 81937.5 | 30 | 2004-10-10 00:00:00 | 8 | 2004-10-17 00:00:00
[NAME] | 1 | C3434 | 1122 | Airframe fasteners | 4.25 | 12500 | 53125 | 30 | 2004-09-05 00:00:00 | 7 | 2004-09-11 00:00:00
[NAME] | 1 | C4545 | 1122 | Airframe fasteners | 4.25 | 15000 | 63750 | 30 | 2004-09-08 00:00:00 | 7 | 2004-09-15 00:00:00
[NAME] | 1 | C5656 | 1122 | Airframe fasteners | 4.25 | 14500 | 61625 | 30 | 2004-09-28 00:00:00 | 7 | 2004-10-03 00:00:00
[NAME] | 1 | [NAME] | 5066 | Shielded Cable/ft. | 0.95 | 25000 | 23750 | 30 | 2004-09-05 00:00:00 | 8 | 2004-09-12 00:00:00
[NAME] | 1 | C7875 | 3166 | Electrical Connector | 1.25 | 5650 | 7062.5 | 30 | 2004-09-05 00:00:00 | 5 | 2004-09-10 00:00:00
[NAME] | 1 | C8854 | 3166 | Electrical Connector | 1.25 | 5425 | 6781.25 | 30 | 2004-09-10 00:00:00 | 5 | 2004-09-15 00:00:00
[NAME] | 1 | C8989 | 9966 | [NAME] | 0.75 | 500 | 375 | 30 | 2004-08-25 00:00:00 | 6 | 2004-08-31 00:00:00
Pylon Accessories | 8 | D1111 | 9764 | Gasket | 3.75 | 1800 | 6750 | 15 | 2004-09-28 00:00:00 | 5 | 2004-10-05 00:00:00
[NAME] | 1 | D1212 | 5066 | Shielded Cable/ft. | 0.95 | 17500 | 16625 | 30 | 2004-09-15 00:00:00 | 8 | 2004-09-22 00:00:00
[NAME] | 1 | D2121 | 1122 | Airframe fasteners | 4.25 | 17500 | 74375 | 30 | 2004-10-25 00:00:00 | 8 | 2004-11-03 00:00:00
[NAME] | 1 | [NAME] | 1122 | Airframe fasteners | 4.25 | 17000 | 72250 | 30 | 2004-10-11 00:00:00 | 8 | 2004-10-19 00:00:00
Pylon Accessories | 8 | D3333 | 9764 | Gasket | 3.75 | 1750 | 6562.5 | 15 | 2004-09-20 00:00:00 | 5 | 2004-09-25 00:00:00
 |  | Report 1: Most On-time Suppliers
[NAME] | Vendor No. |  |  | Average Days Late
Spacetime Technologies | 2
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Alum Sheeting | 5
Alum Sheeting | 5
Alum Sheeting | 5
Alum Sheeting | 5
Spacetime Technologies | 2
Spacetime Technologies | 2
Spacetime Technologies | 2
Spacetime Technologies | 2
Spacetime Technologies | 2
Spacetime Technologies | 2
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
Spacetime Technologies | 2
Spacetime Technologies | 2
Spacetime Technologies | 2
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
[NAME] | 7
[NAME] | 7
[NAME] | 7
[NAME] | 7
Spacetime Technologies | 2
Spacetime Technologies | 2
[NAME] | 7
[NAME] | 7
[NAME] | 7
[NAME] | 7
Pylon Accessories | 8
Alum Sheeting | 5
Alum Sheeting | 5
Steelpin Inc. | 6
Alum Sheeting | 5
Alum Sheeting | 5
Pylon Accessories | 8
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
Durrable Products | 3
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Fast-Tie Aerospace | 4
Steelpin Inc. | 6
Steelpin Inc. | 6
Steelpin Inc. | 6
[NAME] | 7
[NAME] | 1
[NAME] | 1
Pylon Accessories | 8
[NAME] | 7
[NAME] | 1
[NAME] | 1
[NAME] | 1
[NAME] | 7
[NAME] | 1
[NAME] | 1
[NAME] | 1
[NAME] | 1
[NAME] | 1
[NAME] | 1
[NAME] | 1
Pylon Accessories | 8
[NAME] | 1
[NAME] | 1
[NAME] | 1
Pylon Accessories | 8
1. To find out most on-time suppliers, I use the average days late as the index. The vendor
who gets the smallest value on this index should be considered the most on-time supplier.
2. Create the report title Report 1: Most On-time Suppliers;
3. Under this title, there are two table columns, Vendor No. and Average Days Late;
4. Obviously the first column name comes from the field name in the original data source
table. From the source table we notice that there are eight vendor numbers. We therefore
key in 1 underneath the column heading. 5. Select the column heading and the first cell under it, and use the autofill function to fill
this column with the eight vendor numbers. You may notice that the column heading is
repeated several times. Since it does not interfere with our work, simply leave them as
they are.
6. In the first cell underneath the column heading Average Days Late, we will use a
database function DAVERAGE() to generate the value. This function helps to identify
the exact value in a source table based on given criteria.
7. Please activate the menu [NAME] and click the Insert Function icon to see the Function
Arguments dialog box;
8. In the database box, you need to select the entire source table area including the table
headings and use $ to lock up the data range ($A$4:$N$98 in my case);
9. In the Field box, either type 14 or Day Late. According to the system default, column N
is recognized as the 14
th
data column. You may also use the column header in the source
table to refer back to any value in the column. However, the spelling must be exactly the
same as in the header.
10. For Criteria, select the first two cells in the first data column in Report 1 table. This way
Excel will calculate the average of the days late values for Vendor No. 1 in the source
table and display this average value next to 1 in Report 1 table.
11. The correct value should be -0.3. Once you get this value, you are ready to use autofill