Planning Procurement. An automobile manufacturer wants to award contracts for the supply of five different engine components. Some of the components are used in most models, while other components are specialized to one or two models—therefore, the volumes differ. Five contractors have submitted bids on the components, and the following table summarizes the prices bid per unit. Where no entry appears, the contractor did not bid.

The manufacturer wants to determine how many units of each component should be awarded to each contractor in order to minimize the total cost for the purchases. (For these purposes, fractional solutions in the model are acceptable.)
a. What is the minimum total cost for the manufacturer if no other conditions are imposed?
b. In fact, Supplier 2 is capacity-constrained and cannot provide more than 25,000 units in total. In addition, the manufacturer does not want more than $2 million allocated to any one of the suppliers. Recognizing these limitations, what is the minimum total cost for the manufacturer?
c. Describe the qualitative pattern (computational scheme) in the solution for (b).

Use excel with proper formulas.

Component C1 C2 C3 C4 C5 Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 25 55 42 40 28 78 44 41 30 80 58 40 82 57 43 32 88 60 42 Demand 40,000 10,000 25,000 10,000 30,000