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




Custom Search