Linear Programming problem Diet Mix:  Computer Exercise

Set this up in EXCEL and solve using Solver
set up the same problem in POM-Win and compare the results. This comparison should help you interpret the sensitivity and range analyses from the solver output.

Rachel Yang, campus dietitian for a small Illinois college, is responsible for formulating a nutritious meal plan for students. For an evening meal, she feels that the following five meal-content requirements should be met: (1) between 900 and 1,500 calories (you will need two rows for calories, one to set the upper limit, one to set the lower limit); (2) at least 4 milligrams of iron; (3) no more than 50 grams of fat; (4) at least 26 grams of protein; and (5) no more than 50 grams of carbohydrates.
On a particular day, Rachel’s food stock includes seven items that can be prepared and served for supper to meet these requirements. The cost per pound for each food item and its contribution to each of the five nutritional requirements are given in the accompanying table:
What combination and amounts of food items will provide the nutrition Rachel requires at the least total food cost?
(a) Formulate as an LP problem.
(b) What is the cost per meal?
(c) What would the value be of relaxing each of the constraints  (changing the RHS) by one unit? These are the shadow prices.
(d) How sensitive is the solution to price changes in milk, ground meat, fish, and chicken? Report, in each case, upper and lower limits on the ranges of optimality

Procedure:

1. Use the text tool in Acrobat to copy it from the Heizer-Render website. Paste into EXCEL. use data/text-to columns (space delimiter) to parse it, make necessary adjustments. See the Video
2. Copy the data from this page and paste into EXCEL. use data/text-to-columns (space delimiter) to parse it, make necessary adjustments.
3. Type the data in, being careful to avoid transcription errors.

Use copy, paste special /transpose (video on paste special transpose) to put the data in a more familiar configuration (columns for decision variables, rows for objective function and constraints). Duplicate the calories row to allow two (upper and lower) constraints on calories. Move the cost coefficients row (objective function) to the top data row. Add a row for Values (which Solver will manipulate), and rows for Upper and Lower Limits on the ranges of optimality. Add columns for direction of constraint, RHS, amount provided, and Shadow Price for each ingredient.

The formula for each 'amount provided' will consist of the sum of each of the (variable values * amount provided per pound)
use F4 to absolute references to the value row and you will only have to enter the calculation once, then you can just copy it down to subsequent rows.  In the event solver tells you there is "no feasible solution found" check your formulas and make sure you have the right directionality on the constraints. (The selections for sensitivity reports will be greyed out if there is no feasible solution.)
Note that the amount provided column for the cost row will give the cost.
Use Solver to solve this problem and give sensitivity analysis. See the videos on the X-Y problem to see how to add in and use Solver.

Tips for using POM-WIN

Printouts due:

Write brief commentaries on the summary excel sheet about the significance of the result, and the shadow prices and ranges of optimality. Does this look like a wholesome attractive meal that you would like to eat?
 
 

Table of Food Values* and Costs
Food Calories/ Iron Fat Protein Carbohydrates Cost/
Item Pound (Mg/Lb) (Gm/Lb) (Gm/Lb) (Gm/Lb) Pound ($)
Milk 295 0.2 16 16 22 0.60
Ground meat 1216 0.2 96 81 0 2.45
Chicken 394 4.3 9 74 0 1.67
Fish 358 3.2 0.5 83 0 2.25
Beans 128 3.2 0.8 7 28 0.58
Spinach 118 14.1 1.4 14 19 1.17
Potatoes 279 2.2 0.5 8 63 0.33
Source: C. F. Church and H. N. Church, Bowes and Church’s, Food Values of Portions Commonly Used, 12th ed. Philadelphia, J. B.
Lippincott, 1975.
 

Things that can go wrong:

Proofread the directionalities of the constraints.