PLEASE ANSWER WITH EXCELL AND FORMULAS

#1) You are tasked with selecting the supplier for a new part from four possible venders A, B, C, and D. You have data related to price, quality, delivery performance, and a sustainability score (5 is best). Assuming each of these four aspects are equally weighted by your purchasing organization, which supplier would you choose given the following information? The critical part dimension for the quality data has a specification of 2.5+0.05 cm; and daily demand of 120, with a daily standard deviation of 40 units; 365 days a year. For this part your organization prefers a service level of at least 95%. Please be clear as to how you are using each piece of information. (40 pts) Vender A Price/unit $16.15 7, in cm 2.499 o, in cm .0135 Leadtime (days) 18 Leadtime Std. 10 Dev. (days) Sustainability 2 Index (5 is best, 0 is worst) B $16.55 2.497 .0119 14 7 $17.45 2.498 .0112 8 5 D $15.75 2.504 .0146 17 10 4 4 3 #2) Assume your organization focuses primarily on price, assume you are forced to use vendor D (even if D did not win your bid in #1). Using the data in the previous problem, how much should be ordered, and when should be orders be placed if the fixed ordering cost is $150/order and the inventory carrying cost is 45% and what is the total cost of this policy? Suppose vender D offers a 7% discount off the price, provided the order volume is at least 10,000 units, does this change your answer regarding how much to order, and, if so, what is the new total cost? (20 pts)