Explore BrainMass

Explore BrainMass

    Selecting Projects - Spreadsheets

    Not what you're looking for? Search our solutions OR ask your own Custom question.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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?

    © BrainMass Inc. brainmass.com March 4, 2021, 8:03 pm ad1c9bdddf
    https://brainmass.com/economics/personal-finance-savings/selecting-projects-spreadsheets-141289

    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.49

    ADVERTISEMENT