Reference no: EM132969852

Please use Excel Solver and show clear step up of the problem step by step.

Patterson Nut Company wishes to introduce packaged trail mix as a new product. The five ingredients for the trail mix are: seeds, raisins, granola, pecans and walnuts. Each ingredient contains a certain amount of vitamins, minerals, protein and calories. The marketing department has specified the product to be designed so that a certain minimum nutritional profile is met. The data is shown below summarize the parameters of the problem.

Units per Pound Nutritional Requirement

Seeds Raisins Granola Pecans Walnuts

vitamins 10 20 10 30 20 16

Minerals 5 7 4 9 2 10

Protein 1 4 10 2 1 15

Calories 500 450 160 300 500 600

Cost/pound $4 $5 $3 $7 $6

This data can be interpreted as follows. The trail mix should have at least 16 units of vitamins at least 10 units of minerals, at least 15 units of protein, and at least 600 calories. The columns associated with each ingredient provide us their nutritional composition and cost. For example, a pound of seeds provides 10 units of vitamins, 5 units of minerals, 1 unit of protein, 500 calories and costs $4.

Patterson would like to determine the product composition of a trail mix that meets the nutritional requirements at a minimum cost.

a) Write a mathematical linear program for the above optimization problem, including clearly defined decision variables, objective function and constraints.

b) Solve the linear programming model in Excel Solver. Please take screen shots and show step by step process.

c) What is the optimal product composition? That is, how much of each of the five ingredients should be in the trail mix? State this in words.

d) What is the cost of this optimal product composition. State this in words.

e) Patterson's owner insists that each of the trial mix ingredients makes up at least 15% of the overall trail mix. i) Show mathematically how to modify your model to incorporate this requirement and ii) Implement modification in Solver Model in a separate sheet to determine the new optimal product composition and cost. State the new optimal product composition and its cost. The model should be linear.