Linear Programming Problem using EXCEL Solver

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; and the marketing department has specified the product be designed so that a certain minimum nutritional profile is met. The data 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.00     $5.00       $3.00    $7.00     $6.00

This data can be interpreted as follows. The trail mix should have at least 16 units of vitamins, (at least) 10 units of minerals, 15units 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 these nutritional requirements minimum cost. (i.e. they would like to find the cheapest way of making a trail mix that meets the nutritional requirements). 

(a) Write down a Linear Program (i.e., mathematical) formulation for the above optimization problem. Make your formulation clear by defining the decision variables, constraints, objective function etc. clearly.

(b) Implement and solve your linear programming model in Solver. Submit your EXCEL spreadsheet Solver Model for this part (i.e., the actual EXCEL file).

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

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

(e) Patterson’s owner insists that the composition of the packaged trail mix contain at least 15 percent of each of the five ingredients in the mix. i) Indicate mathematically how to modify your model to incorporate this requirement, and ii) Implement your modification (do this in a separate sheet containing its own Solver Model) to determine the new optimal product composition and cost. State the new optimal product composition and its cost. (To obtain full credit the modified model should be linear). Submit your EXCEL Spreadsheet Solver Model for this part (i.e., the actual EXCEL file) along with your answers.