You can also dowload the three files, CHE478E1.XLS, CHE478E2.XLS, and CHE478E3.XLS (compressed self-extracting archive CHEXLS.EXE) from the "Donwload" section (the same example problems used in your on-line GAMS tutorial). This tutorial is based on the file CHE478E3.XLS.

   minimize  f(X) = 2*X1 - X2
     s.t.   h1(X) =   X1 - 2*X2 -  2 = 0
            g1(X) =   X1 +   X2 -  2 > 0
            g2(X) = 2*X1 + 3*X2 - 12 < 0
                  0 < X1 < 10
                  0 < X2 < infinity

   f(X)  : objective function (OBJ)
   X1,X2 : decision variables
   h1    : equality constraint
   g1    : first inequality constraint
   g2    : second inequality constraint

EXCEL WORKSHEET:

Examine the worksheet shown in Fig.1 below. Only the cells with cyan-colored background contain formulas; other cells are all text enteries used to facilitate readibility of the worksheet.

• Cell B20 contains the formula of the objective function: =(2*B15-B1),
• Cell B23 contains the formula of the equality constraint: =B15-2*B16-2,
• Cell B26 contains the formula of the first inequality constraint: =B15+B16-2,
• Cell B29 contains the formula of the second inequality constraint: =2*B15+3*B16-12.

Now, we will call the Solver to find the values of the decision variables that give minimum value to the objective function and, at the same time, satisfy the three constraints.

FIGURE-1:

HOW TO CALL & ENTER THE SOLVER PARAMETERS:

• To activate the SOLVER, select Solver from the Tools menu with mouse. A dialog box will appear as shown in Fig.2 below.

FIGURE-2:

• Set Target Cell: Highlight the target-cell box by selecting the cell contents with mouse, then click on the cell B20 in the worksheet (i.e., cell to the right of "F ="; the objective function). $B$20 should appear in the target-cell box. This target-cell box contains your objective function.
• Equal to: Select the "Min" option for minimization. The "Value of" option is generally used to solve a single algebraic equation (or set of equations, in which case the residual norm of the right-hand sides of the set of functions in the form f(x)=0 is set to zero). See the ChE_110 Home Page for applications of Excel`s Solver in solution of linear/nonlinear set of mass-balance equations.
• By Changing Cells: Highlight this box by selecting the cell contents with mouse, then select the cells B15 and B16 in the worksheet with mouse (i.e., the cell to the right of "x1=" and "x2="). $B$15:$B$16 should appear in the box. This box identifies the location of the decision variable (or the vector of decision variables) in the worksheet.
• Subject to Constraints: Click on the "Add..." button. A new dialog box will appear as shown in Fig.3 below.

FIGURE-3:

• Cell Reference (left box): This cell should contain the cell reference corresponding to the variable (e.g., side constraints --lower/upper bounds--) or function (e.g., equality/inequality constraint) which is constrained. Make sure that the cursor is in this box, then, click on the cell B23 in the worksheet to enter the equality constraint. $B$23 should appear in the left box.
• Constraint (middle box): Select the constraint type as "=" for the equality constraint (options are: equal, less than or equal, greater than or equal, integer).
• Constraint (right box): This cell should contain the right-hand-side value of the constraint (or, the cell reference that contains the value of the right-hand side). Since we are entering the equality constraint in the standard form as h1(x)=x1-2*x2-2=0 enter 0 (zero).
• When you complete the fields, click on the "Add" button. Then you are ready to repeat the steps to enter the next constraint. For our problem, next enter the first inequality constraint (left box: B26, middle box: =>, right box: 0), second inequality constraint (left box: B29, middle box: <=, right box: 0), lower bound on decision variable X1 (left box: B15, middle box: =>, right box: 0), lower bound on decision variable X2 (left box: B16, middle box: =>, right box: 0), and uper bound on decision variable X1 (left box: B15, middle box: <=, right box: 10). When you entered the last constraint, this time click on the "OK" button. You will return automatically to the first dialog pane of the Solver.
• Options: To modify the Solver`s Options used in its numerical algorithms, click the "Options" button; a dialog box will appear as shown in Fig.4 below. The selected options in the figure are for general nonlinear optimization problems (NLPs). For our example problem which is a linear programing problem (LP), it is best to check the "Assume Linear Model". Click on the "Help" button to learn more on each option`s functionality. Do not check the box "Assume Linear Model" if your objective function and/or constraint functions are not linear ! To see the results of the major iterations on the worksheet, check the box "Show iteration results". Click on the "OK" button to return to main Solver menu (Fig.2).

FIGURE-4:

CLOSING REMARKS:

When you complete everthing, the "Solver Parameters" dialog pane should look like Fig.5 given below (for our example LP problem).

FIGURE-5:

Click on the "Solve" button. If you have checked the box "Show Iteration Results", you will be prompted with "Continue" dialog box after each major iteration. You may see the progress of optimization by looking at the worksheet and the bottom of the window.

When you come to "Solver Results" dialog pane, please read its Help (see Fig.6 below). By default "Keep Solver Solution" box will be checked. If you press the "OK" button, your worksheet variables will be replaced by their values at the optimum solution found by the Solver.

Remember that, in Fig.1, the cells B15 and B16 contained the initial gusses of the decision variables. Now, as you see in Fig.6 below, the cells B15 and B16 contain the optimal values of the decision variables with which the objective function is minimized and the constraints are satisfied.

Double click on "Answer" to see an Answer Report (see the sheet tabs at the bottom of the worksheet).

After the final iteration your worksheet will look like Fig.6 given below.

FIGURE-6:

Check the optimal solution suggested by the Solver!
Are all the constraints satisfied?