Excel 2010 Bible

Chapter 37: Analyzing Data Using Goal Seeking and Solver

777

FIGURE 37.13

Using Solver to maximize profit when resources are limited.

The number of toys to make is shown in the range B11:F11. These are the values that Solver deter-

mines (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:

l Ensure that production doesn’t use more resources than are available. This can be

accomplished by specifying that each cell in column I is greater than or equal to 0 (zero).

l Ensure that the quantities produced aren’t negative. This can be accomplished by

specifying that each cell in row 11 be greater than or equal to 0.

Figure 37.14 shows the results that are produced by Solver. It shows the product mix that gener-

ates $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.