Using Excel solve the following Selecting Projects The Texas

Using Excel solve the following:

Selecting Projects. The Texas Electronics Company (TEC) is contemplating a research and development program encompassing eight major projects.   The company is constrained from embarking on all projects by the number of available engineers (40) and the budget available for projects ($300,000). Following are the resource requirements and the estimated profit for each project:

Expense ($000)

Engineers required

Profit ($000)

Project 1

60

7

36

Project 2

110

9

82

Project 3

53

8

29

Project 4

147

4

16

Project 5

92

7

56

Project 6

85

6

61

Project 7

73

8

48

Project 8

65

5

41

What is the maximum profit, and which projects should be selected?

Suppose that management decides that projects 2 and 5 are mutually exclusive. (In other words, TEC will not undertake both). What is the revised project portfolio and the revised maximum profit?

Suppose that management also decides to undertake at least two of projects 5-8. As a result, what are the revised project portfolio and maximum profit? (Assume b. is also true

	Expense ($000)	Engineers required	Profit ($000)
Project 1	60	7	36
Project 2	110	9	82
Project 3	53	8	29
Project 4	147	4	16
Project 5	92	7	56
Project 6	85	6	61
Project 7	73	8	48
Project 8	65	5	41

Solution

In this case we will select those projects that are providing maximum profits in terms of absolute numbers.

The problem can be solved using solver. Let x1, x2, x3......x8 be either 0 or 1 (o means that the project is not selected and 1 means that the project is selected). x1 is for project 1, x2 for project 2 and so on.

Objective is to maximize profits i.e maximize x1*36+x2*82+x3*29+x4*16+x5*56+x6*61+x7*48+x8*41

Subject to the constraints:

x1*7+x2*9+x3*8+x4*4+x5*7+x6*6+x7*8+x8*5<=40 (number of engineers)

x1*60+x2*110+x3*53+x4*147+x5*92+x6*85+x7*73+x8*65<=300 (available budget)

x1 to x8 <=1, x1 to x8>=0, x1 to x8 are integers

Solving in excel, we get the following solution:

So we select project 2, 5 and 6.

Now, supposing that projects 2 and 5 are mutually exclusive:

So we will create 2 scenarios - first where 2 is selected and 5 is not. The results are:

Profit here is 195,000 and projects 1,2,3 and 7 are selected.

If we select project 5, the solution will be:

As the profits is higher in case project 2 is selected, we will select projects 1,2,3,7

Now, if 2 projects from #5 to 8 have to be selected then the new constraint will be x5+x6+x7+x8>=2

Project	expenses	Engineers required	Profit	x (1 to 8)	Total expenses	Engineers required	Profit
1	60	7	36	0	0	0	0
2	110	9	82	1	110	9	82
3	53	8	29	0	0	0	0
4	147	4	16	0	0	0	0
5	92	7	56	1	92	7	56
6	85	6	61	1	85	6	61
7	73	8	48	0	0	0	0
8	65	5	41	0	0	0	0
				Total	287	22	199