Computational Methods for Management and Economics Carla Gomes

1
Computational Methods forManagement and
EconomicsCarla Gomes

• Module 4
• Displaying and Solving LP Models on a Spreadsheet

2
Developing a Spreadsheet Model

• Step 1 Data Cells
• Enter all of the data for the problem on the
  spreadsheet.
• Make consistent use of rows and columns.
• It is a good idea to color code these data
  cells (e.g., light blue).

• Step 2 Changing Cells
• Add a cell in the spreadsheet for every decision
  variable.
• If you dont have any particular initial values,
  just enter 0 in each.
• It is a good idea to color code these changing
  cells (e.g., yellow with border).

3
Developing a Spreadsheet Model

• Step 3 Target Cell
• Develop an equation that defines the objective of
  the model.
• Typically this equation involves the data cells
  and the changing cells in order to determine a
  quantity of interest (e.g., total profit or total
  cost).
• It is a good idea to color code this cell (e.g.,
  orange with heavy border).

4
Developing a Spreadsheet Model

• Step 4 Constraints
• For any resource that is restricted, calculate
  the amount of that resource used in a cell on the
  spreadsheet (an output cell).
• Define the constraint in three consecutive cells.
  For example, if Quantity A Quantity B, put
  these three items (Quantity A, , Quantity B) in
  consecutive cells.

5
A Trial Solution
The spreadsheet for the Wyndor problem with a
trial solution (4 doors and 3 windows) entered
into the changing cells.
6
Identifying the Target Cell and Changing Cells

• Choose the Solver from the Tools menu.
• Select the cell you wish to optimize in the Set
  Target Cell window.
• Choose Max or Min depending on whether you
  want to maximize or minimize the target cell.
• Enter all the changing cells in the By Changing
  Cells window.

7
Adding Constraints

• To begin entering constraints, click the Add
  button to the right of the constraints window.
• Fill in the entries in the resulting Add
  Constraint dialogue box.

8
The Complete Solver Dialogue Box
9
Some Important Options

• Click on the Options button, and click in both
  the Assume Linear Model and the Assume
  Non-Negative box.
• Assume Linear Model tells the Solver that this
  is a linear programming model.
• Assume Non-Negative adds nonnegativity
  constraints to all the changing cells.

10
The Solver Results Dialogue Box
11
The Optimal Solution