Excel Solver for Linear Programming

Only available on StudyMode
  • Download(s) : 120
  • Published : September 1, 2011
Open Document
Text Preview
Linear Programming Using Excel
Subject: Linear Programming using Excel Application: Microsoft Excel 2007 Task: Solving a Linear Program Using Excel Tutorial Date: 25th February, 2010 by Nathan Smith

Install the Solver Add-In

1. In the Microsoft Office button, go to excel options to click Add-ins 2. In the Add-Ins box, select Solver Add-In and click Go...

Linear Programming Using Excel - 1

Install the Solver Add-In (continue)

3. In the Add-Ins available box, check the Analysis ToolPak and then OK

Linear Programming Using Excel - 2

Setting Up the Problem on the Spreadsheet

Example Min Z = 6X + 7Y s.t 2X + 6Y ≥ 10 5X + 3Y ≥ 10 X,Y ≥ 0

Linear Programming Using Excel - 3

(continued)

1. Enter the coefficients of the objective function Z i.e., (6, 7) in cells E5 and F5. 2. Enter the coefficients of the Constraint-1 i.e., (2,6) and RHS value 10 in cells E9, F9 and H9 respectively 3. Enter the coefficients of the Constraint-2 i.e., (5,3) and RHS value 10 in cells E10, F10 and H10 respectively

Linear Programming Using Excel - 4

(continued)

1. For the Objective function value, enter the formula for computing Z = SUMPRODUCT(E5:F5,E6:F6). This formula uses the coefficient values and also the solution values for variables X and Y, which are supposed to be solved. 2. Similarly enter the formula for LHS of the Constraints 1 & 2 i.e., SUMPRODUCT(E9:F9,$E$6:$F$6) & SUMPRODUCT(E10:F10,$E$6:$F$6) respectively

Linear Programming Using Excel - 5

Now Excel Solver will be used, in the Data tab click Solver. The solver box appears as follows.

(continued)

1. Set the Target Cell for the Objective Function Z value i.e., $E$7 2. Check the Equal to Min i.e., Minimum Option. 3. For Changing Cell, select the solution values of the variables X & 7 i.e., $E$6:$F$6 Linear Programming Using Excel - 6

4. For subject to the constraints, LHS >=RHS i.e., click on the Add option and select $E$13:$E$14 >= $E$13:$E$14 5. Also all the...
tracking img