Formulate a mathematical linear programming model

Assignment Help Business Management

Reference no: EM131000706

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.

Reference no: EM131000706

Questions Cloud

Advantages and disadvantages to ibm and smt : What are the advantages and disadvantages to IBM and SMT from this approach and how does SMT's proposed learning rate compare with that of other companies?

Prepare the journal entry to record the payment on december : Prepare the journal entry to record the payment on December 15, 2011, using the gross method of accounting for purchase discounts.

What is the average velocity of this projectile : A catapult throws a projectile at a castle, but it lands 5.00 m short of the castle wall. The position of the projectile as a function of time is given by the equation r→(t) =(21.7m/s)t i^ + [(12.5m/s)t - (4.90m/s2)t2]j^. What is the average veloci..

What is a major objective of accounting for inventory : Indicate the circumstances when it is appropriate to initially measure agricultural inventory at fair value.

Formulate a mathematical linear programming model : 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

Following information related to its merchandise inventory : Determine the inventory balance Altira would report in its August 31, 2011, balance sheet and the cost of goods sold it would report in its August 2011 income statement using each of the following cost flow methods:

What are the options and what is the cost of each option : What are the options? What is the cost of each option and what are the advantages and disadvantages of each option?

How far up the incline does the block travel : A small block travels up a frictionless incline that is at an angle of 30.00 above the horizontal. The block has speed 4.69 m/s at the bottom of the incline. Assume g = 9.80 m/s^2. How far up the incline (measured parallel to the surface of the in..

Find the slop of the curve : Find the derivation of ex ln(x2) + 4-x. Find the slop of the curve 2(x+y)2/3 = y at the point (4, 4). Find the second derivative y'' of x2 + y2 = 10

User Account

All Pages