Question

Bernardo Bellotto is a financial analyst with Canal Financial Planning Inc who specializes in designing retirement income portfolios for retirees using corporate bonds. He has just completed a consultation with a client who expects to have $750,000 in liquid assets to invest when she retires next month. Bernardo and his client agreed to consider upcoming bond issues from the following six companies:
Company Return (%) Years to Maturity Rating
Acme Chemical 8.25 11 1-Excellent
Bravo Films 9.00 10 3-Good
Canada Agriculture 10.00 6 4-Fair
Dynamic Analytics 8.50 10 1-Excellent
Express Electronics 10.60 7 3-Good
Frieda Fashions 7.80 13 2-Very Good

Return represents the expected annual yield on each bond, the column labeled “Years to Maturity” indicates the length of time over which the bonds will be payable, and the column labeled “Rating” indicates an independent underwriter’s assessment of the quality or risk associated with each issue. Bernardo believes that all the companies are relatively safe investments. However, to protect his client’s income, Bernardo and his client agreed that no more than 25% of her money should be invested in any one investment and at least half of her money should be invested in long term bonds that mature in 10 or more years. Also, even though Bravo Films, Canada Agriculture, and Express Electronics offer the highest returns, it was agreed that no more than 35% of the money should be invested in these bonds because they also represent the highest risks. Bernardo needs to determine how to allocate his client’s investments to maximize her income while meeting their agreed upon investment restrictions.

a. Formulate the problem algebraically for a linear programming model for this problem.
b. Solve this problem using Excel.

Answer 1

a. Let the amount invested in each of the bonds be as shown below:

	Amount
Acme Chemical	a
Bravo Films	b
Canada Agriculture	c
Dynamic Analytics	d
Express Electronics	e
Frieda Fashions	f

Objective function = 0.0825*a + 0.09*b + 0.1*c + 0.085*d + 0.106*e + 0.078*e. This is the total income and has to be maximized.

Constraints:

1. a+b+c+d+e+f<=750,000

The next 6 constraints shows that no more than 25% of the total money should be invested in one fund.

2. a<=0.25*(a+b+c+d+e+f)

3.  b<=0.25*(a+b+c+d+e+f)

4. c<=0.25*(a+b+c+d+e+f)

5. d<=0.25*(a+b+c+d+e+f)

6. e<=0.25*(a+b+c+d+e+f)

7. f<=0.25*(a+b+c+d+e+f)

The next constraint shows that at least half of her money should be invested in long term bonds that mature in 10 or more years. The bonds that mature in 10 years or more are Acme Chemical, Bravo Films, Dynamic Analytics, and Frieda Fashions.

8. a+b+d+f>=0.5*(a+b+c+d+e+f)

The next constraint shows that no more than 35% of the money should be invested in Bravo Films, Canada Agriculture, and Express Electronics.

9. b+c+e<=0.35*(a+b+c+d+e+f)

Lastly all the variables >=0 (i.e. non zero)

b. The problem has been solved in excel using the solver function. The result is shown below:

Acme Chemical	187,500.00
Bravo Films	187,500.00
Canada Agriculture	-
Dynamic Analytics	187,500.00
Express Electronics	-
Frieda Fashions	187,500.00
Total amount	750,000.00			Formula
Total income per year	62,906.25			0.0825*a + 0.09*b + 0.1*c + 0.085*d + 0.106*e + 0.078*e
Constraints
750,000.00	<=	750,000.00		a+b+c+d+e+f<=750,000
187,500.00	<=	187,500.00		a<=0.25*(a+b+c+d+e+f)
187,500.00	<=	187,500.00		b<=0.25*(a+b+c+d+e+f)
-	<=	187,500.00		c<=0.25*(a+b+c+d+e+f)
187,500.00	<=	187,500.00		d<=0.25*(a+b+c+d+e+f)
-	<=	187,500.00		e<=0.25*(a+b+c+d+e+f)
187,500.00	<=	187,500.00		f<=0.25*(a+b+c+d+e+f)
750,000.00	>=	375,000.00		a+b+d+f>=0.5*(a+b+c+d+e+f)
187,500.00	<=	262,500.00		b+c+e<=0.35*(a+b+c+d+e+f)

Thus maximized annual income = $62,906.25.

$187,500 each are invested in Acme Chemical, Bravo Films, Dynamic Analytics and Frieda Fashions. No amount is invested in Canada Agriculture and Express Electronics. All constraints are satisfied.

Image of the solver solution by excel: