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
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
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.
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...