Excel Solver Linear Programming

1
Excel Solver Linear Programming

• IOM580
• Hiroshi Ochiumi

2
Linear Programming (LP)

• Concepts
• Linear problem
• Objective function
• Decision variables
• Constraints
• Feasible solutions
• Learn How to use Excel to solve linear programs

3
Example

• Two products, X and Y, both require processing
  time on machines I and II. Machine I has 200
  hours available, and machine II has 400 hours
  available. Product X requires one hour on machine
  I and four hours on machine II. Product Y
  requires one hour on machine I and one hour on
  machine II. Each unit of product X yields 10
  profit and each unit of Y yields 5.

4
Example

• Identify
• Objective function
• Decision variable(s)
• Constraints
• Feasible solutions

5
How to find the optimal solution?

• Paper and pencil method, using graph
• Excel Solver

6
How to solve the LP
Assign cells to decision variables.
7
How to solve the LP

• Assign a cell to the objective function and type
  in the objective.

8
How to solve the LP

• Enter constraints

9
How to solve the LP

• Choose solver under tools menu

10
How to solve the LP

• Target cell objective

11
How to solve the LP

• By changing cells decision variables

12
How to solve the LP

• Add constraint(s)

13
How to solve the LP

• Click on solve!

14
The solver

• You may see the results on the spread sheet, or
  on the answer report.

15
Some Observations.

• Feasible region
• Infeasible problem?
• Unbounded Problems.
• Objective is Parallel to a constraint.
• Cannot do graphs for 3 or more variables.
• Use Excel.