Use matrix approach to implement and solve this problem in Excel showing work.

Drvol Die and Tool needs to order quantities of part XY76 which they need for machining purposes. They have two different suppliers they utilize to order the parts. Drvol Die and Tool has two plants that need XY76 for the next production period – the Omaha plant needs 850 units and the Lincoln plant 1100 units. Assume demand for these parts must be exactly met.

Supplier Alpha can supply up to 1500 units for 5.5 cents each. Supplier Beta can supply up to 1300 units for 4.7 cents each. There are no quantity discounts. To maintain strong relationships with both suppliers, at least 50% of the maximum of XY76 available from each supplier must be used/ordered. To ensure that there are no quality issues, each supplier must provide at least 30% of the individual plant’s need for XY76.

Finally, there is a little cost difference in getting the XY76 from the suppliers to the two Plants. Based on past data, assess a cost of 1.1 cents/unit for parts going from Supplier Alpha to Omaha, 1.6 cents/unit from Supplier Alpha to Lincoln, 1.3 cents/unit for parts going from Supplier Beta to Omaha, and 1 cent/unit from Supplier Beta to Lincoln. Find the least cost way for Drvol Die and Tool to procure part XY76.

Hint: You can simplify the ratio requirements by utilizing the information that part demand must be met exactly.