Basic Data Analysis: Analysis of Supplier Evaluation and Selection The attached MS EXCEL spreadsheet contains data about a company named ABC LLC and its suppliers. The highlights of the data include the following: The objective of the Data Analysis: To summarize information and help the manager determine the Suppliers that have performed well and which ABC LLC would like to continue doing business with such supplier(s). Note that ABC LLC is a retailer dealing with general merchandise (sells all the items listed under Item No.) QUESTIONS: Using MS EXCEL, determine the supplier capacity, the cost-effectiveness, and lead time (see i., ii & iii below) and present your answers as table(s), graph(s), and/or chart(s) in EXCEL - you are the one to decide the best way to present your answers. I strongly that you WATCH the video tutorials to learn the various ways to present information using tables, charts, and graphs. When you complete your work, submit the MS EXCEL file ONLY. i. Supplier Capacity: Which supplier(s) has the capacity (sufficient Supply base) to meet the demand needs of ABC LLC? (Hint: It is preferable to show more than one supplier for the purpose of comparison). 7.5 points. ii. Cost-Effectiveness: Which supplier offers the most cost-effective or competitive price per item? (Hint: It is preferable to show more than one supplier for the purpose of comparison). 7.5 points iii. Lead time: Determine how long does it take each supplier to ship the products to reach the buyer, in this case? (Hint: You can compute lead time by determining the 'difference' between when the order is placed and when the order arrives), etc. This is basic arithmetic (add/subtract) problem. I have given an example under HINTS below. 10 points. NOTES a. You must show ALL your WORKINGS in your MS EXCEL Spreadsheet(s) clearly demonstrating how you arrive at your results. b. After producing your results from the data analyses, present your results in clear formats using tables, graphs, and/or charts. Use your discretion. In the examples/Hints that I give below, I am using TABLES to present my answer. When you carry out data analysis, you are the one to determine the best way to present your results. It is not right to as me what I or the customer wants. Rather, it is what you deem fit or what you think is right. c. You must submit your EXCEL file. If you prefer, you may include further explanations in EXCEL. Note that you will obtain a ZERO grade if you do not submit the EXCEL File. This is Data Analysis using MS EXCEL, DO NOT USE Microsoft WORD. d. Useful Tutorial: https://www.youtube.com/watch?v=xLmtGk7Ymy8 e.

Please answer i, ii, and iii and show work. Thanks in advance!

B D E G H Orders by ABC LLC and the Suppliers 1.10 OOO OOO NNNNNNMM 3 4 Supplier Name Supplier ID Order No. Item ID. 5 Spacetime Technologies 2 A0111 6489 6 Steelpin Inc 6 A0125 5319 7 Steelpin Inc 6 A0123 4312 8 Steelpin Inc. 6 A0204 5319 9 Steelpin Inc. 6 A0205 5677 10 Steelpin Inc. 6 A0207 4312 11 Alum Sheeting 5 A0223 4224 12 Alum Sheeting 5 A0433 5417 13 Alum Sheeting 5 A0443 1243 14 Alum Sheeting 5 A0446 5417 15 Spacetime Technologies 2 A0533 9752 16 Spacetime Technologies 2 A0555 6489 17 Spacetime Technologies 2 A0622 9752 18 Spacetime Technologies 2 A0666 5125 19 Spacetime Technologies 2 777 648 20 Spacetime Technologies 2 A1222 4111 21 Durrable Products 3 A1234 9399 22 Durrable Products 3 A1235 9399 23 Durrable Products 3 A1344 5454 24 Durrable Products 3 A1345 9399 25 Durable Products 3 A1346 9399 26 Spacetime Technologies 2 A1444 4111 27 Spacetime Technologies 2 A1445 4111 28 Spacetime Technologies 2 A1449 4111 29 Durrable Products 3 A1456 5454 30 Durrable Products 3 A1457 4569 31 Durrable Products 3 A1567 1369 32 Manley Valve 7 A2345 6431 33 Manley Valve 7 A2356 7258 34 Manley Valve 7 A2367 9977 35 Manley Valve 7 A2378 6431 36 Spacetime Technologies 2 A3467 4111 37 Spacetime Technologies 2 A5689 4111 38 Manley Valve 7 A9821 6431 39 Manley Valve 7 A9842 7258 40 Manley Valve 7 A9865 9967 41 Manley Valve 7 A9876 9955 42 Pylon Accessories 8 A9999 6433 43 Alum Sheeting 5 B0247 1243 44 Steelpin Inc. 6 B0445 4312 45 Alum Sheeting 5 B0447 5634 46 Alum Sheeting 5 B0479 5634 47 Alum Sheeting 5 B0567 1243 48 Pylon Accessories 8 B1111 9764 49 Durrable Products 3 B1234 7258 50 Durrable Products 3 B1345 7258 ABC-LLC-Data Sheet1 Item Description O-Ring Shielded Cable/ft. Bolt-nut package Shielded Cable/ft Side Panel Bolt-nut package Bolt-nut package Control Panel Airframe fasteners Control Panel Gasket O-Ring Gasket Shielded Cable/ft. O-Ring Bolt-nut package Gasket Gasket Control Panel Gasket Gasket Bolt-nut package Bolt-nut package Bolt-nut package Control Panel Bolt-nut package Airframe fasteners O-Ring Pressure Gauge Panel Decal O-Ring Bolt-nut package Bolt-nut package O-Ring Pressure Gauge Hatch Decal Door Decal O-Ring Airframe fasteners Bolt-nut package Side Panel Side Panel Airframe fasteners Gasket Pressure Gauge Pressure Gauge Item Cost Per Unit 3.00 $ $ 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.55 $ 3.65 $ 3.65 $ 220.00 $ 3.65 $ 3.65 $ 3.55 $ 3.55 $ 3.55 $ 220.00 $ 3.50 $ 4.20 $ 2.85 $ 100.50 $ 1.00 $ 2.85 $ 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 $ 90.00 Demand Cost per order 900 $ 2,700.00 17,500 $ 19.250.00 4.250 $ 15,937.50 16,500 $ 18,150.00 120 $ 23,400.00 4.200 $ 15.750.00 4,500 $ 17.775.00 500 $ 127.500.00 10.000 $ 42,500.00 406 S 103,530.00 1,500 $ 6,075.00 1.100 $ 3,300.00 1,550 $ 6,277.50 15,000 $ 17.250.00 1,050 3,150.00 4,200 $ 14.910.00 1.250 $ 4,562.50 1,450 $ 5,292.50 550 $ 121,000.00 1,470 $ 5,365.50 1.985 $ 7.245.25 4,250 $ 15,087.50 4,200 $ 14.910.00 4,600 $ 16,330.00 500 $ 110,000.00 3,900 $ 13,650.00 15,000 $ 63.000.00 1,250 $ 3.562.50 95 $ 9.547.50 525 $ 525.00 1,350 $ 3,847.50 4,800 $ 17.040.00 4,585 $ 16,276.75 1,300 $ 3.705.00 100 $ 10.050.00 550 $ 467.50 150 $ 82.50 1,500 s 4,425.00 9.000 $ 38,250.00 4,150 $ 15,562.50 150 $ 27.750.00 140 $ 25,900.00 10,500 $ 44,625.00 1.980 $ 7,425.00 100 S 9,000.00 120 $ 10,800.00 Supply 875 17.560 4,250 15,660 120 4.200 4,500 500 10,000 406 1,500 1.100 1,550 15,000 1,050 4,150 1.250 1,450 550 1.470 1,985 4.250 4,100 4,600 500 3.900 15,000 1.250 95 525 1,350 4.400 4,585 1,300 100 550 150 1,500 9,000 4.150 150 140 10.500 1,980 100 120 AP Terms: Days to Pay Invoice Order Date 25 10/10/15 30 08/20/15 30 08/25/15 30 09/15/15 30 12/02/15 30 09/01/15 30 10/15/15 30 10/20/15 30 08/08/15 30 09/01/15 25 09/20/15 25 10/05/15 25 09/25/15 25 10/01/15 25 10/29/15 25 09/15/15 45 10/01/15 45 10/03/15 45 10/09/15 45 10/07/15 45 10/05/15 25 09/20/15 25 09/25/15 25 10/05/15 45 10/15/15 45 10/05/15 45 09/25/15 30 10/05/15 30 10/20/15 30 12/01/15 30 10/01/15 25 09/05/15 25 09/10/15 30 09/25/15 30 10/15/15 30 12/05/15 30 12/01/15 15 10/01/15 30 09/05/15 30 09/03/15 30 10/25/15 30 10/29/15 30 10/10/15 15 09/20/15 45 08/25/15 45 09/05/15 Arrival Date 10/18/15 08/31/15 09/01/15 10/05/15 12/13/15 09/10/15 10/20/15 10/27/15 08/14/15 09/10/15 09/25/15 10/10/15 10/05/15 10/15/15 12/10/15 10/15/15 10/06/15 10/08/15 10/14/15 10/13/15 10/11/12 10/10/15 10/25/15 10/19/15 10/20/15 10/10/15 09/30/15 10/10/15 10/29/15 12/07/15 10/07/15 09/20/15 09/30/15 10/01/15 10/24/15 12/12/15 12/06/15 10/10/15 09/13/15 09/13/15 13/3/2015 13/4/2015 10/17/15 09/29/15 08/28/15 09/09/15 NM en och F B 50 Durrable Products 3 51 Durrable Products 3 52 Durrable Products 3 53 Durrable Products 3 54 Fast-Tie Aerospace 4 55 Fast-Tie Aerospace 4 56 Fast-Tie Aerospace 4 57 Fast-Tie Aerospace 4 58 Fast-Te Aerospace 4 59 Fast-Tie Aerospace 4 60 Fast-Tie Aerospace 4 61 Fast-Tie Aerospace 4 62 Fast-Te Aerospace 4 63 Fast-Tie Aerospace 4 64 Fast-Tie Aerospace 4 65 Fast-Tie Aerospace 4 66 Steelpin Inc. 6 67 Steelpin Inc 6 68 Steelpin Inc 6 69 Steelpin Inc. 6 70 Steelpin Inc 6 71 Steelpin Inc. 6 72 Fast-Tie Aerospace 4 73 Fast-Tie Aerospace 4 74 Fast-Tie Aerospace 4 75 Steelpin Inc. 6 76 Steelpin Inc 6 77 Steelpin Inc 6 78 Manley Valve 7 79 Hulkey Fasteners 1 80 Hulkey Fasteners 1 81 Pylon Accessories 8 82 Manley Valve 7 83 Hulkey Fasteners 1 84 Hulkey Fasteners 1 85 Hulkey Fasteners 1 86 Manley Valve 7 87 Hulkey Fasteners 1 88 Hulkey Fasteners 1 89 Hulkey Fasteners 1 90 Hulkey Fasteners 1 91 Hulkey Fasteners 1 92 Hulkey Fasteners 1 93 Hulkey Fasteners 1 94 Pylon Accessories 8 95 Hulkey Fasteners 1 96 Hulkey Fasteners 1 97 Hulkey Fasteners 1 98 Pylon Accessories 8 ABC-LLC-Data B1345 B1468 B1589 B1666 B2333 B2345 B2356 B2367 B2378 B2498 B2499 B2511 B2519 B2528 B2537 B2566 B3022 B3041 B3042 B3111 B3222 B3333 C0234 00423 00433 C0456 00467 C0589 C1111 C1212 C1313 C2211 C2222 C2323 C2929 C3232 C3333 C3434 C4545 C5656 C6765 07875 CBB54 C8989 D1111 D1212 D2121 D3232 D3333 Sheet1 D 7258 1369 5275 1369 6321 6321 6321 6321 6321 5689 7268 7268 5462 5689 5462 5462 5677 5234 5234 5234 8008 8008 5166 5689 5462 5677 8008 5319 9955 1122 3166 9764 7258 1122 3166 1122 8148 1122 1122 1122 5066 3166 3166 9966 9764 5066 1122 1122 9764 E Pressure Gauge Airframe fasteners Shielded Cable/ft. Airframe fasteners O-Ring O-Ring O-Ring O-Ring O-Ring Side Panel Pressure Gauge Pressure Gauge Shielded Cable/ft. Side Panel Shielded Cable/tt. 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 Airframe fasteners Electrical Connector Gasket Pressure Gauge Airframe fasteners Electrical Connector Airframe fasteners Machined Valve Airframe fasteners Airframe fasteners Airframe fasteners Shielded Cable/ft. Electrical Connector Electrical Connector Hatch Decal Gasket Shielded Cable/ft. Airframe fasteners Airframe fasteners Gasket 155 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 90.00 4.20 1.00 4.20 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 845.00 645.00 1.25 175.00 1.05 195.00 645.00 1.10 0.55 4.25 1.25 3.75 100.50 4.25 1.25 4.25 655.50 4.25 4.25 4.25 0.95 1.25 1.25 0.75 3.75 0.95 4.25 4.25 3.75 G 120 $ 14,000 $ 25,000 $ 10,000 $ 1,300 $ 1,200 $ 2,500 $ 1,250 $ 1,500 $ 150 $ 110 $ 105 $ 22,500 $ 175 $ 21,500 $ 23,000 $ 110 $ 4,500 $ 4,750 $ 4,850 $ 150 $ 100 $ 5,650 $ $ 22,500 $ 130 $ 120 $ 18,100 $ 125 $ 19,500 $ 5,600 $ 1,850 $ 90 $ 15,500 $ 5,500 $ 18,000 $ 125$ 12,500 $ 15,000 $ 14,500 $ 25,000 $ 5,650 $ 5.425 $ 500 $ 1,800 $ 17,500 $ 17,500 $ 17.000 $ 1,750 $ H 10,800.00 58,800.00 25,000.00 42,000.00 3.185.00 2,940.00 6,125.00 3.062.50 3,675.00 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 82,875.00 7,000.00 6.937.50 9,045.00 65,875.00 6,875.00 76,500.00 81,937.50 53,125.00 63,750.00 61,625.00 23,750.00 7,062.50 6,781.25 375.00 6.750.00 16.625.00 74,375.00 72,250.00 6,562.50 120 14,000 23.995 10,000 1,300 1,200 2,500 1.250 1,500 150 110 105 22,500 175 21,500 22,190 110 4,500 4.750 4,850 150 100 5,650 155 22,500 130 120 18,100 J 45 45 45 45 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 15 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 15 09/05/15 09/27/15 10/25/15 09/29/15 08/25/15 09/13/15 09/25/15 10/13/15 10/25/15 12/01/15 12/01/15 12/05/15 08/20/15 12/05/15 08/15/15 08/10/15 12/05/15 08/28/15 09/05/15 09/02/15 10/15/15 10/10/15 10/01/15 10/25/15 08/25/15 10/28/15 10/28/15 08/25/15 12/05/15 08/05/15 08/25/15 09/25/15 10/10/15 09/04/15 09/01/15 10/01/15 10/10/15 09/05/15 09/08/15 09/28/15 09/05/15 09/05/15 09/10/15 08/25/15 09/28/15 09/15/15 10/25/15 10/13/15 09/20/15 09/09/15 10/03/15 10/30/15 10/04/15 09/04/15 09/23/15 10/04/15 10/21/15 12/02/15 12/09/15 12/12/15 12/16/15 08/26/15 12/15/15 08/22/15 08/15/15 12/17/15 09/05/15 09/13/15 09/13/15 10/26/15 10/21/15 10/06/15 13/3/2015 09/02/15 13/7/2015 13/4/2015 09/05/15 12/10/15 08/13/15 08/29/15 10/05/15 10/17/15 09/13/15 09/06/15 10/08/15 10/17/15 09/13/15 09/15/15 10/03/15 09/13/15 09/10/15 09/15/15 08/31/15 10/05/15 09/22/15 12/03/15 10/19/15 09/25/15 125 19,500 5,590 1,850 90 15,500 5,500 17.750 125 12,500 15,000 14,500 25,000 5,650 5,425 500 1,800 17.500 17.250 17,000 1,750 +