Spreadsheet Modeling & Decision Analysis book Question Case 7.2 planning diets for the food stamp program.

Diet Analysis Weekly KiloFood Units calories 1 Potatoes 0 4.29 2 High-Nutrient Vegetables 0 1.39 3 Other Vegetables 0 1.81 4 Mixtures; mostly vegetable 0 16.42 5 Vitamin-C-rich fruits 0 3.41 6 Other fruit 0 2.81 7 Whole-grain/high-fiber breakfast cereals 0 16.73 8 Other cereals 0 17.44 9 Whole-grain/high-fiber flour, meal, rice 0 16.19 10 Other flour, meal, rice, pasta 0 16.44 11 Whole grain/high fiber bread 0 11.18 12 Other breads 0 12.28 13 Bakery products 0 19.54 14 Grain mixtures 0 12.23 15 Milk, yogurt 0 3.42 16 Cheese 0 16.73 17 Cream, mixtures mostly milk 0 10.16 18 Lower-cost red meats, variety meats 0 10.87 19 Higher-cost red meats, variety meats 0 9.45 20 Poultry 0 6.43 21 Fish, shellfish 0 4.01 22 Bacon, sausage, luncheon meats 0 17.92 23 Eggs 0 5.97 24 Dry beans, peas, lentils 0 12.84 25 Mixtures, mostly meat, poultry, fish, egg 0 7.20 26 Nuts, peanut butter 0 25.71 27 Fats, oils 0 33.41 28 Sugar, sweets 0 16.46 29 Seasonings 0 1.89 30 Soft drinks, punches, ades 0 2.31 31 Coffee, tea 0 4.68 Weekly Total 0 Weekly Lower Limit 203.00 Weekly Upper Limit 203.00 Protein 10.40 5.90 8.10 21.80 5.00 1.90 63.40 34.90 47.70 42.10 35.60 35.90 34.10 49.60 20.30 105.60 16.20 73.90 72.10 61.10 65.00 53.20 50.00 82.60 37.10 102.50 2.50 3.50 8.00 0.20 20.00 0 434.00 9999 Vitamin A 0.06 192.86 18.35 11.49 7.33 3.64 135.09 143.65 2.55 0.60 1.16 0.48 4.66 13.07 9.53 46.66 17.48 52.08 0.07 13.26 2.53 8.69 25.50 1.52 55.65 0.71 74.98 0.48 38.79 12.49 0.00 0 350.00 9999 Thiamin 0.43 0.22 0.22 0.46 0.56 0.10 5.98 5.46 1.40 20.50 1.45 1.99 1.45 1.25 0.23 0.12 0.18 0.39 0.92 0.14 0.20 1.13 0.20 0.93 0.50 1.16 0.02 0.05 0.14 0.02 0.02 0 10.50 9999 Riboflavin 0.16 0.25 0.22 0.26 0.16 0.15 5.62 5.77 0.56 1.19 1.03 1.46 1.25 1.00 1.00 1.64 1.00 1.25 0.75 0.52 0.36 0.72 1.93 0.64 0.53 0.48 0.07 0.11 0.24 0.01 0.79 0 11.90 9999 Niacin 7.20 2.60 2.50 12.10 1.70 1.10 67.20 72.80 10.50 18.00 14.30 17.00 14.10 11.30 0.71 0.42 0.59 15.00 14.10 16.20 14.80 12.50 0.30 3.80 10.10 50.30 0.00 1.00 2.17 0.00 44.50 0 133.00 9999 Vitamin B6 1.22 0.47 0.30 1.80 0.30 0.60 6.23 7.09 1.33 0.54 8.26 0.24 0.27 0.48 0.24 0.33 0.19 0.77 0.90 0.80 0.69 0.75 0.53 0.82 0.53 1.61 0.23 0.04 0.52 0.00 0.10 0 14.00 9999 Vitamin B12 0.00 0.00 0.03 0.03 0.30 0.00 15.40 7.24 0.00 0.01 0.00 0.02 0.46 0.84 2.10 3.30 1.91 17.35 6.03 2.15 9.69 6.39 3.31 0.00 1.76 0.00 0.38 0.11 0.00 0.00 0.00 0 14.00 9999 Folacin 50.00 123.00 125.00 197.00 242.00 25.00 896.00 1280.00 102.00 59.00 177.00 151.00 85.00 54.00 31.00 55.00 14.00 92.00 18.00 45.00 28.00 16.00 141.00 548.00 50.00 402.00 9.00 7.00 63.00 97.00 254.00 0 1400.00 9999 Vitamin C Vitamin E Calcium 74.00 0.34 0.39 108.00 5.08 1.57 27.00 1.20 0.84 108.00 16.34 1.27 280.00 0.76 0.85 24.00 1.76 0.30 134.00 38.10 8.19 168.00 5.60 1.76 0.00 2.89 5.39 0.00 1.26 6.53 0.00 1.33 3.64 0.00 1.53 4.92 1.00 7.21 2.61 7.00 1.48 5.53 6.00 0.65 7.27 0.00 3.60 30.51 3.00 1.31 5.70 7.00 1.02 0.38 7.00 0.89 0.29 5.00 1.18 0.41 2.00 2.71 1.85 69.00 1.35 0.64 0.00 3.62 1.96 11.00 1.68 4.06 15.00 3.77 1.19 1.00 36.76 2.14 0.00 54.49 0.66 1.00 0.47 0.75 21.00 0.58 6.33 108.00 0.00 0.54 0.00 0.01 2.74 0 0 0 420.00 70.00 61.95 9999 9999 9999 Page 1 Iron 3.4 3 3 5.6 1.1 1.1 82.2 57.2 11.4 17.1 12.2 13.1 14.6 8 0.4 2.3 0.76 8.6 5.6 4 3.8 5.2 5.7 19.7 5.4 9.3 0.4 2.5 51.1 0.4 10.2 0 70 9999 Magnesium 103 72 66 201 74 42 657 127 595 141 284 109 108 107 79 117 66 65 67 52 111 47 40 452 73 726 6 33 1780 5 778 0 2450 9999 Phosphorous 2.38 1.2 1.61 5.05 1.07 0.47 20.45 4.83 18.05 11.12 8.37 5.61 5.65 6.99 5.77 27.29 4.61 6.12 6.8 4.18 7.67 5.07 7.11 12.86 6.97 15.28 0.65 0.88 2.23 0.6 4.79 0 61.95 9999 Zinc 1.7 1.1 1.6 3.2 0.4 0.3 18.6 12.6 11.5 4.3 5.6 2.8 2.8 4.8 2.5 13.4 4 15.5 10.9 5.3 5.9 7.8 4.4 9.35 4.7 13.5 0.2 0.56 2.5 0.2 1.7 0 105.00 9999 Sodium 1.18 4.31 8.41 35.10 0.09 0.08 19.89 32.54 22.68 17.47 20.12 25.28 31.36 38.73 3.26 43.54 4.34 5.18 6.90 2.05 8.17 41.64 5.04 4.72 29.10 16.74 23.59 1.78 941.11 0.59 1.13 0 0.00 324.80 Carbohydrates 93.80 30.80 37.10 174.40 81.90 71.40 323.60 382.30 328.50 346.00 217.10 225.10 300.40 196.80 32.00 10.50 107.30 1.30 0.60 0.40 2.40 7.60 4.90 235.60 58.40 88.60 14.80 406.90 36.30 58.40 87.70 0 0.00 9999 Total Fat 2.60 1.00 2.00 101.00 0.70 1.60 21.90 6.20 17.90 6.20 14.90 18.60 69.10 25.10 14.80 134.40 60.50 84.90 74.00 42.10 12.70 170.40 40.10 5.00 37.70 221.70 370.00 7.30 5.50 0.00 1.70 0 0.00 676.67 Saturated Fat 1.10 0.10 0.40 26.10 0.00 0.30 4.60 2.50 2.70 1.20 2.60 4.30 21.50 9.50 9.30 85.15 41.80 33.60 28.30 12.00 2.70 63.00 12.40 1.30 11.70 37.80 90.40 4.00 1.80 0.00 0.60 0 0.00 225.56 Monosat. Fats 0.80 0.10 0.50 21.10 0.10 0.30 6.90 1.10 4.30 2.30 3.70 7.10 27.20 8.60 4.10 38.30 13.50 37.10 32.50 16.90 4.30 79.10 15.24 1.00 14.70 102.00 137.20 2.33 1.70 0.00 0.10 0 0.00 9999 Polysat. Fat 0.40 0.40 0.80 10.80 0.10 0.40 8.90 1.80 8.80 11.00 6.40 5.20 14.30 4.80 0.60 4.00 2.00 4.50 5.90 9.20 3.80 18.40 5.50 1.80 8.00 71.10 125.80 0.50 1.20 0.00 0.50 0 0.00 9999 Cholesterol 2.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 52.00 1.00 7.00 112.00 61.00 60.00 413.00 137.00 284.00 278.00 271.00 172.00 287.00 1698.00 5.00 101.00 0.00 152.00 3.00 0.00 0.00 0.00 0 0.00 2100 Sugar 0.00 4.00 16.00 256.00 4.00 32.00 140.00 392.00 8.00 52.00 4.00 16.00 392.00 24.00 4.00 0.00 256.00 0.00 0.00 0.00 4.00 16.00 0.00 44.00 20.00 56.00 24.00 1576.00 4.00 116.00 4.00 0 0.00 2436 Cost per unit $0.391 $1.014 $0.958 $1.897 $0.721 $0.961 $2.770 $3.327 $0.966 $0.720 $1.553 $1.111 $2.460 $1.556 $0.362 $2.811 $1.223 $1.918 $2.801 $1.281 $3.471 $2.481 $0.838 $1.309 $1.967 $2.504 $1.198 $1.111 $2.077 $0.249 $6.018 $0.000 Pref. Rating 6.68 17.81 13.31 24.5 15.9 14.88 8.22 9.43 4.4 0.28 5.52 4.9 9.03 25.67 17.34 22.72 17.1 44.58 89.24 57.53 78.18 17.83 9.35 13.08 46.2 21.26 3.17 2.39 5.8 47.42 7.7 0