Solver is an add-in that may or may not be installed on your computer.  Click the Tools menu.  If you do not see Solver on the menu, you will have to install it.  To install Solver, click Add-Ins on the Tools menu, check Solver and then click OK.  If Solver is not on your list of available Add-Ins, you'll need to insert the MS Office CD in your CD-ROM drive and click the Browse button.  Browse to the CD-ROM drive and search for .XLA or .XLL files.  If you have trouble with this, call or e-mail me immediately.

The Solver dialog box is shown above.  The target cell is the cell that contains the formula whose new result you want to find.  You can find the Max or Min of this cell or you can have this cell set to a particular value.  The changing cells are the value cells Excel should change in order to produce the desired result in the target cell.  You can place restrictions on the changing cells or any other cells on the worksheet by setting constraints.  For example, if cell B6 MUST have a value over 50, you would add the constraint  B6>50.

Let's consider the following problem:

We want to make both radio and magazine ads to reach at least 10 million people at the lowest cost.  We need to know how many radio ads and how many magazine ads to place.  But the total cost of advertising cannot exceed the advertising budget of $125,000.  And we must place at least four magazine ads and at least 8 radio ads.  Download the AdvertisingSolver file and work this problem with me.

Click Solver on the Tools menu.  Set the target cell (remember the target cell must contain a formula) to E6, the total cost of advertising.   Then click the Min function so that solver will solve for the lowest advertising costs.  The changing cells are the number of magazine and radio ads.  Select cells B2:B3.  Next we'll add the constraints.  Click the Add button and then click E6 as the cell reference.  Make sure the operator is <=; then click E7 as the constraint cell.  Remember the constraint cell can be a cell reference or a value.  Click the Add button to add another constraint.  Enter E10 >=E11.  Click Add again and enter B2>=E8.  Click Add again and enter B3>=E9.  Click OK.  Your Solver Parameters dialog box should look like this.  (It doesn't matter is your constraints are in a different order from mine.)

Click the Solve button.  You should see this on your screen. 

For a cost of $120,000, you can place 8 magazine ads and 8 radio ads which will reach an audience of 10 million people.  Notice in the Solver Results dialog box, it states that solver found a solution.  This is not always the case.  There may be situations in which solver cannot reach a solution.  When this happens, you need to change one or more of your constraints.

Now make sure Keep Solver Solution is checked and under Reports, click Answer.  This will create a report for you on another sheet.  The answer report for this problem is shown below.   Notice the original value and the final value are shown for both the target cell and the adjustable cells. The report also examines each constraint and determines whether it is binding or not binding. 

A binding constraint is one in which the resource is fully utilized meaning there is no slack.  The number of radio ads we should make is 8 which is equal to the minimum number of radio ads.  Therefore, this constraint is binding and the slack is zero. 

However, a nonbinding constraint is just the opposite.  It has a nonzero slack, meaning the resource is not fully utilized; therefore, it does not limit the value of the target cell.  The number of magazine ads we should make is 8 which is four over the minimum value of 4.  Therefore, the constraint i not binding and the slack shows 4.  The total cost of advertising is 5,000 short of the advertising budget, therefore, that constraint is not binding.
 

sloopj@octech.edu

Back to The Course