U.S. Labs manufactures mechanical heart valves from the heart valves of pigs. Different heart operations require valves of different sizes. U.S. Labs purchases pig valves from three different...

U.S. Labs manufactures mechanical heart valves from the heart valves of pigs. Different heart operations require valves of different sizes. U.S. Labs purchases pig valves from three different suppliers. The cost and size mix of the valves purchased from each supplier are given in the file P03_35.xlsx. Each month, U.S. Labs places an order with each supplier. At least 500 large, 300 medium, and 300 small valves must be purchased each month. Because of the limited availability of pig valves, at most 500 valves per month can be purchased from each supplier.


a. Use Solver to determine how U.S. Labs can minimize the cost of acquiring the needed valves.

b. Suppose U.S. Labs wants to investigate the effect on total cost of increasing its minimal purchase requirements each month. Specifically, it wants to see how total cost changes as the minimal purchase requirements of large, medium, and small valves all increase from their values in the problem by the same percentage. Revise your model so that SolverTable can be used to investigate these changes when the percentage increase varies from 2% to 20% in increments of 2%. Explain intuitively what happens when this percentage is at least 16%.