1 / 18

Linear Programming

Linear Programming. Excel Solver. The Linear Programming Model. MAX 8X 1 + 5X 2 s.t. 2X 1 + 1X 2 ≤ 1000 (Plastic) 3X 1 + 4X 2 ≤ 2400 (Prod. Time) X 1 + X 2 ≤ 700 (Total Prod.) X 1 - X 2 ≤ 350 (Mix) All x’s ≥ 0.

terra
Download Presentation

Linear Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Linear Programming Excel Solver

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

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

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

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

  6. Drag SUMPRODUCT FormulaDown to get LHS Totals Drag cell E6 down to cells E7:E10 +

  7. Go to ToolsSelect 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. Note: If Solver is not in Tools menu,select Add-Ins from Tools menu andcheck Solver.

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

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

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

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

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

  13. Click Solve Click Solve

  14. Solver Solution Optimal Solution Optimal Objective Function Value Highlight Answer Sensitivity Click OK

  15. The Answer Report Name is first nonnumeric cell to the left of the cell followed by the first nonnumeric cell above it Optimal Objective Function Value Optimal Solution Total LHS Values Difference Between RHS - LHS

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

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

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

More Related