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

AB10:AB13AD10:AD13

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

The third

AB14:AB26AD14:AD26

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

U6:AA6 1

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.