Share
Explore BrainMass

Selecting Projects - Spreadsheets

1. Selecting Projects.

The Texas Electronic 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 scientists (40) and the budget available for projects ($3000,000). Following are the resources requirements and the estimated project for each project.

Project Expense Scientists Profit
Project $0 Required $0
1 60 7 36
2 110 9 82
3 53 8 29
4 47 4 16
5 92 7 56
6 85 6 61
7 73 8 48
8 65 5 41

a. What is the maximum profit, and which project should be selected?

b. Suppose that management decides that projects 2 and 5 are mutually exclusive. This is, TEC should not undertake both. As a result, what are the revised project portfolio and the revised maximum profit?

c. Suppose that management also decides to undertake at least two of the projects involving consumer products. (These happen to be projects 5-8) As a result, what are the revised project portfolio and the revised maximum profit?

Attachments

Solution Preview

Please see the attached file.

"The Texas Electronic 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 scientists (40) and the budget available for projects ($3000,000). Following are the resources requirements and the estimated project for each project.
"

Project Expense Scientists Profit
Project $0 Required $0
1 60 7 36
2 110 9 82
3 53 8 29
4 47 4 16
5 92 7 56
6 85 6 61
7 73 8 48
8 65 5 41
a. What is the maximum profit, and which project should be selected?
The decision Variable is whether a project is selected or not. Since the project can be selected as a fraction, the decision variables are binary.

Project
1 1
2 1
3 0
4 1
5 0
6 1
7 1
8 1

Constraints:
The number of scientist required should be less than 40
Scientist required 39 <= 40

The expense on selected projects should be less than budget
Total Expense 440 <= 3000

Objective Function
The profits from the projects should be maximized
Total profit 284

Use solver to solve the problem. Go to tools, select solver and see the model:
Note: Budget seems to be a bit ...

Solution Summary

Spreadsheets are assessed.

$2.19