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 
 What is the optimal solution and total profit?
 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?
 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?
 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?
 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.
 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 resourceallocation 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.
 Use the graphical method to solve this model.
 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.
 Use the spreadsheet model and Solver instead to do parts a and b.
 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.)
 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.
 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 (P_{D} and P_{W}), the hours of production time used for each door or window produced across the three plants (HD_{1}, H_{W}_{2}, H_{D}_{3}, and H_{W}_{3}) Page 196and the three righthandsides representing the hours available at each plant (RHS_{1}, RHS_{2}, and RHS_{3}). 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 
P_{D} 
$300 
$250–$350 
P_{W} 
$500 
$400–$600 
HD_{1} 
1 
0.9–1.1 
HW_{2} 
2 
1.6–2.4 
HD_{3} 
3 
2.5–3.5 
HW_{3} 
2 
1.8–2.2 
RHS_{1} 
4 
3.5–4.5 
RHS_{2} 
12 
11–13 
RHS_{3} 
18 
16–20 
0 comments