Question

The brokerage firm Michel Awski Ltd. is proposing investment guidelines for its middle-income investors. Currently, they are considering investments in several areas. Only whole units (integer) of an investment can be purchased.

Investment type	Cost per unit ($)	Expected annual return per unit ($)
large cap common stock	1200	60
small cap common stock	3100	186
preferred stock	7000	385
government bonds	4500	292
industrial bonds	6000	312
hi-tech mutual funds	3800	220
energy mutual funds	2800	154

Mr. and Mrs. Kaushon would like to invest $25,000. In addition to maximizing expected annual return, several additional restrictions on the investment portfolio have been agreed upon, with to deal with diversity of portfolio and risk considerations. These restrictions are:

Fall 2019 Page 2

ADM2302 A, B, C, D and E Assignment # 3

• at most one of the two common stock investments types should be made;

• either both or neither of large cap common stock and preferred stock should appear in the

  portfolio;

• hi-tech mutual funds will not be in the portfolio unless preferred stock is also in the portfolio;

• energy mutual funds will not be in the portfolio unless both government bonds and industrial

  bonds are in the portfolio.

1. Formulate the Integer/Binary Linear Programming model in algebraic form. Define the decision variables, objective function, and constraints. (16 points)

2. Formulate this same problem on a spreadsheet and SOLVE using Excel solver (Provide the corresponding “Excel Spreadsheet” and the “Answer Report”). (5 points)

Answer 1

Integer/Binary Linear Programming model in algebraic form is formulated as below

Decision variables:

Let

XL = 1, if an investment is made in Large cap common stock, otherwise XL = 0

XS = 1, if an investment is made in Small cap common stock, otherwise XS = 0

XP = 1, if an investment is made in Preferred stock, otherwise XP = 0

XG = 1, if an investment is made in Government bonds, otherwise XG = 0

XN = 1, if an investment is made in Industrial bonds, otherwise XN = 0

XH = 1, if an investment is made in Hi-tech mutual funds, otherwise XH = 0

XE = 1, if an investment is made in Energy mutual funds, otherwise XE = 0

L be the number of units to be purchased of Large cap common stock

S be the number of units to be purchased of Small cap common stock

P be the number of units to be purchased of Preferred stock

G be the number of units to be purchased of Government bonds

N be the number of units to be purchased of Industrial bonds

H be the number of units to be purchased of Hi-tech mutual funds

E be the number of units to be purchased of Energy mutual funds

Objective function:

Maximize Z = 60L+186S+385P+292G+312N+220H+154E

Constraints:

1200L+3100S+7000P+4500G+6000N+3800H+2800E <= 25000

L-20XL <= 0   (a max of 20 whole units of large cap common stock can be purchased. The number 20 is obtained by dividing total funds to be invested by unit value of the investment, i.e. 25000/1200 = 20.8 ~ rounded down to 20 whole units)

S-8XS <= 0

P-3XP <= 0

G-5XG <= 0

N-4XN <= 0

H-6XH <= 0

E-8XE <= 0

XL+XS <= 1 (at most one of the two common stock investments types should be made)

XL-XP = 0 (either both or neither of large cap common stock and preferred stock should appear in the portfolio)

XP-XH >= 0 (hi-tech mutual funds will not be in the portfolio unless preferred stock is also in the portfolio)

XG+XN-2XE >= 0 (energy mutual funds will not be in the portfolio unless both government bonds and industrial bonds are in the portfolio)

L-XL>=0

S-XS>=0

P-XP>=0

G-XG>=0

N-XN>=0

H-XH>=0

E-XE>=0

L,S,P,G,N,H,E are integers

XL,XS,XP,XG,XN,XH,XE are binary integers

b)

Create spreadsheet model as follows:

Enter Solver Parameters:

Click Solve to generate the solution:

Optimal solution:

Investment should be made in 2 units of Small cap common stock and 4 units of Government bonds

Total expected annual return = $ 1,540