Chapter 2 Hands-on MIS Application Problem Statement IMPROVING DECISION MAKING: USE A SPREADSHEET TO SELECT SUPPLIERS Software skills: Spreadsheet date functions, data filtering, DAVERAGE function Business skills: Analyzing supplier performance and pricing 2- 11 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. In the BlackBoard \"Learning Module\" folder you will find a spreadsheet file 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. 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. Hint on Excel Skills and Usage (See Excel Tutorial Links Below) This exercise requires some student knowledge of spreadsheet database functions. At a minimum, students should know how to sort the database by various criteria such as item description, item cost, vendor number, vendor, name, or A/P terms. Students may need to be told that A/P Terms is expressed as the number of days that the customer has to pay the vendor for a 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. Students will need to add additional columns for calculating the delivery time for each order. The delivery time can be calculated by subtracting the Order Date from the Arrival Date. Vendors with the shortest delivery times are obviously desirable. These numbers are useful when trying to determine who is the vendor with the best on-time delivery track record. Students can use the DAVERAGE or the SUMIF and COUNTIF functions to determine the average delivery time for each vendor. Students can also use one of the database functions to determine the vendor with the best accounts payable terms. To determine the vendor with the lowest prices for the same item when it is supplied by multiple vendors, students can filter the database using the item description. This filtered list can then be sorted by item cost and vendor number. Excel Tutorials Links Choose Link below Based on Your current Excel Version: http://www.gcflearnfree.org/excel2013/27 For MS Excel 2013 users - 28 Lessons in all subjects needed http://www.tutorialspoint.com/excel/ For MS Excel 2010 users - Lessons with examples in all subjects needed - click on training of interest from the Left Side Menu. http://www.gcflearnfree.org/excel2007 For MS Excel 2007 users - Lessons in all subjects needed - avoid the Ads, the training material is there as you scroll down the pages. Orders and Suppliers Vendor Name Spacetime Technologies Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Alum Sheeting Alum Sheeting Alum Sheeting Alum Sheeting Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Vendor NoOrder No. Item No. Item Description 2 A0111 6489 O-Ring 6 6 6 6 6 5 5 5 5 2 2 2 2 2 A0125 A0123 A0204 A0205 A0207 A0223 A0433 A0443 A0446 A0533 A0555 A0622 A0666 A0777 Spacetime Technologies Durrable Products Durrable Products 2 3 3 Durrable Products Item Cost $ 3.00 $ 1.10 $ 3.75 $ 1.10 $ 195.00 $ 3.75 $ 3.95 $ 255.00 $ 4.25 $ 255.00 $ 4.05 $ 3.00 $ 4.05 $ 1.15 $ 3.00 5319 Shielded Cable/ft. 4312 Bolt-nut package 5319 5677 Shielded Cable/ft. Side Panel 4312 4224 Bolt-nut package Bolt-nut package 5417 Control Panel 1243 Airframe fasteners 5417 Control Panel 9752 6489 9752 Gasket O-Ring Gasket 5125 Shielded Cable/ft. A1222 A1234 A1235 6489 4111 9399 9399 O-Ring Bolt-nut package Gasket Gasket 3 A1344 5454 Control Panel $ 220.00 Durrable Products Durrable Products Spacetime Technologies Spacetime Technologies Spacetime Technologies 3 3 2 2 2 A1345 A1346 A1444 A1445 A1449 9399 9399 4111 4111 4111 Gasket Gasket Bolt-nut package Bolt-nut package Bolt-nut package $ $ $ $ $ Durrable Products Durrable Products Durrable Products Manley Valve Manley Valve Manley Valve Manley Valve 3 3 3 7 7 7 7 A1456 A1457 A1567 A2345 A2356 A2367 A2378 5454 Control Panel 4569 1369 6431 Bolt-nut package Airframe fasteners O-Ring 7258 9977 Pressure Gauge Panel Decal Spacetime Technologies Spacetime Technologies 2 2 A3467 A5689 Manley Valve Manley Valve Manley Valve Manley Valve Pylon Accessories Alum Sheeting Steelpin Inc. Alum Sheeting Alum Sheeting Alum Sheeting Pylon Accessories Durrable Products 7 7 7 7 8 5 6 5 5 5 8 3 A9821 A9842 A9865 A9876 A9999 B0247 B0445 B0447 B0479 B0567 B1111 B1234 6431 4111 4111 6431 O-Ring Bolt-nut package Bolt-nut package O-Ring $ 220.00 $ 3.50 $ 4.20 $ 2.85 $ 100.50 $ 1.00 $ 2.85 7258 9967 9955 Pressure Gauge Hatch Decal Door Decal 6433 1243 4312 O-Ring Airframe fasteners Bolt-nut package 5634 5634 Side Panel Side Panel 1243 9764 Airframe fasteners Gasket 7258 Pressure Gauge $ $ $ 3.55 3.65 3.65 3.65 3.65 3.55 3.55 3.55 $ $ 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 Durrable Products Durrable Products Durrable Products Durrable Products 3 3 3 3 B1345 B1468 B1589 B1666 7258 Pressure Gauge Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace 4 4 4 4 4 Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Steelpin Inc. Steelpin Inc. Manley Valve $ 90.00 $ 4.20 $ 1.00 $ 4.20 1369 Airframe fasteners 5275 Shielded Cable/ft. B2333 B2345 B2356 B2367 B2378 1369 6321 6321 6321 6321 6321 Airframe fasteners O-Ring O-Ring O-Ring O-Ring O-Ring 4 4 4 4 4 4 4 6 6 6 6 6 6 4 4 4 6 6 6 7 B2498 B2499 B2511 B2519 B2528 B2537 B2566 B3022 B3041 B3042 B3111 B3222 B3333 C0234 C0423 C0433 C0456 C0467 C0589 C1111 5689 7268 7268 5462 5689 5462 5462 5677 5234 5234 5234 8008 8008 5166 5689 5462 5677 8008 5319 9955 Side Panel Pressure Gauge Pressure Gauge Shielded Cable/ft. Side Panel Shielded Cable/ft. Shielded Cable/ft. Side Panel 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 Hulkey Fasteners 1 C1212 1122 Hulkey Fasteners Pylon Accessories Manley Valve 1 8 7 C1313 C2211 C2222 3166 9764 Gasket 7258 Pressure Gauge Hulkey Fasteners 1 C2323 1122 Airframe fasteners $ 4.25 Hulkey Fasteners 1 C2929 3166 Electrical Connector $ 1.25 Hulkey Fasteners 1 C3232 1122 Airframe fasteners $ 4.25 Manley Valve 7 C3333 8148 Machined Valve $ 655.50 Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners 1 1 1 C3434 C4545 C5656 1122 1122 1122 Airframe fasteners Airframe fasteners Airframe fasteners $ $ $ 4.25 4.25 4.25 Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Pylon Accessories Hulkey Fasteners 1 1 1 1 8 1 C6765 C7875 C8854 C8989 D1111 D1212 5066 3166 3166 9966 Shielded Cable/ft. Electrical Connector Electrical Connector Hatch Decal 9764 Gasket 5066 Shielded Cable/ft. $ $ $ $ $ $ 0.95 1.25 1.25 0.75 3.75 0.95 Hulkey Fasteners Hulkey Fasteners 1 1 D2121 D3232 1122 1122 Airframe fasteners Airframe fasteners $ $ 4.25 4.25 $ $ $ $ $ 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 1.65 645.00 645.00 1.25 175.00 1.05 195.00 645.00 1.10 0.55 Airframe fasteners $ 4.25 Electrical Connector $ 1.25 $ 3.75 $ 100.50 Pylon Accessories 8 D3333 9764 Gasket $ 3.75 Quantity Cost per order 900 17,500 4,250 16,500 120 4,200 4,500 500 10,000 406 1,500 1,100 1,550 15,000 1,050 $ $ $ $ $ $ $ $ 2,700.00 19,250.00 15,937.50 18,150.00 23,400.00 15,750.00 17,775.00 127,500.00 $ 42,500.00 $ $ $ $ $ $ 103,530.00 6,075.00 3,300.00 6,277.50 17,250.00 3,150.00 4,200 $ 1,250 $ 1,450 $ 14,910.00 4,562.50 5,292.50 550 $ 121,000.00 1,470 1,985 4,250 4,200 4,600 $ $ $ $ $ 5,365.50 7,245.25 15,087.50 14,910.00 16,330.00 500 3,900 15,000 1,250 95 525 1,350 $ $ 110,000.00 13,650.00 $ 63,000.00 $ $ $ $ 3,562.50 9,547.50 525.00 3,847.50 4,800 $ 4,585 $ 17,040.00 16,276.75 1,300 100 550 150 1,500 9,000 4,150 150 140 10,500 1,980 100 A/P Terms 25 30 30 30 30 30 30 30 30 30 25 25 25 25 25 25 45 45 45 45 45 25 25 25 45 45 45 30 30 30 30 25 25 30 30 30 30 $ $ $ $ $ 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 30 30 30 30 30 $ $ 7,425.00 9,000.00 15 45 15 Order Date Arrival Date 10/10/13 08/20/13 08/25/13 09/15/13 12/02/13 09/01/13 10/15/13 10/20/13 08/08/13 09/01/13 09/20/13 10/05/13 09/25/13 10/01/13 10/29/13 10/18/13 08/31/13 09/01/13 10/05/13 12/13/13 09/10/13 10/20/13 10/27/13 08/14/13 09/10/13 09/25/13 10/10/13 10/08/13 10/15/13 12/10/13 07/15/13 10/01/13 10/03/13 10/15/13 10/06/13 10/08/13 10/09/13 10/14/13 10/07/13 10/05/13 09/20/13 06/25/13 10/05/13 10/15/13 11/11/13 10/10/13 10/25/13 10/19/13 10/15/13 10/05/13 09/25/13 10/05/13 10/20/13 12/01/13 10/01/13 10/20/13 10/10/13 10/30/13 10/10/13 10/29/13 09/05/13 09/10/13 09/20/13 09/30/13 09/25/13 10/15/13 12/05/13 12/01/13 10/01/13 09/05/13 09/03/13 10/25/13 10/29/13 10/10/13 09/20/13 08/25/13 10/01/13 10/24/13 12/12/13 12/06/13 10/10/13 09/13/13 09/13/13 12/03/13 12/04/13 10/17/13 09/29/13 08/28/13 12/07/13 10/07/13 120 14,000 25,000 10,000 $ 10,800.00 $ 58,800.00 $ 25,000.00 1,300 1,200 2,500 1,250 1,500 $ $ $ $ $ $ 42,000.00 3,185.00 2,940.00 6,125.00 3,062.50 3,675.00 150 110 105 22,500 175 21,500 23,000 110 4,500 4,750 4,850 150 100 5,650 155 22,500 130 120 18,100 125 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 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 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 19,500 $ 82,875.00 5,600 $ 1,850 $ 90 $ 7,000.00 6,937.50 9,045.00 15,500 $ 65,875.00 5,500 $ 6,875.00 18,000 $ 76,500.00 125 $ 81,937.50 12,500 $ 15,000 $ 14,500 $ 53,125.00 63,750.00 61,625.00 25,000 5,650 5,425 500 1,800 17,500 $ $ $ $ $ $ 23,750.00 7,062.50 6,781.25 375.00 6,750.00 16,625.00 17,500 $ 17,000 $ 74,375.00 72,250.00 45 45 45 45 09/05/13 09/27/13 10/25/13 09/29/13 09/09/13 10/03/13 10/30/13 10/04/13 30 30 30 30 30 30 30 30 30 30 30 30 30 30 08/25/13 09/13/13 09/25/13 10/13/13 10/25/13 09/04/13 09/23/13 10/04/13 10/21/13 12/02/13 12/01/13 12/01/13 12/05/13 08/20/13 12/05/13 08/15/13 08/10/13 12/05/13 08/28/13 09/05/13 09/02/13 10/15/13 10/10/13 10/01/13 10/25/13 08/25/13 10/28/13 10/28/13 08/25/13 12/05/13 12/09/13 12/12/13 12/16/13 08/26/13 12/15/13 08/22/13 08/15/13 12/17/13 09/05/13 09/13/13 09/13/13 10/26/13 10/21/13 10/06/13 03/14/14 09/02/13 03/07/14 12/04/13 09/05/13 12/10/13 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 08/05/13 08/13/13 08/25/13 09/25/13 10/10/13 08/29/13 10/05/13 10/17/13 09/04/13 09/13/13 09/01/13 09/06/13 10/01/13 10/08/13 10/10/13 10/17/13 09/05/13 09/08/13 09/28/13 09/13/13 09/15/13 10/03/13 09/05/13 09/05/13 09/10/13 08/25/13 09/28/13 09/15/13 09/13/13 09/10/13 09/15/13 08/31/13 10/05/13 09/22/13 10/25/13 10/13/13 12/03/13 10/19/13 1,750 $ 6,562.50 15 09/20/13 09/25/13