Excel Assignment

Farm Implement Company manufactures various pieces of farm equipment. It is seeking suppliers for three components that are used in a number of products in its line. Four suppliers have submitted bids on these components. The table below summarizes the per unit price each supplier has quoted for each of the components. Where no entry is made, the supplier did not submit a quote.

Supplier Component
1 2 3
1 $72 $16 $175
2 $68 $20 $170
3 $64 - $150
4 $65 $15 -
Demand (units) 1,000 690 2,500

The demand for a component does not have to be supplied entirely by one supplier. In fact, certain suppliers have indicated that they cannot supply more than a certain number of units at the quoted price. Supplier 1 can supply no more than 800 units of Component 2; Supplier 2 can supply no more than 500 units of Component 2; Supplier 3 can supply no more than 300 units of Component 1 and no more than 2,000 units of Component 3; finally, Supplier 4 can supply no more than 500 units of Component 1.

There is no provision that the supply contract be awarded to the lowest bidder. The equipment manufacturer wants to determine how many units of each component should be awarded to each supplier so as to minimize the total cost for acquiring enough components to meet demand. Supplier 1 has specified that it requires a minimum award of $100,000 if it is to supply any components at all. Farm Implement wishes to avoid awarding over $300,000 in contracts to any one supplier.

1. Formulate a mathematical linear programming model and type it in worksheet 1 of your Excel file.

2. In worksheet 2 of your Excel file, formulate an Excel model that corresponds to your mathematical model; and use Solver to find the optimal solution to this problem.

Hint: Generally speaking, there are two approaches to solve this problem.

(a) Include a binary decision variable, say, S to represent whether supplier 1 is used. If supplier 1 is used, then s = 1; otherwise, s = 0. When adding constraint that s is binary, your cell reference will be where the value of s is, and the type of the constraint will be "bin". You can find it by clicking "<=". Constraint will be empty.

(b) You can solve two independent linear programming models: one with supplier 1, the other without supplier 1. In the end, compare and see which model gives you better result.