Setting Up a Model

To set up an optimization model as a 1-2-3 spreadsheet, you will follow these essential steps:

1. Reserve a cell to hold the value of each decision variable.

2. Pick a cell to represent the objective function, and enter a formula that calculates the objective function value in this cell.

3. Pick other cells and use them to enter the formulas that calculate the left hand sides of the constraints.

4. The constraint right hand sides can be entered as numbers in other cells, or entered directly in the Solver's Add Constraint dialog box.

Within this overall structure, you have a great deal of flexibility in how you lay out and format the cells which represent variables and constraints, and which formulas and built-in functions you use. For example, the formulas needed for a linear programming problem can always be specified with the @SUMPRODUCT function.

Cells for decision variables, the objective function, and the left hand sides of constraints must be on the active worksheet. Constraint right hand sides which are simple cells or cell ranges must also be on the active worksheet. To use a cell on another worksheet, enter a formula referencing this cell on the right hand side of a constraint.