Question

Grainy Nutrition produces a cereal that must meet the Canadian Recommended Daily Allowance for four key nutrients:

NUTRIENT	Requirement for single serving
Protein	3 units
Riboflavin	2 units
Phosphorus	1 unit
Magnesium	0.425 unit

Grainy Nutrition uses three base grains (A, B, and C) ordered by Kgs which have the following properties:

GRAIN	COST PER Kg (CENTS)	PROTEIN (UNITS/Kg)	RIBOFLAVIN (UNITS/Kg)	PHOSPHOROUS (UNITS/Kg)	MAGNESIUM (UNITS/Kg)
A	33	22	16	8	5
B	47	28	14	7	0
C	38	21	25	9	6

Step 1. Create a formulation by explicitly stating the decision variables, the optimization function, and all of the constraint functions. (both explicit and non-negativity).

Step 2. Use Excel (including Excel QM if you prefer) to set up a Linear Programming spreadsheet that can be used to solve this problem. See Lecture 15 for a step by step design.

Step 3. When the spreadsheet is complete and all of your parameters are present, run Solver to find the optimal solution.

Step 4. What is the optimal solution to this linear problem (Grain A, Grain B, Grain C) and the optimal cost this represents on a per-serving basis? Grain A amount = _______ Kg, Grain B amount = _______ Kg, Grain C amount = _______ Kg, Cost = _______cents.

Answer 1

Excel Spreadsheet : Note we used following formula to get objective function and constraints :

Obj. function :  =I4*I5+J4*J5+K4*K5

Constraints : =I4*I7+J4*J7+K4*K7

=I4*I8+J4*J8+K4*K8

=I4*I9+J4*J9+K4*K9

=I4*I10+J4*J10+K4*K10

By running "Simplex LP" option in solver, we get following solution :

4.

X1 = 0.13626 kg , X2 = X3 = 0 ,  

cost = 4.5 cents