Business Finance
Complete Data Analytics – Exercise 2-11: Improving Decision Making, excel homework help

Question Description

Using Excel to choose target supplier

Unformatted Attachment Preview

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 No.Order No. Item No. 2 A0111 6489 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 Description O-Ring 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 7 7 7 7 8 5 6 5 5 5 A9821 A9842 A9865 A9876 A9999 B0247 B0445 B0447 B0479 B0567 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 Airframe fasteners $ $ $ $ $ 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 Pylon Accessories Durrable Products Durrable Products Durrable Products Durrable Products Durrable Products 8 3 3 3 3 3 B1111 B1234 B1345 B1468 B1589 B1666 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 9764 Gasket 7258 7258 Pressure Gauge Pressure Gauge $ 3.75 $ 90.00 $ 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 $ 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 Hulkey Fasteners 1 C1212 1122 Airframe fasteners $ Hulkey Fasteners Pylon Accessories Manley Valve 1 8 7 C1313 C2211 C2222 3166 Electrical Connector 9764 Gasket 7258 Pressure Gauge $ 1.25 $ 3.75 $ 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 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 1 1 1 1 8 C6765 C7875 C8854 C8989 D1111 5066 3166 3166 9966 Shielded Cable/ft. Electrical Connector Electrical Connector Hatch Decal 9764 Gasket $ $ $ $ $ 0.95 1.25 1.25 0.75 3.75 $ $ $ $ $ 2.45 2.45 2.45 2.45 2.45 4.25 Hulkey Fasteners 1 D1212 5066 Shielded Cable/ft. $ 0.95 Hulkey Fasteners Hulkey Fasteners 1 1 D2121 D3232 4.25 4.25 8 D3333 Airframe fasteners Airframe fasteners Gasket $ $ Pylon Accessories 1122 1122 9764 $ 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 4,800 4,585 63,000.00 3,562.50 9,547.50 525.00 3,847.50 $ $ 17,040.00 16,276.75 1,300 $ 100 $ 550 $ 150 $ 1,500 $ 9,000 $ 4,150 $ 150 $ 140 $ 10,500 $ 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 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 15 30 30 30 30 30 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 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 12/07/13 10/07/13 1,980 100 120 14,000 25,000 10,000 $ $ $ 7,425.00 9,000.00 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 $ 23,750.00 7,062.50 6,781.25 375.00 6,750.00 15 45 45 45 45 45 09/20/13 08/25/13 09/05/13 09/27/13 10/25/13 09/29/13 09/29/13 08/28/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 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 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/13/13 09/10/13 09/15/13 08/31/13 10/05/13 15 30 30 30 30 30 30 30 30 30 30 30 30 15 17,500 $ 16,625.00 09/22/13 $ $ 74,375.00 72,250.00 30 30 30 09/15/13 17,500 17,000 10/25/13 10/13/13 12/03/13 10/19/13 1,750 $ 6,562.50 15 09/20/13 09/25/13 Category Excellent (10) Good (8) Fair (6) Inadequate (4) Most appropriate features of the software have been employed. Most Fields used in decision used Excel formulas to do calculations and formula use was appropriate. Includes appropriate, accurate displays of the data. Use of the software tool is rudimentary. A few Fields used in decision used Excel formulas to do calculations and formula use was inappropriate. Capabilities of the software have not been adequately utilized. Formulas not used for calculations and/or used incorrectly Data Display (10%) Use Excel graphs and charts to present findings Evidence shows mastery of the software with all appropriate features used. All Fields used in decision used Excel formulas to do calculations and formula use was appropriate. Includes appropriate, well-labeled, accurate displays (graphs and/or tables) of the data. Includes appropriate but not accurate displays of the data. Does not include appropriate displays of the data. Category Excellent (20) Good (16) Fair (12) Analysis of the data is appropriate, mostly accurate, and fairly thorough. Conclusion includes an answer to the statistical question that is consistent with the data analysis but not with the data collection method. Some chapter topics are related in the recommendations with some support from the analysis. Gives a good overall picture of the decision process taken —logical, well thought out, steps taken were focused and made sense, but was weak in one of these areas. Analysis of the data is appropriate, but only somewhat accurate and thorough. Conclusion does not include an answer to the statistical question that is consistent with the data analysis. Analysis of the data is not appropriate or is missing. The recommendations have little connection to the chapter topic and have little support from the analysis. Overall picture of the decision process was presented but contained gaps in demonstrating or explaining. Recommendations are unrelated to chapter topics or has minimal relation to the analysis. Use of software tool (10%) Use Excel to calculate values, sort results Analysis Procedure (20%) Analysis of the data is accurate, thorough, and appropriate. Data Analysis Conclusion (20%) Conclusion includes a clear answer to the statistical question that is consistent with the data analysis and the method of data collection. Recommendations are consistent with the chapter material and are backed up by the analysis results. Gives a good overall picture of the decision process taken —logical, well thought out, steps taken were focused and made sense. Recommended Actions (20%) Decision Approach (20%) Approach must be described and explained in writing in a Word Doc or in the Excel Spreasheet. The rubric is designed to yield a total score of 100 for a perfect assignment. Inadequate (8) No conclusion is presented. No evidence of a decision process was presented 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. ...
Purchase answer to see full attachment

Final Answer

Yes. Am uploading now

Vendor Name
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners

Vendor No.
1
1
1
1
1
1
1
1

Order No. Item No.
C1212
C2323
C3232
C3434
C4545
C5656
D2121
D3232

Alum Sheeting
Alum Sheeting
Alum Sheeting
Durrable Products
Durrable Products
Durrable Products
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners

5
5
5
3
3
3
1
1
1
1

A0443
B0247
B0567
A1567
B1468
B1666
C1313
C2929
C7875
C8854

Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies

2
2
2
2
2
2

A1222
A1444
A1445
A1449
A3467
A5689

Alum Sheeting
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Durrable Products
Hulkey Fasteners
Hulkey Fasteners
Spacetime Technologies
Fast-Tie Aerospace
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Durrable Products
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Alum Sheeting
Alum Sheeting
Durrable Products

5
6
6
6
3
1
1
2
4
6
6
6
3
6
6
6
5
5
3

A0223
A0123
A0207
B0445
A1457
C6765
D1212
A0666
C0234
B3041
B3042
B3111
B1589
A0125
A0204
C0589
A0433
A0446
A1344

1122
1122
1122
1122
1122
1122
1122
1122
1243
1243
1243
1369
1369
1369

3166
3166
3166
3166
4111
4111
4111
4111
4111
4111
4224
4312
4312
4312
4569

5066
5066
5125
5166
5234
5234
5234
5275
5319
5319
5319
5417
5417
5454

Durrable Products
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Alum Sheeting
Alum Sheeting
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace

3
4
4
4
4
5
5
6
6
6
4
4
4

A1456
B2519
B2537
B2566
C0433
B0447
B0479
A0205
B3022
C0456
B2498
B2528
C0423

5454
5462
5462
5462
5462
5634
5634
5677
5677
5677
5689
5689
5689

Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace

4
4
4
4
4

B2333
B2345
B2356
B2367
B2378

Manley Valve
Manley Valve
Manley Valve
Pylon Accessories
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Manley Valve
Manley Valve
Durrable Products
Durrable Products
Manley Valve
Fast-Tie Aerospace
Fast-Tie Aerospace
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Manley Valve

7
7
7
8
2
2
2
7
7
3
3
7
4
4
6
6
6
7

A2345
A2378
A9821
A9999
A0111
A0555
A0777
A2356
A9842
B1234
B1345
C2222
B2499
B2511
B3222
B3333
C0467
C3333

6321
6321
6321
6321
6321
6431
6431
6431
6433
6489
6489
6489

Durrable Products
Durrable Products
Durrable Products
Durrable Products

3
3
3
3

A1234
A1235
A1345
A134...

Mattey (148)
UT Austin

Anonymous
I was on a very tight deadline but thanks to Studypool I was able to deliver my assignment on time.

Anonymous
The tutor was pretty knowledgeable, efficient and polite. Great service!

Anonymous
Heard about Studypool for a while and finally tried it. Glad I did caus this was really helpful.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors