Columbia University in the City of New York



Introduction to Solver

This document is intended to introduce the use of Excel Solver to solve linear programming (LP) problems. We will use the example of Malcolm’s glass molding operation (see the Word files malcolm.doc, smalcolm.doc, and the Excel file malcolm.xls). The sample Excel screens shown here are from Excel 5.0; more recent versions of Excel have similar features.

|Maximize: | | | | | | | |

| |Z |= |500X1 |+ |450X2 | | |

| | | | | | | | |

|Subject to: | | | | | | | |

| | | |6 X1 |+ |5 X2 |( |60 |

| | | |1 X1 |+ |0 X2 |( |8 |

| | | |10 X1 |+ |20 X2 |( |150 |

| | | |1 X1 |+ |0 X2 |( |0 |

| | | |0 X1 |+ |1 X2 |( |0 |

The Excel file malcolm.xls shows how to set up the problem for solution with Solver. Figure 1 shows the LP Formulation worksheet from malcolm.xls.

When you set up a problem for Solver, you follow four steps:

1. Create a spreadsheet with all the information from the LP problem. In the example (Figure 1) you can see the LP information in the first 10 rows.

2. Specify the target cell (which contains the formula for the objective function) that you want to minimize, maximize, or set to a certain value. In MALCOLM.XLS, the target cell is F2, which contains the formula =+B4*B2+C4*C2.

3. Specify the changing cells (called decision variables in LP lexicon) that you want to be adjusted until a solution is found. You can specify up to 200 changing cells, but for this problem we only need 2.

The number of cases of 6-oz. glasses to be produced appears in cell B2 (in hundreds of cases), and the number of cases of 10-oz. glasses to be produced appears in cell C2. It doesn’t matter what exact values you put into these cells, because Solver will change them anyway, but you do need to put some feasible starting values in the cells. In this example the starting values are set to 1.

[pic]

Figure 1 LP Formulation worksheet

4. Specify constraint cells that must fall within certain limits or satisfy target values. You can specify up to 500 constraints, 2 for each changing cell plus 100 additional constraints, representing a total of no more than 1000 cells in a problem. In this example there are five constraints.

In the example, the constraint cells are D13 through D17. Each of these cells contains a formula which refers to the constraint matrix from B6 to D10. For example, cell D13 contains the formula =+B6*B2+C6*C2, cell D14 contains the formula =+B7*B2+C7*C2, and so forth.

After setting up the problem, select Tools - Solver to open up the Solver Parameters dialog box (Figure 2).

[pic]

Figure 2 Blank Solver Parameters dialog box

In this dialog box, you specify (1) the target cell containing the formula for the objective function (F2 in the example); (2) the range of cells containing the decision variables, which Solver calls changing cells (B2.C2 in the example); (3) a set of constraints, clicking on the Add button to use the Add Constraint dialog box (Figure 3). Don’t forget to choose the appropriate Equal to radio button for the problem; in this case we want to maximize profit so we select Max.

[pic]

Figure 3 Add Constraint dialog box

The completed Solver Parameters dialog box for the example problem appears as Figure 4.

[pic]

Figure 4 Completed Solver Parameters dialog box

After setting up the problem, click on the Solve button and wait for Solver to find a solution. You can create three types of reports that summarize the results of a successful solution process. For the purposes of this course, we are interested in the Answer Report (Figure 5) and the Sensitivity Report (Figure 6).

[pic]

Figure 5 Answer Report

In the Answer Report (Figure 5) we see several important facts about the Solver solution: (1) the solution will yield a profit of $5,142.85; (2) the solution calls for Malcolm to produce 642 cases of 6-oz glasses and 428 cases of 10-oz glasses; (3) the binding constraints (the constraints which prevent Malcolm from increasing his profit) are the limits on molding capacity and storage space. This is important information because it quickly tells Malcolm that there is no point in trying to increase sales of 6-oz glasses.

From the information in the Sensitivity Report (Figure 6), we can make a number of inferences about the Solver solution, most of which are beyond the scope of this course. However, you should pay attention to the column called Shadow Price in the Constraints table. There is a shadow price associated with each of the constraints, as you can see in the table.

The shadow price can be interpreted as the amount that the objective function would improve for every unit of relaxation in the constraint. For example, the shadow price associated with the Storage Space constraint is $2.86. This can be interpreted to mean that Malcolm can increase his profit if he can acquire additional storage space at less than $2.86 per unit. You may recall that storage space was defined in hundredths in our formulation, so Malcolm should be prepared to spend up to $.0286 per cubic foot per week for additional storage space.

[pic]

Figure 6 Sensitivity Report

Notes on using Solver:

(1) Notice that the Solver solution suggests that Malcolm produce only 642 cases of 6-oz glasses. Remember that Malcolm only has one customer for this product and that she will buy no more than 800 cases.

This is an example of an LP formulation that may be leaving out important information. It may be that this customer needs more than 642 cases and will abandon Malcolm as a supplier unless he can produce all the 6-oz glasses she needs. It would be possible to reformulate the problem with a new constraint, setting some minimum quantity of 6-oz glasses, but many of the nuances of management cannot be captured in a mathematical program.

Malcolm may want to call his customer before implementing the solution and find out whether or not 642 cases will be satisfactory to her. He might decide to implement a sub-optimal plan, forgoing some profit this week in order to keep a loyal customer. The point here is that the results of any mathematical model ought to be scrutinized with common sense before being implemented.

(2) Solver is a relatively new optimization tool, and does not yet completely replace older and more powerful software (e. g. Lindo, Storm, AMPL). In this class we are using Solver for its accessibility, as a vehicle to introduce basic concepts of linear optimization with a familiar spreadsheet package. To solve more complicated problems, you will probably want to take a course in mathematical programming or solicit the help of someone who has.

(3) Some users have reported computational problems with Solver. There are some problems, for example, for which Solver comes up with different answers depending on the initial values of the changing cells. One way to help avoid problems with this is to solve your problem several times using different sets of initial changing cell values.

(4) When selecting reports for Solver to generate, hold down the Shift key to select more than one type of report at a time.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download