The University of North Carolina
at Pembroke
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.
4. Optimizer will
try to solve your problem. If it is successful, it will present a
box asking what results you want it to show: the answer report, the sensitivity
report, and/or the limits report. If it is not successful, it will
give a message such as "did not converge in time allowed", "problem is
unbounded", or "no feasible region". If this happens check the following:
-
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.
Sometimes these errors are inherent
in the problem, but in textbook examples they are more likely the result
of an error in setting up the problem.