Microsoft Office Tutorials and References

In Depth Information

**Chapter 35: The Solver Add-In—Optimizer**

The general model is formulated as:

X

n

Maximize

NPV

¼

P
j
X
j

:

j

¼

1

X

n

Subject to

P
j
X
j
<

B

:

j

¼

1

¼

1 if project is selected

X
j

:

¼

0 if project j is not selected

Where:

B the capital available for investment is $7,000 (in $1,000s).

C
j
the capital needed for each project.

Project

1

Project

2

Project

3

Project

4

Project

5

Project

6

Project

7

Project

8

Project

9

Project

10

$628

$352

$1,245

$814

$124

$985

$2,356

$226

$1,650

$714

P
j
¼

the NPV or profit associated with project j.

Project

1

Project

2

Project

3

Project

4

Project

5

Project

6

Project

7

Project

8

Project

9

Project

10

$72

$36

$212

$70

$11

$56

$93

$65

$48

$39

more than the

$7,000 budget allocated. Your decision variables are in column E. (Remember,

they can take either the value of 1 or 0.) The total NPV of your decision will be the

multiplication of the NPV of each project by the decision variable. Multiplying

the NPV by 1 if you choose a project will add its NPV to the total when the decision is

to go ahead with the investment. Otherwise, it will be a 0.

The

If you wanted to invest in all projects you would need $9,094

—

function that multiplies

the NPVs by the decision variables

is

¼

SUMPRODUCT(D2:D11, $E$2:$E$11).

The budget/capital constraint is calculated in column G.

¼

SUMPRODUCT

(G2:G11, $E$2:$E$11)

The objective is to find the correct combination of projects that will

maximize the total NPV. It is subject to the budget constraints. One more thing:

you will have to assign to the decision variables in column E either the value of 0 or 1

(zero or one).

To optimize the choice

—

in other words, to find the optimal solution

—

you can

use Excel Solver. I showed how to activate the Solver in Chapter 34.