Improving Decision Making: Using a Spreadsheet to Select Suppliers 12 Software skills: Spreadsheet date functions, data filtering, DAVERAGE function Business skills: Analyzing supplier performance and pricing ACTIVITY OBJECTIVES: 1. Student will learn how to use spreadsheet software to improve management decisions about selecting suppliers. 2. Student 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. In a separate folder, you will find a spreadsheet file (MISch02_questionfile.als) that 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. (Laudon & Laudon; 2014; p.71). INSTRUCTIONS 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: A. Supplier's track record for on-time deliveries B. Suppliers offering the best accounts payable terms C. Suppliers offering lower pricing when the same item can be provided by multiple suppliers. 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 Supplier's Price per Item Laudon, K.C. & Laudon J. P. (2014). Global E-Business and Collaboration. Management of Information Systems (13 ed.]. Upper Saddle River, NJ: Pearson Education Inc. *Modified for educational purposes.Order Dain Arrival Date A0125 5319 19.260 40123 4312 4 250 30 08/25414 16 400 40205 195 30 4312 3.75 4 200 15.75 4324 40433 5417 27.500 1243 10533 9752 4 05 A0565 3.306 4 05 5125 3.150 10/2514 A1322 345 Durable Products 61234 9329 3.65 Durable Products 220 121.009 Durable Products 3.65 3.85 10/05/14 Durable Products 355 3.55 4.200 A14-49 4111 3.45 4.600 18 350 10/05/14 Durable Products AEASE 220 110.00 10/15/14 Durable Products ALAST 3 900 10/05/14 Durable Products 15 000 2.85 7258 13/01/14 Manley Valve 2.85 1.350 4111 3.55 17 040 4119 3.55 Manley Valve 6431 2.85 3.705 100-5 10/15/14 DAS 19876 0.55 82 5 Num Sheeling BOMAT 1243 4 25 9.000 80445 4312 3.75 15 562 5 37.750 10/35/14 BO478 185 10/29/14 0567 1243 4.25 10/0714 8-1234 31345 10.80 4.2 14 080 Durable Products 5275 10/25/14 81566 42.008 3.185 82345 8321 Q-Ring 2.45 1.200 2.94 82356 8321 2.45 2 500 6.125 Fast-The Aer 2 45 1.350 10/12/14 8321 10 45 13/13714 82511 95 9.975 12/05/14 121614 1.05 22 500 23.635 175 B2537 1.05 21.500 22 575 30 08/15/14 33022 5677 5234 5234 TOO2 S 83222 8008 CO234 1.25 5.650 10/01/4 CO423 B da Panel 175 27.125 10/05/14 Shielded Cableit 105 23/025 CO-456 5677 195 10/26/14 1006/14 5319 Door Decal 0.55 #1212 1122 C1313 1.25 Pylon Accessories C2211 3.75 1850 Manley Valve #2222 10/10/14 C2323 1122 4 25 C2929 3156 1.25 5600 1122 Manley Valve C-3434 1122 15 080 1122 CATS 0.95 29 750 27875 1.25 5 850 1.25 6.781.25 0 75 375 6.750 0.95 16.625 1122 4.25 74 3T D1217 1122 17000 72.250 23313 3.75 1.750 19/20/4