Linear Programming Case study
Content
1) Formulation of the Problem as a Linear Programming Model
Let xi = the amount of money invested in each of the potential investments in € ,
where (i=1,2,…8)
x1 = the amount of money invested in Savings Account
x2 = the amount of money invested in ‘Belfort’ Stock
x3 = the amount of money invested in ‘Bayside Stock’
x4 = the amount of money invested in ‘Goodies Stock’
x5 = the amount of money invested in Basic Insurance program
x6 = the amount of money invested in Government bonds
x7 = the amount of money invested in ‘Intra CO’ Stock
x8 = the amount of money invested in ‘GR’ Stock
Min Z
Z= 0x1 + 0x2 + 0,25x3 + 0,3x4 + 0,2x5 + 0,15x6 +0,65x7 + 0,40x8
The above investment strategy minimizes the total risk value.
Constraints:
x1 + x2 + x3 + x4 + x5 + x6 +x7 + x8 = 100.000
0,04x1 +0,052x2 +0,071x3 + 0,1x4 + 0,082x5 + 0,065x6 +0,2x7 + 0,125x8 ≥ 7.500
x1 + x2 + x5 +x7 ≥ 50.000
x1 + x3 + x4 +x7 + x8 ≥ 40.000
x1 + x2 ≤ 30.000
x1 , x2 , x3 , x4 , x5 , x6 ,x7 , x8 ≥ 0
Katrakaza Sevina
Σελίδα 1
2) Optimal Solution of the Problem – Using Excel’s Solver tool
Answer Report (my Excel was in Greek, so unfortunately the answer report of
Solver is also in Greek)
Target Cell (Min)
Κελί προορισμού
(Ελάχιστο)
Κε Όνομ
Αρχική
λί
α
τιμή
$J$
4
0
Τελική
τιμή
16266,66
667
Adjustable Cells
Ρυθμιζόμενα
κελιά
Αρχική
τιμή
Τελική
τιμή
17333,33
333
12666,66
667
Κελί
Όνομα
$B$3
x1
0
$C$3
$D$
3
x2
0
x3
0
$E$3
x4
0
$F$3
$G$
3
$H$3
$I$3
x5
0
0
22666,66
667
47333,33
333
x6
x7
x8
0
0
0
0
0
0
Constraints
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
Constrai
nts
$J$
8
$J$
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
333 $8
40000 $J$9>=$L
Σελίδα 2
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
υποχρεωτικ
ός
Υποχρεωτικ
Απόκλισ
η
0
0
27333,33
333
0
9
$J$
10
$9
$J$10<=$
30000 L$10
ός
Υποχρεωτικ
ός
0
Sensitivity Report
Adjustable Cells
Ρυθμιζόμενα κελιά
Τελική
Κε
λί
$B
$3
$C
$3
$D
$3
$E$
3
$F$
3
$G
$3
$H
$3
$I$
3
Όνο
μα
x1
x2
x3
τιμή
17333,3
33
12666,6
67
x5
0
22666,6
67
47333,3
33
x6
0
x7
0
x8
0
x4
Μειωμέν
ο
κόστος
Αντικειμε
νικός
συντελεσ
τής
Επιτρεπό
μενη
Επιτρεπό
μενη
μείωση
0
0
αύξηση
0,0117647
06
0
0,046666
667
0
0,005
0,25
0
0,3
0
0,006666
667
0,016666
667
0,016666
667
0,2
1E+30
0,0038461
54
0,0042553
19
0,15
1E+30
0,65
1E+30
0,4
1E+30
0,005
0,0066666
67
0,0166666
67
0,0166666
67
Περιορι
σμός
R.H.
Side
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
μείωση
4634,146
341
520
6341,463
415
380
0,005
0,0117647
06
0,0466666
67
0,0117647
06
Constraints
Περιορισμοί
Κελ
ί
$J$
6
$J$
Όνομα
Constra
ints
Katrakaza Sevina
Τελική
Σκιώδης
τιμή
τιμή
100000
7500
0,073333
333
3,333333
Σελίδα 3
100000
7500
7
$J$
8
$J$
9
$J$
10
333
77333,
333
0
50000
40000
0,04
40000
30000
-0,1
30000
27333,33
333
21111,11
111
17333,33
333
1E+30
28888,88
889
6333,333
333
3) The Optimal Investment Strategy that the consultant should
suggest & The Total Risk Value with this Strategy
The optimal investment Strategy that the consultant should suggest can be found on
the Answer Report. The solution is given in the Final Values (Τελικές Τιμές), as we can
see underneath with red color.
Target Cell (Min)
Κελί προορισμού
(Ελάχιστο)
Κε Όνομ
Αρχική
λί
α
τιμή
$J$
4
0
Τελική
τιμή
16266,6
6667
Adjustable Cells
Ρυθμιζόμενα
κελιά
Κελί
Όνομα
Katrakaza Sevina
Αρχική
τιμή
Τελική
τιμή
Σελίδα 4
17333,3
3333
12666,6
6667
$B$3
x1
0
$C$3
$D$
3
x2
0
x3
0
$E$3
x4
0
$F$3
$G$
3
$H$3
$I$3
x5
0
0
22666,6
6667
47333,3
3333
x6
x7
x8
0
0
0
0
0
0
The optimal investment strategy is 17.333,33€ invested in Savings Account,
12.666,67€ invested in ‘Belfort Stock’, 22.666,67€ invested in ‘Goodies Stock’, and
47.333,33€ invested in Basic Insurance program.
The total risk value with this Strategy is 16.266,67€.
4) Suggestion in order for the consultant to invest in Bayside or
in Intra CO’s stocks
If we go to the Answer report, we can easily see that the investment programs x 3 and
x7 are not being suggested (Bayside and Intra CO’s).
If we go to the Sensitivity Report, we can see that in order to propose to invest to
Bayside stock, the risk factor should be decreased by 0,0467, so the factor should be
less than 0,2033, or 20,33%.
Similarly, in order to propose to invest to Intra Co’s stock, the risk factor should be
decreased by 0,0167, so the factor should be less than 0,6333, or 63,33%.
Katrakaza Sevina
Σελίδα 5
Ρυθμιζόμενα κελιά
Τελική
Κε
λί
$B
$3
$C
$3
$D
$3
$E$
3
$F$
3
$G
$3
$H
$3
$I$
3
Όνο
μα
x1
x2
x3
τιμή
17333,3
33
12666,6
67
x5
0
22666,6
67
47333,3
33
x6
0
x7
0
x8
0
x4
Μειωμέν
ο
κόστος
Αντικειμε
νικός
συντελεσ
τής
Επιτρεπό
μενη
Επιτρεπό
μενη
μείωση
0
0
αύξηση
0,0117647
06
0
0,04666
6667
0
0,005
0,25
0
0,3
0
0,006666
667
0,01666
6667
0,016666
667
0,2
1E+30
0,0038461
54
0,0042553
19
0,15
1E+30
0,65
1E+30
0,4
1E+30
0,005
0,0117647
06
0,0466666
67
0,0117647
06
0,005
0,0066666
67
0,0166666
67
0,0166666
67
5) The Change in the total risk value and in the final
investment strategy, if the consultant wishes to invest at
least 30% of the available budget in immediately liquid
investments.
Katrakaza Sevina
Σελίδα 6
The 10% reduction in the available budget in immediately liquid investment, should
change our constraint of 40.000€ to 30.000€, so we would have a change of 10.000€.
We go to the Sensitivity report, and we can see that we are in the range of feasibility
(allowable decrease 28.888.89€), so the shadow price is valid, and the total risk value
will be reduced by 10.000*0,04=400€ to an new Z= 15.866,67€.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
However, as we can see the constraint is binding, so the final investment strategy
changes. We have to run solver again to find the new investment strategy.
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
Constrai
nts
$J$
8
$J$
9
$J$
10
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
333 $8
$J$9>=$L
40000 $9
$J$10<=$
30000 L$10
Σελίδα 7
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
υποχρεωτικ
ός
Υποχρεωτι
κός
Υποχρεωτικ
ός
Απόκλισ
η
0
0
27333,33
333
0
0
6) The consultant wants to know if he could follow the same
investment strategy by increasing the % investment of the
available budget in one of the A-rated or immediate liquid,
or zero-risk investments.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
Τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
The consultant wants to know if he can increase the amount of money available to
invest in one of the investment groups, the A-rated, immediate liquid or zero –risk. As
we can see from the sensitivity report, we have with red color the allowable increase of
the 3 constraints. If the increase is in the range of feasibility:
For the A-rated investments the allowable increase is 27.333,33€.
For the Immediately liquid investement, the allowable increase is 21.111,11€.
For the Zero-risk investment the allowable increase is 17.333,33€.
If they are in the feasibility range, then the shadow price is valid, and we can calculate
the total risk value by adding the result of ‘ Increase*Shadow price’.
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
$J$
Constrai
nts
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
Σελίδα 8
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
Απόκλισ
η
0
0
27333,33
8
$J$
9
$J$
10
333 $8
$J$9>=$L
40000 $9
$J$10<=$
30000 L$10
υποχρεωτι
κός
Υποχρεωτι
κός
Υποχρεωτι
κός
333
0
0
However, as we can see the A-rated investment is not binding, and its shadow price is
0, so for A-rated investments the investment strategy will not change if the increase is
within 27.333,33€.
In order to have the final investment strategy for the other 2 investments, we have to
run again the solver.
7) The change in the total risk factor and in the final
investment strategy, if the consultant decides to invest
25.000€ in zero risk investments.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
Μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
If we go again to the sensitivity report, we can see that the constraint referring to the
zero-risk investments, and we can also see that it is binding. If we decrease the zero
risk investment from 30.000€ to 25.000€, then we must check if the 5.000€ within the
feasibility range of the allowable decrease (6.333,33€). As we can see they are within
the range. As a consequence the total risk value will increase to 5.000*0,1=500€, so
Z=16.766,67€.
Again, since the constraint is binding, we should run again the solver in order to find
which will be the change to the final investment strategy.
Katrakaza Sevina
Σελίδα 9
8) The changes in the total risk value and in the investment
strategy, if the risk factor for Goodie’s stock becomes 26%.
Ρυθμιζόμενα κελιά
Τελική
Κε
λί
$B
$3
$C
$3
$D
$3
$E$
3
$F$
3
$G
$3
$H
$3
$I$
3
Όνο
μα
x1
x2
x3
τιμή
17333,3
33
12666,6
67
x5
0
22666,6
67
47333,3
33
x6
0
x7
0
x8
0
x4
Μειωμέν
ο
κόστος
Αντικειμε
νικός
συντελεσ
τής
Επιτρεπό
μενη
Επιτρεπό
μενη
μείωση
0
0
αύξηση
0,0117647
06
0
0,046666
667
0
0,005
0,25
0
0,3
0
0,006666
667
0,016666
667
0,016666
667
0,2
1E+30
0,003846
154
0,0042553
19
0,15
1E+30
0,65
1E+30
0,4
1E+30
0,005
0,0117647
06
0,0466666
67
0,011764
706
0,005
0,0066666
67
0,0166666
67
0,0166666
67
The Goodies stock is the x4. If instead of 0,2, the new risk factor would be 0,26, we
must check for a 0,04 decrease, which as we can see is not in the feasibility range. As
a consequence we do not know either the change of the total risk value, or the final
investment strategy, and we have to run the solver again.
9) The change in the total risk factor and in the investment
strategy, if the amount invested in A-rated investments is
increased by 10%.
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
Constrai
nts
$J$
8
$J$
9
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
333 $8
$J$9>=$L
40000 $9
Σελίδα 10
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
υποχρεωτι
κός
Υποχρεωτικ
ός
Απόκλισ
η
0
0
27333,33
333
0
$J$
10
$J$10<=$
30000 L$10
Υποχρεωτικ
ός
0
As we can see the A-rated investment is not binding. We are discussing of an increase
of 10%, so the ‘at least 50.000€’budget should have an increase of 5.000€.
As we can see we are within feasibility range, and concerning the binding term, and the
allowable increase as we can see underneath from the sensitivity report.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
Τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
Αύξηση
μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
As a consequence, there will be no change either in the total risk value, or the final
investment strategy.
We can also see in order to check if we are correct, that the optimal solution, is
proposing to invest 77.333€ to A rated investments, so there is no change in the
solution.
Katrakaza Sevina
Σελίδα 11
10)
Assume that the consultant is thinking to add in the
portfolio an extra potential investment with a 9% annual
expected return, A-quality rating and immediate liquidity.
What is the risk factor that would be recommended in order
to be possible for this new investment to be chosen?
We shall name the new investment x9.We go to the sensitivity report of the affected
constraints:
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
-
-
-
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
Τιμή
100000
0,07333
3333
3,33333
3333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
Μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
The new investment would affect the 1st constraint, since it will be added to the
total budget of 100.000€. The decrease of the RHS is shadow price * x 9 =
-0,073x9
It also affects the 2nd constraint, since it should have annual return if it was
chosen. Its annual return is 9%, so it would be 0,09 decrease * shadow price =
0,09*3,33=0,2997x9
It is an A-rated investment so the 3rd constraint is affected, but the shadow price
is 0, so the total risk factor will not change.
It is also an immediate liquidity investment so the 4 th constraint changes as well,
and the RHS constraint will decrease by shadow price* x 9 = 0,04x9
Since the change in the total risk value can be calculated using the shadow
prices for each constraint, we can find the risk factor for the new investment:
-0,073x9 + 0,2997x9 + 0 + 0,04x9 = 0,2667.
So the risk factor of the new investment that is recommended, in order for it to
be chosen in the portfolio is 26,67%.
Katrakaza Sevina
Σελίδα 12
Let xi = the amount of money invested in each of the potential investments in € ,
where (i=1,2,…8)
x1 = the amount of money invested in Savings Account
x2 = the amount of money invested in ‘Belfort’ Stock
x3 = the amount of money invested in ‘Bayside Stock’
x4 = the amount of money invested in ‘Goodies Stock’
x5 = the amount of money invested in Basic Insurance program
x6 = the amount of money invested in Government bonds
x7 = the amount of money invested in ‘Intra CO’ Stock
x8 = the amount of money invested in ‘GR’ Stock
Min Z
Z= 0x1 + 0x2 + 0,25x3 + 0,3x4 + 0,2x5 + 0,15x6 +0,65x7 + 0,40x8
The above investment strategy minimizes the total risk value.
Constraints:
x1 + x2 + x3 + x4 + x5 + x6 +x7 + x8 = 100.000
0,04x1 +0,052x2 +0,071x3 + 0,1x4 + 0,082x5 + 0,065x6 +0,2x7 + 0,125x8 ≥ 7.500
x1 + x2 + x5 +x7 ≥ 50.000
x1 + x3 + x4 +x7 + x8 ≥ 40.000
x1 + x2 ≤ 30.000
x1 , x2 , x3 , x4 , x5 , x6 ,x7 , x8 ≥ 0
Katrakaza Sevina
Σελίδα 1
2) Optimal Solution of the Problem – Using Excel’s Solver tool
Answer Report (my Excel was in Greek, so unfortunately the answer report of
Solver is also in Greek)
Target Cell (Min)
Κελί προορισμού
(Ελάχιστο)
Κε Όνομ
Αρχική
λί
α
τιμή
$J$
4
0
Τελική
τιμή
16266,66
667
Adjustable Cells
Ρυθμιζόμενα
κελιά
Αρχική
τιμή
Τελική
τιμή
17333,33
333
12666,66
667
Κελί
Όνομα
$B$3
x1
0
$C$3
$D$
3
x2
0
x3
0
$E$3
x4
0
$F$3
$G$
3
$H$3
$I$3
x5
0
0
22666,66
667
47333,33
333
x6
x7
x8
0
0
0
0
0
0
Constraints
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
Constrai
nts
$J$
8
$J$
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
333 $8
40000 $J$9>=$L
Σελίδα 2
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
υποχρεωτικ
ός
Υποχρεωτικ
Απόκλισ
η
0
0
27333,33
333
0
9
$J$
10
$9
$J$10<=$
30000 L$10
ός
Υποχρεωτικ
ός
0
Sensitivity Report
Adjustable Cells
Ρυθμιζόμενα κελιά
Τελική
Κε
λί
$B
$3
$C
$3
$D
$3
$E$
3
$F$
3
$G
$3
$H
$3
$I$
3
Όνο
μα
x1
x2
x3
τιμή
17333,3
33
12666,6
67
x5
0
22666,6
67
47333,3
33
x6
0
x7
0
x8
0
x4
Μειωμέν
ο
κόστος
Αντικειμε
νικός
συντελεσ
τής
Επιτρεπό
μενη
Επιτρεπό
μενη
μείωση
0
0
αύξηση
0,0117647
06
0
0,046666
667
0
0,005
0,25
0
0,3
0
0,006666
667
0,016666
667
0,016666
667
0,2
1E+30
0,0038461
54
0,0042553
19
0,15
1E+30
0,65
1E+30
0,4
1E+30
0,005
0,0066666
67
0,0166666
67
0,0166666
67
Περιορι
σμός
R.H.
Side
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
μείωση
4634,146
341
520
6341,463
415
380
0,005
0,0117647
06
0,0466666
67
0,0117647
06
Constraints
Περιορισμοί
Κελ
ί
$J$
6
$J$
Όνομα
Constra
ints
Katrakaza Sevina
Τελική
Σκιώδης
τιμή
τιμή
100000
7500
0,073333
333
3,333333
Σελίδα 3
100000
7500
7
$J$
8
$J$
9
$J$
10
333
77333,
333
0
50000
40000
0,04
40000
30000
-0,1
30000
27333,33
333
21111,11
111
17333,33
333
1E+30
28888,88
889
6333,333
333
3) The Optimal Investment Strategy that the consultant should
suggest & The Total Risk Value with this Strategy
The optimal investment Strategy that the consultant should suggest can be found on
the Answer Report. The solution is given in the Final Values (Τελικές Τιμές), as we can
see underneath with red color.
Target Cell (Min)
Κελί προορισμού
(Ελάχιστο)
Κε Όνομ
Αρχική
λί
α
τιμή
$J$
4
0
Τελική
τιμή
16266,6
6667
Adjustable Cells
Ρυθμιζόμενα
κελιά
Κελί
Όνομα
Katrakaza Sevina
Αρχική
τιμή
Τελική
τιμή
Σελίδα 4
17333,3
3333
12666,6
6667
$B$3
x1
0
$C$3
$D$
3
x2
0
x3
0
$E$3
x4
0
$F$3
$G$
3
$H$3
$I$3
x5
0
0
22666,6
6667
47333,3
3333
x6
x7
x8
0
0
0
0
0
0
The optimal investment strategy is 17.333,33€ invested in Savings Account,
12.666,67€ invested in ‘Belfort Stock’, 22.666,67€ invested in ‘Goodies Stock’, and
47.333,33€ invested in Basic Insurance program.
The total risk value with this Strategy is 16.266,67€.
4) Suggestion in order for the consultant to invest in Bayside or
in Intra CO’s stocks
If we go to the Answer report, we can easily see that the investment programs x 3 and
x7 are not being suggested (Bayside and Intra CO’s).
If we go to the Sensitivity Report, we can see that in order to propose to invest to
Bayside stock, the risk factor should be decreased by 0,0467, so the factor should be
less than 0,2033, or 20,33%.
Similarly, in order to propose to invest to Intra Co’s stock, the risk factor should be
decreased by 0,0167, so the factor should be less than 0,6333, or 63,33%.
Katrakaza Sevina
Σελίδα 5
Ρυθμιζόμενα κελιά
Τελική
Κε
λί
$B
$3
$C
$3
$D
$3
$E$
3
$F$
3
$G
$3
$H
$3
$I$
3
Όνο
μα
x1
x2
x3
τιμή
17333,3
33
12666,6
67
x5
0
22666,6
67
47333,3
33
x6
0
x7
0
x8
0
x4
Μειωμέν
ο
κόστος
Αντικειμε
νικός
συντελεσ
τής
Επιτρεπό
μενη
Επιτρεπό
μενη
μείωση
0
0
αύξηση
0,0117647
06
0
0,04666
6667
0
0,005
0,25
0
0,3
0
0,006666
667
0,01666
6667
0,016666
667
0,2
1E+30
0,0038461
54
0,0042553
19
0,15
1E+30
0,65
1E+30
0,4
1E+30
0,005
0,0117647
06
0,0466666
67
0,0117647
06
0,005
0,0066666
67
0,0166666
67
0,0166666
67
5) The Change in the total risk value and in the final
investment strategy, if the consultant wishes to invest at
least 30% of the available budget in immediately liquid
investments.
Katrakaza Sevina
Σελίδα 6
The 10% reduction in the available budget in immediately liquid investment, should
change our constraint of 40.000€ to 30.000€, so we would have a change of 10.000€.
We go to the Sensitivity report, and we can see that we are in the range of feasibility
(allowable decrease 28.888.89€), so the shadow price is valid, and the total risk value
will be reduced by 10.000*0,04=400€ to an new Z= 15.866,67€.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
However, as we can see the constraint is binding, so the final investment strategy
changes. We have to run solver again to find the new investment strategy.
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
Constrai
nts
$J$
8
$J$
9
$J$
10
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
333 $8
$J$9>=$L
40000 $9
$J$10<=$
30000 L$10
Σελίδα 7
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
υποχρεωτικ
ός
Υποχρεωτι
κός
Υποχρεωτικ
ός
Απόκλισ
η
0
0
27333,33
333
0
0
6) The consultant wants to know if he could follow the same
investment strategy by increasing the % investment of the
available budget in one of the A-rated or immediate liquid,
or zero-risk investments.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
Τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
The consultant wants to know if he can increase the amount of money available to
invest in one of the investment groups, the A-rated, immediate liquid or zero –risk. As
we can see from the sensitivity report, we have with red color the allowable increase of
the 3 constraints. If the increase is in the range of feasibility:
For the A-rated investments the allowable increase is 27.333,33€.
For the Immediately liquid investement, the allowable increase is 21.111,11€.
For the Zero-risk investment the allowable increase is 17.333,33€.
If they are in the feasibility range, then the shadow price is valid, and we can calculate
the total risk value by adding the result of ‘ Increase*Shadow price’.
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
$J$
Constrai
nts
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
Σελίδα 8
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
Απόκλισ
η
0
0
27333,33
8
$J$
9
$J$
10
333 $8
$J$9>=$L
40000 $9
$J$10<=$
30000 L$10
υποχρεωτι
κός
Υποχρεωτι
κός
Υποχρεωτι
κός
333
0
0
However, as we can see the A-rated investment is not binding, and its shadow price is
0, so for A-rated investments the investment strategy will not change if the increase is
within 27.333,33€.
In order to have the final investment strategy for the other 2 investments, we have to
run again the solver.
7) The change in the total risk factor and in the final
investment strategy, if the consultant decides to invest
25.000€ in zero risk investments.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
Μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
If we go again to the sensitivity report, we can see that the constraint referring to the
zero-risk investments, and we can also see that it is binding. If we decrease the zero
risk investment from 30.000€ to 25.000€, then we must check if the 5.000€ within the
feasibility range of the allowable decrease (6.333,33€). As we can see they are within
the range. As a consequence the total risk value will increase to 5.000*0,1=500€, so
Z=16.766,67€.
Again, since the constraint is binding, we should run again the solver in order to find
which will be the change to the final investment strategy.
Katrakaza Sevina
Σελίδα 9
8) The changes in the total risk value and in the investment
strategy, if the risk factor for Goodie’s stock becomes 26%.
Ρυθμιζόμενα κελιά
Τελική
Κε
λί
$B
$3
$C
$3
$D
$3
$E$
3
$F$
3
$G
$3
$H
$3
$I$
3
Όνο
μα
x1
x2
x3
τιμή
17333,3
33
12666,6
67
x5
0
22666,6
67
47333,3
33
x6
0
x7
0
x8
0
x4
Μειωμέν
ο
κόστος
Αντικειμε
νικός
συντελεσ
τής
Επιτρεπό
μενη
Επιτρεπό
μενη
μείωση
0
0
αύξηση
0,0117647
06
0
0,046666
667
0
0,005
0,25
0
0,3
0
0,006666
667
0,016666
667
0,016666
667
0,2
1E+30
0,003846
154
0,0042553
19
0,15
1E+30
0,65
1E+30
0,4
1E+30
0,005
0,0117647
06
0,0466666
67
0,011764
706
0,005
0,0066666
67
0,0166666
67
0,0166666
67
The Goodies stock is the x4. If instead of 0,2, the new risk factor would be 0,26, we
must check for a 0,04 decrease, which as we can see is not in the feasibility range. As
a consequence we do not know either the change of the total risk value, or the final
investment strategy, and we have to run the solver again.
9) The change in the total risk factor and in the investment
strategy, if the amount invested in A-rated investments is
increased by 10%.
Περιορισμοί
Κελ
ί
Όνομα
$J$
6
$J$
7
Constrai
nts
$J$
8
$J$
9
Katrakaza Sevina
Τιμή
κελιού
Τύπος
$J$6=$L$
100000 6
$J$7>=$L
7500 $7
77333,33 $J$8>=$L
333 $8
$J$9>=$L
40000 $9
Σελίδα 10
Κατάστασ
η
Μη
υποχρεωτικ
ός
Υποχρεωτικ
ός
Μη
υποχρεωτι
κός
Υποχρεωτικ
ός
Απόκλισ
η
0
0
27333,33
333
0
$J$
10
$J$10<=$
30000 L$10
Υποχρεωτικ
ός
0
As we can see the A-rated investment is not binding. We are discussing of an increase
of 10%, so the ‘at least 50.000€’budget should have an increase of 5.000€.
As we can see we are within feasibility range, and concerning the binding term, and the
allowable increase as we can see underneath from the sensitivity report.
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
Τιμή
100000
0,073333
333
3,333333
333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
Αύξηση
μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
As a consequence, there will be no change either in the total risk value, or the final
investment strategy.
We can also see in order to check if we are correct, that the optimal solution, is
proposing to invest 77.333€ to A rated investments, so there is no change in the
solution.
Katrakaza Sevina
Σελίδα 11
10)
Assume that the consultant is thinking to add in the
portfolio an extra potential investment with a 9% annual
expected return, A-quality rating and immediate liquidity.
What is the risk factor that would be recommended in order
to be possible for this new investment to be chosen?
We shall name the new investment x9.We go to the sensitivity report of the affected
constraints:
Περιορισμοί
Κελ
ί
$J$
6
$J$
7
$J$
8
$J$
9
$J$
10
-
-
-
Όνομα
Constra
ints
Περιορι
σμός
R.H.
Side
Τελική
Σκιώδης
τιμή
Τιμή
100000
0,07333
3333
3,33333
3333
100000
0
50000
40000
0,04
40000
30000
-0,1
30000
7500
77333,
333
7500
Επιτρεπό
μενη
Επιτρεπό
μενη
αύξηση
Μείωση
4634,146
341
6341,463
415
520
27333,33
333
21111,11
111
17333,33
333
380
1E+30
28888,88
889
6333,333
333
The new investment would affect the 1st constraint, since it will be added to the
total budget of 100.000€. The decrease of the RHS is shadow price * x 9 =
-0,073x9
It also affects the 2nd constraint, since it should have annual return if it was
chosen. Its annual return is 9%, so it would be 0,09 decrease * shadow price =
0,09*3,33=0,2997x9
It is an A-rated investment so the 3rd constraint is affected, but the shadow price
is 0, so the total risk factor will not change.
It is also an immediate liquidity investment so the 4 th constraint changes as well,
and the RHS constraint will decrease by shadow price* x 9 = 0,04x9
Since the change in the total risk value can be calculated using the shadow
prices for each constraint, we can find the risk factor for the new investment:
-0,073x9 + 0,2997x9 + 0 + 0,04x9 = 0,2667.
So the risk factor of the new investment that is recommended, in order for it to
be chosen in the portfolio is 26,67%.
Katrakaza Sevina
Σελίδα 12
