Tutorial Solving Linear Programming Problem by Excel solver and the Integer programing Brief introduction: 1.Excel Solver can solve linear programming problems with small size. 2.Professional optimization tools,such as,CPLEX solver,LINDO are used to handle LP problem with large size
Brief introduction: 1. Excel Solver can solve linear programming problems with small size. 2. Professional optimization tools, such as, CPLEX solver, LINDO are used to handle LP problem with large size
Outline Excel solver for linear programming o Techniques to formulate problem by using binary variables
Outline Excel solver for linear programming Techniques to formulate problem by using binary variables
Solve LP by Excel solver Example 1: min Z=3x1 2x2 s.t 2x1+x2 ≥ 10 -3x1+2x2 ≤6 C1+C2 ≥6 x1≥0， x2≥0. ● This problem is called linear programming problem,since the objective function and the constraints are linear and the decision variables are continuous. If all the decision variable can only take integer value,then the problem is called the integer programming problem. If some of the decision variables take only the integer value,this problem is called the mixed-integer programming problem
Solve LP by Excel solver Example 1: This problem is called linear programming problem, since the objective function and the constraints are linear and the decision variables are continuous. If all the decision variable can only take integer value, then the problem is called the integer programming problem. If some of the decision variables take only the integer value, this problem is called the mixed-integer programming problem
Solve LP by Excel solver(continue) -Represent the decision variables by the cells in ECXEL.In our example, “$A$1”and“$A$2”are used to represent the decision variable x1 and x2 respectively.Fill the initial value of them (e.g."O"). Select a cell to represent objective function.In the example,"SA$3" represents objective function and it is filled by formula"=3*$A$1+2*$A$2". -Select cells to represent constraints.For example,"$B$1"represents the left-hand-side of constraint 1 and it is filled by formula “=2*$A$1+$A$2”.The cell“$C$1”represents the right-hand-side of constraint1 and it is filled by“6”.Similarly,“$B$2”and“$B$3”represents the left-hand-side of constraints2and3.“$C$2”and“$C$3”represents the right-hand-side of constraints 2 and 3
Solve LP by Excel solver (continue) Represent the decision variables by the cells in ECXEL . In our example, “$A$1” and “$A$2” are used to represent the decision variable x1 and x2 respectively. Fill the initial value of them (e.g. “0”). Select a cell to represent objective function. In the example, “$A$3” represents objective function and it is filled by formula “=3*$A$1+2*$A$2”. Select cells to represent constraints. For example, “$B$1” represents the left-hand-side of constraint 1 and it is filled by formula “=2*$A$1+$A$2”. The cell “$C$1” represents the right-hand-side of constraint 1 and it is filled by “6”. Similarly, “$B$2” and “$B$3” represents the left-hand-side of constraints 2 and 3. “$C$2” and “$C$3” represents the right-hand-side of constraints 2 and 3
Solve LP by Excel solver (continue) Open the Excel Solver Click "Tools"on menu bar to check whether there is tool called“Solver..”.If it is not there,click“Add-lns.”to add this tool. Solver Parameters Set the target value as Set Target Cell: 5As3 国 Solve “$A$3”and set the changing Equal To: Max OM恤 OValue of: 0 Close cells as“$A$1,$A$2”. By Changing Cells: SA$1,$A52 Guess Subject to the Constraints: Options Add Change Reset All Delete Help
Solve LP by Excel solver (continue) Open the Excel Solver Click “Tools” on menu bar to check whether there is tool called “Solver…”. If it is not there, click “Add-Ins..” to add this tool. Set the target value as “$A$3” and set the changing cells as “$A$1, $A$2