The City of Clyde Ohio is using the binary linear programmin

The City of Clyde, Ohio is using the binary linear programming (ELP) formulation of a capital budgeting problem shown below. Assume $175,000 is available for investment and all investments are indivisible; MARR = 12%; n = 10yrs. Given the incomplete SOLVER parameter box shown, respond to parts a-f below if it is desired to obtain an optimum investment portfolio. Specify the contents of the target cell. Specify the contents for By Changing Cells. Specify all constraints to be added. Now, suppose the original problem is modified so that investments 1 and 2 are mutually exclusive. Show how you would incorporate that constraint in SOLVER. Now, suppose the original problem is modified so investment 2 is contingent on investment 3 being pursued. Show how you would incorporate that constraint in SOLVER. Now, suppose the original problem is modified to specify that at least three and no more than four investments can be pursued. Show how you would incorporate that constraint in SOLVER.

Solution

c)

Total portfolio Investment=H10=175000

Value of x=B9:G9>=0

Value of x=B9:G9<=1

sum(B9:G9)=1

d)I1=if(C9>0,0,B9)

I2=if(B9>0,0,C9)

set constraint in solver,B9=I1 and C9=I2

e)put this formula in a cell P1=if(D9>0,C9,0)

set constraint in solver,C9=P1

f)put separate cells,if(B9>0,1,0),if(C9>0,1,0),if(D9>0,1,0),if(E9>0,1,0),if(F9>0,1,0),if(G9>0,1,0)

then put count function on cells as Count(B9:G9),then put the constraint in solver as Count(B9:G9)>=4 and Count(B9:G9)<=3.