Linear Programming

80 Views

Download Presentation
## Linear Programming

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Linear Programming**Excel Solver**The Linear Programming Model**MAX 8X1 + 5X2 s.t. 2X1 + 1X2≤ 1000 (Plastic) 3X1 + 4X2 ≤ 2400 (Prod. Time) X1 + X2 ≤ 700 (Total Prod.) X1 - X2 ≤ 350 (Mix) All x’s ≥ 0**Setting Up the Excel Spreadsheet**• Use one column for each decision variable and label each column. • Leave a blank row where the results will be calculated – row of “Changing Cells” and one more blank row below that • Label each row (changing cells, objective function and constraints) to the left with a brief description. • Leave one column in between the column for the last variable and the sign of the constraint for the total of left hand side. • Label the row as Total LHS (for left hand side)**Input Coefficients/ Label Rows**Changing Cells Label Changing Cells Where results will be given Label for Left Hand Side Total Constraint Labels Coefficients Objective Function Label**Enter SUMPRODUCT Formula for the Total Proift**Highlight cells C4 and D4and press the F4 functionkey to enter $ signs Highlight cellsC6 and D6 =SUMPRODUCT($C$4:$D$4,C6:D6) is equivalent to =$C$4*C6+$D$4*D6**Drag SUMPRODUCT FormulaDown to get LHS Totals**Drag cell E6 down to cells E7:E10 +**Go DataSelect Solver**1. Target cell is the cell that contains object function value – Click cell E6. $E$6 2. Click Max or Min (Default is Max). $C$4:$D$4 3. The Changing Cells are the cells containing the decision variables – Highlight cells C4 and D4. 4. Click Add to add constraints.**Types of Constraints**• There are 3 types of functional constraints that can be added: • “≤” • “=” • “≥” • There are also 2 other constraints in Solver that deal with requiring the value of a decision variable to be: • Integer • Binary**Adding A Functional Constraint**$G$7 $E$7 Click on the cell reference containing the corresponding RHS value Click on a cell reference containinga total LHS value ≤=≥ Click Add if more constraints are to be entered Click OK if no more constraints are to be entered • The general approach is:**Adding Several Constraints Simulataneously**• If several consecutive constraints all have the same relation (“≤”, “=”, or “≥”) these can be entered all at once by: • Highlighting the set of total LHS values • Choosing the relationship • Highlighting the corresponding set of RHS values. $G$7:$G$10 $E$7:$E$10 <= This is what we enter in the example given here; then we click OK.**Clicking Options**We must finally say that the problem is to be solved as a linear program and that the variables are “≥ 0”. This is done in the Options dialogue box.**The Options Dialogue Box**Click OK Check Assume Linear Model Check Assume Non-Negative Most of the rest of the entries deal with integer and nonlinear models.**Click Solve**Click Solve**Solver Solution**Optimal Solution Optimal Objective Function Value Highlight Answer Sensitivity Click OK**The Answer Report**Optimal Objective Function Value Optimal Solution Total LHS Values Difference Between RHS - LHS**The Sensitivity Report**The amount the corresponding objective function coefficient can increase or decrease without changing the optimal solution The Objective Coefficients Input Effect on objective function if X ≥ 1 is added Optimal Solution The amount the RHS value can increase or decrease without changing the shadow price The amount the objective function would change with 1 more unit on the RHS The RHS Coefficients Input Total LHS Value**Notes on Sensitivity Report Output**• 1E+30 is Excel’s way of saying “infinity” • Allowable Increases and Decreases apply to changing that one coefficient only – keeping all of the other coefficients the same. • Reduced Cost has many meanings: • How would the objective function be affected if the variable had to assume a value of at least 1 • How much would the objective function coefficient have to change before it is economically beneficial for the corresponding variable to be positive.**Review**• How to set up an Excel spreadsheet to solve a linear program • Filling in the Solver dialogue box. • How to “Add Constraints” • Filling in the Options dialogue box • Reading and interpreting: • Excel Output • The Answer Report • The Sensitivity Report