The QA 525 (Quantitative Approaches for Decision Making)

Using Excel^Ò 2000 Solver

The Solver option in EXCEL 2000 (and earlier versions) may be used to solve linear and nonlinear optimization problems.  Integer restrictions may be placed on the decision variables.  Solver may be used to solve problems with up to 200 decision variables, 100 explicit constraints and 400 simple constraints (lower and upper bounds and/or integer restrictions on the decision variables).  

To use Solver, first make sure that it is installed. To do so select Tools from the main menu. If the option Solver appears, then Solver is already installed and you are ready to proceed. If the option Solver does not appear, then you must install Solver (no big deal - this is very easy to do). To install Solver select Add-Ins from the Tools menu, 

then scroll through the Add-Ins dialog box until you find the Solver option.

Click on this option (a check-mark should appear in the corresponding box) and then click on OK. After a brief period the installation will be complete.

Now you can invoke Solver by selecting Tools from the main menu and then selecting Solver.  The Solver Parameters dialog box will appear as shown below.

            When the Add button is clicked, the Add Constraint dialog box appears:

Clicking on the Cell Reference Box allows you to specify a cell location (usually a cell with a formula).  The constraint type may be set by selecting the down arrow (<=, >=, =, int, where int refers to integer, or bin, where bin refers to binary).  The Constraint box may contain a formula of cells, a simple cell reference, or a numerical value.  The Add button adds the currently specified constraint to the existing model and returns to the Add Constraint dialog box.  The OK button adds the current constraint to the model and returns you to the Solver Dialog box.  Note:  Solver does not assume nonnegativity of the decision variables.  The options dialog box discussed below allows you to specify that the variables must be nonnegative.

            If the Options button is selected from the Solver Parameters dialog box, the following

dialog box appears:

Max Time allows you to set the number of seconds before Solver will stop.  Iterations, similar to Max Time, allows you to specify the maximum number of iterations (steps of the solver algorithm) before stopping.  Precision is the degree of accuracy of the solver algorithm (for example, how close does the value of the left-hand side of a constraint have to be before it is considered equal to the right-hand side).  Tolerance is used for integer programs.  It specifies a percentage within which the solution is guaranteed to be optimal.  If you seek the optimal solution, this must be set to zero.  If run time becomes too long, you may wish to set this to a higher value (if you are willing to accept a solution within this percent of optimality).

If your model is a linear program or a linear integer program, you should check Assume Linear Model.  This tells Solver to use the simplex algorithm rather than a more time consuming nonlinear algorithm (Generalized Reduced Gradient Method).  Assume Non-Negative should be checked if you want all of your changing cell values to be ³ 0.  Check Show Iterations Results if you want to see information iteration by iteration (this can really slow things down!).  Use Automatic Scaling is useful if your model is poorly scaled (if the inputs are of drastically different orders of magnitude).  Finally, the bottom section of the dialog box concerns options for the nonlinear algorithm, namely, how it estimates nonlinearities,  how rates of change are estimated and the type of search technique employed.

Generally speaking, the default values of most of these parameters work fine.  The important thing to remember is to check Assume Linear Model if you have a linear program or integer linear program.  Check Assume Non-Negativity if you want the changing cells to take on only non-negative values.  Also, if you are solving an integer program and seek the optimal solution, be sure to set the Tolerance to 0%.

An Example:  The Television Production Problem (as discussed in class)

A local manufacturer makes 19’ black & white and color television sets. It takes 6 hours to fabricate the components and 2 hours to assemble a black & white sets. On the other hand, color sets require 2 hours to fabricate the components and 4 hours to assemble. Only 1800 fabrication hours and 1600 assembly hours are available during any time period. Additionally, the firm has only 350 color picture tubes available, and they have a prior order for 75 color televisions that they need to fill. Color televisions can be produced for $242 and sold for $250, while black & white sets can be produced for $147 and sold for $150. Obviously this organization would like to determine how many of each product (black & white and color television sets) they should make in order to maximize profit.

An appropriate linear programming model of this problem follows:

Let x₁ be the number of black & white sets produced

      x₂ be the number of color sets produced

Our formulation is

Maximize	3x1	+	8x2
Subject to	2x1	+	4x2	£	1600	(# of assembly hours available)
	6x1	+	2x2	£	1800	(# of processing hours available)
			x2	£	350	(# of color tubes available)
			x2	³	75	(minimum # of color sets to produce)
	x1	,	x2	³	0	(nonnegativity)

We must first input this problem into an Excel Worksheet in a format that Solver can understand.

1. Select select and label the By Changing Variable Cells (cells that will contain the values of individual decision variables).

2. Select and label the Set Cell (cell that will contain the Objective Function as a cell function of the By Changing Variable Cells). In the Set Cell  write the Objective Function as a cell function of the By Changing Variable Cells.

3. Select and label the Subject to the Constraint Cells (cells that will contain the left hand sides of the constraints as a cell functions of the By Changing Variable Cells) and the corresponding Right-Hand Side Cells. In the Subject to the Constraint cells write the left-hand sides of the constraints as cell functions of the By Changing Variable Cells. In the Right-Hand Side Cells write the right-hand side values of the constraints.

The resulting worksheet could look like this:

Note that I said the resulting worksheet could look like this - one of the advantages of using spreadsheets for these models is the incredible flexibility they afford the user. You can literally locate the cells containing various model parameters and decision variables anywhere on the worksheet (or even on another worksheet in the same file)! Thus we can build optimization models that look like balance statements, income sheets, financial analyses, production schedules, marketing reports, etc. 

Now we can actually load the model into Solver. Once we invoke Solver, we are presented with the Solver Parameters Dialog Box. Simply 

1. Enter the cell location of the Set Cell in the Set Cell box and Select Max or Min to indicate whether you wish to solve a maximization or minimization problem.

2. Enter the cell locations of the Changing Cells in the By Changing Variable Cells box.

2. Click on the Add box adjacent to the Subject to the Constraint Cells to bring up the Add Constraint dialogue box.

• In the Cell Reference box enter the cell address of the Subject to the Constraint Cell that contains the left-hand side for the first constraint;

• Use the button in the middle of the Add Constraint dialogue box to indicate if you want this to be a £, ³ or an = constraint. You can also use this to indicate that you want to restrict a variable (whose By Changing Variable Cell has been entered into the Cell Reference box) to integer (int) or binary (bin) values.

• In the Constraint box write the cell address of  the right-hand side value for this constraint.

Use the Add button to continue adding constraints in this fashion and the OK button to indicate you have entered all constraints (this will return you to the Solver Parameters Dialog Box). The following Add Constraint dialogue box demonstrates entry of the first (assembly hours) constraint for the Television Production Problem discussed in class.

3. At the Solver Parameters Dialog Box select Options to bring up the Solver Options Box. Click on Assume Non-Negative to automatically add nonnegativity constraints for all decision variables. Click on Recognize Linear Vars to indicate this is a Linear Programming Problem. Then click on the OK button to indicate you have entered all options (this will return you to the Solver Parameters Dialog Box). The following Solver Options Box demonstrates selections of these options (as well as the defaults for many other options).

4. Upon returning to the Solver Parameters Dialog Box) (shown below for the entire Television Production Problem discussed in class)

click on the Solve button to initiate the solution algorithm (in this case a form of Simplex). Solver will then present you with a status box called the Solver Results Box. For the Television Production Problem (which is feasible) the Solver Results Box will look like this

click on Keep Solver Solution and then on the OK button to retain the solution to your problem in the By Changing Variable Cells. Keep in mind that we will discuss the available reports soon.

An EXCEL spreadsheet model of this scenario can be downloaded by clicking here:

Additional instructions are forthcoming.

For other Solver tutorials try:

Frontline Systems' Solver Tutorial (from the distributors of Solver)

or

University of British Columbia's MBA Program Online Tutorial

Return to the QA 233 Virtual Classroom