|
The multiperiod option creates
a model similar to the capital budgeting problem but explicitly
represents the individual time periods during a specified
time horizon. Projects with non-uniform cash flows are
easily represented on the data form. The most useful new
feature is that cash flow demands can be specified for
the individual periods and feasible portfolios must generate
sufficient cash flows to provide for these requirements.
To create a model choose Multiperiod from the
Portfolio menu. We use an example adapted from class notes
by A. Ruszczynski.
|
Three bonds are available to the investor.
The cash flows for one unit of each bond are shown below.
The negative number for year 0 is the initial investment
and the positive numbers for the remainder of the years
are the returns from the bond. All cash flows occur at
the end of the year.
Investment\Year |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
Bond 1 |
-980 |
0 |
60 |
60 |
60 |
60 |
1060 |
|
|
|
|
|
|
|
|
|
Bond 2 |
-970 |
0 |
65 |
65 |
65 |
65 |
65 |
65 |
65 |
65 |
65 |
65 |
1060 |
|
|
|
Bond 3 |
-1050 |
0 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
75 |
1075 |
Multiple units of each bond may be purchased from an
investment budget of $250,000. Fractional amounts may
be purchased. The portfolio must yield cash flows in the
fifteen years as shown in the table below. The amounts
are in $1000's. Cash is distributed at the end of each
year.
Year |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
Requirements |
11 |
12 |
14 |
15 |
16 |
18 |
20 |
21 |
22 |
24 |
25 |
30 |
31 |
31 |
31 |
The investor is to select the number of units the bonds
to meet the cash requirements and maximize the total cash
available at the end of the last year (year 15). Cash
not invested in the bonds earns a 5% return. |
The Multiperiod menu item calls the dialog shown below.
The number of projects is 3 to represent the three bond alternatives.
The time horizon is 15. Excel financial functions used on the
worksheet limit the time horizon to 28. We choose to maximize
the final cash flow.
In addition to the MARR entry that is used to
calculate net present worth values, we must include a rate for
cash either not used by the initial investment or cash generated
by the investments but not used immediately for cash requirements.
We choose 5% for both quantities.
The Integer Variables checkbox and the
Bound Variables checkbox are left unchecked for this
example.
On clicking OK, the program calls the model formulation
dialog from the Math Programming add-in.The fields
have been automatically set to represent the problem. The experienced
user may change the options on this dialog, but it is probably
better to simply click the OK button. The dialog for the example
is shown below. Although some of the data items do not appear
to be correct, the add-in modifies some features after the model
is created.
The form for the data describing the projects is placed below
the math programming model. Although the returns for the example
are not entirely uniform because of the zero returns for year
1, it is convenient to enter the data for the uniform cash flow
in the range I39 through K42.
The math programming model is shown below after
the solution has been obtained. The model is linear and continuous
so its solution an be obtained with linear programming. The
entries for variables 1, 2 and 3 (columns I, J and K) are automatically
placed on the constraint form. We have replaced the cash flows
in cells I16, J16 and K16 with 0's to reflect the non-uniform
data. The requirements data is entered in column B. The budget
amount is transferred by formula to cell B15. All the yellow
cells contain formulas that link the data to the model, so generally
they should not be changed. Non-uniform cash flows must be entered
directly as constraint coefficients.
Only part of the model is shown. There is a variable
for each cash flow over the horizon so the model proceeds to
the right for 14 more cash flows. The only nonzero objective
coefficient is for the cash flow for year 15. That coefficient
is 1. The solution for the cash flows are below. The final cash
flow value, $146,468, is provided by the optimum portfolio. |