Question

An agricultural supply company is developing a livestock feed mix that will consist of three ingredients: ingredient A, ingredient B, and ingredient C. The table below displays nutrition and cost information per ounce of each of these ingredients.

Ingredient	Calories	Far gram	% RDA Vitamin A	%RDAVitamin D	RDAProtein	cost
A	830	4	35%	5%	35%	$0.75
B	600	6	25%	41%	21%	$0.63
C	750	3	4%	15%	22%	$0.20

The company wants to create a mix that contains no more than 750 calories per ounce and no more than 10 grams of fat. The desired mix should also meet at least 25% of the Recommended Daily Allowance (RDA) of each of the following nutrients: Vitamin A, Vitamin D, and Protein. The company wants to develop the feed mix as cheaply as possible.

A) Following the steps below formulate this problem as a linear programming model, and use the Excel solver to find the optimal percentages of each ingredient to include in the feed mix. Submit your Excel workbook together with your answer sheet.

Decision variables.(We want to find out the percentage of each one the three ingredients in one ounce of the mix)

Objective function.(How do we measure the cost of the mix in terms of percentages of the ingredients? What is the sense of optimization?)

Constraints.The total of the percentages of ingredients has to be equal to 100%.

Limits on the following nutrients in the mix.(Left hand side: express the amount of nutrient in the mix in terms of percentages of ingredientsRight hand side: limit amount)

Calories

Fat

Vitamin A

Vitamin D

Protein

B) Inspect the sensitivity report and find out the change in the optimal cost if we reduce the calorie limit from 750 to 700, and the fat limit from 10 to 8. Does the cost increase or decrease and by how much?

Answer 1

Solution

Formulation as Linear Programming model:

Decision variables: A,B,C = Ounces of each ingredient to be used to create the an ounce of feed mix.

Objective: Minimize Z = 0.75A + 0.63B + 0.20C

s.t.

A+B+C = 1

830A + 600B + 750C <= 750

4A+6B+3C <= 10

0.35A + 0.25B + 0.04C >= 0.25

0.5A + 0.41B + 0.15C >= 0.25

0.35A + 0.21B + 0.22C >= 0.25

A,B,C >= 0

Solution of the problem using Excel Solver is as follows

Formula: E2 =SUMPRODUCT(B2:D2,$B$11:$D$11) Â Â copy to E2:E8

Optimal result:

Ingredient A = 0.498 oz

Ingredient B = 0.265 oz

Ingredient C = 0.237 oz

Total cost per ounce of feed mix = $ 0.59