Microsoft Office Tutorials and References
In Depth Information
Nonlinear Example Using the Solver
FIGURE 35.1 Capital Budgeting Example Using the Solver
The focus of our problem is cell G12. This is the Objective function and you wish
to maximize it.
1. Click on cell G12 and select the Solver under the Data ribbon. Select the Max
2. In the Solver Parameters menu, select the decision variables in column E (E2:
E11) for the By Changing Cells field.
3. Click on Add for the constraints where the total amount of the investment
to the budget.
4. Click one more time on Add and select the Changing Cells of column E on the
left part of the menu and select the Bin option
making them binary. In other
words, binary means that these cells can take the values of either 0 or 1. See
The resulting Solver menu is shown in Figure 35.3. It has the two constraints, the
binary constraint and the budget.
Before we run the solver we have to click on the Options to activate the Solver
Options menu and check off the Ignore Integer Constraints (this is new for Excel
2010. It is different for the other version as you can see in the appendix to the
chapter). See Figure 35.4.
You can click the Solve button. The end result is shown in Figure 35.5.
The Solver chose for you project 1, 3, 5, 6, 7 and 9. You are going to use $6,988
and your NPV will be $492.
In this example the problem was a linear programming problem. There are times
when the problem is not linear and the relationship is not a straight-linear rela-
tionship. The following example is a case when the relationship is a nonlinear one.
NONLINEAR EXAMPLE USING THE SOLVER
A gym operator hired you to figure out the annual membership he should charge in
order to maximize his revenue. You were able to figure out that his operating costs