linear optimization problems in one excel workbook.

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?