Over the past few weeks we have discussed different types of portfolio management (i.e., selection of which projects to implement). This is not an easy problem for organizations with scarce and limited resources. Should we work on Project A or Project B? Project A may have a greater potential profit but might "eat-up" more resources than Project B. Let's assume there are many projects to select from; can you imagine the combinatorial complexity of determining the optimal project selection? This is the problem we are addressing in this exercise.

We have discussed different types of scoring algorithms based upon perceived criteria. These are somewhat vague (i.e., pr of success, etc.). We also discussed parity analysis. In this lab we apply linear/integer (0,1) programming to provide the optimal solution.

Please be sure to reference the video that we recorded in our last virtual office. We demonstrate how to set up the problem and actually solve it step by step.

Problem Statement:

Organization GreenCat has 14 internal projects that have been recommended by various superintendents, plant managers, etc. to be initiated next year. For simplicity we will simply term them Project 1, Project 2, etc. Each of them has various costs associated with them along with various resource needs. We will assume that our planning horizon is one year. We realize that we may not be able to initiate all of the projects due to resource and budgetary limitations. Again, the question is, what is the optimal combination of projects to initiate this coming fiscal year (i.e., portfolio management)?

Consider the following information regarding each of the projects:

Projects -GreenCatCorporation
	Expected Profit Next Year	Anticipated Cost	Expected Civil Engineering Hours	Expected Electrical Engineering Hours	Expected Management Engineering Hours
Project 1	2	0.7	371	230	114
Project 2	4	1	527	389	256
Project 3	5	2.5	569	347	219
Project 4	8	2.7	782	539	356
Project 5	10	5	669	401	171
Project 6	1	0.25	228	182	131
Project 7	12	4	730	576	235
Project 8	15	7.5	981	706	539
Project 9	3	0.7	196	127	71
Project 10	6	3	841	546	485
Project 11	17	3.5	712	526	123
Project 12	4	2	572	446	325
Project 13	5	1.7	502	356	308
Project 14	5	2.5	589	353	357

Section One:

Consider the following constraints:

1 - We have a budget of only $20,000,000.

2 - Project 2, Project 6, and Project 7 are to be performed at 3 different locations and require specialized equipment. We can only perform one of these projects (i.e., if we select Project 6, we cannot initiate Project 2 or Project 7).

3 - Project 1 and Project 4 are to be initiated together (i.e., either BOTH be initiated or neither are to be initiated).

Build your model in Excel and solve using MATLAB.

Provide the following:

1 - Provide a screen shot of your MATLABscript

2 - The mathematical formulation - similar to what I prepared in the video - you may write out by hand, take a picture and cut and paste into your word document.

3 - The results - which projects were selected?

4 - What is the projected increase in profit assuming a linear and an integer solution?

5 - If you increased your budget from $20,000,000 to $25,000,000; what would be the result?

6 - At what point in raising or lowering the budget do you maximize your return on investment? Please start at a budget of $5,000,000 and increment by $10,000,000 and plot the results (i.e., ratio of profit to investment)

Here is the script that I used:

Beq = xlsread('Project Selection','A Eq','$H$12:$H$12');

Aeq = xlsread('Project Selection','A Eq','$I$12:$V$12');

binvars = [1:14]

A = xlsread('Project Selection','A Less Than','$I$12:$V$13');

b = xlsread('Project Selection','A Less Than','$H$12:$H$13');

F = xlsread('Project Selection','A Eq','$I$10:$V$10');

lb = xlsread('Project Selection','A Eq','$I$7:$V$7');

ub = xlsread('Project Selection','A Eq','$I$6:$V$6');

[Variables,Objective] = linprog(F,A,b,Aeq,Beq,lb,ub);

[IntVariables,IntObjective] = intlinprog(F,binvars,A,b,Aeq,Beq,lb,ub);

Section Two:

Consider the following constraints:

1 - We have a budget of only $20,000,000.

2 - Project 2, Project 6 and Project 7 are to be performed at 3 different locations and require specialized equipment. We can only perform one of these projects (i.e., if we select Project 6, we cannot initiate Project 2 or Project 7).

3 - Project 1 and Project 4 are to be initiated together (i.e., either BOTH be initiated or neither are to be initiated).

4 - We have a limit of 5,000 hours for Civil Engineering.

5- We have a limit of 3,500 hours for Electrical Engineering.

6 - We have a limit of 2,000 hours for Management Engineering.

Build your model in Excel and solve using MATLAB.

Provide the following:

1 - Provide a screen shot of your MATLAB script

2 - The mathematical formulation - similar to what I prepared in the video - you may write out by hand, take a picture and cut and paste into your word document.

3 - The results - which projects were selected?

4 - What is the projected increase in profit assuming a linear and an integer solution?

5 - At what point in raising your budget do you not receive any additional return on your investment? Please start at a budget of $20,000,000 and increment until your additional profit plateaus. Please provide this budget +/- $2,000,000.

6 - Why, with additional budget do we not receive additional profits (e.g., try a budget of $200,000,000 - do you receive increased profits? Assuming you have a budget of $50,000,000; what would you then change to increase profits? Make at least one run demonstrating your solution.

Here is the script that I used:

Beq = xlsread('Project Selection','A Eq','$H$12:$H$12');

Aeq = xlsread('Project Selection','A Eq','$I$12:$V$12');

binvars = [1:14]

A = xlsread('Project Selection','A Less Than','$I$12:$V$16');

b = xlsread('Project Selection','A Less Than','$H$12:$H$16');

F = xlsread('Project Selection','A Eq','$I$10:$V$10');

lb = xlsread('Project Selection','A Eq','$I$7:$V$7');

ub = xlsread('Project Selection','A Eq','$I$6:$V$6');

[Variables,Objective] = linprog(F,A,b,Aeq,Beq,lb,ub);

[IntVariables,IntObjective] = intlinprog(F,binvars,A,b,Aeq,Beq,lb,ub);

Section Three:

Assume that the first 6 projects come from the Indiana plant, the next 4 projects come from the Ohio location, and the last 4 projects come from the Michigan location. How would you change your mathematical formulation to insure that at least one project is given to each of the locations. You may simply write out your formulation with paper and pencil if you prefer, take a picture and cut and paste it.