Microsoft Office Tutorials and References

In Depth Information

**Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver**

the goal is to breakeven. You

want to figure out how many units you need to sell, as well as their selling price to

attain your goal. Also, your marketing department is convinced that the highest

price the market can bear is $21.00, an increase of 5 percent above the current price.

What should you do?

Using the Solver you can apply the following procedure:

Go over the problem again using the above data

—

Click on the target cell C20, where the current value is

$140,000.

n

Activate/click the Solver icon under the Data ribbon.

n

Select Value of 0 (the default is Max). In other words you want your Operating

Income to be at least 0.

n

In the Cell: By Changing Cells select cell C3 (Units Sold).

n

Type, (comma).

n

Select cell C4 (Price per Unit). Now both C3 and C4 are the changing cells or the

decision variables.

n

See Figure 34.7.

Since the cells C3 and C4 were named before, they will change in the Solver

Parameters menu. They will reappear later as
Units Sold
and
Price per Unit
.

Remembering the marketing department

s input about market price, you want to

add a constraint for the unit price. See the steps in Figure 34.8.

’

1.
Click on Add constraint.

2.
Add the constraint C4

<
¼

21.

3.
Click on Solve.

The Solver calculated a solution where the number of units to be sold is 55,625

and the price should be $21.00 in order to break even (C20

¼

$0).

See Figure 34.9.

FIGURE 34.6
The Add-Ins Menu