(Google Drive Solver Procedures are Available Separately)

2. Enter all data from the problem into cells. The format below is acceptable but not required (Excel doesn't care where you put things, but you do have to tell the Solver program where key elements are located). Notes:

- include separate cells for both the values and objective coefficients of the decision variables.
- objective coefficients are the numbers in the objective function
- 'values' represent the actual values of the decision variables. Initially, the "values" are normally set to zero. After solution, Excel will place the optimal decision variable values in the value cells.
- include one cell in which profit is computed from the decision variable values and objective coefficients (easiest with a sumproduct function--click here for explanation).
- include cells in which the total quantity used of each constraint is calculated, i.e., the constraint's left-hand side (again, easiest with a sumproduct).
- careful use of absolute cell references will allow you to enter
just the first formula, then copy it to the remaining rows--see Example
below.

- include cells that contain the right-hand side of each constraint.
- you may optionally include cells for constraint type (<=, >=, or =). Doing so is encouraged to make your spreadsheet more readable and to remind you of the constraint types, but these cells are not used by the solver.
- do not include non-negativity constraints on the spreadsheet (non-negativity is handled under Solver Options).

Max Z= 5 X1 + 8 X2

ST 2 X1 + 4 X2 <= 40

6 X1 + 3 X2 <= 42

X1 >= 3

X1, X2 >= 0You could set up a spreadsheet as follows, in which arrows indicate where example cell formulas go. Note: it is not necessary to put data in exactly this format (click here for example spreadsheet):

The following steps vary slightly with Excel version:

3. Start Solver:

Excel
2002/03: click
Tools at the top, then Solver.

Excel 2007/10/13/16: click the Data tab at the top, then click Solver in the Analysis section toward the top right (called "Analyze" in Excel 2016).

Excel 2007/10/13/16: click the Data tab at the top, then click Solver in the Analysis section toward the top right (called "Analyze" in Excel 2016).

4. Click in the "Set Target Cell" box for Excel 2002/03/07, or the "Set Objective" box for Excel 2010/13/16, then select the one cell containing the objective function formula. Also click the Max or Min button as applicable (linear programming does not use the "Value of" option).

5. Click in the "By Changing Cells" box for Excel 2002/03/07, or the "By Changing Variable Cells" box for Excel 2010/13/16, then select the decision variable value cells.

6. Click the Add button next to the "Subject to the Constraints" section, which brings up:

For each constraint:

- click in the "Cell Reference" box, then click the cell that contains the formula for the left-hand side total
- change the constraint type if necessary
- click the Constraint box and select the cell that contains the constraint right-hand side
- click Add to finish the constraint

In Excel 2010/13/16, it will look like this:

7. Important: two options must be set:

In Excel 2002/03/07: Click Options, then click to place checkmarks by "Assume Linear Model" and "Assume Non-Negative" as follows:

Leave the other options alone. Click OK to return to the Solver Parameters box, which should look the same as shown in step 6 above.

In Excel 2010/13/16: the necessary options are on the main Solver Parameters screen shown above:

- Check the box "Make Unconstrained Variables Non-Negative"
- Click the down arrow to the right of "Select a Solving Method" and change it from "GRG Nonlinear" to "Simplex LP".

See Tips below if you open a spreadsheet in an Excel version other than the one in which it was created.

8. Check the problem as entered. If everything appears correct, click Solve. If all is well, you will see "Solver found a solution." If solver says anything else, it did NOT find a solution--see Tips below.

9. After Solver finds a solution, click to select the Answer and/or Sensitivity reports in the Reports section as required for your assignment. Do not change any other settings. Click OK, and Solver will create new spreadsheet(s) containing the reports. Solver will also place the optimal decision variable values in the respective cells of the original sheet. Click on the tab at the bottom of the screen to view or print each report. (click here for solved example spreadsheet). The Answer Report for the above problem should look something like this in Excel 2016, with some variation in other Excel versions:

Possible bug: If you use Excel 2007, you may encounter a bug when you attempt to create the reports. If you get a solution but it won't create the reports, visit the following web page for a work-around:

http://faculty.sfasu.edu/fisherwarre/solver_workaround.html

Better yet, click here for instructions on how to get Office 365 for free!

Tips:

- with proper use of absolute cell references, you only have to
enter the first
constraint left-hand side formula. It can then be copied to the
other constraint rows. That is done in the above example.

- do not use the "Guess" button in Excel 2002/03/07 Solver Parameters. It almost never guesses correctly.
- if
you have two or more constraints in a row of the same type
(<=, for example), you can enter them at one time. To do so,
drag across both left-hand side total formulas for the Cell Reference,
and drag across both right-hand side cells for the Constraint
box. Although it looks like one constraint in Solver, Excel
treats each Cell Reference/Constraint cell pair as a separate
constraint.

- if you open a spreadsheet with linear programming in a version of Excel other than the version used to create the spreadsheet, re-check that the options are set correctly (see step 7 above).
- if the problem won't solve, re-check all the above steps including setting options(step
7). Still won't solve? In Dr. Fisher's classes, bring your
spreadsheet to him on a flash drive, or e-mail it to him. In
other classes, follow your professor's instructions.