In this example, we are converting the Working Capital Management example, a linear programming model, on the Finance worksheet to an equivalent model in Fast Problem Setup form, on the Finance Fast Setup worksheet.  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.