Question

Please provide a mathematical (algebraic) formulation for each problem along with a solution using the Excel Solver.

Problem #1: Investment Problem (Product Mix Problem)

Brian Givens is a financial analyst for Retirement Planning Services, Inc. who specializes in designing retirement income portfolios for retirees using corporate bonds. He has just completed a consultation with the client who expects to have $750,000 in liquid assets to invest when she retires next month. Brian and his client agreed to consider upcoming bond issues from the following six companies.

Company	Return	Years to Maturity	Rating
Acme Chemical	8.65%	11	1-Excellent
DynaStar	9.50%	10	3-Good
Eagle Vision	10.00%	6	4-Fair
Micro Modeling	8.75%	10	1-Excellent
Opti Pro	9.25%	7	3-Good
Sabre Systems	9.00%	13	2-Very Good

The column labeled “Return” in this table represents the expected annual yield on each bond, the column labeled “Years to Maturity” indicates the length of time over which bonds will be payable, and the column labeled “Rating” indicates an independent underwriter’s assessment of the quality or risk associated with each issue. Assume that Brian re-invests into a bond when it matures. The expected annual yield does not change for each bond (has already factored compounded annual growth for the % return provided). This will also avoid the need to consider any NPV analysis that can alter the allocation. Think simple and keep the formulation linear.

Brian believes that all the companies are relatively safe investments. However, to protect his client’s income, Brian and his client agreed that no more than 25% of the money should be invested in any one investment and at least half of her money should be invested in long-term bonds which mature in ten or more years. Also, even though DynaStar, Eagle Vision, and Opti Pro offer the highest returns, it was agreed that no more than 35% percent of the money should be invested in these bonds since they also represent the highest risks (i.e., they were rated lower than “very good”).

Brian needs to determine how to allocate his client’s investments to maximize her income while meeting their agreed upon investment restrictions.

Answer 1

Let the individual investment (in $) in each bond isssue be:

Acme Chemical - x1

DynaStar - x2

Eagle Vision - x3

Micro Modeling - x4

Opti Pro - x5

Sabre Systems - x6

Total Income, T = x1*(1+8.65/100)^11 + x2*(1+9.5/100)^10 + x3*(1+10/100)^6 + x4*(1+8.75/100)^10 + x5*(1+9.25/100)^7 + x6*(1+9/100)^13

So, Objective function becomes:

Maximize T = x1*(1+8.65/100)^11 + x2*(1+9.5/100)^10 + x3*(1+10/100)^6 + x4*(1+8.75/100)^10 + x5*(1+9.25/100)^7 + x6*(1+9/100)^13

subject to constraints:

1) x1 + x2 + x3 + x4 + x5 + x6 = 750,000

2) x1 + x2 + x4 + x6 >= 50% * 750,000 , or x1 + x2 + x4 + x6 >= 375,000

3) x2 + x3 + x5 <= 35% * 750,000, or x2 + x3 + x5 <= 262,500

4) x1 <= 25% * 750,000, or x1 <= 187,500

similarly,

5) x2 <= 187,500

6) x3 <= 187,500

7) x4 <= 187,500

8) x5 <= 187,500

9) x6 <= 187,500

The above LP is formulated and solved as:

Thus, we can see that the optimal allocation is:

Acme Chemical, x1 = $ 187,500

DynaStar, x2 = $ 187,500

Eagle Vision, x3 = $ 0

Micro Modeling, x4 = $ 187,500

Opti Pro, x5 = $ 0

Sabre Systems, x6 = $ 187,500

Returning a total income (maximized) of $ 1,940,322.71

Please provide an Upvote if you like this answer. It will be very encouraging for me!