linear optimization problems in one excel workbook.
[ad_1]
Instructions:
· Solve the following Linear Optimization problems in one Excel workbook.
· Name your Excel workbook using your last name (ex: LastName_HW3)
· Place one problem on each worksheet, and clearly label the worksheets (including worksheets showing sensitivity analysis).
· Make sure that your models are well-organized and contain optimal solutions.
· Make sure that answers to questions are clearly labeled (including report headings).
· Report any fractional values using at least 2 decimal places.
· Upload your completed Excel workbook to the ‘HW4’ Dropbox.
Problems:
1. The Copperfield Mining Company (CMC) owns two mines which produce three grades of ore: high, medium, and low. CMC has a contract to supply a smelting company with 12 tons of high-grade ore, 8 tons of medium-grade ore, and 24 tons of low-grade ore. Each mine produces a certain amount of each type of ore each hour it is in operation. CMC wants to determine the number of hours to operate each mine so that the contracted obligations can be met at the lowest cost.
Mine
High-Grade (tons/hour)
Medium-Grade
(tons/hour)
Low-Grade
(tons/hour)
Cost of Operation ($/hour)
1
6
2
4
200
2
2
2
12
160
a. Construct and solve a Linear Optimization model for this problem in Excel.
b. What does the optimal solution tell us? Which constraints are binding? What does a non-binding constraint mean in this problem
c. Run an Optimization Sensitivity Report (label the report sheet ‘1c’). Answer the following questions based on this report:
i. The cost of Mine 2 is increasing from $160/hr to $190/hr. Will this change the optimal solution?
ii. If the requirement for medium-grade ore increases by a ton, what impact does this have on the objective function?
2. The Auto Company of America (ACA) produces four types of cars: subcompacts, compact, intermediate, and luxury. ACA also produces trucks and vans. Vendor capacities limit total production capacity to, at most, 1.2 million vehicles per year. Subcompacts and compacts are built together in a facility with a total annual capacity of 620,000 cars. Intermediate and luxury cars are produced in another facility with a capacity of 400,000; and the truck/van facility has a capacity of 275,000. ACA’s marketing strategy requires that subcompacts and compacts must constitute at least half of the product mix for the four car types. The Corporate Average Fuel Economy (CAFE) standards require an average fleet fuel economy of at least 27 MPG. Profit margins, market potential, and fuel efficiencies are summarized below.
Type
Profit Margin ($/vehicle)
Market Potential
(sales in ‘000)
Fuel Economy
(MPG)
Subcompact
150
600
40
Compact
225
400
34
Intermediate
250
300
15
Luxury
500
225
12
Truck
400
325
20
Van
200
100
25
a. Construct and solve a Linear Optimization model for this problem in Excel.
b. What does the optimal solution tell us? Which constraints are binding, and what do these binding constraints mean in terms of the problem?
c. Perform a Sensitivity Analysis for the Van Profit Margin. Keep the lower value at 200, but set the upper value at 500. Run a report that shows the Objective Function value and the values for each Decision Variable in 13 intervals (label this report sheet ‘2c’). Answer the following questions:
i. How does production of vans change over this range?
ii. What other patterns can be found in the report? What explains these patterns?
3. A local bank wants to build a bond portfolio from a set of five bonds with $1 million available for investment. The expected annual return, the worst-case annual return, and the duration (a measure of the bond’s sensitivity to changes in interest rates) of each bond are given in the following table.
Expected Return
Worst-Case Return
Duration
Bond 1
12.5%
8.0%
8
Bond 2
11.5%
7.5%
7
Bond 3
10.5%
6.8%
6
Bond 4
9.5%
7.0%
5
Bond 5
8.5%
7.4%
3
The bank wants to maximize the expected return from its bond investments, subject to three conditions: the average worst-case return for the portfolio must be at least 7.2%; the average duration of the portfolio must be at most 6; and at most 40% of the total amount invested can be invested in a single bond.
a. Construct and solve a Linear Optimization model for this problem in Excel.
b. What does the optimal solution tell us? Which constraints are binding? What do the binding and non-binding constraints specifically tell us about this solution?
c. What is the rate of return for this investment decision?
d. Perform a sensitivity analysis for the available investment. Set the lower level at $750,000 and the upper level at $1,250,000. Run the report in 11 intervals and report the Objective Function value (label the report sheet ‘3d’). For each interval on the report, calculate the rate of return. What does this tell you?
