Iproving Decision Making: Use a Spreadsheet to Select Suppliers Resources needed:  1. A windows PC with Microsoft Excel installed. If you need this softw

Iproving Decision Making: Use a Spreadsheet to Select Suppliers Resources needed: 

1.

A windows PC with Microsoft Excel installed. If you need this software, follow 

these steps to install it.

2.

Ess13ch02_Questionfile.xlsx

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.

Scenario: 

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 

Assignments area under Week 2, you will find a spreadsheet file 

(Ess13ch02_Questionfile.xlsx)

that contains a list of all the items 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 

suppliers offering lower pricing when the same item can be provided by multiple 

suppliers. (extra credit)

Use your spreadsheet software to prepare reports to support your recommendations.

Information about the data:

At a minimum you will need to be able to sort the database by various criteria such as 

item description, item cost, vendor number, or A/P terms

A/P terms is expressed as the number of days that the customer has to pay the vendor 

for the purchase. In other words, 30 designates net 30 days. The vendor that allows 

customers the longest amount of time to pay for an order would, of course, offer the 

most favorable payment terms. Sheet3

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/17 10/18/17

Steelpin Inc. 6 A0125 5319 Shielded Cable/ft. $ 1.10 17,500 $ 19,250.00 30 08/20/17 08/31/17

Steelpin Inc. 6 A0123 4312 Bolt-nut package $ 3.75 4,250 $ 15,937.50 30 08/25/17 09/01/17

Steelpin Inc. 6 A0204 5319 Shielded Cable/ft. $ 1.10 16,500 $ 18,150.00 30 09/15/17 10/05/17

Steelpin Inc. 6 A0205 5677 Side Panel $ 195.00 120 $ 23,400.00 30 12/02/17 12/13/17

Steelpin Inc. 6 A0207 4312 Bolt-nut package $ 3.75 4,200 $ 15,750.00 30 09/01/17 09/10/17

Alum Sheeting 5 A0223 4224 Bolt-nut package $ 3.95 4,500 $ 17,775.00 30 10/15/17 10/20/17

Alum Sheeting 5 A0433 5417 Control Panel $ 255.00 500 $ 127,500.00 30 10/20/17 10/27/17

Alum Sheeting 5 A0443 1243 Airframe fasteners $ 4.25 10,000 $ 42,500.00 30 08/08/17 08/14/17

Alum Sheeting 5 A0446 5417 Control Panel $ 255.00 406 $ 103,530.00 30 09/01/17 09/10/17

Spacetime Technologies 2 A0533 9752 Gasket $ 4.05 1,500 $ 6,075.00 25 09/20/17 09/25/17

Spacetime Technologies 2 A0555 6489 O-Ring $ 3.00 1,100 $ 3,300.00 25 10/05/17 10/10/17

Spacetime Technologies 2 A0622 9752 Gasket $ 4.05 1,550 $ 6,277.50 25 09/25/17 10/05/17

Spacetime Technologies 2 A0666 5125 Shielded Cable/ft. $ 1.15 15,000 $ 17,250.00 25 10/01/17 10/15/17

Spacetime Technologies 2 A0777 6489 O-Ring $ 3.00 1,050 $ 3,150.00 25 10/29/17 12/10/17

Spacetime Technologies 2 A1222 4111 Bolt-nut package $ 3.55 4,200 $ 14,910.00 25 09/15/17 10/15/17

Durrable Products 3 A1234 9399 Gasket $ 3.65 1,250 $ 4,562.50 45 10/01/17 10/06/17

Durrable Products 3 A1235 9399 Gasket $ 3.65 1,450 $ 5,292.50 45 10/03/17 10/08/17

Durrable Products 3 A1344 5454 Control Panel $ 220.00 550 $ 121,000.00 45 10/09/17 10/14/17

Durrable Products 3 A1345 9399 Gasket $ 3.65 1,470 $ 5,365.50 45 10/07/17 10/12/17

Durrable Products 3 A1346 9399 Gasket $ 3.65 1,985 $ 7,245.25 45 10/05/17 10/11/17

Spacetime Technologies 2 A1444 4111 Bolt-nut package $ 3.55 4,250 $ 15,087.50 25 09/20/17 10/10/17

Spacetime Technologies 2 A1445 4111 Bolt-nut package $ 3.55 4,200 $ 14,910.00 25 09/25/17 10/25/17

Spacetime Technologies 2 A1449 4111 Bolt-nut package $ 3.55 4,600 $ 16,330.00 25 10/05/17 10/19/17

Durrable Products 3 A1456 5454 Control Panel $ 220.00 500 $ 110,000.00 45 10/15/17 10/20/17

Durrable Products 3 A1457 4569 Bolt-nut package $ 3.50 3,900 $ 13,650.00 45 10/05/17 10/10/17

Durrable Products 3 A1567 1369 Airframe fasteners $ 4.20 15,000 $ 63,000.00 45 09/25/17 09/30/17

Manley Valve 7 A2345 6431 O-Ring $ 2.85 1,250 $ 3,562.50 30 10/05/17 10/10/17

Manley Valve 7 A2356 7258 Pressure Gauge $ 100.50 95 $ 9,547.50 30 10/20/17 10/29/17

Manley Valve 7 A2367 9977 Panel Decal $ 1.00 525 $ 525.00 30 12/01/17 12/07/17

Manley Valve 7 A2378 6431 O-Ring $ 2.85 1,350 $ 3,847.50 30 10/01/17 10/07/17

Spacetime Technologies 2 A3467 4111 Bolt-nut package $ 3.55 4,800 $ 17,040.00 25 09/05/17 09/20/17

Spacetime Technologies 2 A5689 4111 Bolt-nut package $ 3.55 4,585 $ 16,276.75 25 09/10/17 09/30/17

Manley Valve 7 A9821 6431 O-Ring $ 2.85 1,300 $ 3,705.00 30 09/25/17 10/01/17

Manley Valve 7 A9842 7258 Pressure Gauge $ 100.50 100 $ 10,050.00 30 10/15/17 10/24/17

Manley Valve 7 A9865 9967 Hatch Decal $ 0.85 550 $ 467.50 30 12/05/17 12/12/17

Manley Valve 7 A9876 9955 Door Decal $ 0.55 150 $ 82.50 30 12/01/17 12/06/17

Pylon Accessories 8 A9999 6433 O-Ring $ 2.95 1,500 $ 4,425.00 15 10/01/17 10/10/17

Alum Sheeting 5 B0247 1243 Airframe fasteners $ 4.25 9,000 $ 38,250.00 30 09/05/17 09/12/17

Steelpin Inc. 6 B0445 4312 Bolt-nut package $ 3.75 4,150 $ 15,562.50 30 09/03/17 09/12/17

Alum Sheeting 5 B0447 5634 Side Panel $ 185.00 150 $ 27,750.00 30 10/25/17 12/03/17

Alum Sheeting 5 B0479 5634 Side Panel $ 185.00 140 $ 25,900.00 30 10/29/17 12/04/17

Alum Sheeting 5 B0567 1243 Airframe fasteners $ 4.25 10,500 $ 44,625.00 30 10/10/17 10/17/17

Pylon Accessories 8 B1111 9764 Gasket $ 3.75 1,980 $ 7,425.00 15 09/20/17 09/29/17

Durrable Products 3 B1234 7258 Pressure Gauge $ 90.00 100 $ 9,000.00 45 08/25/17 08/28/17

Durrable Products 3 B1345 7258 Pressure Gauge $ 90.00 120 $ 10,800.00 45 09/05/17 09/09/17

Durrable Products 3 B1468 1369 Airframe fasteners $ 4.20 14,000 $ 58,800.00 45 09/27/17 10/03/17

Durrable Products 3 B1589 5275 Shielded Cable/ft. $ 1.00 25,000 $ 25,000.00 45 10/25/17 10/30/17

Durrable Products 3 B1666 1369 Airframe fasteners $ 4.20 10,000 $ 42,000.00 45 09/29/17 10/04/17

Fast-Tie Aerospace 4 B2333 6321 O-Ring $ 2.45 1,300 $ 3,185.00 30 08/25/17 09/04/17

Fast-Tie Aerospace 4 B2345 6321 O-Ring $ 2.45 1,200 $ 2,940.00 30 09/12/17 09/23/17

Fast-Tie Aerospace 4 B2356 6321 O-Ring $ 2.45 2,500 $ 6,125.00 30 09/25/17 10/04/17

Fast-Tie Aerospace 4 B2367 6321 O-Ring $ 2.45 1,250 $ 3,062.50 30 10/12/17 10/21/17

Fast-Tie Aerospace 4 B2378 6321 O-Ring $ 2.45 1,500 $ 3,675.00 30 10/25/17 12/02/17

Fast-Tie Aerospace 4 B2498 5689 Side Panel $ 175.00 150 $ 26,250.00 30 12/01/17 12/09/17

Fast-Tie Aerospace 4 B2499 7268 Pressure Gauge $ 95.00 110 $ 10,450.00 30 12/01/17 12/12/17

Fast-Tie Aerospace 4 B2511 7268 Pressure Gauge $ 95.00 105 $ 9,975.00 30 12/05/17 12/16/17

Fast-Tie Aerospace 4 B2519 5462 Shielded Cable/ft. $ 1.05 22,500 $ 23,625.00 30 08/20/17 08/26/17

Fast-Tie Aerospace 4 B2528 5689 Side Panel $ 175.00 175 $ 30,625.00 30 12/05/17 12/15/17

Fast-Tie Aerospace 4 B2537 5462 Shielded Cable/ft. $ 1.05 21,500 $ 22,575.00 30 08/15/17 08/22/17

Fast-Tie Aerospace 4 B2566 5462 Shielded Cable/ft. $ 1.05 23,000 $ 24,150.00 30 08/10/17 08/15/17

Steelpin Inc. 6 B3022 5677 Side Panel $ 195.00 110 $ 21,450.00 30 12/05/17 12/17/17

Steelpin Inc. 6 B3041 5234 Electrical Connector $ 1.65 4,500 $ 7,425.00 30 08/28/17 09/05/17

Steelpin Inc. 6 B3042 5234 Electrical Connector $ 1.65 4,750 $ 7,837.50 30 09/05/17 09/13/17

Steelpin Inc. 6 B3111 5234 Electrical Connector $ 1.65 4,850 $ 8,002.50 30 09/02/17 09/12/17

Steelpin Inc. 6 B3222 8008 Machined Valve $ 645.00 150 $ 96,750.00 30 10/15/17 10/26/17

Steelpin Inc. 6 B3333 8008 Machined Valve $ 645.00 100 $ 64,500.00 30 10/10/17 10/21/17

Fast-Tie Aerospace 4 C0234 5166 Electrical Connector $ 1.25 5,650 $ 7,062.50 30 10/01/17 10/06/17

Fast-Tie Aerospace 4 C0423 5689 Side Panel $ 175.00 155 $ 27,125.00 30 10/25/17 12/03/17

Fast-Tie Aerospace 4 C0433 5462 Shielded Cable/ft. $ 1.05 22,500 $ 23,625.00 30 08/25/17 09/02/17

Steelpin Inc. 6 C0456 5677 Side Panel $ 195.00 130 $ 25,350.00 30 10/28/17 12/07/17

Steelpin Inc. 6 C0467 8008 Machined Valve $ 645.00 120 $ 77,400.00 30 10/28/17 12/04/17

Steelpin Inc. 6 C0589 5319 Shielded Cable/ft. $ 1.10 18,100 $ 19,910.00 30 08/25/17 09/05/17

Manley Valve 7 C1111 9955 Door Decal $ 0.55 125 $ 68.75 30 12/05/17 12/10/17

Hulkey Fasteners 1 C1212 1122 Airframe fasteners $ 4.25 19,500 $ 82,875.00 30 08/05/17 08/13/17

Hulkey Fasteners 1 C1313 3166 Electrical Connector $ 1.25 5,600 $ 7,000.00 30 08/25/17 08/29/17

Pylon Accessories 8 C2211 9764 Gasket $ 3.75 1,850 $ 6,937.50 15 09/25/17 10/05/17

Manley Valve 7 C2222 7258 Pressure Gauge $ 100.50 90 $ 9,045.00 30 10/10/17 10/17/17

Hulkey Fasteners 1 C2323 1122 Airframe fasteners $ 4.25 15,500 $ 65,875.00 30 09/04/17 09/12/17

Hulkey Fasteners 1 C2929 3166 Electrical Connector $ 1.25 5,500 $ 6,875.00 30 09/01/17 09/06/17

Hulkey Fasteners 1 C3232 1122 Airframe fasteners $ 4.25 18,000 $ 76,500.00 30 10/01/17 10/08/17

Manley Valve 7 C3333 8148 Machined Valve $ 655.50 125 $ 81,937.50 30 10/10/17 10/17/17

Hulkey Fasteners 1 C3434 1122 Airframe fasteners $ 4.25 12,500 $ 53,125.00 30 09/05/17 09/12/17

Hulkey Fasteners 1 C4545 1122 Airframe fasteners $ 4.25 15,000 $ 63,750.00 30 09/08/17 09/15/17

Hulkey Fasteners 1 C5656 1122 Airframe fasteners $ 4.25 14,500 $ 61,625.00 30 09/28/17 10/03/17

Hulkey Fasteners 1 C6765 5066 Shielded Cable/ft. $ 0.95 25,000 $ 23,750.00 30 09/05/17 09/12/17

Hulkey Fasteners 1 C7875 3166 Electrical Connector $ 1.25 5,650 $ 7,062.50 30 09/05/17 09/10/17

Hulkey Fasteners 1 C8854 3166 Electrical Connector $ 1.25 5,425 $ 6,781.25 30 09/10/17 09/15/17

Hulkey Fasteners 1 C8989 9966 Hatch Decal $ 0.75 500 $ 375.00 30 08/25/17 08/31/17

Pylon Accessories 8 D1111 9764 Gasket $ 3.75 1,800 $ 6,750.00 15 09/28/17 10/05/17

Hulkey Fasteners 1 D1212 5066 Shielded Cable/ft. $ 0.95 17,500 $ 16,625.00 30 09/15/17 09/22/17

Hulkey Fasteners 1 D2121 1122 Airframe fasteners $ 4.25 17,500 $ 74,375.00 30 10/25/17 12/03/17

Hulkey Fasteners 1 D3232 1122 Airframe fasteners $ 4.25 17,000 $ 72,250.00 30 10/12/17 10/19/17

Pylon Accessories 8 D3333 9764 Gasket $ 3.75 1,750 $ 6,562.50 15 09/20/17 09/25/17

Submit a Comment

Open chat