In: Operations Management
Energy Management Corporation (EMC) must decide its level of capital investment in the six energy ventures described below. EMC wishes to maximize its total expected return on a maximum total investment of $10,000,000. At least half of this must be in the United States, including Alaska. No more than 20% can be in sour crude and coal investments. In addition, allocations must at least meet the minimums specified in column three below. For example, investment in Wyoming Coal must be at least $1,000,000.
Venture (Location) |
Expected Return |
Minimum Investment |
Primary Product |
Wyoming Coal |
75% |
$1,000,000 |
Coal |
Colorado Shale |
62% |
$400,000 |
Sour crude |
Prudhoe Bay Alaska |
125% |
$1,000,000 |
Sweet crude |
Mexico |
135% |
None |
Sweet crude |
Alberta Tar Sands |
80% |
None |
Sour crude |
Virginia Coal |
85% |
$300,000 |
Coal |
1. Formulate the linear programming model. Clearly state your decision variables, objective function and all constraints. Use fractions rather than percentages in your formulation of the objective function.
2. Solve the model using Microsoft Excel Solver. Attach the Microsoft Excel file as part of your submission.
3. Interpret the results from Microsoft Excel Solver, including the Sensitivity Report. What aspects of the computer-generated strategy are you in agreement with (based on a consideration of the sensitivity data)? What aspects of it would you disagree with? Why?
1) Linear program model is following:
Decision variables:
Let W be the amount ($million) to be invested in Wyoming Coal
C be the amount ($million) to be invested in Colorado Shale
P be the amount ($million) to be invested in Prudhoe Bay Alaska
M be the amount ($million) to be invested in Mexico
A be the amount ($million) to be invested in Alberta Tar Sands
V be the amount ($million) to be invested in Virginia Coal
Objective function:
Max .75W+.62C+1.25P+1.35M+.8A+.85V (total expected return)
Constraints:
W+C+P+M+A+V <= 10 (maximum total investment of $ 10 million)
W+C+A+V <= 2 (no more than 20% =.2*10 = $ 2m of the total investment can be in sour crude and coal)
W+C+P+V >= 5 (at least half =10*.5 = $ 5m of the total investment should be US including Alaska)
W >= 1 (minimum investment of $ 1 million in Wyoming Coal)
C >= 0.4 (minimum investment of $ 0.4 million in Colorado Shale)
P >= 1 (minimum investment of $ 1 million in Prudhoe Bay Alaska)
V >= 0.3 (minimum investment of $ 0.3 million in Virginia Coal)
W,C,P,M,A,V >= 0
2) Solution using Excel Solver is following:
Step 1: Create Excel model
Step 2: Enter Solver parameters:
Step 3: Click Solve button to generate the optimal solution:
3) Results:
Following amounts should be invested
Venture (Location) | Investment ($ million) |
Wyoming Coal | 1 |
Colorado Shale | 0.4 |
Prudhoe Bay Alaska | 3.3 |
Mexico | 5 |
Alberta Tar Sands | 0 |
Virginia Coal | 0.3 |
Total | 10 |
After obtaining the solution, On the Solver Results notification window, Select Sensitivity report and click Ok to generate sensitivity report as follows:
I agree with the constraints which have non-negative shadow prices, because these are binding constraints