Optimization

I need someone who can answer these questions i need excel file and word file

5.7. Ken and Larry, Inc., supplies its ice cream parlors with three flavors of ice cream: chocolate, vanilla, and banana. Due to extremely hot weather and a high demand for its products, the company has run short of its supply of ingredients: milk, sugar, and cream. Hence, they will not be able to fill all the orders received from their retail outlets, the ice cream parlors. Due to these circumstances, the company has decided to choose the amount of each flavor to produce that will maximize total profit, given the constraints on the supply of the basic ingredients.

The chocolate, vanilla, and banana flavors generate, respectively, $1.00, $0.90, and $0.95 of profit per gallon sold. The company has only 200 gallons of milk, 150 pounds of sugar, and 60 gallons of cream left in its inventory. The linear programming formulation for this problem is shown below in algebraic form.

Let

C = Gallons of chocolate ice cream produced

V = Gallons of vanilla ice cream produced

B = Gallons of banana ice cream produced

Maximize Profit = 1.00C + 0.90V + 0.95B

subject to

Milk: 0.45C + 0.50V + 0.40B ≤ 200 gallons

Sugar: 0.50C + 0.40V + 0.40B ≤ 150 pounds

Cream: 0.10C + 0.15V + 0.20B ≤ 60 gallons

and

C≥0 V≥0 B≥0

This problem was solved using Solver. The spreadsheet (already solved) and the sensitivity report are shown below. (Note: The numbers in the sensitivity report for the milk constraint are missing on purpose, since you will be asked to fill in these numbers in part f.)

For each of the following parts, answer the question as specifically and completely as possible without solving the problem again with Solver. Note: Each part is independent (i.e., any change made to the model in one part does not apply to any other parts).

Variable Cells

Cell

Name

Final Value

Reduced Cost

Objective Coefficient

Allowable Increase

Allowable Decrease

$B$10

Gallons Produced Chocolate

0

−0.0375

1

0.0375

1E + 30

$C$10

Gallons Produced Vanilla

300

0

0.9

0.05

0.0125

$D$10

Gallons Produced Banana

75

0

0.95

0.0214

0.05

Constraints

Cell

Name

Final Value

Shadow Price

Constraint R. H. Side

Allowable Increase

Allowable Decrease

$E$5

Milk Used

$E$6

Sugar Used

150

1.875

150

10

30

$E$7

Cream Used

60

1

60

15

3.75

  1. What is the optimal solution and total profit?
  1. Suppose the profit per gallon of banana changes to $1.00. Will the optimal solution change and what can be said about the effect on total profit?
  1. Suppose the profit per gallon of banana changes to 92¢. Will the optimal solution change and what can be said about the effect on total profit?
  1. Suppose the company discovers that three gallons of cream have gone sour and so must be thrown out. Will the optimal solution change and what can be said about the effect on total profit?
  1. Suppose the company has the opportunity to buy an additional 15 pounds of sugar at a total cost of $15. Should it do so? Explain.
  1. Fill in all the sensitivity report information for the milk constraint, given just the optimal solution for the problem. Explain how you were able to deduce each number.


5.12. Consider a resource-allocation problem having the following data:

Resource

Resource Usage per Unit of Each Activity

Amount of Resource Available

1

2

1

1

3

8

2

1

1

4

Unit profit

$1

$2

The objective is to determine the number of units of each activity to undertake so as to maximize the total profit.

  1. Use the graphical method to solve this model.
  1. Use graphical analysis to determine the shadow price for each of these resources by solving again after increasing the amount of the resource available by one.
  1. Use the spreadsheet model and Solver instead to do parts a and b.
  1. For each resource in turn, use a parameter analysis report to systematically generate the optimal solution and the total profit when the only change is that the amount of that resource available increases in increments of 1 from 4 less than the original value up to 6 more than the original value. Use these results to estimate the allowable range for the amount available for each resource. (Optional: solving this question requires Analytic Solver. If you don’t have it, you don’t have to solve this question. Just move on to the next.)
  1. Use Solver’s sensitivity report to obtain the shadow prices. Also use this report to find the range for the amount of each resource available over which the corresponding shadow price remains valid.
  1. Describe why these shadow prices are useful when management has the flexibility to change the amounts of the resources being made available.


5.19.* Reconsider Problem 5.12. Now suppose that all of the parameters are uncertain, with ranges of uncertainty as given in the table below. Use the procedure for robust optimization with independent parameters to find the solution that maximizes profit when the solution also is guaranteed to be feasible.

Resource

Resource Usage per Unit of Each Activity

Amount of Resource Available

1

2

1

0.9–1.1

2.7–3.3

7.2–8.8

2

0.8–1.2

0.7–1.3

3.5–4.5

Unit Profit

$0.90–$1.10

$1.75–$2.25


5.18. Reconsider the example illustrating the use of robust optimization that was presented in Section 5.7. Wyndor management now feels that there is uncertainty in all of the parameters of the problem—the unit profit per door and window (PD and PW), the hours of production time used for each door or window produced across the three plants (HD1, HW2, HD3, and HW3) Page 196and the three right-hand-sides representing the hours available at each plant (RHS1, RHS2, and RHS3). The original estimates along with the ranges of uncertainty are shown in the table below. Apply the procedure for robust optimization with independent parameters to find the solution that maximizes profit when the solution also is guaranteed to be feasible.

Parameter

Original Estimate

Range of Uncertainty

PD

$300

$250–$350

PW

$500

$400–$600

HD1

1

0.9–1.1

HW2

2

1.6–2.4

HD3

3

2.5–3.5

HW3

2

1.8–2.2

RHS1

4

3.5–4.5

RHS2

12

11–13

RHS3

18

16–20

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
error: Content is protected !!