Question

Linear Programming

The Whole Food Nutrition Centre uses three bulk grains, A, B, and C, to blend a natural cereal that it sells by the pound. The cost of each bulk grain is indicated in the table below, along with the units of four types of nutrient (Nutrient 1, 2, 3, and 4) that is contained in each pound of the grains.  

After consulting with the nutritionist, Whole Food has determined that each pound serving of the cereal should contain 24 units of Nutrient 1, 16 units of Nutrient 2, 8 units of Nutrient 3, and 3.4 units of Nutrient 4. Whole Food wishes to decide how to blend the three types of grain in order to minimize the total cost of each pound of the cereal. In other words, they wish to solve the following optimization:  

Minimize 33A + 47B + 38C Subject to:  

22A + 28B + 21C ≥ 24

16A + 14B + 25C ≥ 16  

8A+7B +9C≥8 5A + 6C ≥ 3.4  

A+B +C=1 A,B,C 0  

	Grain A	Grain B	Grain C	Nutritionist's requirement
Nutrient 1	22	28	21	24
Nutrient 2	16	14	25	16
Nutrient 3	8	7	9	8
Nutrient 4	5	0	6	3.4
Cost per pound of grain (cents)	33	47	38

1. Solve this problem in Excel using Solver and Report the results to the manager of Whole Food in plain English.   

2. Based on the solution you found in (1), how many constraints regarding the nutrients are binding. For each of the non-binding constraints, what is the surplus?   

In order to gain more insights, Whole Food obtained sensitivity report in Solver. This will help you to answer Questions (4)-(6) below.  

3. How sensitive is Whole Food’s blending strategy to the cost per pound for each type of grains individually? Which is it most sensitive to?   

4. If the cost of Grain B changes to 40 cents per pound, while all other costs remain the same, should Whole Food keep its current blending strategy or change it? Why or why not?   

5. If the nutrient requirement for Nutrient 2 increases from 16 units to18 units, should Whole Food keep its current blending strategy or change it? Why or why not?

Answer 1

1)

Create Excel model as follows:

Enter Solver Parameters as follows:

Click Solve to generate results. After clicking, values appear automatically in yellow cells.

On the Solver Results window, check Sensitivity to generate sensitivity report

Click OK

Sensitivity report

-------------------------------------------------------------------------------------------------

2)

Binding constraint is one, whose Final Value is equal to the Constraint R.H. Side.

Looking at the sensitivity report, we see that Nutrient 1, 3 and 4 are binding constraints. and last constraint (A+B+B=1) is also a binding constraint.

Nutrient 2 is non-binding constraint. For non-binding constraint, surplus = Final Value - Constraint R.H. Side

Surplus for Nutrient 2 = 18.8 - 16 = 2.8

-------------------------------------------------------------------------------------------------

3)

Refer Variable Cells section of the Sensitivity report, Allowable Decrease for C is 7.33, which is the lowest

This means that this strategy is more sensitive to nutrient C.

If cost of B is reduced by more than 7.33 cents, then optimal solution will change

-------------------------------------------------------------------------------------------------

4)

If the cost of grain B is changed to 40 cents per pound, this is a a decrease of 7 cents, from the current price of 47 cents. But its allowable decrease is 19. Hence, current blending strategy will NOT change.

-------------------------------------------------------------------------------------------------

5)

Refer Constraints section of the Sensitivity report,

Allowable increase for Nutrient 2 is 2.8

Increase from 16 to 18 units is only 2 , which is less than allowable increase.

Therefore, current blending strategy will NOT change.