Transcript LP Solution with Excel Solver - Sepuluh Nopember Institute

LP Solution with Excel Solver
By: Dr. Eng. Rudi W. Prastianto
Dept. of Ocean Engineering, ITS
Surabaya
Activation Excel Solver (2007)
Click “Office button”
Click “Excel Options”
button
Click “Add-Ins”
Select “Excel Add-ins”
Click “Go …”
Select “Solver Add-in”
Check “Solver Add-in”
• The Excel
will install
the Solver
for a few
minutes
Click “OK”
Solver Activated
• Solver appears in “Data” group  ready to
use by Clicking “Solver”
The spreadsheet is the input and output
medium for the Excel Solver
The prepared data includes four types of
information:
1. Input data cells (shaded areas, B5:C9 and F6:F9),
2. Cells representing the variables and the
objective function we seek to evaluate (solid
rectangle cells, B13:013),
3. Algebraic definitions of the objective function
and the left-hand side of the constraints (dashed
rectangle cells, 05:09), and
4. Cells that provides explanatory names or
symbols  enhances the readability of the
model and serves no other purpose.
“Solver Parameters” Dialogue Box
“Add Constraint” Dialogue Box
“Solver Options” Dialogue Box
How does Solver link to the spreadsheet
data?
1. Provide equivalent "algebraic“ definitions of
the objective function and the left-hand side
of the constraints using the input data
(shaded cells B5:C9 and F6:F9),
2. The objective function and variables (solid
rectangle cells B13:013), and
3. Place the resulting formulas in the
appropriate cells of the dashed rectangle
D5:09.
• Table shows the original LP functions and their
placement in the appropriate cells: