Question

Bob, a nutritionist who works for the University Medical Center, has been asked to prepare special diets for two patients, Susan and Tom. Bob has decided that Susan's meals should contain at least 400 mg of calcium, 20 mg of iron, and 50 mg of vitamin C, whereas Tom's meals should contain at least 350 mg of calcium, 15 mg of iron, and 40 mg of vitamin C. Bob has also decided that the meals are to be prepared from three basic foods: Food A, Food B, and Food C. The special nutritional contents of these foods are summarized in the accompanying table. Find how many ounces of each type of food should be used in a meal so that the minimum requirements of calcium, iron, and vitamin C are met for each patient's meals. Contents (mg/oz) Calcium Iron Vitamin C Food A 30 1 2 Food B 25 1 5 Food C 20 2 4 Susan's meals: Food A oz Food B oz Food C oz Tom's meals: Food A oz Food B oz Food C oz?

Answer 1

Create spreadsheet model as follows:

T H 4 A Ⓡfx =SUMPRODUCT(B4:G4,$B$13:$G$13) B C D E F G Susan's meals Tom's meals Food A Food B Food C Food A Food B | Food C H I J K Total 1 25 20 400 30 1 2 6 Calcium 7 Iron 8 Vitamin C 9 Calcium 10 Iron 11 Vitamin c 20 L | 2 30 1 25 1 | | 5 T | 2 4 0 13 Result:

EXCEL FORMULAS:

	Susan's meals			Tom's meals
	Food A	Food B	Food C	Food A	Food B	Food C
Total	1	1	1	1	1	1	=SUMPRODUCT(B4:G4,$B$13:$G$13)
Calcium	30	25	20				=SUMPRODUCT(B6:G6,$B$13:$G$13)	>=	400
Iron	1	1	2				=SUMPRODUCT(B7:G7,$B$13:$G$13)	>=	20
Vitamin C	2	5	4				=SUMPRODUCT(B8:G8,$B$13:$G$13)	>=	50
Calcium				30	25	20	=SUMPRODUCT(B9:G9,$B$13:$G$13)	>=	350
Iron				1	1	2	=SUMPRODUCT(B10:G10,$B$13:$G$13)	>=	15
Vitamin C				2	5	4	=SUMPRODUCT(B11:G11,$B$13:$G$13)	>=	40
Result:

Enter Solver Parameters as follows:

S Solver Parameters Set Objective: $H54 To: Max Min value of: 0 By Changing Variable Cells: $B$13:$G$13 Subject to the Constraints: SH$6:SH$11 >= $J$6:$J$11 Add Change Delete Reset All Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. Solve Close

Click Solve to generate the solution:

H4 A B @ fx =SUMPRODUCT(B4:G4,$B$13:$G$13) C D E F G Susan's meals Tom's meals Food B Food Food A Food B Food C K L M N O P Q R Food A Solver Results Total 28.33 400 400 20 6 Calcium 7 Iron 8 Vitamin C 9 Calcium 10 Iron 11 Vitamin C 12 13 Result: 14 | || || | Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution Sensitivity Limits Restore Original Values 350 40 Return to Solver Parameters Dialog 7.50 3.334 .58 7.50 3.332 .08 OK | Cancel Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution.

Optimal solution:

Susan's meals: Food A = 7.50 oz, Food B = 3.33 oz, Food C = 4.58 oz

Tom's meals: Food A = 7.50 oz, Food B = 3.33 oz, Food C = 2.08 oz