Table 1 shows the spreadsheet model for example of optimal size and allocation of a factory. Table 2 displays the contents of cells AB6 through AF26, which contain the equations for the model. Information about a) objective, b) constraints, c) integers is put in the Dialog Box for Solver:

Target Cell AD8 Objective Minimise

Changing cells (decision variables) B6:AA6

Constraints AB10:AB13AD10:AD13

AB14:AB26AD14:AD26

U6:AA6 1 U6:AA6 integer

The first (B6:AA6  0) requires that all the decision variables be non-negative. The second expression

requires that the region needs be met (or exceeded).

The third

includes the balance constraints, the capacity constraints, and the only-one-factory-per-site constraints. The last two constraint sets relate only to the zero/one integer variables.

The constraint

restricts the values to be less than or equal to 1, and the last constraint requires that they be integer valued. Since they must be greater than or equal 0, and less than or equal 1, and integer, they must be either 0 or 1.

In addition, after clicking the options button, the box indicating linear model must be marked.

Table 1.

Table 2.