# linear optimization problems in one excel workbook.

Instructions:

· Solve the following Linear Optimization problems in one Excel workbook.

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

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

(tons/hour)

(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?

Calculate the price
Pages (550 words)
\$0.00
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with Australia Assessments
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Testimonials
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
SEO
Great work.Recommendable
Customer 463647, May 14th, 2023
Military
Very good job
Customer 456821, May 11th, 2022
Medicine
Great job.
Customer 462457, April 28th, 2022
Good work.
Customer 458115, May 10th, 2022
Communications
Nice. Be keen on instructions.
Customer 462787, April 1st, 2022
Very well done, thank you!
Customer 462533, February 6th, 2022
Excellent as always!
Customer 454647, June 26th, 2020
English 101
Looks really good, thanks
Customer 454425, June 16th, 2020
Thank you very much! Efficient and professional! I was literally "blocked" since 3 days
Customer 462519, February 3rd, 2022
Nursing
yes
Customer 452577, January 27th, 2020
Healthcare & Medical
Good work!
Customer 463469, October 17th, 2022
Other
Its great, Thank you!
Customer 464127, April 26th, 2023
11,595
Customer reviews in total
96%
Current satisfaction rate
3 pages
Average paper length
37%
Customers referred by a friend