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

option.

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

should be

<
¼

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

Figure 35.2.

—

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