Question

Nutri-Jenny is a weight-management center. It produces a wide variety of frozen entrées for consumption by its clients. The entrées are strictly monitored for nutritional content to ensure that the clients are eating a balanced diet. One new entrée will be a beef sirloin tips dinner. It will consist of beef tips and gravy, plus some combination of peas, carrots, and a dinner roll. Nutri-Jenny would like to determine what quantity of each item to include in the entrée to meet the nutritional requirements, while costing as little as possible. The nutritional information for each item and its cost are given in the table on the bottom of the preceding page.

The nutritional requirements for the entrée are as follows: (1) it must have between 280 and 320 calories, (2) calories from fat should be no more than 30 percent of the total number of calories, and (3) it must have at least 600 IUs of vitamin A, 10 milligrams of vitamin C, and 30 grams of protein. Furthermore, for practical reasons, it must include at least 2 ounces of beef, and it must have at least half an ounce of gravy per ounce of beef.

Formulate and solve a linear programming model for this problem on a spreadsheet by using the Excel Solver. Use Analytic Solver to formulate and solve this model in a spreadsheet. Formulate this same model algebraically. Please show the formulas on all the cells show I can see how to see this.

Answer 1

a.Let

X1 = Ounces of Beef tips included in the diet element.
X2 = Ounces of Gravy included in the diet element.
X3 = Ounces of Peas included in the diet element.
X4 = Ounces of Carrots included in the diet element.
​​​​​​​X5 = Ounces of Dinner Roll included in the diet element.

Minimize Z = total cost = 40 X1 + 35 X2 + 15 X3 + 18 X4 + 10 X5

Subject to,

54 X1 + 20 X2 + 15 X3 + 8 X4 + 40 X5 >= 280 (Min calories)

54 X1 + 20 X2 + 15 X3 + 8 X4 + 40 X5 <= 320 (Max calories)

19 X1 + 15 X2 + 0 X3 + 0 X4 + 10 X5 <= 30% * (73 X1 + 35 X2 + 15 X3 + 8 X4 + 50 X5)
or, -2.9 X1 + 4.5 X2 - 4.5 X3 - 2.4 X4 - 5.0 X5 <= 0

0 X1 + 0 X2 + 15 X3 + 350 X4 + 0 X5 >= 600 (min vitamin A)

0 X1 + 1 X2 + 3 X3 + 1 X4 + 0 X5 >= 10 (min vitamin C)

8 X1 + 0 X2 + 1 X3 + 1 X4 + 1 X5 >= 30 (min protein)

X1 >= 2 (min beef)

X2 - 0.5 X1 >= 0 (min gravy)

X1, X2, X3, X4, X5 >= 0

(b)

Solution

Objective Cell (Min)
	Cell	Name	Original Value	Final Value
	$H$4	Cost	0.00	252.93
Variable Cells
	Cell	Name	Original Value	Final Value
	$C$3	Ounces of Beef tips	0.00	3.17
	$D$3	Ounces of Gravy	0.00	1.59
	$E$3	Ounces of Peas	0.00	2.27
	$F$3	Ounces of Carrots	0.00	1.62
	$G$3	Ounces of Dinner Roll	0.00	0.75