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
Search JabSto ::




Custom Search