Question

Dan Boyd is a financial planner trying to determine how to invest $100,000 for one of his clients The cash flows for the five investments under consideration are summarized in the following table Summary of Cash in-Flows and Out-Flows (at Beginning of Year) Year 1 1.00 1.00 1.00 0.00 0.00 1.00 0.00 Year 20.45 0.00 0.00 Year 3 +1.05 1.00 0.00 0.00 1.25 Year 4 +1.30 +1.30 0.00 +1.65 0.00 For example, if Dan invests $1 in investment A at the beginning of year 1, he will receive $0.45 at the beginning of year 2 and another $1.05 at the beginning of year 3. Alternatively, he can invest $1 in investment B at the beginning of year 2 and receive $1.30 at the beginning of year 4 Entries of "O.00" in the preceding table indicate times when no cash in-flows or out-flows can occur. The minimum required investment for each of the possible investments is $50,000. Also at the beginning of each year, Dan may also place any or all of the available money in a money market account that is expected to yield 5% per year. How should Dan plan his investments if he wants to maximize the amount of money available to his client at the end of year 4? a. Formulate an ILP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. c. What is the optimal solution?

Answer 1

a) ILP model is following:

Let A, B, C, D, E be the amount to be invested in investment A, B, C, D, E resp.

Xa, Xb, Xc, Xd, Xe = 1, if any amount to be invested in investment A, B, C, D, E resp. otherwise, value of this set of variables is 0

M1, M2, M3, M4, M5 be the amount of money available at the start of year 1,2,3,4,5 resp.

Max M5

s.t.

-A-C-E-M1=-100000

.45A-B+1.05M1-M2=0

1.05A-D+1.25E+1.05M2-M3=0

1.3B+1.65C+1.3D+1.05M3-M4=0

1.05M4-M5=0

A-50000Xa>=0

B-50000Xb>=0

C-50000Xc>=0

D-50000Xd>=0

E-50000Xe>=0

-A+MXa>=0, (where M is a hypthetically large number, large enough than possible values of A,B,C,D,E. Let's say 999999)

-B+MXb>=0

-C+MXc>=0

-D+MXd>=0

-E+MXe>=0

Xa,Xb,Xc,Xd,Xe binary

A,B,C,D,E,M1,M2,M3,M4,M5 >=0

b) Spreadsheet model and solution using Solver is following:

fx SUMPRODUCT(B3:P3,SB$22:$P$22) Q RS C D L M N BIC Xb M5 Xd Xe M1 M2 M3 M4 3 Available cash at end of year 4 1 207821 5 Beginning of year 1 100000-100000 6 Beginning of vear 2 0.45 1 0 1.05-1 1.05 -1 7 Beginning of year 3 1.05 00 1 1.25 Beginning of year 4 0 1.3 1.65 1.3 1.05 E-11 0 9 Beginning of vear 5 1.05 10 50000 50000 12 50000 13 50000 102250 50000 14 15 16 17 18 847749 19 20 21 22 Result: 100000 0 0 152250 0 0 45000 0 197925 207821 23 24

EXCEL FORMULAS:

Q3 =SUMPRODUCT(B3:P3,$B$22:$P$22) copy to Q5:Q19

S Solver Parameters sQs3l Set Objective: O Max O Min Value Of: 0 14% By Changing Variable Cells: SBS22:SPS22 Subject to the Constraints: SQS5:SQS9 SSS5:SSS9 Add SGS22:SKS22 binary SQS10:SQS19SSS10:SSS19 Change Delete Reset AlI Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems Solve Close

c) Optimal solution:

A = 100,000

B = 0

C = 0

D = 152,250

E = 0

M2 = 45,000

M4 = 197,925

M5 = 207,821

Optimal value = $ 207,821