**Note:**This model illustrates some complex transformations, primary because the decision variables are not in a simple row or column range. Many real-world models will require only simple transformations, though they'll often have large numbers of variables and constraints.

#### Objective Function

The objective at cell H8 contains =SUM(B13:H13), where B13 through H13 contain formulas. We'll "expand" the objective, replacing each cell with its formula:B14*D6*B6 + C14*D6*B6 + D14*D6*B6 + B15*D7*B7 + E14*D6*B6 + F14*D6*B6 + G14*D6*B6 + E15*D7*B7 + B16*D8*B8

Although this looks complex, you can see that it is in "SUMPRODUCT form" -- a sum of terms where the first cell in each term is a decision variable, and the other cells are constant in the problem. Also, the term D6*B6 occurs six times in this formula, and the term D7*B7 occurs twice. So, we'll put these terms into cells as follows:

- A range named X (cells B26:G26) containing six cases of =D6*B6
- A cell named Y (B27) containing =D7*B7
- A cell named Z (B28) containing =D8*B8

Then we can write the objective function in cell B24 as:DOTPRODUCT((B14:G14, B15, E15, B16), (X, Y, Y, Z))This is in the correct form for Fast Problem Setup: A single call to the DOTPRODUCT function, where one argument range is all decision variables, and the other argument range is constant in the problem.

#### First Constraint

The constraint at B18 is the simplest one to modify, so let's consider it first. It is:

SUM(B11:B13) - SUMPRODUCT(B14:B16,D6:D8) - B17 >= 100000

With some algebra (adding B17 and subtracting the SUM from both sides), this is:

-SUMPRODUCT(B14:B16,D6:D8) >= 100000 + B17 - SUM(B11:B13)

Then, multiplying both sides by -1 yields:

SUMPRODUCT(B14:B16,D6:D8) <= -100000 - B17 + SUM(B11:B13)

We put the left hand side of this constraint in cell B20, and the right hand side in cell B22. Now, the ** left hand side is a single call to SUMPRODUCT**, where the first argument is all decision variables and the second argument is all constant cells. The right hand side is a formula, but ** all terms in this formula are constant in the problem** (no decision variable cells are involved). Hence, a constraint B20 <= B22 satisfies all the rules for Fast Problem Setup.

#### Further Constraints

The constraint at C18 is similar to the one at B18, but it involves the term SUM(C11:C13) instead of SUM(B11:B13). Cells B11:B13 are constants, but cells C11:C13 are **formulas that depend on the decision variables** -- so the algebraic transformation of this constraint will be more complex. Let's work through it: The constraint is

SUM(C11:C13) - SUMPRODUCT(C14:C16,D6:D8) - C17 >= 100000

Since the range C14:C16 includes two empty cells C15 and C16, the SUMPRODUCT can be replaced with just C14*D6. We'll **replace the SUM** with the contents of cells C11 through C13:

B18 + B14*D6 + B14*D6*B6 - C14*D6 - C17 >= 100000

Note that cells B6, D6 and C17 are all constants; B14 and C14 are decision variables; and B18 is the first constraint left hand side. We'll **replace B18** by its formula:

SUM(B11:B13) - SUMPRODUCT(B14:B16,D6:D8) - B17 + B14*D6 + B14*D6*B6 - C14*D6 - C17 >= 100000

The first term of the SUMPRODUCT is -B14*D6, which cancels the term +B14*D6, so we have:

SUM(B11:B13) - SUMPRODUCT(B15:B16,D7:D8) - B17 + B14*D6*B6 - C14*D6 - C17 >= 100000

Next, we subtract SUM(B11:B13) and add B17 and C17 (all constants) from both sides, then multiply both sides by -1, as we did for the first constraint:

SUMPRODUCT(B15:B16, D7:D8) - B14*D6*B6 + C14*D6 <= -100000 + SUM(B11:B13) - B17 - C17

-D6*B6 is a constant, but we need this value in a cell. So we'll calculate =-D6*B6 in cell B30 on the new worksheet, and **give this cell the name XX**.

Now we have the right form: On the left hand side, we have a SUMPRODUCT of C14, B14, B15, B16 -- all decision variables -- with D6, XX, D7 and D8 -- all constant coefficients. Since these cells are not in contiguous ranges, **we can use DOTPRODUCT** as follows:

DOTPRODUCT((B14:B16,C14),(XX,D7,D8,YY)) <= -100000 + SUM(B11:B13) - B17 - C17

We put the left hand side of this constraint in cell C20, and the right hand side in cell C22. In a similar manner, we can work out formulas for the rest of the constraints in the range B18:H18.

**< Return to Converting an Excel Model to Fast Setup Form**

**< Back to Support Center for Frontline Systems Excel Solvers**