changing cells are B4 and C4, which hold the number of units sold for each product.
For constraints, you might want to add that both the product profit cells (B12 and C12) should also be 0.
Loading the Solver add-in
An add-in is software that adds one or more features to Excel. Installing add-ins gives you additional Excel features that aren’t available on the Ribbon by default. Bundled add-in software is included with Excel but isn’t automatically installed when you install Excel. Several add-ins come standard with Excel, including Solver.
You install bundled add-ins by using the Excel Options dialog box; you can find them in the Add-Ins section. After they're installed, add-ins are available right away. They usually appear on a tab related to their function. For example, Solver appears on the Data tab.
Here are the steps to follow to load the Solver add-in:
1 Choose File ⇒ Options.The Excel Options dialog box appears.
2 Choose Add-Ins.
3 In the Manage list, select Excel Add-Ins and then select Go.Excel displays the Add-Ins dialog box.
4 Select the Solver Add-In check box.
5 Click OK.Excel adds a Solver button to the Data tab’s Analysis group.
Optimizing a result with Solver
You set up your Solver model by using the Solver Parameters dialog box. In the Set Objective field, you specify the objective cell. You use the options in the To group to tell Solver what you want from the objective cell: the maximum possible value, the minimum possible value, or a specific value. Finally, you use the By Changing Variable Cells box to specify the cells that Solver can use to plug in values to optimize the result.
With your Solver-ready worksheet model prepared, here are the steps to follow to find an optimal result for your model using Solver:
1 Choose Data ⇒ Solver.Excel opens the Solver Parameters dialog box.
2 In the Set Objective box, enter the address of your model’s objective cell.In the example in the “When should you use Solver?” section, previously in the chapter (refer to Figure 2-11), the objective cell is B14. Note that if you click the cell to enter it, Solver automatically enters an absolute cell address (for example, $B$14 instead of B14). Solver works fine either way.
3 In the To group, select an option:Max: Returns the maximum possible value.Min: Returns the minimum possible value.Value Of: Enter a number to set the objective cell to that number.For the example model, I selected Value Of and entered 0 in the text box.
4 In the By Changing Variable Cells box, enter the addresses of the cells you want Solver to change while it looks for a solution.In the example, the changing cells are B4 and C4. Figure 2-12 shows the completed Solver Parameters dialog box. (What about constraints? I talk about those in the next section.)FIGURE 2-12: The completed Solver Parameters dialog box.
5 Click Solve.Solver gets down to business. As Solver works on the problem, the Show Trial Solution dialog boxes might show up one or more times.
6 In any Show Trial Solution dialog box that appears, click Continue to move things along.When the optimization is complete, Excel displays the Solver Results dialog box, shown in Figure 2-13.
7 Select the Keep Solver Solution option.If you don't want to accept the result, select the Restore Original Values option instead.
8 Click OK.
Answer: Displays information about the model’s objective cell, variable cells, and constraints. For the objective cell and variable cells, Solver shows the original and final values.
Sensitivity: Attempts to show how sensitive a solution is to changes in the model’s formulas. The layout of the Sensitivity report depends on the type of model you’re using.
Limits: Displays the objective cell and its value, as well as the variable cells and their addresses, names, and values.
Simplex LP: Use if your worksheet model is linear. In the simplest possible terms, a linear model is one in which the variables are not raised to any powers and none of the so-called transcendent functions — such as SIN and COS — are used.
GRG Nonlinear: Use if your worksheet model is nonlinear and smooth. In general terms, a smooth model is one in which a graph of the equation doesn’t show sharp edges or breaks.
Evolutionary: Use if your worksheet model is nonlinear and nonsmooth.
Do you have to worry about any of this? Almost certainly not. Solver defaults to using GRG Nonlinear, and that should work for almost anything you do with Solver.
FIGURE 2-13: The Solver Results dialog box and the solution to the break-even problem.
Adding constraints to Solver
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 can’t be a negative number, and your advertising costs might be restricted to 10 percent of total expenses.
Similarly, suppose that you’re running a break-even analysis on two products, as I discuss in the preceding section. If you run the optimization without any restrictions, Solver might reach a total profit of 0 by setting one product at a slight loss and the other at a slight profit, where the loss and profit cancel each other out. In fact, if you take a close look at Figure 2-13, this is exactly what Solver did. To get a true break-even solution, you might prefer to see both product profit values as 0.
Such restrictions and conditions are examples of what Solver calls constraints. Adding constraints tells Solver to find a solution so
