Figure 37.1 shows the mortgage loan worksheet used in the preceding chapter. This worksheet has four input cells (C4:C7) and four formula cells (C10:C13). Originally, this worksheet was used for a what-if analysis example. This example demonstrates the opposite approach. Rather than supply different input cell values to look at the calculated formulas, this example lets Excel determine one of the input values that will produce the desired result.

Figure 37.1. This worksheet is a good demonstration of goal seeking.

Assume that you’re in the market for a new home and you know that you can afford an $1,800 monthly mortgage payment. You also know that a lender can issue a 30-year fixed-rate mortgage loan for 6.50 percent, based on an 80-percent loan-to-value (that is, a 20-percent down payment). The question is “What is the maximum purchase price I can handle?” In other words, what value in cell C4 causes the formula in cell C11 to result in $1,800? In this simple example, you could plug values into cell C4 until C11 displays $1,800. With more complex models, Excel can usually determine the answer much more efficiently.

To answer the question posed in the preceding paragraph, first set up the input cells to match what you already know. Specifically:

Next, choose Data Data Tools What-If Analysis Goal Seek. Excel displays the Goal Seek dialog box, shown in Figure 37.2. Completing this dialog box is similar to forming a sentence. You want to set cell C11 to 1800 by changing cell C4. Enter this information in the dialog box either by typing the cell references or by pointing with the mouse. Click OK to begin the goal-seeking process.

Figure 37.2. The Goal Seek dialog box.

In less than a second, Excel displays the Goal Seek Status box, which shows the target value and the value that Excel calculated. In this case, Excel found an exact value. The worksheet now displays the found value in cell C4 ($284,779). As a result of this value, the monthly payment amount is $1,800. At this point, you have two options:

Excel can’t always find a value that produces the result that you’re seeking. Sometimes, a solution simply doesn’t exist. In such a case, the Goal Seek Status box informs you of that fact.

Other times, however, Excel may report that it can’t find a solution, but you’re pretty sure that one exists. If that’s the case, you can try the following options:

Problems that are appropriate for Solver fall into a relatively narrow range. They typically involve situations that meet the following criteria:

After you set up your worksheet appropriately, you can use Solver to adjust the changing cells and produce the result that you want in your target cell—and simultaneously meet all the constraints that you’ve defined.

I start with a simple example to introduce Solver and then present some increasingly complex examples to demonstrate what this feature can do.

Figure 37.3 shows a worksheet that is set up to calculate the profit for three products. Column B shows the number of units of each product, Column C shows the profit per unit for each product, and Column D contains formulas that calculate the total profit for each product by multiplying the units by the profit per unit.

Figure 37.3. Use Solver to determine the number of units to maximize the total profit.

You don’t need an MBA degree to realize that the greatest profit comes from Product C. Therefore, in order to maximize total profit, the logical solution is to produce only Product C. If things were really this simple, you wouldn’t need tools such as Solver. As in most situations, this company has some constraints that must be met:

These four constraints make the problem more realistic and a bit more challenging. In fact, it’s a perfect problem for Solver.

I go into more detail in a moment, but here is the basic procedure for using Solver:

To start Solver to tackle this example, choose Data Analysis Solver. Excel displays its Solver Parameters dialog box, shown in Figure 37.4.

Figure 37.4. The Solver Parameters dialog box.

In this example, the target cell is D6—the cell that calculates the total profit for three products.

At this point, you have the following options:

If you specify any report options, Excel creates each report on a new worksheet, with an appropriate name. Figure 37.7 shows an Answer Report. In the Constraints section of the report, two of the constraints are binding, which means that these constraints were satisfied at their limit with no more room to change.

Figure 37.7. One of three reports that Solver can produce.

This simple example illustrates how Solver works. The fact is, you could probably solve this particular problem manually just as quickly. That, of course, isn’t always the case.

Before presenting more complex examples, this section discusses the Solver Options dialog box. From this dialog box, you control many aspects of the solution process, as well as load and save model specifications in a worksheet range.

It’s not unusual for Solver to report that it can’t find a solution, even when you know that one should exist. Often, you can change one or more of the Solver options and try again. When you choose the Options button in the Solver Parameters dialog box, Excel displays the Solver Options dialog box, shown in Figure 37.8.

Figure 37.8. You can control many aspects of how Solver solves a problem.

This list describes Solver’s options:

Usually, you want to save a model only when you’re using more than one set of Solver parameters with your worksheet. This is because Excel saves the first Solver model automatically with your worksheet (using hidden names). If you save additional models, Excel stores the information in the form of formulas that correspond to the specifications. (The last cell in the saved range is an Array formula that holds the options settings.)

This example describes how to solve a set of three linear equations with three variables. Here’s an example of a set of linear equations:

4x + 2y -2z =0
2x - 3y +3z =9
-6x -2y +z = 0

The question that Solver will answer is “What values of x, y, and z satisfy all three of these equations?”

Figure 37.9 shows a workbook set up to solve this problem. This workbook has three named cells, which makes the formulas more readable:

Figure 37.9. Solver will attempt to solve this series of linear equations.

The three named cells are all initialized to 1 (which certainly doesn’t solve the equations).

The three equations are represented by formulas in the range B6:B8:

These formulas use the values in the x, y, and z named cells. The range C6:C8 contains the “desired” result for these three formulas.

Solver will adjust the values in x, y, and z (that is, the changing cells in C11:C13), subject to these constraints:

Figure 37.10 shows the solution. The x (0.75), y (-2.0), and z (0.5) values satisfy all three equations.

Figure 37.10. Solver solved the simultaneous equations.

This example involves finding alternative options for shipping materials, while keeping total shipping costs at a minimum (see Figure 37.11). A company has warehouses in Los Angeles, St. Louis, and Boston. Retail outlets throughout the United States place orders, which the company then ships from one of the warehouses. The company wants to meet the product needs of all six retail outlets from available inventory and keep total shipping charges as low as possible.

Figure 37.11. This worksheet determines the least expensive way to ship products from warehouses to retail outlets.

This workbook is rather complicated, so each part is explained individually:

Cell G24 is the bottom line, the total shipping costs for all orders.

Solver fills in values in the range D12:F17 in such a way that minimizes shipping costs while still supplying each retail outlet with the desired number of units. In other words, the solution minimizes the value in cell C24 by adjusting the cells in D12:F17, subject to the following constraints:

Setting up the problem is the difficult part. For example, you must enter 27 constraints. When you have specified all the necessary information, click the Solve button to put Solver to work. Solver displays the solution shown in Figure 37.12.

Figure 37.12. The solution that was created by Solver.

The total shipping cost is $55,515, and all the constraints are met. Notice that shipments to Miami come from both St. Louis and Boston.

The example in this section is a common type of problem that’s ideal for Solver. Essentially, problems of this sort involve optimizing the volumes of individual production units that use varying amounts of fixed resources. Figure 37.13 shows an example for a toy company.

Figure 37.13. Using Solver to maximize profit when resources are limited.

www.solver.com

This company makes five different toys, which use six different materials in varying amounts. For example, Toy A requires 3 units of blue paint, 2 units of white paint, 1 unit of plastic, 3 units of wood, and 1 unit of glue. Column G shows the current inventory of each type of material. Row 10 shows the unit profit for each toy.

The number of toys to make is shown in the range B11:F11. These are the values that Solver determines (the changing cells). The goal of this example is to determine how to allocate the resources to maximize the total profit (B13). In other words, Solver determines how many units of each toy to make. The constraints in this example are relatively simple:

Figure 37.14 shows the results that are produced by Solver. It shows the product mix that generates $12,365 in profit and uses all resources in their entirety, except for glue.

Figure 37.14. Solver determined how to use the resources to maximize the total profit.

This example demonstrates how to use Solver to help maximize the return on an investment portfolio. A portfolio consist of several investments, each of which has a different yield. In addition, you may have some constraints that involve reducing risk and diversification goals. Without such constraints, a portfolio problem becomes a no-brainer: Put all of your money in the investment with the highest yield.

This example involves a credit union, a financial institution that takes members’ deposits and invests them in loans to other members, bank CDs, and other types of investments. The credit union distributes part of the return on these investments to the members in the form of dividends, or interest on their deposits.

This hypothetical credit union must adhere to some regulations regarding its investments, and the board of directors has imposed some other restrictions. These regulations and restrictions comprise the problem’s constraints. Figure 37.15 shows a workbook set up for this problem.

Figure 37.15. This worksheet is set up to maximize a credit union’s investments, given some constraints.

The following constraints are the ones to which you must adhere in allocating the $5 million portfolio:

The changing cells are C5:C9, and the goal is to maximize the total yield in cell D12. Starting values of 1,000,000 have been entered in the changing cells. When you run Solver with these parameters, it produces the solution shown in Figure 37.16, which has a total yield of 8.59 percent.

Figure 37.16. The results of the portfolio optimization.

However, a total yield of 8.59 percent is not the optimal solution. If you select the Use Automatic Scaling option (in the Solver Options dialog box), Solver will arrive at a solution that has a total yield of 9.25 percent.

This demonstrates that you can’t always trust Solver to arrive at the optimal solution with one try, even when the Solver Results dialog box tells you that All constraints and optimality conditions are satisfied. The best advice? Make sure that you understand Solver well before you entrust it with helping you make major decisions. Try different starting values and adjust the options to see whether Solver can do better.