Optimization Toolbox: Modeling guide

Complete worksheet modeling contract for variables, objectives, constraints, quadratic terms, nonlinear expressions, scenarios, and automatic problem classification.

← Optimization Toolbox overview · Getting started · Solver guide · Examples · Reference

Modeling philosophy

The Toolbox treats Excel as a declarative model surface. Instead of embedding a different Python program for every problem, the workbook stores normalized rows and the notebook compiles those rows into the numerical form required by the selected solver.

The central rule is simple:

Rows with the same Model value belong to one mathematical model.

The active model is selected on Start. Every other input sheet is filtered to enabled rows with that same ID before solving.

This resembles the intent of MATLAB’s problem-based optimization workflow—describe variables, objectives, and constraints first, then solve—but the representation is deliberately tabular for Excel users rather than symbolic MATLAB expressions.

Model anatomy

A model can include any supported combination of:

  • continuous, integer, or binary decision variables;
  • scalar parameters;
  • one or more explicit named objectives, each with its own sense;
  • ranged linear constraints;
  • quadratic objective terms;
  • safe nonlinear objectives;
  • safe nonlinear constraints;
  • residual expressions;
  • equation expressions;
  • named scenario overrides.

The enabled rows determine the detected problem class. The class is not selected manually.

Start sheet

Solve controls

Control Allowed values Meaning
Active model Model ID Selects rows on every model-definition sheet
Objective direction Auto, Minimize, Maximize Auto preserves the worksheet objective Sense; explicit override is single-objective only
Scenario Baseline or enabled scenario name Applies named overrides before compilation
Solver mode Recommended, Local, MultiStart, Global Selects solve strategy independently of problem class; unsupported class/strategy combinations fail validation
Random seed Integer Reproducibility seed for stochastic starts/global search
Global budget 30–2000 Search budget used to size browser-safe global search
Pareto points 3–31 Number of epsilon values attempted in the two-objective frontier

Model catalog

The Start sheet also contains a model catalog with:

Column Meaning
Model Machine ID used as the join key
Label Human-readable model name
Expected class Expected mathematical class; compiler compares it with detected structure and emits a diagnostic on mismatch
Preferred strategy Recommended, Local, MultiStart, or Global; used when Start → Solver mode is Recommended
Description Human-readable purpose

The compiler never trusts the catalog to determine mathematics. Expected class is a validation expectation, while Preferred strategy controls dispatch only after the model has been structurally compiled and classified.

Variables table

Location: Variables → Decision variables.

Column Required Meaning
Model Yes Model ID
Variable Yes Machine-safe variable name
Label Recommended Display label
Type Yes Continuous, Integer, or Binary
LowerBound No Blank means negative infinity; binary is forced to 0
UpperBound No Blank means positive infinity; binary is forced to 1
InitialValue Recommended for nonlinear models Starting point, clipped to bounds before local solving
Scale Recommended for nonlinear models Characteristic scale used for diagnostics; it does not currently rescale the solver internally
Group No Author metadata for organization
Enabled Yes Truthy values include True/Yes/1/Enabled/On

Variable naming

Valid examples:

  • units_a
  • x1
  • _reserve

Invalid examples:

  • Units A
  • 1st_shift
  • price-per-unit

Names must match ^[A-Za-z_][A-Za-z0-9_]*$ because nonlinear expressions resolve variables by identifier.

Variable types

The beta accepts only three types:

  • Continuous — real-valued decision variable.
  • Integer — integral decision variable for MILP.
  • Binary — integral decision forced to [0, 1].

Unsupported types fail validation. The Toolbox does not silently reinterpret an unknown type as continuous.

Bounds

For local LP/MILP/NLP work, bounds may be infinite. For the current bounded global-search workflow, every variable must have finite lower and upper bounds.

Parameters table

Location: Variables → Parameters.

Column Required Meaning
Model Yes Model ID
Parameter Yes Machine-safe name usable in expressions
Value Yes Numeric scalar
Units No Documentation only
Description No Documentation only

Parameters are useful when an expression should be readable and a business assumption should be editable without changing the expression text.

Example:

Model Parameter Value Units Description
pricing budget 100 $000 Total spend limit

Then an expression can use budget by name.

Objective definitions

Location: Linear Model → Objective definitions.

Column Required Meaning
Model Yes Model ID
Objective Yes Unique machine name referenced by linear, quadratic, and nonlinear objective terms
Label Recommended Human-readable objective label
Sense Yes Minimize or Maximize
Enabled Yes Include/exclude objective
Description No Author documentation

Every optimization objective must be defined here before any term table references it. This makes objective existence and direction independent of whether the objective happens to contain linear, quadratic, nonlinear, or mixed continuous terms. It also prevents a quadratic-only objective from disappearing during compilation.

Linear objectives

Location: Linear Model → Linear objective terms.

Column Required Meaning
Model Yes Model ID
Objective Yes Named objective
Variable Yes Existing decision variable
Coefficient Yes Linear coefficient
Enabled Yes Include/exclude row

Multiple rows with the same Objective are summed.

For example:

Model Objective Variable Coefficient Enabled
mix Profit chairs 45 True
mix Profit desks 80 True

represents:

\[Profit = 45\,chairs + 80\,desks\]

Objective terms must reference an enabled row in Objective definitions. If exactly one enabled objective exists, it is the primary objective. More than one enabled objective enters the Multiobjective validation path; the current epsilon-constraint adapter then requires exactly two linear objectives, continuous variables, and linear constraints.

Linear constraint definitions

Location: Linear Model → Constraint definitions.

Column Required Meaning
Model Yes Model ID
Constraint Yes Machine ID referenced by constraint terms
Label Recommended Display label
LowerBound No Lower activity bound
UpperBound No Upper activity bound
Group No Author metadata
Enabled Yes Include/exclude constraint

The ranged representation covers all common linear cases:

  • only UpperBound → activity <= upper;
  • only LowerBound → activity >= lower;
  • equal lower/upper → equality;
  • different finite lower/upper → ranged constraint.

Linear constraint terms

Location: Linear Model → Constraint terms.

Column Required Meaning
Model Yes Model ID
Constraint Yes Existing constraint ID
Variable Yes Existing decision variable
Coefficient Yes Matrix coefficient
Enabled Yes Include/exclude term

Rows are accumulated into the constraint matrix. A missing variable/constraint reference fails validation rather than being ignored.

Example:

Constraint Variable Coefficient
labor chairs 2
labor desks 4

with labor UpperBound = 240 produces:

\[2\,chairs + 4\,desks \le 240\]

Quadratic objective terms

Location: Quadratic & Conic → Quadratic objective terms.

Column Required Meaning
Model Yes Model ID
Objective Yes Existing/named objective
Variable1 Yes First variable
Variable2 Yes Second variable
Coefficient Yes Scalar coefficient
Enabled Yes Include/exclude term

A diagonal term contributes:

\[c\,x_i^2\]

A cross term contributes symmetrically as:

\[2c\,x_i x_j\]

This convention is used so one cross row represents both symmetric positions in the implied quadratic matrix.

The current backend is SLSQP. The Toolbox does not yet prove that an arbitrary authored quadratic matrix is positive semidefinite, so the result is treated as a local constrained optimization result.

The sheet name mentions conic modeling because SOCP is on the capability roadmap; there is no active cone-definition schema in the beta.

Nonlinear expression language

Location: Expressions.

The expression table supports four roles:

  • Objective
  • Constraint
  • Residual
  • Equation

Expression columns

Column Required Meaning
Model Yes Model ID
ExpressionId Yes Diagnostic identifier
Role Yes Objective, Constraint, Residual, or Equation
Objective Objective rows Named objective to which expression contributes
Expression Yes Restricted arithmetic expression
LowerBound Constraint/Equation as needed Lower permitted expression value
UpperBound Constraint/Equation as needed Upper permitted expression value
Weight Residual/objective optional Defaults to 1
Enabled Yes Include/exclude row

Supported operators

  • addition: +
  • subtraction: -
  • multiplication: *
  • division: /
  • exponentiation: **
  • unary plus/minus

Supported functions

  • exp(x)
  • log(x)
  • sqrt(x)
  • sin(x)
  • cos(x)
  • tanh(x)
  • abs(x)
  • minimum(x, y)
  • maximum(x, y)

Supported names

An expression name must be one of:

  • an enabled decision variable in the active model;
  • a parameter in the active model;
  • one of the whitelisted functions above.

Deliberately rejected syntax

The parser rejects:

  • attribute access such as obj.value;
  • indexing such as x[0];
  • imports;
  • comprehensions;
  • lambdas;
  • arbitrary function calls;
  • keyword arguments;
  • any unknown identifier.

The expression is compiled only after this AST validation and is evaluated with empty Python builtins.

Objective expressions

Example:

120*log(1+search) + 90*sqrt(social) + 60*log(1+email)

An Objective row contributes Weight * Expression to the named objective.

Nonlinear constraints

For a Constraint row, the expression is checked against the row’s lower/upper bounds.

For example, expression:

x*y

with LowerBound = 10 means:

\[x y \ge 10\]

The same bound is independently checked after the solver returns a candidate.

Residuals

For a residual value \(r_i(x)\) with weight \(w_i\), the workbench passes:

\[\sqrt{w_i} r_i(x)\]

to least_squares, so the minimized sum of squares is:

\[\sum_i w_i r_i(x)^2\]

Equations

Equation rows are residual functions expected to equal zero. The supplied equation fixture explicitly stores lower and upper bounds of 0 for readability, while acceptance is based on the residual norm returned by the equation workflow.

Scenarios

Location: Scenarios.

Column Required Meaning
Model Yes Model ID
Scenario Yes Scenario name selected on Start
TargetType Yes Parameter, VariableBound, ObjectiveCoefficient, or ConstraintBound
Target Yes Target identifier; objective coefficient uses Objective|Variable
Field Depends Bound/coefficient field name
Value Yes Replacement numeric value
Enabled Yes Include/exclude override

Parameter override

  • TargetType: Parameter
  • Target: parameter name
  • Value: replacement value

Variable-bound override

  • TargetType: VariableBound
  • Target: variable name
  • Field: LowerBound or UpperBound

Objective-coefficient override

  • TargetType: ObjectiveCoefficient
  • Target: Objective|Variable, for example Profit|desks
  • Field: conventionally Coefficient

Constraint-bound override

  • TargetType: ConstraintBound
  • Target: constraint ID
  • Field: LowerBound or UpperBound

Scenarios are applied to an in-memory copy of the base model. The base worksheet row is not overwritten.

Automatic problem classification

The compiler first validates the requested feature combination, then reports the mathematical class independently of solve strategy:

Condition Detected class
One or more Residual rows Least squares
One or more Equation rows Equations
More than one enabled objective Multiobjective
Any Integer/Binary variable MILP
Nonlinear objective or nonlinear constraint NLP
Any quadratic objective terms QP
Otherwise LP

The current adapters deliberately reject combinations they cannot represent faithfully. Least-squares and equation workflows support variable bounds but not general constraints or discrete variables. Multiobjective currently requires exactly two linear objectives with continuous variables and linear constraints. Integer models must remain linear. A QP may contain linear constraints, but the beta does not certify convexity.

Global is not a detected problem class. It is a solve strategy that can be requested for a continuous NLP with finite variable bounds and, in the current beta, no general linear or nonlinear constraints. MultiStart is likewise a strategy rather than a mathematical class.

Objective direction

The numeric solvers are mostly minimizers. Each row in Objective definitions therefore stores the natural Sense, and the compiler applies an internal sign when a maximization objective is passed to a minimizer.

Start → Objective direction = Auto preserves the objective’s worksheet Sense. Minimize or Maximize can override that sense for a single-objective model. Multiobjective models always use the per-objective senses and reject a Start-level direction override.

The displayed objective is always converted back to its natural direction.

Build a new linear model: worked schema

Suppose you want a small transportation allocation with variables north and south.

1. Add catalog row

Model: ship_plan
Label: Shipping Plan
Expected class: LP
Preferred strategy: Recommended
Description: Allocate shipments at minimum cost.

2. Add variables

Model Variable Label Type Lower Upper Initial Scale Group Enabled
ship_plan north North shipments Continuous 0 100 50 50 Routes True
ship_plan south South shipments Continuous 0 100 50 50 Routes True

3. Define the objective

Model Objective Label Sense Enabled Description
ship_plan Cost Shipping cost Minimize True Total route cost

4. Add objective terms

Model Objective Variable Coefficient Enabled
ship_plan Cost north 12 True
ship_plan Cost south 9 True

5. Add demand constraint

Definition:

Model Constraint Label LowerBound UpperBound Group Enabled
ship_plan demand Total demand 120 Service True

Terms:

Model Constraint Variable Coefficient Enabled
ship_plan demand north 1 True
ship_plan demand south 1 True

5. Select and solve

Set:

  • Active model = ship_plan
  • Objective direction = Minimize
  • Scenario = Baseline

Then review Diagnostics first. If the model is structurally valid, the classifier should report LP and dispatch to HiGHS.

Author checklist

Before treating a new model as operational:

  • Confirm every row uses the intended Model ID.
  • Set objective direction explicitly.
  • Validate all variable types and bounds.
  • Confirm every objective/constraint term references an existing variable.
  • Prefer bounds over redundant one-variable constraints.
  • Give nonlinear variables realistic initial values and scales.
  • Use finite global bounds only when they are defensible business/design limits.
  • Add an infeasible or stressed case when contradictory business rules are plausible.
  • Compare returned decisions with an independently understood small case.
  • Record named scenarios instead of overwriting the baseline for recurring analyses.

Unsupported modeling patterns in the beta

Do not model these as though they were already supported:

  • second-order cone constraints;
  • semi-infinite constraints;
  • general nonlinear multiobjective optimization;
  • mixed-integer nonlinear optimization;
  • arbitrary Python callbacks from worksheet cells;
  • vector/array expression syntax;
  • user-defined functions inside the expression language;
  • automatic symbolic or automatic differentiation.

Use the Solver guide to understand why those boundaries matter.