Question

Consider “The Big Mac Attack”. Suppose you wish to select a nutritionally complete diet consisting of foods taken from this menu at the least cost. Specifically, suppose you would like to design a diet that provides at least 100% of the U.S.RDA (recommended daily allowance) of vitamins A, C, B, B1, B2, niacin, calcium, and iron, supplies at least 55 grams of protein, contains at most 3 grams of sodium, and provides at most 30% of its calories from fat. (IMPORTANT NOTE: 1 gram of fat contains nine calories)

Please scroll over to see the entire data set.

a.) Using Excel Solver, determine the least cost daily diet that satisfies all of the nutritional requirements mentioned above. (Hint: We also have to make sure that solver doesn’t assign decimals to something that we must buy in whole units).

	Hamburger	McLean Deluxe	Big Mac	French Fries	Chic McNuggets	Honey	Chef Salad	Garden Salad	Egg McMuffin	Wheaties	Vanilla Yogurt	Milk	Orange Juice	Grapefruit Juice	Apple Juice
Price	($)	0.59	1.79	1.65	0.68	1.56	0	2.69	1.96	1.36	1.09	0.63	0.56	0.88	0.68	0.68
Calories		255	320	500	220	270	45	170	50	280	90	105	110	80	80	90
Protein	(g)	12	22	25	3	20	0	17	4	18	2	4	9	1	1	0
Fat	(g)	9	10	26	12	15	0	9	2	11	1	1	2	0	0	0
Sodium	(mg)	490	670	890	110	580	0	400	70	710	220	80	130	0	0	5
Vit. A		4	10	6	0	0	0	100	90	10	20	2	10	0	0	0
Vit. C		4	10	2	15	0	0	35	35	0	20	0	4	120	100	2
Vit B1		20	25	30	10	8	0	20	6	30	20	2	8	10	4	2
Vit. B2	% U.S. RDA	10	20	25	0	8	0	15	6	20	20	10	30	0	2	0
Niacin		20	35	35	10	40	0	20	2	20	20	2	0	0	2	0
Calcium		10	15	25	0	0	0	15	4	25	2	10	30	0	0	0
Iron		15	20	20	2	6	0	8	8	15	20	0	0	0	0	4

Answer 1

Steps to be followed:

1.Define objective function : sumproduct of quantities of each product and price as shown in figue and in solver it is kept as minimum

2. As shown above constraints are calculated using sumproduct of quantity and respective variable .

3. Constraints are defined as shown in figure below :

4.Enter values in solver as shown below and click on solve . Following output will be seen