Part V: Analyzing Data with Excel
766
FIGURE 37.3Use 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,
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 con-
straints that must be met:l (^) The combined production capacity is 300 total units per day.
l The company needs 50 units of Product A to fill an existing order.
l (^) The company needs 40 units of Product B to fill an anticipated order.
l Because the market for Product C is relatively limited, the company doesn’t want to pro-
duce 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.
I go into more detail in a moment, but here is the basic procedure for using Solver:
- Set up the worksheet with values and formulas. Make sure that 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. - Choose Data ➪ Analysis ➪ Solver to bring up the Solver Parameters dialog box.
- Specify the target cell.
- Specify the range that contains the changing cells.
- Specify the constraints.
- Change the Solver options, if necessary.
- Let Solver solve the problem.
To start Solver to tackle this example, choose Data ➪ Analysis ➪ Solver. Excel displays its Solver
Parameters dialog box, shown in Figure 37.4.