Excel Data Analysis For Dummies, 2nd Edition (2014)

Part III. Advanced Tools

Chapter 12. Optimization Modeling with Solver

In This Chapter

Understanding optimization modeling

Setting up a Solver worksheet

Solving an optimization modeling problem

Reviewing the Solver reports

Noodling around with the Solver options

Setting a limit on Solver

Understanding the Solver error messages

In the preceding chapters of this book, I discuss how to use Excel tools to analyze data stored in an Excel workbook. However, you can also perform another sort of data analysis. You can perform data analysis that looks not at labels and values stored in cells but rather at formulas that describe business problems. In fact, Excel includes just such a tool for working on these kinds of problems: the Solver.

When you use optimization modeling and the Excel Solver, you aren't problem solving or analyzing based on raw data. You are problem-solving and analyzing based on formulaic descriptions of a situation. Nevertheless, although the abstraction takes some getting used to, analyzing situations or problems based on formulaic descriptions of objective functions and constraints can be a powerful tool. And powerful tools can lead to powerful new insights.

In this chapter, I describe the sorts of data analysis problems that Solver helps you figure out. I show you a simple example of how the Solver works in action. Although Solver seems terribly complicated, it’s actually an easier tool to use than you might think, so stick with me here.

Understanding Optimization Modeling

Suppose that you’re a one-person business. This example is sort of artificial, but I need to take some liberties in order to make optimization modeling and what the Solver does easy to understand.

Optimizing your imaginary profits

In your business, you make money two ways: You write books and you give seminars. Imagine that when you write a book, you make $15,000 for roughly six weeks of work. If you work out the math on that — dividing $15,000 by 240 hours — you see that you make roughly $62.50 an hour by writing a book.

Also assume that you make $20,000 for giving a one-day seminar on some subject on which you’re an expert. You make about $830.00 an hour for giving the seminar. I calculate this number by dividing the $20,000 that you make by the 24 hours that presenting the seminar requires you to invest.

In many situations, you might be able to figure out how many books you want to write and how many seminars you want to give simply by looking at the profit that you make in each activity. If you make roughly $62 an hour writing a book and you make roughly $830 an hour giving a seminar, the obvious answer to the question, "How many books should I write and how many seminars should I give?" is, do as many seminars as possible and as few books as possible. You make more money giving seminars, so you should do that more.

Recognizing constraints

In many situations, however, you can't just look at the profit per activity or the cost per activity. You typically need to consider other constraints in your decision-making. For example, suppose that you give seminars on the same subject that you write books about. In this case, it might be that in order to be in the seminar business, you need to write at least one book a year. And so that constraint of writing one book a year needs to be considered while you think about what makes most sense about how you maximize your profits.

Commonly, other constraints often apply to a problem like this. For example — and I know this because one of my past jobs was publishing books — book publishers might require that you give a certain number of seminars a year in order to promote your books. So it might also be that in order to write books, you need to give at least four seminars a year. This requirement to give at least four book-promoting seminars a year becomes another constraint.

Consider other constraints, too, when you look at things such as financial resources available and the capacity of the tools that you use to provide your products or services. For example, perhaps you have only $20,000 of working capital to invest in things like writing books or in giving seminars. And if a book requires $500 to be tied up in working capital but a seminar requires $2,500 to be tied up in working capital, you’re limited in the number of books that you can write and seminars that you can give by your $20,000 of working capital balance.

Another common type of constraint is a capacity constraint. For example, although there are 2,080 hours in a working year, assume that you want to work only 1,880 hours in a year. This would mean, quite conventionally, that you want to have ten holidays a year and three weeks of vacation a year. In this case, if a book requires 240 hours and a seminar requires 24 hours, that working-hours limit constrains the number of books and seminars that you can give, too.

This situation is exactly the kind of problem that Solver helps you figure out. What Solver does is find the optimum value of what's called your objective function. In this case, the objective function is the profit function of the business. But Solver, in working through the numbers, explicitly recognizes the constraints that you describe.

Setting Up a Solver Worksheet

Figure 12-1 shows an Excel workbook set up to solve an optimization modeling problem for the one-person business that I describe earlier in this chapter. Here I describe the pieces and parts of this workbook. If you've carefully read the earlier discussion in the chapter about what optimization modeling is, you should have no trouble seeing what's going on here.

Figure 12-1: A sample workbook set up to solve an optimization modeling problem for a one-person business.

The Solver workbook is available on this book's companion website. (Find out how to access it in the Introduction.) You might want to retrieve this workbook before you begin noodling around with the optimization modeling problem that I describe here. Having a workbook set up for you makes things easier, especially if you’re working with the Solver for the first time.

If you choose to construct the Solver workbook example yourself (a fine idea), you want to tell Excel to display actual formulas rather than formula results in the workbook. This is what the workbook shown in Figure 12-1 does, by the way. To do this, select the worksheet range in which you want to display the actual formulas rather than the formula results and then simultaneously press the Ctrl and the ` (grave accent) keys. By pressing Ctrl+`, you tell Excel to display the formula rather than the formula result within the selected range.

Setting up a Solver workbook requires three steps:

1. Identify the Solver variables.

First, you want to identify the variables in your optimization modeling problem. In the case of trying to figure out the number of books to write and seminars to give to make the most money in your one-person business, the two Solver variables are books and seminars.

In Figure 12-1, I enter the labels shown in range A1:A3 and then the starting variable values shown in range B2:B3. This part of the worksheet isn't anything magical. It simply identifies which variables go into the objective function. The objective function is the formula that you want to maximize or minimize. The values stored in the worksheet range B2:B3 are my starting guesses about what the optimal variable values should be. In Figure 12-1, for example, I’m just guessing that the optimal number of books to write is two and that the optimal number of seminars to give is eight. You won't know what the optimal number of books and seminars actually is until you work out the problem.

Although you don't have to name the cells that hold the variable values — in this case, cells B2 and B3 — naming those cells makes your objective function formula and your constraint formulas much easier to understand. So I recommend that you name the cells.

If you set up a workbook like the one shown in Figure 12-1, you can name the variable value cells by selecting the worksheet range A2:B3 and then clicking the Formula tab’s Create from Selection command button. When Excel displays the Create Names from Selection dialog box, as shown in Figure 12-2, select the Left Column check box and click OK. This tells Excel to use the labels in the left column: This would be the range A2:A3 — to name the range B2:B3. In other words, by following these steps, you name cell B2 Books and you name cell B3 Seminars.

Figure 12-2: The Create Names from Selection dialog box.

2. Describe the objective function.

The objective function, shown in cell B5 in Figure 12-1, gives the formula that you want to optimize. In the case of a profit formula, you want to maximize a function because you want to maximize profits, of course.

I should note and you should remember that not all objective functions should be maximized. Some objective functions should be minimized. For example, if you create an objective function that describes the cost of some advertising program or the risk of some investment program, you can logically choose to minimize your costs or minimize your risks.

To describe the objective function, create a formula that describes the value that you want to optimize. In the case of a profit function for the one-person business that I detail in the earlier section “Recognizing constraints,” you make $15,000 for every book that you write and $20,000 for every seminar that you give. You can describe this by entering the formula =15000*Books+20000*Seminars. In other words, you can calculate the profits of your one-person business by multiplying the number of books that you write times $15,000 and the number of seminars that you give times $20,000. This is what shows in cell B5.

3. Identify any objective function constraints.

In the worksheet range A8:C11, I describe and identify the constraints on the objective function. As I note earlier, four constraints can limit the profits that you can make in your business:

· Cash required limit: The first constraint shown in Figure 12-1 (cell A8) quantifies the cash required constraint. In this example, each book requires $500 cash, and each seminar requires $2,500 cash. If you have $20,000 cash to invest (I assume to temporarily invest) in books and seminars, you’re limited in the number of books that you can write and the number of seminars that you can give by the cash, up-front investment that you need to make in these activities. The formula in cell B8, =Books*500+Seminars*2500, describes the cash required by your business. The value shown in cell C8, 20000, identifies the actual constraint.

· Working hours limit: The working hours limit constraint is quantified by having the formula =Books*240+Seminars*24 in cell B9 and the value 1880 in cell C9. Use these two pieces of information, the formula and the constant value, to describe a working hours limit. In a nutshell, this constraint says that the number of hours that you spent on books and seminars needs to be less than 1880.

· Minimum number of books policy: The constraint that you must write at least one book a year is set up in cells B10 and C10. The formula =Books goes into cell B10. The minimum number of books, 1, goes into cell C10.

· Minimum number of seminars policy: The constraint that you must give at least four seminars a year is set up in cells B11 and C11. The formula =Seminars goes into cell B11. The minimum number of seminars constant value, 4, goes into cell C11.

After you give the constraint formulas and provide the constants to which the formula results will be compared, you're ready to solve your optimization modeling problem. With the workbook set up (refer to Figure 12-1), solving the function is actually very easy.

Setting up the workbook and defining the problem of objective function and constraint formulas is the hard part.

Solving an Optimization Modeling Problem

After you have your workbook set up, you solve the optimization modeling problem by identifying where you've stored the solver variables, the objective function formula, the constraint formulas, and the constant values to which constraint formulas need to be compared. This is actually very straightforward. Here are the steps that you follow:

1. Tell Excel to start the Solver by clicking the Data tab’s Solver command button.

Excel displays the Solver Parameters dialog box, as shown in Figure 12-3.

If the Tools menu doesn't supply the Solver command, you need to install the Solver add-in. To do this, choose the File ⇒ Options command. When Excel displays the Excel Options dialog box, select the Add-Ins item from the left box that appears along the left edge of the Excel Options dialog box. Excel next displays a list of the possible add-ins — including the Solver add-in. Select the Solver add-in item and click Go. Excel apparently doesn’t think you’re serious about this Solver Add-in business because it displays another dialog box, called the Add-Ins dialog box. Select the Solver add-in from this dialog box and click OK. Excel installs the Solver add-in. Whew. From this point on, you can use the Solver without trouble.

Figure 12-3: The Solver Parameters dialog box.

2. In the Set Objective text box of the Solver Parameters dialog box, identify the cell location of the objective function formula.

In the case of the example workbook shown earlier in Figure 12-1, the objective function formula is stored in cell B5. If you were solving an optimization modeling problem using the workbook from Figure 12-1, therefore, you enter $B$5 into the Set Objective text box.

3. Describe what optimization means.

As I note earlier, not every objective function should be maximized in order to be optimized. In the case of a profit function, because you want to maximize profits — which is the case here — you want to make the objective function formula result as big as possible. But other objective functions might need to be minimized or even set to some specific value.

Select one of the To radio buttons available in the Solver Parameters dialog box to define what optimization means. For example, in the case of a profit function that you want to maximize, select the Max radio button. If instead you’re working with a cost function and you want to save costs, you select the Min radio button. In the special case in which optimizing the objective function means getting the function to return a specific value, you can even select the Value Of radio button and then make an entry in the Value Of text box to specify exactly what the objective function formula should return.

4. In the By Changing Variable Cells text box of the Solver Parameters dialog box, identify the Solver variables.

You need to identify the variables that can be adjusted in order to optimize the objective function. In the case of a one-person business in which you're noodling around with the number of books that you should write and the number of seminars that you should give, the Solver variables are books and seminars.

To identify the Solver variables, you can enter either the cell addresses into the By Changing cells text box or the cell names. In Figure 12-3, I enter Books, a comma, and then Seminars into the By Changing Cells text box. Note that these labels refer to cells B2 and B3. I could have also entered $B$2, $B$3 into the By Changing Cells text box.

5. Click the Add button in the Solver Parameters dialog box to describe the location of the constraint formulas and the constant values to which the constraint formulas should be compared.

Excel displays the Add Constraint dialog box, as shown in Figure 12-4. From the Add Constraint dialog box, you identify the constraint formula and the constant value for each of your constraints. For example, to identify the cash requirements constraint, you need to enter $B$8 into the Cell Reference text box. Select the less-than or equal-to logical operator from the drop-down list (between the Cell Reference and the Constraint text boxes). Then enter $C$8 into the Constraint text box. In Figure 12-4, you can see how you indicate that the cash requirements constraint formula is described in cells B8 and C8.

Note that the logical operator is very important. Excel needs to know how to compare the constraint formula with the constant value.

After you describe the constraint formula, click the Add button. To add another constraint, you click the Add button and follow the same steps. You need to identify each of the constraints.

Figure 12-4: The Add Constraint dialog box.

6. (Optional) Identify any integer constraints.

Sometimes you have implicit integer constraints. In other words, you might need to set the Solver variable value to an integer value. In the example of the one-person business, to get paid for a book, you need to write an entire book. The same thing might be true for a seminar, too. (Or it might not be true for a seminar — perhaps you can do, for example, half of a seminar and have a consulting-buddy do the other half… .)

To identify integer constraints, you follow the same steps that you take to identify a regular constraint except that you don't actually need to store integer constraint information in your workbook. What you can do is click the Add button on the Solver Parameters dialog box. In the Add Constraint dialog box that appears, enter the Solver variable name into the Cell Reference box and select int from the drop-down list, as shown in Figure 12-5.

Figure 12-5: Set up an integer constraint here.

7. (Optional) Define any binary constraints.

In the same manner that you define integer constraints, you can also describe any binary constraints. A binary constraint is one in which the solver variable must equal either 0 or 1.

To set up a binary constraint, click the Add button in the Solver Parameters dialog box. When Excel displays the Add Constraint dialog box, enter the Solver variable name into the Cell Reference box. Select Bin from the drop-down list and then click OK.

8. (Optional) Tell Excel you would accept negative value variables.

By default, Excel checks the Make Unconstrained Values Non-negative box. This means Excel only considers “real” those solutions where your input values end up as either zero or positive numbers. Usually, this makes sense.

If you were solving for the optimal number of books and seminars, for example, you might accept as a practical suggestion the value zero or some positive value. But obviously you can’t write -2 books and you can’t give -3 seminars. Those suggestions would be just plain goofy.

In some optimization modeling, though, you can practically work with negative values. If you were optimizing the investment amounts you wanted to make in, say, some new oil field, the optimal value could be a negative number. In other words, the right choice could just be to divest money (subtract money) rather than invest money (add money).

If you will accept negative variable values, therefore, you can uncheck the Make Unconstrained Variables Non-negative box.

9. (Optional) Select a Solving Method.

The Solver Parameters dialog box provides a Select a Solving Method drop-down list box that provides three engines to solving your optimization problem: GRG Nonlinear (which you can usually use and works for smooth but nonlinear problems), Simplex LP (which works for linear problems), and Evolutionary (which works for nonsmooth problems).

Microsoft didn’t design and program the Solver commands algorithms. Another company, Frontline Solvers, did. Which doesn’t seem all that relevant except for the fact that the www.solver.com website provides some great discussions of when you might want to use the Simplex LP or Evolutionary solving method rather than the default GRG Nonlinear method. To get to this information, visit the www.solver.com website and type the solving method you have a question about into the search box.

10. Solve the optimization modeling problem.

After you identify the objective function, identify the Solver variables, and describe the location of the constraint formulas, you're ready to solve the problem. To do this, simply click the Solve button.

Assuming that Excel can solve your optimization problem, it displays the Solver Results dialog box, as shown in Figure 12-6. The Solver Results dialog box provides two radio buttons. To have Solver retain the optimal solution, select the Keep Solver Solution radio button and then click OK. In the case of the one-person book and seminar business, for example, the optimal number of books to write a year is 7 and the optimal number of seminars to give is 6.6 (shown in cells B2 and B3 in the sample workbook shown in Figure 12-6).

The Solver Parameters dialog box also includes two presumably self-descriptive command buttons: Change and Delete. To remove a constraint from the optimization model, select the constraint from the Subject to the Constraints list box and then click the Delete button. If you want to change a constraint, select the constraint and then click the Change button. When Excel displays the Change Constraint dialog box, which resembles the Add Constraint dialog box, use the Cell Reference text box, the operator drop-down list, and the Constant text box to make your change.

Figure 12-6: Get Solver results here.

Reviewing the Solver Reports

Refer to the Solver Results dialog box in Figure 12-6 to see the Reports list box. The Reports list box identifies three solver reports you can select: Answer, Sensitivity, and Limits. You might be able to use these to collect more information about your Solver problem.

The Answer Report

Figure 12-7 shows an Answer Report for the one-person business optimization modeling problem. I should tell you that I needed to remove the integer constraints to show all the Solver reports, so these values don't jibe perfectly with the Solver results shown in Figure 12-6. Don’t worry about that but instead look at the information provided by the Answer Report.

The main piece of information provided by the Answer Report is the value of the optimized objective function. This information appears in cell E16 in Figure 12-7. In the case of the one-person book writing and seminar business, for example, the final value, which is the value of the optimized objective function, equals 238945.5778. This tells you that the best mix of book-writing and seminar-giving produces roughly $238,946 of profit.

Figure 12-7: The Answer Report.

The Answer Report also shows the original value of the objective function. If you set your original Solver variable values to your first guess or your current configuration, you could compare the original value and the final value to see by what amount Solver improves your objective function value. In this case, such a comparison could show you by what amount Solver helped you increase your profits, which is pretty cool.

The Variable Cells area of the Answer Report compares the original value and final values of the solver variables. In the case of the one-person book-writing and seminar-giving business, this area of the worksheet compares the original value for the number of books written (two) with the final value for the number of books written (roughly seven books). The Adjustable Cells area also shows the original value of the number of seminars given (eight) and the final value of the number of seminars given (roughly six and a half seminars).

The Constraints area of the Answer Report is really interesting. Though you can’t see this in Figure 12-7 — so you need to be following along on your computer and then scrolling down the workbook — the Constraints area shows you what constraint limits the objective function. You might, in the simple case of the one-person business, be able to guess what the limiting factors were intuitively. But in many real-life optimization modeling problems, guessing about what constraint is binding or limiting is more difficult.

In the case of the simple one-person business problem, the Constraints area shows that the first two constraints, cash requirements and working hours, are the ones that limit, or bind, the optimization modeling problem. You can easily see this by looking at the Slack column (shown in cells G27:G30 if you’re using the example workbook available from the companion website). Slack equals zero for both the cash requirements function and the working hours limit. This means that the objective function value uses up all the cash and all the working hours to produce the final value of $238946. The other two constraints concerning the minimum number of books written and the minimum number of seminars given aren't limiting because they show slack.

The Sensitivity Report

Figure 12-8 shows the Sensitivity Report. A Sensitivity Report shows reduced gradient values and Lagrange multipliers, which sounds like a whole lot of gobbledygook. But actually these values aren't that hard to understand and can be quite useful.

A reduced gradient value quantifies the change in the objective function if the variable value increases by one. The Lagrange multiplier quantifies how the objective function changes if a constant constraint value increases by one.

In the Sensitivity Report shown in Figure 12-8, the reduced gradient values equal zero. This zero indicates that the variable value can’t be increased. For example, the reduced gradient value of zero for books indicates that you can't write more books because of the limiting effect of the constraints. The same thing is true for the reduced gradient value of zero for the seminars variable.

Figure 12-8: A Sensitivity Report.

The Lagrange multiplier values sometimes show as zero, too. When the Lagrange multiplier value shows as zero, that means that constraint isn’t limiting. For example, in Figure 12-8, the Lagrange multiplier for both the minimum number of books policy formula and the minimum number of seminars policy formula show as zero. As you may recall from the earlier discussion of the Solver results, neither of these two constraints is binding. The Lagrange multiplier value of 7.55102041 in cell E17 shows the amount by which the objective function would increase if the cash requirements constant value increased by one dollar. The Lagrange multiplier value of 46.7687075 in cell E18 shows the amount by which the objective function value would increase if you had one additional hour in which to work.

The Limits Report

The Limits Report, an example of which is shown in Figure 12-9, shows the objective function optimized value, the Solver variable values that produce the optimized objective function value, and the upper and lower limits possible for the Solver variables.

The upper and lower limits show the possible range of Solver variable values along with the resulting objective function values. For example, if you take a close look at Figure 12-9, you see that the lower limit for the number of seminars (shown in cell F14) equals 4 and that at this level the objective function equals 187653.0613, or roughly $187,653 dollars.

Figure 12-9: The Limits Report.

The upper limit for the number of seminars (shown in cell I14) equals 6.56462585 and, at this level, the objective function returns 238945.5778, or $238,945, which is the optimal value.

To produce a Sensitivity Report or a Limits Report, the solver problem cannot have integer constraints. Accordingly, to produce the reports shown in Figures 12-7, 12-8 and 12-9, I had to remove the integer constraints on books. Removing this integer constraint changed the objective function optimal value.

Some other notes about Solver reports

You can run the Solver multiple times and get new sets of Answer, Sensitivity, and Limits reports each time that you do. The first set of Solver reports that you get is numbered with a 1 on each sheet tab. The second set, cleverly, is numbered with a 2.

If you want to delete or remove Solver report information, just delete the worksheet on which Excel stores the Solver report. You can delete a report sheet by right-clicking the sheet's tab and then choosing Delete from the shortcut menu that appears.

Working with the Solver Options

If you’re an observant reader, you might have noticed that the Solver Parameters dialog box includes an Options button. Click this button, and Excel displays the Solver Options dialog box, as shown in Figure 12-10. You might never need to use this dialog box. But if you want to fine-tune the way that Solver works, you can use the buttons and boxes provided by the Solver Options dialog box to control and customize the way that Solver works. Here I briefly describe how these options work.

Figure 12-10: The All Methods tab of the Solver Options dialog box.

Using the All Methods options

The All Methods tab’s options (the tab shows in Figure 12-10) provides boxes you can use for any solving method. Accordingly, I'll go over these babies first.

Using automatic scaling

You can select the Use Automatic Scaling check box when you’re working with variables that greatly differ in magnitude. For example, if you’re working with interest rates and multimillion dollar account balances, you might want to use the automatic scaling option to tell Excel, "Hey dude, the Solver variable values greatly differ in magnitude, so you ought to automatically scale these babies."

Showing iteration results

If you don't have anything better to do, select the Show Iteration Results check box. When you do this, Excel stops after it calculates each objective function using a new set of Solver variable values and shows you the intermediate calculation results. Most people won't and shouldn't care about seeing intermediate calculation results. But heck, I suppose that in some cases, you might want to see how Solver is working toward the objective function optimal result.

Solving with integer constraints

Using integer constraints may complicate your optimization modeling, so Excel’s Solver provides some tweaks you can make to models that “technically” should return integer values. For example, you can check the Ignore Integer Constraints box to tell Excel you want to try solving the problem (just for giggles) without the integer constraints.

Another integer-constraint-related tweak: The Integer Optimality % box lets you specify the maximum percentage difference that you’ll accept between the best solution that uses integer constraints and the best solution that ignores integer constraints.

Setting a limit on Solver

Use the Max Time and Iterations text boxes to limit the amount of work that Solver does to solve an optimization modeling problem. Now, the simple example that I discuss here doesn't take much time to solve. But real-life problems are often much more complicated. A real-life problem might have many more Solver variables to deal with. The constraints might be more numerous and more complicated. And you might complicate optimization by doing things such as working with lots of integer or binary constraints.

When you work with large, complex, real-life problems, the time that Solver takes to optimize might become very lengthy. In these cases, you can set a maximum time limit that Solver takes by using the Max Time text box. You can also set a maximum number of iterations that Solver makes by using the Iterations text box.

You can stop the Solver's calculations by pressing Esc.

If you’re using the Evolutionary solving method in a situation with integer constraints, you can also specify the maximum number of subproblems you want the Solver to work on using the Max Subproblems box and then the maximum number of feasible integer solutions you want Solver to produce using the Max Feasible Solutions box.

Using the GRG Nonlinear tab

The GRG Nonlinear tab (see Figure 12-11) provides buttons and boxes for managing the way Solver works when you’re using the GRG Nonlinear solving method.

Figure 12-11: GRG Nonlinear tab of the Solver Options dialog box.

Saying when

Have you ever been to a restaurant where your server wanders around at some point in the meal with a huge peppermill asking whether you want black pepper on your salad? If you have, you know that part of the ritual is that at some point, you tell the server when she has ground enough pepper for your green salad.

The Convergence text box provided on the GRG Nonlinear tab of the Solver Options dialog box works in roughly the same way. If you are using the GRG Nonlinear Solving method, you use the Convergence box to tell Excel when it should stop looking for a better solution. The Convergence text box accepts any value between 0 and 1. When the change in the objective function formula result is less then the value shown in the convergence text box, Excel figures that things are getting close enough, so additional iterations aren't necessary.

Oh, and something that I should mention: With larger convergence values, Excel reaches a reasonable solution more quickly and with less work. And with smaller or very precise convergence values, Excel works harder and takes more time.

Forward versus central derivatives

Select from the two Derivatives radio buttons — Forward and Central — to tell Excel how to estimate partial derivatives when it's working with the objective function and constraint formulas. In most cases, everything works just fine if Excel uses forward derivatives. But, in some cases, forward derivatives don't work. And in this situation, you might be able to specify that Excel use central derivatives.

Using central derivatives requires much more work of Excel, but some highly constrained problems can more easily and more practically be solved using central derivatives.

Working with the Multistart settings

If you check the Multistart box on the GRG Nonlinear tab, you tell Solver to, in effect, solve the optimization problem by beginning from several different starting points. The Population Size box lets you specify the number of starting points. The Random Seed box lets you provide an integer to be used as the seed for a random number generator that produces the actual starting points. Finally, you can check and uncheck the Require Bounds On Variables box to specify that this whole multistart craziness only occurs when you’ve had the decency to define both upper and lower limits for the variables.

Using the Evolutionary tab

Okay, now here’s something that is probably going to come as a big surprise to you: The Evolutionary tab (see Figure 12-12) provides buttons and boxes for managing the way Solver works when you’re using the Evolutionary solving method.

Figure 12-12: The Evolutionary tab of the Solver Options dialog box.

For example, you can use the Convergence box to specify how closely Solver needs to get to the optimal function value in order for you to call the job done. In precise terms, the value you enter into the Convergence text box specifies the maximum percentage difference in the objective function values that Solver should allow in order to justify stopping its search for an optima.

The Mutation Rate box, which accepts values between 0 and 1, lets you control how much variables are altered (or “mutated”) in a search for an optimal solution. And the Population Size box lets you specify how many different data points the Solver maintains at a time in its search for an optimal solution.

The Random Seed box lets you supply a starting integer for the random number generator used by the Evolutionary method.

The Maximum Time (In Seconds) box lets you do just what you’d guess: Tell Excel to stop wasting time at some point if it’s not making progress.

Finally, as with the GRG Nonlinear solving method, you can check and uncheck the Require Bounds On Variables box to specify that the evolutionary solving only occurs when you set both upper and lower limits for the variables.

Saving and reusing model information

The Solver Options dialog box provides a Save/Load button that you can to use to save and then later reload optimization modeling problem information. If you click the Load/Save Model button, Excel displays the Load/Save Model dialog box, as shown in Figure 12-13.

Figure 12-13: The Load/Save Model dialog box.

To save the current optimization modeling information, you enter a worksheet range address or the upper left corner cell of a worksheet range address in the text box that Excel can use to save the model information and then click Save.

To later reuse that model information, display the Load/Save Model dialog box, enter the full worksheet range holding the previously saved model, and then click Load.

Understanding the Solver Error Messages

For simple problems, Solver usually quickly finds the optimal Solver variable values for the objective function. However, in some cases — in fact, maybe quite frequently in the real world — Solver has trouble finding the Solver variable values that optimize the objective function. In these cases, however, Solver typically displays a message or an error message that describes or discusses the trouble that it's having with your problem. Quickly, before I wrap up this chapter, I briefly identify and comment on the most common messages and error messages that Solver might display as it finishes or gives up on the work that it’s doing.

Solver has found a solution

The Solver has found a solution message tells you that Solver has done is job and found a set of variable values that satisfy your constraints. You rock man.

Solver has converged to the current solution

The Solver has converged to the current solution message tells you that Excel has found a solution but isn't particularly confident in the solution. In essence, this message alerts you to the possibility that a better solution to your optimization modeling problem might exist. To look for a better solution, adjust the Convergence setting in the Solver Options dialog box so that Excel works at a higher level of precision. I describe how you do this in the earlier sections on the GRG Nonlinear and Evolutionary tabs.

Solver cannot improve the current solution

The Solver cannot improve the current solution message tells you that, well, Excel has calculated a rough, pretty darn accurate solution, but, again, you might be able to find a better solution. To tell Excel that it should look for a better solution, you need to increase the precision setting that Solver is using. This means, of course, that Excel will take more time. But that extra time might result in it finding a better solution. To adjust the precision, you again use the Solver Options dialog box.

Stop chosen when maximum time limit was reached

The Stop chosen when maximum time limit was reached message tells you that Excel ran out of time. You can retry solving the optimization modeling problem with a larger Max Time setting. (Read more about this in the earlier section, “Setting a limit on Solver.”) Note, however, that if you do see this message, you should save the work that Excel has already performed as part of the optimization modeling problem solving. Save the work that Excel has already done by clicking the Keep Solver Results button when Excel displays this message. Excel will be closer to the final solution the next time that it starts looking for the optimal solution.

Solver stopped at user’s request

Er, obvious right? Solver good dog. Solver stopped because master told it to stop. Solver get treat.

Stop chosen when maximum iteration limit was reached

The Stop chosen when maximum iteration limit was reached message tells you that Excel ran out of iterations before it found the optimal solution. You can get around this problem by setting a larger iterations value in the Solver Options dialog box. Read the earlier section, “Showing iteration results.”

Objective Cell values do not converge

The Objective Cell values do not converge message tells you that the objective function doesn't have an optimal value. In other words, the objective function keeps getting bigger (or keeps getting smaller) even though the constraint formulas are satisfied. In other words, Excel finds that it keeps getting a better objective function value with every iteration, but it doesn't appear any closer to a final objective function value.

If you encounter this error, you’ve probably not correctly defined and described your optimization modeling problem. Your objective function might not make a lot of sense or might not be congruent with your constraint formulas. Or maybe one or more of your constraint formulas — or probably several of them — don't really make sense.

Solver could not find a feasible solution

The Solver could not find a feasible solution message tells you that your optimization modeling problem doesn't have an answer. As a practical matter, when you see this message, it means that your set of constraints excludes any possible answer.

For example, returning one last time to the one-person business, suppose that it takes 3,000 hours to write a book and that only 2,000 hours for work are available in a year. If you said that you wanted to write at least one book a year, there’s no solution to the objective function. A book requires up to 3,000 hours of work, but you only have 2,000 hours in which to complete a 3,000-hour project. That's impossible, obviously. No optimal value for the objective function exists.

Linearity conditions required by this LP Solver are not satisfied

The Linearity conditions required by this LP Solver are not satisfied message indicates that although you selected the Simplex LP solving method, Excel has now figured out that your model isn't actually linear. And it's mad as heck. So it shows you this message to indicate that it can't solve the problem if it has to assume that your objective function and constraint formulas are linear.

If you do see this message, by the way, go ahead and try the GRG Nonlinear solving method.

The problem is too large for Solver to handle

The Problem is too large for Solver to handle message means that you’ve got a problem too large for solver either because you’ve tried to model with more than 200 decision variable or more than 100 constraints. To work around this problem, you may be able to try minimizing the number of variables or constraints so their counts fall below the “hey buddy that’s just too large” constraint.

Solver encountered an error value in a target or constraint cell

The Solver encountered an error value in a target or constraint cell message means that one of your formula results in an error value or that you goofed in describing or defining some constraint. To work around this problem, you need to fix the bogus formula or the goofy constraint.

There is not enough memory available to solve the problem

The There is not enough memory available to solve the problem message is self-descriptive. If you see this message, Solver doesn't have enough memory to solve the optimization modeling problem that you’re working on. Your only recourse is to attempt to free up memory, perhaps by closing any other open programs and any unneeded documents or workbooks. If that doesn't work, you might also want to add more memory to your computer, especially if you’re going to commonly do optimization modeling problems. Memory is cheap.

Error in model. Please verify that all cells and constraints are valid

The Error in model. Please verify that all cells and constraints are valid message means that you’ve got something goofy — probably also something fixable — in your optimization problem. Check your formulas and your input values. Make sure there’s nothing obviously wrong. Oh, and one other thing: Make sure you’re not using the word “solver” in any of your named variables. That can confuse Solver.