The University of North Carolina at Pembroke
Using OPTIMIZER in Corel QuattroPro

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.