View the step-by-step solution to:

Question

Kabeni,

I a m having a very hard time answering this question and would be grateful if you could help me

with this and the excel portion of the question.

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.
Background image of page 1
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.
Background image of page 2
Orders and Suppliers Vendor Name Vendor NoOrder No. Item No. Item Description Item Cost Spacetme Technologies 2 A0111 6489 O-Ring $ 3.00 S±eelpin Inc. 6 A0125 5319 Shielded Cable/f. $ 1.10 S±eelpin Inc. 6 A0123 4312 Bolt-nut package $ 3.75 S±eelpin Inc. 6 A0204 5319 Shielded Cable/f. $ 1.10 S±eelpin Inc. 6 A0205 5677 Side Panel $ 195.00 S±eelpin Inc. 6 A0207 4312 Bolt-nut package $ 3.75 Alum Sheetng 5 A0223 4224 Bolt-nut package $ 3.95 Alum Sheetng 5 A0433 5417 Con±rol Panel $ 255.00 Alum Sheetng 5 A0443 1243 Airframe fasteners $ 4.25 Alum Sheetng 5 A0446 5417 Con±rol Panel $ 255.00 Spacetme Technologies 2 A0533 9752 Gasket $ 4.05 Spacetme Technologies 2 A0555 6489 O-Ring $ 3.00 Spacetme Technologies 2 A0622 9752 Gasket $ 4.05 Spacetme Technologies 2 A0666 5125 Shielded Cable/f. $ 1.15 Spacetme Technologies 2 A0777 6489 O-Ring $ 3.00 Spacetime Technologies 2 A1222 4111 Bolt-nut package $ 3.55 Durrable Products 3 A1234 9399 Gasket $ 3.65 Durrable Products 3 A1235 9399 Gasket $ 3.65 Durrable Produc±s 3 A1344 5454 Con±rol Panel $ 220.00 Durrable Products 3 A1345 9399 Gasket $ 3.65 Durrable Products 3 A1346 9399 Gasket $ 3.65 Spacetime Technologies 2 A1444 4111 Bolt-nut package $ 3.55 Spacetime Technologies 2 A1445 4111 Bolt-nut package $ 3.55 Spacetime Technologies 2 A1449 4111 Bolt-nut package $ 3.55 Durrable Produc±s 3 A1456 5454 Con±rol Panel $ 220.00 Durrable Produc±s 3 A1457 4569 Bolt-nut package $ 3.50 Durrable Produc±s 3 A1567 1369 Airframe fasteners $ 4.20 Manley Valve 7 A2345 6431 O-Ring $ 2.85 Manley Valve 7 A2356 7258 Pressure Gauge $ 100.50 Manley Valve 7 A2367 9977 Panel Decal $ 1.00 Manley Valve 7 A2378 6431 O-Ring $ 2.85 Spacetime Technologies 2 A3467 4111 Bolt-nut package $ 3.55 Spacetime Technologies 2 A5689 4111 Bolt-nut package $ 3.55 Manley Valve 7 A9821 6431 O-Ring $ 2.85 Manley Valve 7 A9842 7258 Pressure Gauge $ 100.50 Manley Valve 7 A9865 9967 Ha±ch Decal $ 0.85 Manley Valve 7 A9876 9955 Door Decal $ 0.55 Pylon Accessories 8 A9999 6433 O-Ring $ 2.95 Alum Sheetng 5 B0247 1243 Airframe fasteners $ 4.25 S±eelpin Inc. 6 B0445 4312 Bolt-nut package $ 3.75 Alum Sheetng 5 B0447 5634 Side Panel $ 185.00 Alum Sheetng 5 B0479 5634 Side Panel $ 185.00 Alum Sheetng 5 B0567 1243 Airframe fasteners $ 4.25 Pylon Accessories 8 B1111 9764 Gasket $ 3.75 Durrable Produc±s 3 B1234 7258 Pressure Gauge $ 90.00
Background image of page 1
Durrable Products 3 B1345 7258 Pressure Gauge $ 90.00 Durrable Products 3 B1468 1369 Airframe fasteners $ 4.20 Durrable Products 3 B1589 5275 Shielded Cable/f. $ 1.00 Durrable Products 3 B1666 1369 Airframe fasteners $ 4.20 Fast-Tie Aerospace 4 B2333 6321 O-Ring $ 2.45 Fast-Tie Aerospace 4 B2345 6321 O-Ring $ 2.45 Fast-Tie Aerospace 4 B2356 6321 O-Ring $ 2.45 Fast-Tie Aerospace 4 B2367 6321 O-Ring $ 2.45 Fast-Tie Aerospace 4 B2378 6321 O-Ring $ 2.45 Fast-Tie Aerospace 4 B2498 5689 Side Panel $ 175.00 Fast-Tie Aerospace 4 B2499 7268 Pressure Gauge $ 95.00 Fast-Tie Aerospace 4 B2511 7268 Pressure Gauge $ 95.00 Fast-Tie Aerospace 4 B2519 5462 Shielded Cable/f. $ 1.05 Fast-Tie Aerospace 4 B2528 5689 Side Panel $ 175.00 Fast-Tie Aerospace 4 B2537 5462 Shielded Cable/f. $ 1.05 Fast-Tie Aerospace 4 B2566 5462 Shielded Cable/f. $ 1.05 Steelpin Inc. 6 B3022 5677 Side Panel $ 195.00 Steelpin Inc. 6 B3041 5234 Electrical Connector $ 1.65 Steelpin Inc. 6 B3042 5234 Electrical Connector $ 1.65 Steelpin Inc. 6 B3111 5234 Electrical Connector $ 1.65 Steelpin Inc. 6 B3222 8008 Machined Valve $ 645.00 Steelpin Inc. 6 B3333 8008 Machined Valve $ 645.00 Fast-Tie Aerospace 4 C0234 5166 Electrical Connector $ 1.25 Fast-Tie Aerospace 4 C0423 5689 Side Panel $ 175.00 Fast-Tie Aerospace 4 C0433 5462 Shielded Cable/f. $ 1.05 Steelpin Inc. 6 C0456 5677 Side Panel $ 195.00 Steelpin Inc. 6 C0467 8008 Machined Valve $ 645.00 Steelpin Inc. 6 C0589 5319 Shielded Cable/f. $ 1.10 Manley Valve 7 C1111 9955 Door Decal $ 0.55 Hulkey Fasteners 1 C1212 1122 Airframe fasteners $ 4.25 Hulkey Fasteners 1 C1313 3166 Electrical Connector $ 1.25 Pylon Accessories 8 C2211 9764 Gasket $ 3.75 Manley Valve 7 C2222 7258 Pressure Gauge $ 100.50 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 1 C3434 1122 Airframe fasteners $ 4.25 Hulkey Fasteners 1 C4545 1122 Airframe fasteners $ 4.25 Hulkey Fasteners 1 C5656 1122 Airframe fasteners $ 4.25 Hulkey Fasteners 1 C6765 5066 Shielded Cable/f. $ 0.95 Hulkey Fasteners 1 C7875 3166 Electrical Connector $ 1.25 Hulkey Fasteners 1 C8854 3166 Electrical Connector $ 1.25 Hulkey Fasteners 1 C8989 9966 Hatch Decal $ 0.75 Pylon Accessories 8 D1111 9764 Gasket $ 3.75 Hulkey Fasteners 1 D1212 5066 Shielded Cable/f. $ 0.95 Hulkey Fasteners 1 D2121 1122 Airframe fasteners $ 4.25 Hulkey Fasteners 1 D3232 1122 Airframe fasteners $ 4.25
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

View the full answer
Ess11ch02-11 - Data (1).xlsx

Orders a
Vendor Name Vendor No. Order No. Item No. Item Description Alum Sheeting
Alum Sheeting
Alum Sheeting
Alum Sheeting
Alum Sheeting
Alum Sheeting
Alum Sheeting
Alum Sheeting
Durrable...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online