| Most problems in business aren't as easy as the Goal Seek examples. You'll usually face formulas with at least two and sometimes dozens of variables. Often a problem will have more than one solution, and your challenge will be to find the optimal solution (that is, the one that maximizes profit, or minimizes costs, or matches other criteria). For these bigger challenges, you need a more muscular tool. Excel has just the answer: Solver. Solver is a sophisticated optimization program that enables you to find the solutions to complex problems that would otherwise require high-level mathematical analysis. Solver, like Goal Seek, uses an iterative method to perform its magic. This means that Solver tries a solution, analyzes the results, tries another solution, and so on. However, this cyclic iteration isn't just guesswork on Solver's part. The program looks at how the results change with each new iteration and, through some sophisticated mathematical trickery, it can tell (usually) in what direction it should head for the solution. However, the fact that Goal Seek and Solver are both iterative doesn't make them equal. In fact, Solver brings a number of advantages to the table: Solver enables you to specify multiple adjustable cells. You can use up to 200 adjustable cells in all. Solver enables you to set up constraints on the adjustable cells. For example, you can tell Solver to find a solution that not only maximizes profit, but also satisfies certain conditions, such as achieving a gross margin between 20% and 30%, or keeping expenses less than $100,000. These conditions are constraints on the solution. Solver seeks not only a desired result (the "goal" in Goal Seek), but also the optimal one. This means that you can find a solution that is the maximum or minimum possible. For complex problems, Solver can generate multiple solutions. You then can save these solutions under different scenarios, as described later in this chapter.
Solver is a powerful tool that most Excel users don't need. It would be overkill, for example, to use Solver to compute net profit given fixed revenue and cost figures. Many problems, however, require nothing less than the Solver approach. These problems cover many different fields and situations, but they all have the following characteristics in common: They have a single target cell that contains a formula you want to maximize, minimize, or set to a specific value. This formula could be a calculation, such as total transportation expenses or net profit. The target cell formula contains references to one or more changing cells (also called unknowns or decision variables). Solver adjusts these cells to find the optimal solution for the target cell formula. These changing cells might include items such as units sold, shipping costs, or advertising expenses. Optionally, there are one or more constraint cells that must satisfy certain criteria. For example, you might require that advertising be less than 10% of total expenses or that the discount to customers be a number between 40% and 60%.
What types of problems exhibit these kinds of characteristics? A surprisingly broad range, as the following list shows: The transportation problemThis problem involves minimizing shipping costs from multiple manufacturing plants to multiple warehouses while meeting demand. The allocation problemThis problem requires minimizing employee costs while maintaining appropriate staffing requirements. The product mix problemThis problem requires generating the maximum profit with a mix of products while still meeting customer requirements. You solve this problem when you sell multiple products with different cost structures, profit margins, and demand curves. The blending problemThis problem involves manipulating the materials used for one or more products to minimize production costs, meet consumer demand, and maintain a minimum level of quality. Linear algebraThis problem involves solving sets of linear equations.
Loading Solver Solver is an add-in to Microsoft Excel, so you'll need to load Solver before you can use it. Choose Tools, Add-Ins to display the Add-Ins dialog box. In the Add-Ins Available list, activate the Solver Add-In check box and then click OK. Excel installs the add-in and adds a Solver command to the Tools menu. Using Solver Suppose your concern is to find a break-even point for a new product. The break-even point is the number of units that need to be sold to produce a profit of 0. This is a straightforward Goal Seek problem: you change the number of units until your profit formula reaches the value of 0. But what if you need to find the break-even point for two products? The goal in this case is to compute the number of units to sell for both products so that the total profit is 0. The most obvious way to proceed is to use Goal Seek to determine the break-even points for each product separately. Figure 2.17 shows the results. Figure 2.17. The break-even points for two products (using separate Goal Seek calculations on the Product Profit cells). 
This method works, but the problem is that the two products don't exist in a vacuum. For example, cost savings will be associated with each product because of joint advertising campaigns, combined shipments to customers (larger shipments usually mean better freight rates), and so on. To allow for this, you need to reduce the cost for each product by a factor related to the number of units sold by the other product. In practice, this would be difficult to estimate, but to keep things simple, I'll use the following assumption: The costs for each product are reduced by $1 for every unit sold of the other product. For instance, if the Langstrom wrench sells 10,000 units, the costs for the Finley sprocket are reduced by $10,000. I'll make this adjustment in the Variable Costs formula. For example, the formula that calculates variable costs for the Finley sprocket (cell B8) becomes the following: =B4 * B7 - C4
Similarly, the formula that calculates variable costs for the Langstrom wrench (cell C8) becomes the following: =C4 * C7 - B4
By making this change, you move out of Goal Seek's territory. The Variable Costs formulas now have two variables: the units sold for the Finley sprocket and the units sold for the Langstrom wrench. I've changed the problem from one of two single-variable formulas, which Goal Seek can easily handle (individually), to a single formula with two variables, which is the terrain of Solver. To see how Solver handles such a problem, follow these steps: 1. | Choose Tools, Solver. Excel displays the Solver Parameters dialog box.
| 2. | In the Set Target Cell text box, enter a reference to the target cellthat is, the cell with the formula you want to optimize. In the example, you enter B14.
| 3. | In the Equal To section, select the appropriate option button: Select Max to maximize the target cell, select Min to minimize it, or select Value Of to solve for a particular value (in which case, you also need to enter the value in the text box provided). In the example, you activate Value Of and enter 0 into the text box.
| 4. | Use the By Changing Cells box to enter the cells you want Solver to change while it looks for a solution. In the example, you enter B4,C4. Figure 2.18 shows the completed Solver Parameters dialog box for the example (note that Solver changes all cell addresses to the absolute reference format).
Figure 2.18. Use the Solver Parameters dialog box to set up the problem for Solver. 
The By Changing Cells Text Box You can enter a maximum of 200 cells into the By Changing Cells box. Also, the Guess button enters into the By Changing Cells text box all the nonformula cells that are directly or indirectly referenced by the target cell's formula. |
| 5. | Click Solve. (I discuss constraints in the next section.) Solver works on the problem and then displays the Solver Results dialog box, which tells you whether it found a solution.
| 6. | If Solver found a solution that you want to use, click the Keep Solver Solution option and then click OK. If you don't want to accept the new numbers, click Restore Original Values and click OK, or just click Cancel.
|
Figure 2.19 shows the results for the example. As you can see, Solver has produced a total profit of 0 by running one product (the Langstrom wrench) at a slight loss and the other at a slight profit. Although this is certainly a solution, it's not really the one you want. Ideally, for a true break-even analysis, both products should end up with a product profit of 0. The problem is that you didn't tell Solver that was the way you wanted the problem solved. In other words, you didn't set up any constraints. Figure 2.19. When Solver finishes its calculations, it displays the Solver Results dialog box and enters the solution (if it found one) into the worksheet cells. 
Adding Constraints The real world puts restrictions and conditions on formulas. A factory might have a maximum capacity of 10,000 units a day, the number of employees in a company has to be a number greater than or equal to zero (negative employees would really reduce staff costs, but nobody has been able to figure out how to do it yet), and your advertising costs might be restricted to 10% of total expenses. All are examples of what Solver calls constraints. Adding constraints tells Solver to find a solution so that these conditions are not violated. To find the best solution for the break-even analysis, you need to tell Solver to optimize both Product Profit formulas to 0. The following steps show you how to do this: Returning to the Worksheet If Solver's completion message is still onscreen from the last section, select Cancel to return to the worksheet without saving the solution. |
1. | Choose Tools, Solver to display the Solver Parameters dialog box. Solver reinstates the options you entered the last time you used Solver.
| 2. | To add a constraint, click Add. Excel displays the Add Constraint dialog box.
| 3. | In the Cell Reference box, enter the cell you want to constrain. For the example, you enter cell B12 (the Product Profit formula for the Finley sprocket).
| 4. | Use the drop-down list in the middle of the dialog box to select the operator you want to use. The list contains several comparison operators for the constraintless than or equal to (<=), equal to (=), and greater than or equal to (>=)as well as two other data type operatorsinteger (int) and binary (bin). For the example, select the equal to operator (=).
Constraints as Binary or Integer Values Use the int (integer) operator when you need a constraint, such as total employees, to be an integer value instead of a real number. Use the bin (binary) operator when you have a constraint that must be either trUE or FALSE (or 1 or 0). |
| 5. | If you chose a comparison operator in step 4, use the Constraint box to enter the value by which you want to restrict the cell. For the example, enter 0. Figure 2.20 shows the completed dialog box for the example.
Figure 2.20. Use the Add Constraint dialog box to specify the constraints you want to place on the solution. 
| 6. | If you want to enter more constraints, click Add and repeat steps 35. For the example, you also need to constrain cell C12 (the Product Profit formula for the Langstrom wrench) so that it, too, equals 0.
| 7. | When you're done, click OK to return to the Solver Parameters dialog box. Excel displays your constraints in the Subject to the Constraints list box.
Changing or Deleting Constraints You can add a maximum of 100 constraints. Also, if you need to make a change to a constraint before you begin solving, click the constraint in the Subject to the Constraints list box, click Change, and then make your adjustments in the Change Constraint dialog box that appears. If you want to delete a constraint that you no longer need, click it and then click the Delete button. |
| 8. | Click Solve. Solver again tries to find a solution, but this time it uses your constraints as guidelines.
|
Figure 2.21 shows the results of the break-even analysis after adding the constraints. As you can see, Solver was able to find a solution in which both product margins are 0. Figure 2.21. The solution to the break-even analysis after adding the constraints. 
|
