Microsoft Office Tutorials and References
In Depth Information
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.
Search JabSto ::

Custom Search