Excel 2019 Bible

Chapter 32: Analyzing Data Using Goal Seeking and Solver

3232

You don’t need an MBA to realize that the greatest profit comes from Product C. Therefore,

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 com-

pany has some constraints that must be met:

■ (^) The combined production capacity is 300 total units.
■ The company needs 50 units of Product A to fill an existing order.
■ (^) The company needs 40 units of Product B to fill an anticipated order.
■ Because the market for Product C is relatively limited, the company doesn’t want to
produce more than 40 units of this product.
These four constraints make the problem more realistic and a bit more challenging. In fact,
it’s a perfect problem for Solver.
Here are the basic steps for using Solver:

1. Set up the worksheet with values and formulas. Make sure you format cells logi-
   cally; for example, if you can’t produce partial units of your products, format those
   cells to contain numbers with no decimal values.


2. Choose Data ➪ Analyze ➪ Solver. The Solver Parameters dialog box appears.


3. Specify the target cell (also known as the objective).


4. Specify the range that contains the changing cells.


5. Specify the constraints.


6. Change the Solver options, if necessary.


7. Click Solve, and let Solver go to work.

To start Solver to tackle this example, choose Data ➪ Analyze ➪ Solver. The Solver

Parameters dialog box appears. Figure 32.5 shows this dialog box, set up to solve the

problem.

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

products.

1. Enter D6 into the Set Objective field of the Solver Parameters dialog box.


2. Because the objective is to maximize this cell, select the Max option button.


3. Specify the changing cells (which are in the range B3:B5) in the By Changing
   Variable Cells field. The next step is to specify the constraints on the prob-
   lem. The constraints are added one at a time and appear in the Subject to the
   Constraints list.


4. To add a constraint, click the Add button. The Add Constraint dialog box, shown
   in Figure 32.6, appears. This dialog box has three parts: a Cell Reference value, an
   operator, and a Constraint value.