Question

A linear programming computer package is needed. Edwards Manufacturing Company purchases two component parts from three different suppliers. The suppliers have limited capacity, and no one supplier can meet all the company's needs. In addition, the suppliers charge different prices for the components. Component price data (in price per unit) are as follows. Supplier Component 2 1 $11 $12 $13 2 $9 $10 59 Each supplier has a limited capacity in terms of the total number of components it can supply. However, as long as Edwards provides sufficient advance orders, each supplier can devote its capacity to component 1, component 2, or any combination of the two components, if the total number of units ordered is within its capacity. Supplier capacities are as follows. 2 3 Supplier Capacity 400 800 600 If the Edwards production plan for the next period includes 300 units of component 1 and 600 units of component 2, what purchases do you recommend? That is, how many units of each component should be ordered from each supplier? Component 1, Supplier 1 units Component 1, Supplier 2 units Component 1, Supplier 3 units Component 2, Supplier 1 units Component 2, Supplier 2 units Component 2, Supplier 3 units What is the total purchase cost (in $) for the components? $

Answer 1

Let,

xij = cost of purchasing component i to supplier j where i = {1,2} and j = {1,2,3}

objective is to minimize purchase cost = Min 11x₁₁+12x₁₂+13x₁₃+9x₂₁+10x₂₂+9x₂₃

subject to,

x₁₁+x₁₂+x₁₃ = 800 (Component 1 demand)
x₂₁+x₂₂+x₂₃ = 600 (Component 2 demand)

Capacity constraints
x₁₁+x₂₁ <= 400
x₁₂+x₂₂ <= 800
x₁₃+x₂₃ <= 600

xij >= 0

Setting this up in excel:

Initial formulation:

с D F E G - H J K L M N O P Q 6 x11 x12 x13 x21 x22 X23 7 0 0 0 0 0 0 8 11 12 13 9 10 9 ol 9 Objective function: 10 11 Constraints: 12 1 1 1 0 = 0 = 1 1 1 800 600 400 800 13 1 1 0 <= 14 1 1 0 <= 15 1 1 0 <= 600 16 17 18 19 20 21 22 23 24 25 26

D K L M N 6 E F G H I J x11 x12 x13 x21 22 x23 0 0 0 7 0 0 0 8 9 Objective function: 11 12 13 9 10 9 =SUMPRODUCT(E7:37,E9:19) 10 1 1 1 11 Constraints: 12 1 1 1 13 1 1 =SUMPRODUCT($E$7:$J$7,E11:J11) = 800 =SUMPRODUCT($E$7:$J$7,E12:J12) 600 =SUMPRODUCT($E$7:$J$7,E13:J13) <= 400 =SUMPRODUCT($E$7:$J$7,E14:J14) <= 800 =SUMPRODUCT($E$7:$J$7,E15:15) <= 600 14 1 1 15 1 1 16 17 18 19 20 21

Solver constraints:

Solver Parameters X Set Objective: SLSO ti To: Max Min O Value Of: 0 By Changing Variable Cells: SE$7:$J$7 1 Subject to the Constraints: SLS11:SLS12 = SN$11:SN$12 SL$13:$L$15 <= $N$13:SN$15 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Clicking solve,

C D K L M N o P Q 6 E F G H I J x11 x12 x13 x21 22 x23 400 400 0 0 01 600 7 00 9 11 12 13 9 10 9 14600 Objective function: 10 11 Constraints: 12 1 1 1 1 1 1 13 1 1 800 = 800 600 = 600 400 <= 400 400 <= 800 600 <= 600 14 1 1 15 1 1 16 17 18 19 20 21

Component 1, supplier 1 = 400 units
Component 1, supplier 2 = 400 units
Component 1, supplier 3 = 0 units
Component 2, supplier 1 = 0 units
Component 2, supplier 2 = 0 units
Component 2, supplier 3 = 600 units

Total purchase cost = 14600

PLEASE LEAVE A LIKE. IT REALLY HELPS ME A LOT. THANK YOU!!!