Question

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 1 2 3
                
1 $12 $13 $14

2 $10 $11 $10

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.

Supplier 1 2 3

Capacity 600 1000 800

If the Edwards production plan for the next period includes 1000 units of component 1 and 800 units of component 2, how many units of each component should be ordered from each supplier? What is the total purchase cost for the components?

Develop a linear program.

Answer 1

Enter the given data in Excel, as shown below:

F G Component Price (S) Supplier 3 Component 1 12 10 13 10 Supplier Capacity 8 Supplier1 9 Capacity 600 10 1000 800 Order size 12 13 Component 1 14 1 15 2 16 Supplier 600 SUM(B14:B15) -SUM(C14:C15) -SUM(D14:D15) 600 400 1.4210854715202 800 0 SUM(B14:D14) 31000 SUM(B15:D15) 3 800 18 19 20 1000 800 Total Purchase Cost: SUMPRODUCT(B4D5,B14:D15)

Go to the Data Tab and open Excel Solver. Enter the decision variables, objective function and the constraints, as shown below:

Solver Parameters Set Objective: To: Max By Changing Variable Cells: sc$20 Min Value Of. 0 $B$14:$D$15 Subject to the Constraints: SB$16:$D$ 16 <= $BS18:SD$18 $E$14:$E$15 $G$14:$G$15 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method GRG Nonlinear 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

• The objective function (Total Purchase Cost) is in cell C20.
• The decision variables are in cells B14:D15

Click on Solve. The following solution will appear:

Component Price (S) Supplier 3 Component1 12 10 13 10 Supplier Capacity 8 Supplier 9 Capacity 10 600 1,000 800 Order size 12 13 Component1 Supplier 400 400 1,000 600 0 600 800 800 10001,000 800 15 16 17 18 19 20 Total Purchase Cost: 800 600 800 $20,400.00

Thus, the total purchase cost is $20,400.