Question

Image transcription text

INPUT Bond 1 Bond 2 Price 5 980.00 5 965.00 Coupon % 6% 6.50% Maturity (years) 5 12 Face Value 5 1,000.00 5 1,000.00 ISavings rate 4% DECISIONS OBJECTIVE Bonds 1 Bonds 2 Total cash Total cash 95.80 90.15 195683.67 195683.67 CALCULATIONS Cash flow per I I I Cash flow Savings from Savings carried Year Bond 1 Bond 2 LIabIIItY from bonds year (n-1) to year (n+1) NEt flow 0 -980 -965 10,000 -180879.18 195683.67 4804.50 0.00 1 60 65 11,000 11607.804 4996.68 5604.48 0.00 2 60 65 12,000 11607.804 5828.66 5436.46 0.00 3 60 65 14,000 11607.804 5653.92 3261.73 0.00 4 60 65 15,000 11607.804 3392.20 0.00 0.00 5 1060 65 17,000 107403.58 0.00 90403.58 0.00 6 0 65 19,000 5860.0578 94019.72 80879.78 0.00 7 0 65 20,000 5860.0578 84114.97 69975.03 0.00 8 0 65 22,000 5860.0578 72774.03 56634.09 0.00 9 0 65 24,000 5860.0578 58899.45 40759.51 0.00 10 0 65 25,000 5860.0578 42389.89 22249.94 0.00 11 0 65 29,000 5860.0578 23139.94 0.00 0.00 12 0 1065 31,000 96014.793 0.00 65014.79 0.00 13 0 0 33,000 0 67615.38 34615.38 0.00 14 0 0 36,000 0 36000.00 0.00 0.00 NPV of Liabilities 230,437 

Image transcription text

Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $C$14 Bonds 1 95.79577251 0 0 109.036447 126.183613 $D$14 Bonds 2 90. 15473512 0 0 144.641105 437.305172 $E$14 Total cash 195683.6732 0 1 1E+30 $H$20 Savings carried to year (n+1) 4804.496733 0 0 2.86611391 0.10012194 SH$21 Savings carried to year (n+1) 5604.480735 0 0 3.74895565 0.1016564 SH$22 Savings carried to year (n+1) 5436.464098 0 0 5.51599734 0.10330295 SH$23 Savings carried to year (n+1) 3261.726795 O 0 10.8198409 0.1050729 $H$24 Savings carried to year (n+1) 0 0.10697916 0 1E+30 0.10697916 SH$25 Savings carried to year (n+1) 90403.57664 0 0 1.28340256 0.12576926 $H$26 Savings carried to year (n+1) 80879.77749 0 0 1.51123998 0.12788176 $H$27 Savings carried to year (n+1) 69975.02637 0 0 1.85343418 0.13015539 $H$28 Savings carried to year (n+1) 56634.08521 0 0 2.42434277 0.13260734 SH$29 Savings carried to year (n+1) 40759.5064 O 0 3.56703826 0.13525732 SH$30 Savings carried to year (n+1) 22249.94444 0 0 6.99688275 0.13812804 SH$31 Savings carried to year (n+1 0 0.14124577 0 1E+30 0.14124577 $H$32 Savings carried to year (n+1) 65014.7929 0 0 1E+30 0.41061518 SH$33 Savings carried to year (n+1) 34615.38462 0 1E+30 0.39482229 o o SH$34 Savings carried to year (n+1) 0 0.37963682 LE+30 0.37963682 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $1$20 Net flow -4.3656E-11 1 0 1E+30 195683.673 $1$21 Net flow 4.1964E-09 0.96153846 0 1.3027E+19 4996.6766 $1$22 Net flow 4.8931E-09 0.92455621 0 1.3548E+19 5196.54367 $1$23 Net flow 4.7476E-09 0.88899636 0 1.409E+19 5404.40541 $1$24 Net flow 2.8485E-09 0.85480419 0 1.4653E+19 3392.19587 $1$25 Net flow 0 0.71906253 0 23383.4414 101543.519 $1$26 Net flow 7.8915E-08 0.69140628 0 24318.7791 94019.7197 $1$27 Net flow 7.0606E-08 0.66481373 0 25291.5302 84114.9686 $1$28 Net flow 6. 1111E-08 0.63924397 0 26303.1914 72774.0274 $1$29 Net flow 4.9426E-08 0.61465767 0 27355.3191 58899.4486 $1$30 Net flow 3.5579E-08 0.59101699 0 28449.5319 42389.8867 $1$31 Net flow -1.9434E-08 0.56828557 0 29587.5131 23139.9422 $1$32 Net flow 0 0.41061518 0 33535.6203 96014.7929 $1$33 Net flow -5.676E-08 0.39482229 0 34877.0451 67615.3846 $1$34 Net flow 3.0217E-08 0.37963682 0 36272.1269 36000 

I need help in evaluating the below scenarios:

1) IF the liabilities in each of years 6-14 increase by 2500 how much would the cost of the optimal portfolio increase by?

2) In row 10 of the sensitivity report for the decision variable corresponding to the bond 1 decision variable, the objective coefficient is 0 and the allowable increase is 109.03.

a) If the cost of buying each unit of bond 1 increases by 100 dollars to 1080 then in the new optimal solution we will still buy 95.80 bonds of type 1 and 90.15 bonds of type 2.

b) If the cost of buying each unit of bond 1 incre

Answer rating: 100% (QA)

To evaluate the scenarios we need to perform sensitivity analysis on the original optimization problem 1 Scenario 1 Increasing liabilities in years 61... View the full answer