# Excel Solver Handout

• Doc File533.00KByte

• ### Variable row reference in excel

• Doc File 533.00KByte

﻿

| |

| |

|Excel Solver |

CSOM OIT Helpdesk

|CSOM OIT Helpdesk |

|Location |L-119 |

|Phone |612-625-5550 |

|E-mail |help@csom.umn.edu |

|Hours |M-TH 7:30 am – 9:00 pm |

| |F 8:00 am – 4:30 pm |

OIT Helpdesk services include:

• Software Support – Get help

on all OIT supported software

• Borrowing Equipment – Check out notebook computers, LCD projectors, Zip drives, and external CD-ROM players

Excel Solver 1

Creating a Model 1

Using Solver 2

Solver Result Options 4

Naming Cells 4

Linear Models 5

Example – CampMidwest 6

Modifying a Constraint 8

Creating Reports 9

Example – Coffee 13

Excel Solver

Solver is an Add-in of Excel that can be used to find the best solution, such as allocate scarce resources, maximizing profits, or minimizing costs. Using Solver, you can find an optimal value for a formula in the “target” cell of a worksheet. Solver uses a group of cells that are related to a formula in the target cell. It adjusts the values of cells you specify (adjustable cells) to produce a result you specify from the target cell formula. You can apply constraints to restrict the values used.

Solver is an “add-in” of Excel and may not be available with a standard Excel installation. If not, select Tools, Add-Ins, select Solver Add-in, and click OK.

Creating a Model

The first step, upon analyzing your data, is to set up a solver-friendly worksheet or model. You should create a “target” cell which is the goal of your problem. You also need one or more variable cells that solver can change to achieve the goal. In addition, there may be other formulas that use the target cell and the variable cells. For training purposes, several models have been created for you.

Tip: To view existing formulas on these worksheets, press Ctrl + ~. To return to the formula results, press Ctrl + ~ again.

Examples used in class are at: If you wish to practice or review, open this file and save to your computer.

Note: In addition to the worksheets used during this training, most computers will have sample files installed with MS Office. The typical location is: C:\Program Files\Microsoft Office\Office XP\Office10\Samples. The solver parameters have been entered, but solver has not been run to find the solution. These may assist you in setting up a model or understanding the application.

Using Solver

In this class we will use these basic instructions to create solutions for different examples. (Use Electronics worksheet.)

Follow the steps below to use Solver.

1. Create the worksheet for which you need a solution.

2. Select Tools, Solver. The Solver Parameters dialog box displays.

[pic]

3. In the Target Cell: field, type (or select) the cell for which you want to find the optimal value.

Hint: To collapse the dialog box, click [pic], select the cells, then click [pic] again.

4. In the Equal To: field, select the desired option. This may be minimum, maximum, or a specific value. This is the goal you want Solver to achieve.

5. In the By Changing Cells: field, select (or type) the cells you want to change to achieve your goal.

Hint: To select non-contiguous cells, press and hold Ctrl while clicking the desired cells.

Note: You can click Guess. Solver will propose the appropriate changing cells based on your target cell. However, results may not be reliable.

[pic]

7. In the Cell Reference: field, specify the desired constraint cell(s).

8. Select a comparative operator from the drop-down list in the middle of the dialog box.

9. In the Constraint: field, type a number or enter a cell to add your constraint. Note: If you select an Integer (int) constraint, the solution may take longer. The solution is rounded “down.” You cannot, for instance, create a partial television, so you might select Integer as a constraint.

10. If you want to add additional constraints, click Add and complete steps 7-9 for each constraint.

11. When finished adding constraints, click OK. You are returned to the Solver Parameters dialog box. The constraints display in alphabetical order.

[pic]

12. Click Solve. Solver plugs trial values into your variable cells, recalculates the worksheets, and finds a solution to meet your objective and to satisfy your constraints. Your worksheet displays the results, and the Solver Results dialog box displays.

[pic]

13. Select the desired option(s) and click OK. Options are described below.

Solver Result Options

You have multiple options available from the Solver Results dialog box.

• Keep Solver Solution displays the results in the cells you specified. You cannot “undo” your solution.

• Restore Original Values restores the values that your variables held before activating Solver. (You can also click Cancel.) However, the solver parameters remain in the Solver Parameters dialog box if you save the file.

• Save Scenario allows you to save a scenario. Scenarios are covered in more detail in the Excel Advanced class. If you save a scenario, you are required to name it. This is a useful option when working with Solver. If you save as a scenario, click Cancel to return to the original values. You can view the saved scenario (or solution) by selecting Tools, Scenarios, selecting the desired scenario, and clicking Show.

• Reports can create a variety of reports. These will be covered in more detail later in this class. See page 9.

Naming Cells

If you assign a name to a target cell, Solver uses that name in its reports. If you don’t name the cell, Solver creates a name based upon the column and/or row headings.

To name a cell or range of cells, select the cell(s) you want to name, then click in the Name box (left of the Formula bar), type the desired name, and press Enter. Names cannot contain spaces.

To edit named cell ranges, select Insert, Name, Define, to view the Name Define dialog box.

[pic]

Linear Models

You can set Solver to assume a linear model option. This can be activated only for models in which all the relationships are linear; models that use simple addition and subtraction and worksheet functions such as SUM are linear in nature. This information is provided in the event your class assignment involves linear programming. If you select a linear option, you will have additional fields in your Sensitivity report. See page 9 for more information on reports.

1. Select Tools, Solver. The Solver Parameters dialog box displays.

[pic]

2. Enter the appropriate cells and constraints.

3. Click Options. The Solver Options dialog box displays.

[pic]

4. Select Assume Linear Model and click OK.

Note: You can set other options here, if desired. Consult Help for more information. Default settings are usually sufficient for most problems.

Example – CampMidwest

Your company, CampMidwest, has inventory that includes items used to assemble camping kits. These kits consist of two types: standard or group. We will use Solver to determine the number of each kind of kit that can be assembled from existing inventory while maximizing profit. The constraints are that the numbers used to assemble cannot exceed the inventory, and we must have a minimum of 3 group kits.

For this exercise use the following file: Solver Exercises.xls, worksheet CampMidwest.

[pic]

1. Select Tools, Solver. The Solver Parameters dialog box displays.

[pic]

2. In the Target Cell: field, enter E15. These are the total profits. Our goal is to maximize our total profits from the inventory available.

3. In the Equal To: field, select Maximum.

4. In the By Changing Cells: field, enter C4:D4. We want to determine how many kits we can assemble with the inventory available.

[pic]

6. In the Cell Reference: field, enter E6:E12 to specify the number of inventory items used.

7. Select =3. This is to ensure that we have a minimum of 3 group kits assembled.

11. Click Add to add an integer constraint. We can’t sell a partial camping kit.

12. Click OK. You are returned to the Solve Parameters dialog box. The constraints display in alphabetical order.

[pic]

13. Click Solve. Your result should display as follows.

[pic]

14. If your results are the same as those above, click OK. If not, make changes and click Solve again.

Modifying a Constraint

After using Solver in a worksheet, Excel retains the settings in the Solver Parameters dialog box. Follow these steps if you need to modify these settings.

1. Select, Tools, Solver. The Solver Parameters dialog box displays.

[pic]

2. In the Subject to the Constraints: area, select the constraint you want to modify, and click Change. The Change Constraint dialog box displays.

[pic]

3. Modify as necessary, and click OK. (Using the CampMidwest worksheet, modify the constraint to >=6).

Note: If you have created a report (see below) before modifying a constraint, the report will not reflect your recent change. You may want to create another report.

Creating Reports

You can create three types of reports using Solver. Each provides information on how the solution was reached. Each report is created on a separate worksheet.

Note: You can create a report at any time. However, if you want the original values to display in your Answer report, first enter the solver cells and constraints, click Solve, and create your report at that time. If you create a report after the solution has been displayed on your worksheet, the Answer report will only display the solved values in the original values field.

• The Answer report lists target cell results, the values of the cells that are changed, and the constraints. It also includes information about the value of each constraint. Status can be binding, not binding, or not satisfied. The slack value is the difference between the solution value of the constraint and the number that appears to the right of the constraint formula. A binding constraint has a slack value of 0. (Everything was used to solve the problem.)

• The Sensitivity report indicates how sensitive the model is to changes in the target cell formula and the changing cells.

o The Adjustable Cells area displays a Reduced Gradient value. This indicates how a one-unit increase in the changing cell would affect the target cell.

o The Constraints area displays a Lagrange Multiplier column, which indicates how the target cell would be affected by a one-unit increase in the corresponding constraint value.

o If you selected “Assume Linear Model,” (page 5) the Sensitivity report includes several additional columns. The Reduced Cost column shows the increase in the target cell value per unit of change. The Objective Coefficient column shows the degree to which the changing cell and the target cell are related. The Allowable Increase and Decrease columns reflect the amount the Objective Coefficient must change before the changing cells are affected. In the constraints area, the Shadow Price column indicates the increase in the target value for each unit that the constraint increases. The Constraint RH displays the constraint values used in the problem. The allowable increase and Decree columns show the amount that the constraint value must change before the changing cells are affected.

• The Limits report indicates how much the values of the variable cells can be increased or decreased without breaking the constraints of your problem. The report lists the optimal value, as well as the lowest and highest values that can be used without violating the constraints.

Note: Sensitivity Reports and Limits Reports and not relevant for problems with Integer constraints.

You are planning an ad campaign for one of your new products. You want to reach the largest readership at the lowest possible cost. You will use six publications. Each publication reaches a different number of readers and charges a different rate per page. The following constraints will apply:

• The total budget for print advertising is \$12,000,000.

• The total amount spent in Publications C and D should not exceed \$7,500,000.

• You plan to use each publication a minimum of six times.

• The total percent of the print advertising budget should be no more than 33.3% for any one publication.

For this exercise use the following file: Solver Exercises.xls, worksheet Advertising.

[pic]

1. Select Tools, Solver. The Solver Parameters dialog box displays.

[pic]

2. In the Set Target Cell: field, enter E8 (TotalCost).

3. In the Equal To: field, select Min. Our goal is to get the most advertising for the smallest investment.

4. In the By Changing Cells: field, enter D2:D7. We want to change the number of ads placed in each publication.

5. In the Subject to Constraints: area, click Add.

[pic]

6. Enter the constraints as displayed below.

[pic]

• \$D2:\$D7 = integer (this means that it will “round down” to the next integer. You can’t, for instance, have advertisements in a half a publication.)

• \$D2:\$D7 >= \$G\$15 (The number of ads must be a minimum of six per publication.)

• \$F\$2:\$F\$7 ................
................