Linear Programming

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 xs 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
5
Enter SUMPRODUCT Formula for the Total Proift
6
Drag SUMPRODUCT FormulaDown to get LHS Totals
7
Go DataSelect 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

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

E7E10
G7G10
lt
This is what we enter in the example given here
then we click OK.
11
Clicking Options
12
The Options Dialogue Box

Most of the rest of the entries deal with integer
and nonlinear models.
13
Click Solve
14
Solver Solution
15
The Answer Report
16
The Sensitivity Report
17
Notes on Sensitivity Report Output

• 1E30 is Excels 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