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

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:

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.

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 (P_D and P_W), the hours of production time used for each door or window produced across the three plants (HD₁, H_W2, H_D3, and H_W3) Page 196and the three right-hand-sides representing the hours available at each plant (RHS₁, RHS₂, and RHS₃). 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.