Question

FarmFresh Foods manufactures a snack mix called TrailTime by blending three ingredients: a dried fruit mixture, a nut mixture, and a cereal mixture. Information about the three ingredients (per ounce) is shown below.

Ingredient	Cost	Volume	Fat Grams	Calories
Dried Fruit	0.40	1/4 cup	0	160
Nut Mix	0.60	3/8 cup	9	390
Cereal Mix	0.25	1 cup	2	60

The company needs to develop a linear programming model whose solution would tell them how many ounces of each mix to put into the TrailTime blend. TrailTime is packaged in boxes that will hold between three and four cups. The blend should contain no more than 1,100 calories and no more than 30 grams of fat. Dried fruit must be at least 25% of the volume (in cups) of the mixture, and nuts must be no more than 20% of the weight (in ounces) of the mixture.

Use Excel's Solver to obtain the optimal solution for this problem. In other words: what is the most cost effective way to create the TrailTime blend? What is the cost?

Answer 1

Where Objective function Min C9+D9+E9

Subject to,

Constraint 1:   C13*D3+D13*D4+E13*D5 <= 4

Constraint 2:   C13*D3+D13*D4+E13*D5 >= 3

Constraint 3:   C15*C8+D15*D8+E15*E8 <= 1100

Constraint 4:   C16*C8+D16*D8+E16*E8 <= 30

Constraint 5:   0.25*(C17*C8+D17*D8+E17*E8) <= D3*C8

Constraint 6: 0.2*(C18+D18+E18) >= D8

Solution is

The Minimum Cost = 1.76

Dried Fruit = 3 ounce

Nut Mix = 0 ounce

Cereal Mix = 2.25 ounce