Using Solver

Rather than the somewhat tedious and error-prone graphical method (which is limited to linear programs with 2 variables), special computer programs can be used to find solutions to linear programming models. The most widespread program is undoubted Solver, included in all recent versions of the Excel spreadsheet program. Solver, while not a state of the art code (which can cost upwards of $15,000 per copy) is a reasonably robust, easy-to-use tool for linear programming. Solver uses standard spreadsheets together with an interface to define variables, objective, and constraints to define a linear program.

It is difficult to describe in words how to create a Solver spreadsheet, so we will do one or two in class. Here is a brief outline and some hints and shortcuts:

• We will start with a spreadsheet that has all of the data entered in some reasonably neat way.
• We will create the model in a separate part of the spreadsheet. We will have one cell for each variable. Solver will eventually put the optimal values in each cell.
• We will have a single cell to represent the objective. We will enter a formula that represents the objective. This formula must be a linear formula, so it must be of the form: cell1*cell1'+cell2*cell2'+..., where cell1, cell2 and so on contain constant values and cell1', cell2' and so are the variable cells.

  Helpful Hint: Excel has a function sumproduct() that is designed for linear programs. sumproduct(a1..a10,b1..b10) is identical to a1*b1+a2*b2+a3*b3+...+a10*b10. This function will save much time and aggravation. All that is needed is that the length of the first range is the same as the length of the second range (so one can be horizontal and the other vertical).

  Helpful Hint: It is possible to assign names to cells and ranges (under the Insert-Name menu). Rather than use a1..a10 as the variables, you can name that range var (for example) and then use var wherever a1..a10 would have been used.

• We then have a cell to represent the left hand side of each constraint (again a linear function) and another cell to represent the right hand side (a constant).
• We then select Solver under the Tools menu. This gives a form to fill out to define the linear program.
• In the ``Set Cell'' box, select the objective cell. Choose Maximize or Minimize.
• In the ``By Changing Cells'', put in the range containing the variable cells.
• We next add the constraints. Press the ``Add...'' button to add constraints. The dialog box has three parts for the left hand side, the type of constraint, and the right hand side. Put the cell references for a constraint in the form, choose the right type, and press ``Add''. Continue until all constraints are added. On the final constraint, press ``OK''.
• We need to explicitly include nonnegativity constraints.

  Helpful Hint: It is possible to include ranges of constraints, as long as they all have the same type. c1..e1 <= c3..e3 means c1 <= c3, d1 <= d3, e1 <= e3. a1..a10 >= 0 means each individual cell must be greater than or equal to 0.

• Push the options button and toggle the ``Assume Linear Model'' in the resulting dialog box. This tells Excel to call a linear rather than a nonlinear pgrogramming routine so as to solve the problem more efficiently. This also gives you sensitivity ranges, which are not available for nonlinear models.
• Push the Solve button. In the resulting dialog box, select ``Answer'' and ``Sensitivity''. This will put the answer and sensitivity analysis in two new sheets. Ask Excel to ``Keep Solver values'', and your worksheet will be updated so that the optimal values are in the variable cells.