Using OPTIMIZER in Corel QuattroPro

Link to Dr.
Frederick's page

Link to MBA 510 page

To perform a linear programming
problem in Quattro, follow these steps:

1. In an Quattro spreadsheet,
set up a matrix of coefficients and limits for the constraints and for
the objective function. Note that it is necessary to include the
nonnegativity constraints explicitly.

A | B | C | D | E | F | |

1 | Constraints | X1 | X2 | X3 | Formulas | Limits |

2 | Labor time | 64 | 33 | 14 | @sumproduct(b2..d2,b$9..d$9) | 400 |

3 | Machine time | 50 | 60 | 9 | @sumproduct(b3..d3,b$9..d$9) | 400 |

4 | Management time | 3 | 3 | 3 | @sumproduct(b4..d4,b$9..d$9) | 80 |

5 | Nonnegativity of X1 | 1 | 0 | 0 | +b9 | 0 |

6 | Nonnegativity of X2 | 0 | 1 | 0 | +c9 | 0 |

7 | Nonnegativity of X3 | 0 | 0 | 1 | +d9 | 0 |

8 | Objective Function | 3.5 | 0.45 | 2.2 | @sumproduct(b8..d8,b$9..d$9) | |

9 | Decision Variables | 1 | 1 | 1 | ||

10 |

2. Click on Tools, then Numeric Tools, then Optimizer

3. Enter the instructions in the Optimizer box.

- Solution Cell: Enter the address of the cell that contains the formula for the objective function.
- Equal to: Click on the proper button to indicate whether the objective function should be minimized or maximized.
- Variable Cells: Enter the addresses that contain the decision variables.
- Constraints: Click on Add. The Cell Constraint box will appear.
- Cell: enter the address of the cell that contains the formula for the left-hand side of the constraint. That would be the cell that contains an expression such as @sumproduct(b2..d2,b$9..d$9).
- select <=, >= or = depending on whether the formula should be less than, greater than, or strictly equal to the value given on the right-hand side.
- Constant: enter the address of the right-hand side value. The right-hand side value is the number which is the upper or lower limit that the solution must satisfy.
- When each constraint is completed, click Add. When the last constraint is completed, click OK. (You may click OK instead of Add after the last constraint.)
- Constraints may be entered one at a time. When there is a group of constraints that are all less-than constraints or all greater-than constraints, they may be entered as a group by entering the block of formula cells in Cell entering the block of limits in Constant.
- Click on Options. When the Optimizer Options box appears, click on Assume Linear Model, then OK
- Click on the Solve button.

- make sure you have told Excel to perform the right kind of analysis: maximize or minimize.
- make sure your constraints are greater-than or less-than when they should be.
- make sure your cell formulas are correct.
- make sure there are coefficients in the cells that the formulas use.