Question

Consuela Lopez is a dietitian for the basketball team at BBB College, and she is attempting to determine a nutritious lunch menu for the team. She has set the following nutritional guidelines for each lunch serving:

            1. Between 1,500 and 2,000 calories

            2. At least 5 mg of iron

            3. At least 20 but no more than 60 g of fat

            4. At least 30 g of protein

            5. At least 40 g of carbohydrates

            6. No more than 30 mg of cholesterol

She selects the menu from seven basic food items, as follows, with the nutritional contribution per pound and the cost as give:

	Calories (Per lb)	Iron (mg/lb)	Protein (g/lb)	Carbohydrates (g/lb)	Fat (g/lb)	Cholesterol (mg/lb)	$/lb
Chicken	520	4.4	17	0	30	180	0.80
Fish	500	3.3	85	0	5	90	3.70
Ground beef	860	0.3	82	0	75	350	2.30
Dried beans	600	3.4	10	30	3	0	0.90
Lettuce	50	0.5	6	0	0	0	0.75
Potatoes	460	2.2	10	70	0	0	0.40
Milk (2%)	240	0.2	16	22	10	20	0.83

The dietitian wants to select a menu to meet the nutritional guidelines while minimizing the total cost per serving.

a. Formulate a linear programming model for this problem

b. Solve the model using Excel solver

Please show step by step in excel

Answer 1

a.

Linear programming formulation in spreadsheet

b.

Solving in solver we get following optimal solution

Solver formula

Part 1

Part 2

Solver window