The kitchen manager for Sing Sing Prison is trying to decide what to feed its prisoners.She would like to offer some combination of milk,beans,and oranges.The goal is to minimize cost,subject to meeting the minimum nutritional requirements imposed by law.The cost and nutritional content of each food,along with the minimum nutritional requirements,are shown below.What diet should be fed to each prisoner? (a)Formulate and solve a linear programming model for this problem in a spreadsheet.(b)Formulate this same model algebraically.

Question

The kitchen manager for Sing Sing Prison is trying to decide what to feed its prisoners.She would like to offer some combination of milk,beans,and oranges.The goal is to minimize cost,subject to meeting the minimum nutritional requirements imposed by law.The cost and nutritional content of each food,along with the minimum nutritional requirements,are shown below.What diet should be fed to each prisoner?  (a)Formulate and solve a linear programming model for this problem in a spreadsheet.(b)Formulate this same model algebraically.

Quizplus · Accepted Answer

The Answer is (a)This is a cost-benefit-trade-off problem.The activities are the quantities of food to feed each prisoner and the required benefits are the minimum nutritional requirements.We will start to build a spreadsheet by entering the data.The data for this problem are the nutrient content of each food,the minimum daily requirement for each nutrient,and the cost of each food.The data in the spreadsheet would be entered as displayed below,where range names of UnitCost (C5:E5),NutritionalContents (C9:E11),and MinimumRequirement (H9:H11)are assigned to the corresponding data cells.

(a)This is a cost-benefit-trade-off problem.The activities are the quantities of food to feed each prisoner and the required benefits are the minimum nutritional requirements.We will start to build a spreadsheet by entering the data.The data for this problem are the nutrient content of each food,the minimum daily requirement for each nutrient,and the cost of each food.The data in the spreadsheet would be entered as displayed below,where range names of UnitCost (C5:E5),NutritionalContents (C9:E11),and MinimumRequirement (H9:H11)are assigned to the corresponding data cells. The decisions to be made in this problem are how much of each food type should be fed to each prisoner.Therefore,we add three changing cells in C13:E13,with range name Quantity.The values in Quantity (C13:E13)will eventually be determined by Solver.For now,an arbitrary value of 1 is entered for each food type.The goal is to minimize the total cost per prisoner.Thus,the objective cell should calculate this cost: Total Cost = ($2)(gallons of milk)+ ($0.20)(cups of beans)+ ($0.25)(number of oranges)or Total Cost = SUMPRODUCT(UnitCost,Quantity).This formula is entered into cell H14. The functional constraints in this problem involve the minimum daily requirement of each nutrient.Given the amount of food fed each prisoner (the changing cells in C13:E13),we calculate the total resources used in F9:F11.For niacin,this will be =SUMPRODUCT(C9:E9,$C$13:$E$13).Using an absolute reference for the acres planted,this formula can be copied into cells F10-F11 to calculate the thiamin and vitamin C.The benefit achieved (total of each nutrient)must be >= the minimum needed (H9:H11),as indicated by the >= in G9:G11. The Solver information and solved spreadsheet are shown below. Thus,each prisoner should be fed a daily average of 2.574 cups of beans and 0.484 oranges for a total cost of $0.64.(b)To build an algebraic model for this problem,start by defining the decision variables.In this case,the three decisions are how many gallons of milk,cups of beans,and how many oranges to feed each prisoner.These variables are defined below: Let M = gallons of milk fed to each prisoner,B = cups of beans fed to each prisoner,O = number of oranges fed to each prisoner.Next determine the goal of the problem.In this case,the goal is to meet the nutritional requirements at the lowest possible cost.Each gallon of milk costs $2,each cup of beans costs $0.20,and each orange costs $0.25.The objective function is therefore Minimize Total Cost = $2.00M + $0.20B + $0.25O.The nutritional requirements include minimum requirements for niacin,thiamin,and vitamin C.The data for the nutritional contents of each type of food can be used to calculate the total level of each nutrient achieved as a function of the decision variables.The total nutrients need to be greater than or equal to the minimum requirement.These constraints are therefore as follows: Niacin: 3.2M + 4.9B + 0.8O ≥ 13mg,Thiamin: 1.12M + 1.3B + 0.19O ≥ 1.5mg,Vitamin C: 32M + 93O ≥ 45mg.After adding nonnegativity constraints,the complete algebraic formulation is given below: Let M = gallons of milk fed to each prisoner,B = cups of beans fed to each prisoner,O = number of oranges fed to each prisoner.Minimize Total Cost = $2.00M + $0.20B + $0.25O.subject to Niacin: 3.2M + 4.9B + 0.8O ≥ 13mg,Thiamin: 1.12M + 1.3B + 0.19O ≥ 1.5mg,Vitamin C: 32M + 93O ≥ 45mg.and M ≥ 0,B ≥ 0,O ≥ 0.

The decisions to be made in this problem are how much of each food type should be fed to each prisoner.Therefore,we add three changing cells in C13:E13,with range name Quantity.The values in Quantity (C13:E13)will eventually be determined by Solver.For now,an arbitrary value of 1 is entered for each food type.The goal is to minimize the total cost per prisoner.Thus,the objective cell should calculate this cost: Total Cost = ($2)(gallons of milk)+ ($0.20)(cups of beans)+ ($0.25)(number of oranges)or Total Cost = SUMPRODUCT(UnitCost,Quantity).This formula is entered into cell H14.

The functional constraints in this problem involve the minimum daily requirement of each nutrient.Given the amount of food fed each prisoner (the changing cells in C13:E13),we calculate the total resources used in F9:F11.For niacin,this will be =SUMPRODUCT(C9:E9,$C$13:$E$13).Using an absolute reference for the acres planted,this formula can be copied into cells F10-F11 to calculate the thiamin and vitamin C.The benefit achieved (total of each nutrient)must be >= the minimum needed (H9:H11),as indicated by the >= in G9:G11.

The Solver information and solved spreadsheet are shown below.

Thus,each prisoner should be fed a daily average of 2.574 cups of beans and 0.484 oranges for a total cost of $0.64.(b)To build an algebraic model for this problem,start by defining the decision variables.In this case,the three decisions are how many gallons of milk,cups of beans,and how many oranges to feed each prisoner.These variables are defined below: Let M = gallons of milk fed to each prisoner,B = cups of beans fed to each prisoner,O = number of oranges fed to each prisoner.Next determine the goal of the problem.In this case,the goal is to meet the nutritional requirements at the lowest possible cost.Each gallon of milk costs $2,each cup of beans costs $0.20,and each orange costs $0.25.The objective function is therefore Minimize Total Cost = $2.00M + $0.20B + $0.25O.The nutritional requirements include minimum requirements for niacin,thiamin,and vitamin C.The data for the nutritional contents of each type of food can be used to calculate the total level of each nutrient achieved as a function of the decision variables.The total nutrients need to be greater than or equal to the minimum requirement.These constraints are therefore as follows: Niacin: 3.2M + 4.9B + 0.8O ≥ 13mg,Thiamin: 1.12M + 1.3B + 0.19O ≥ 1.5mg,Vitamin C: 32M + 93O ≥ 45mg.After adding nonnegativity constraints,the complete algebraic formulation is given below: Let M = gallons of milk fed to each prisoner,B = cups of beans fed to each prisoner,O = number of oranges fed to each prisoner.Minimize Total Cost = $2.00M + $0.20B + $0.25O.subject to Niacin: 3.2M + 4.9B + 0.8O ≥ 13mg,Thiamin: 1.12M + 1.3B + 0.19O ≥ 1.5mg,Vitamin C: 32M + 93O ≥ 45mg.and M ≥ 0,B ≥ 0,O ≥ 0.

The Kitchen Manager for Sing Sing Prison Is Trying to Decide