Question

M4_A5. Porter Investments needs to develop an investment portfolio from the following list of possible investments COST ($) INVESTMENTS EXPECTED RETURN ($) A 1,700 15,000 17,000 1,850 8,500 1,400 10,000 1,500 E 13,500 1,750 13,000 1,650 9,000 1,500 H 9,500 1,700 The client can invest up to $100,000. The following conditions should be met: (1) If investment C is chosen, then investment D must also be part of the portfolio, (2) at least four investments should be chosen, (3) of investment A and B, exactly one of these investments should be included, (4) of investments F,G,H - exactly two of these should be included in the portfolio. What investments should be included in the portfolio? Remember the investments is either included or not - it cannot be partially included and multiples of the investment cannot be included. Formulate this as a binary linear program and solve in Excel. The objective should be to maximize total return a) How many decision variables does this problem have? b) Not counting the non-negativity constraint or binary/integer constraint - how many constraints does this problem have? c) What is the total return for the portfolio? d) Which investments should be included in the portfolio?

Answer 1

Investments	Cost($)	Expected Return($)
A	15000	1700
B	17000	1850
C	8500	1400
D	10000	1500
E	13500	1750
F	13000	1650
G	9000	1500
H	9500	1700

The Constraints used , the objective function and the Allocation table can be found in the following pic..

DE DC DR DS DT DL DV DW DX DY Dт 7 0 Not included; 1 Included 10 Allocation Table Investments Cost(S) Investments Expected Return($ 11 Inclusion 12 15000 1700 13 17000 1850 1400 14 8500 1 15 10000 1500 1 16 13500 1750 17 13000 1650 9000 9500 18 G 1500 G 1700 19 20 9850SUMPRODUCT(DW12:DW19,DS12:DS19) Total Return 21 Formula used Constraints 1IfC is chosen, D Si no Constraint chosen 24 C-D -SUM (DV12 : DV19) -SUMIDV12:DV13) 2SUM(DV17:DV19) 71500 SUMPRODUCTIDV12:DV19,DR12:DR19)-100000 usen 3 Of A & B, one must be included 42 of F,G & H must be included 5 Total Investment 26 27 28 29

The Constraints used , the objective function and the Allocation cells can be found in the below snapshot.

Solver Parameters $DV$21 Set Objective: OValue Of: To: 0 Min Max By Changing Variable Cells: $DV$12:$DV$19 Subject to the Constraints: $DT$25 $DT$26 1 $DT$27 2 $DT$28 $DV$12:$DV$19 binary $DV$14 $DV$15 >= 4 Add = Change 100000 <= Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving GRG Nonlinear Options Method: Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Upon solving the problem in solver, we get the following allocations for different investments.

Allocation Table	0 = Not included ; 1 = Included
Investments	Inclusion
A	0
B	1
C	1
D	1
E	1
F	1
G	0
H	1

A. There are 8 decision variables in this case, whose value is to be found. Those are A, B, C, D ,E, F ,G ,H.

B. There are 5 constraints in this problem:

1. If C is chosen, D must be chosen.

2. At least 4 investments must be chosen.

3. Of A & B , one must be included.

4. Two of F,G & H must be included.

5. Total Investment is limited to $100,000.

C. Total Return for the Portfolio = $9850.

D. The investments that are to be included are B, C, D, E, F & H.