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?

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more