Question

please show how to do in excel solver

Problem: Coneheads 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 of 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 only has 200 gallons of milk, 150 pounds of sugar, and 60 gallons of cream left in its inventory. A gallon of each of the different flavors of ice cream requires the amounts of each of the main ingredients shown in the table below. Ingredient Chocolate Vanilla Banana Milk (gallons) 0.45 0.50 0.40 Sugar (pounds) 0.50 0.40 0.40 Cream (gallons) 0.10 0.15 0.20 a. Solve a linear program to determine the profit-maximizing quantities of each flavor to produce subject to the available ingredients. [20 points] b. Suppose the profit per gallon of banana ice cream changes to $1.00. Will the optimal solution change? What can be said about the effect on total profit? [10 points] c. Suppose the company discovers that three gallons of cream have gone sour and must be thrown out. Will the optimal solution change? What can be said about the effect on total profit? [10 points] d. 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 your recommendation. [10 points]

Answer 1

A. Let number of ice creams of flavor chocolate, vanilla and banana be x, y, z respectively.

Profit Z = 1x +0.90y+0.95z

Milk - 0.45x+0.50y+0.40z = 200

Sugar - 0.50x+0.40y+0.40z = 150

Cream - 0.10x+0.15y+0.20z = 60

Where x,y,z >0

B. Yes, the optimal solution changes, since more ice creams of banana flavor will be produced. The profit will be increased by $0.50.

C. No, the optimal solution will not change, but the number of products produced will be reduced. And the total profit will be reduced as the number of products will be reduced.

D. No, the company should not buy only sugar. Because with increased sugar alone it cannot produce the ice creams. Therefore, the company should not buy sugar.