develop a spreadsheet to calculate value index

CHAPTER 2 OPERATIONS AND SUPPLY CHAIN STRATEGIES 37 Minimum order 99.9 Performance 2 days 5.20 per liter Odens Minimum order Size of 100 530 perler Qualifier level minimum requiremento 97. pure - Supplier Order qualifier Conformance Order winners Delivery speed Con How quickly How much can the do the chemicals be chemical delhered? Cost! Are the chemicals pure? Flexibility Cane place mal ordes for the chemical 3. Reconsider Figure 2.3. Suppose Supplier improves its conformance quality so that the chemicals it produces are now 99.9% pure. The chart at the top of this page shows the new competitive situation a. Will this be enough to make Supplier B competitive? Which supplier do you think will win the business? b. (*) Managers at Supplier A have determined that if they increase the minimum order size to 80 liters, they can decrease their costs to $18 per liter. Should they do it? Explain your logic. (Hint: There is no single right answer to this problem.) 4. (*) (Microsoft Exed The following figure shows an Excel spreadsheet that calculates the value index for each of two alternative suppliers. Re-create this spreadsheet in Excel. You should develop the spreadsheet so that the results will be recalculated if any of the values in the high- lighted cells are changed. Your formatting does not have to be exactly the same, but the numbers should be. As a test, see what happens if you change all of the importance scores to your new value indices for Supplier 1 and Sup- plier 2 should be 72 and 63. respectively) B Calculating the Value Index for The Alternative Suppliers 1 2 3 4 Performance 1 poor" to 5 cent" Importance 1 "completely unimportant to 5 orical 5 6 Surple Supplier 15 16 20 10 5 7 Performance quality 9 Conformance quality 10 Delivery reliability 11 Delivery seed 12 Cost 13 Mix flexibility 14 Volume flexibility 15 Performance Importance Supplier 1 Supplier 2 4 4 5 4 1 3 3 5 2 1 4 1 Total 10 6 4 CHAPTER 2 OPERATIONS AND SUPPLY CHAIN STRATEGIES 37 Minimum order 99.9 Performance 2 days 5.20 per liter Odens Minimum order Size of 100 530 perler Qualifier level minimum requiremento 97. pure - Supplier Order qualifier Conformance Order winners Delivery speed Con How quickly How much can the do the chemicals be chemical delhered? Cost! Are the chemicals pure? Flexibility Cane place mal ordes for the chemical 3. Reconsider Figure 2.3. Suppose Supplier improves its conformance quality so that the chemicals it produces are now 99.9% pure. The chart at the top of this page shows the new competitive situation a. Will this be enough to make Supplier B competitive? Which supplier do you think will win the business? b. (*) Managers at Supplier A have determined that if they increase the minimum order size to 80 liters, they can decrease their costs to $18 per liter. Should they do it? Explain your logic. (Hint: There is no single right answer to this problem.) 4. (*) (Microsoft Exed The following figure shows an Excel spreadsheet that calculates the value index for each of two alternative suppliers. Re-create this spreadsheet in Excel. You should develop the spreadsheet so that the results will be recalculated if any of the values in the high- lighted cells are changed. Your formatting does not have to be exactly the same, but the numbers should be. As a test, see what happens if you change all of the importance scores to your new value indices for Supplier 1 and Sup- plier 2 should be 72 and 63. respectively) B Calculating the Value Index for The Alternative Suppliers 1 2 3 4 Performance 1 poor" to 5 cent" Importance 1 "completely unimportant to 5 orical 5 6 Surple Supplier 15 16 20 10 5 7 Performance quality 9 Conformance quality 10 Delivery reliability 11 Delivery seed 12 Cost 13 Mix flexibility 14 Volume flexibility 15 Performance Importance Supplier 1 Supplier 2 4 4 5 4 1 3 3 5 2 1 4 1 Total 10 6 4