General Optimizer#

Overview#

General Optimizer is a tool to select the best element, with regard to some criterion (i.e., objective function), from some set of available alternatives (i.e., variables). Potential applications include facility location optimization, portfolio optimization, inventory optimization, etc.

General Optimizer requires a formula cell evaluating the objective function based on the variable cells. Bounds and constraints of variables can also be added if applicable. During the solving process, General Optimizer keeps writing the variable cells and reading the updated objective function cell. When the optimum is found, optimal values will be filled in the variable cells.

Compared to Linear Programming, General Optimizer can be applied to more general problems, i.e., both linear and nonlinear problems, but it is less efficient to solve linear programs.

Videos#

Note

Video goes here.

Examples#

Here is a step-by-step example of using General Optimizer to maximize the area of a triangle with a given perimeter of 9.

  1. Prepare an Excel sheet with cells of objective function, variables, bounds, and constraints. The objective function cell and the constraint cells can be formula cells.

    In this example, the variables at A3:A5 are the lengths of three sides. The objective function is the area computed by Heron’s formula. The first three constraints are triangle inequality. The last constraint sets the perimeter to 9. Here are the formulas.

    • Objective function at E3: =SQRT((A3+A4+A5)*(A3+A4-A5)*(A4+A5-A3)*(A5+A3-A4))/4

    • Constraint left-hand side at A9: =A3+A4-A5

    • Constraint left-hand side at A10: =A4+A5-A3

    • Constraint left-hand side at A11: =A5+A3-A4

    • Constraint left-hand side at A12: =A3+A4+A5

  2. Open Finplicity, and then click Optimize > General Optimizer. The form of General Optimizer will appear, as shown below.

  3. In Step 1, select the Excel cell for the objective function. The selected cell must be a formula cell that computes the objective function. In this example, select E3.

  4. In Step 1, select “Maximize” if you want to maximize the objective function or “Minimize” if you want to minimize. In this example, select “Maximize.”

  5. In Step 1, select the Excel range for the variables. In this example, select A3:A5. Now you have finished the first step in this form as shown below, and you can click “Next” to continue.

  6. In Step 2, select the Excel range for the bounds of variables. In this example, select B3:B5 as the lower bounds and C3:C5 as the upper bounds. Now you have finished the second step in this form as shown below, and you can click “Next” to continue.

  7. In Step 3, add the constraints by selecting the left-hand side, the symbol, and the right-hand side, and then clicking the “Add Constraints” button. Two sides of constraints must have the same number of cells. In this example, select A9:A11, >=, C9:C11, click “Add Constraints,” select A12, =, C12, and click “Add Constraints.” Now you have finished the third step in this form as shown below, and you can click “Next” to continue.

  8. In Step 4, select the optimization method. Based on the optimization problem, the dropdown menu lists all available methods, with the most recommended one on top. In this example, select SLSQP. At this point, you can already click “Run” to run the optimization solver. Continue reading to learn about the callback workflow, a workflow that runs after each iteration during optimization.

  9. In this example, we use a callback workflow to record the history of the objective function during optimization. First, create a SQL+ task that appends the current objective function to the history.

    The key is the following SQL script:

    SELECT objective_history FROM objective_history
UNION ALL
SELECT objective AS objective_history FROM objective;

    Then, create a workflow of this SQL+ task in Task Workflow Manager.

    Finally, check “Callback workflow” and select the workflow.

  10. Now you have finished all the steps in this form, and you can click “Save” to save this task and click “Run” to run the optimization solver. Below is the optimization result. As you can see, the maximum area 3.897 can be achieved with an equilateral triangle with sides of length 3. The history shows how the objective function value converges to 3.897.

FAQ#

  • Is it necessary to provide initial values of variables?

No, but we strongly recommend doing so. Default/Bad Initial values may lead to a slow or even failed solving process. Try to provide initial values that satisfy the constraints and are close to optimal values if possible.

  • What if my optimization problem has no bounds/constraints?

Bounds and constraints are not required in General Optimizer, and you can directly click the “Next” button to skip the corresponding step. Consider using a reasonably large upper bound when the variable only has a lower bound, and vice versa.

  • What if I input wrong bounds/constraints by accident?

For bounds, re-select the area or click “Delete bounds.” For constraints, select the wrong constraints and click “Delete Selected Constraints.”

  • Which optimization solver should I choose?

The available solvers will be listed in the dropdown menu in Step 4. We recommend the default (first) solver in the list. Note that this list is generated based on whether the optimization problem is univariate, unbounded, unconstrained, etc., so the default choice is not necessarily the same across different problems.

  • Does General Optimizer guarantee to find a solution?

No, especially for large-scale optimization problems. If it takes too long or just fails, here are a few things to try. (1) Change a solver. (2) Relax some of the constraints. (3) Assign reasonable initial values to variables. (4) Reformulate the problem and simplify.

  • Why do I get an error “Unable to find solution”?

One of the most common reasons why the optimization failed is that the optimizer cannot even find a feasible solution that satisfies all constraints, not to mention optimizing. A simple example is requiring x <= 0 and x >= 1 at the same time. To fix this problem, try to relax some of the constraints (especially equality constraints). Here are a few other things to try. (1) Change a solver. (2) Assign reasonable initial values to variables. (3) Reformulate the problem and simplify.

  • What is the callback workflow?

It is a workflow that runs after each iteration during optimization.

On this page