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.

< Return to Converting an Excel Model to Fast Setup Form

< Back to Support Center for Frontline Systems Excel Solvers