Answer to Question #221287 in Excel for Mingle

Question

Daily requirements of 70 g of protein, 1 g calcium, 12 mg iron, and 3000 calories are needed for a balanced diet. The following foods are available for consumption with the cost and nutrients per 100 g as shown.

 

Protein

(g)

Calories

Calcium

(g)

Iron

Cost

GH¢

Brown Bread

12

246

0.1

3.2

0.5

Cheese

24.9

423

0.2

0.3

2

Butter

0.1

793

0.03

1

Baked Beans

6

93

0.05

2.3

0.25

Spinach

3

26

0.1

2

0.25

 

The objective is to find a balanced diet with minimum cost.

(a) Formulate a linear programming model for this problem.

(b) Use solver to find optimal solution and sensitivity report.     

Answer 1

(a) Formulate a linear programming model for this problem.

Minimize Cost Z = 0.5BB+2C+B+0.25BE+0.25S

s.t. constraints -

12BB+24.9C+0.1B+6BE+3S ≤ 70 (Protein)

246BB+423C+793B+93BE+25S ≤ 3000 (Calories)

0.1BB+0.2C+0.03B+0.05BE+0.1S ≤ 1 (Calcium)

3.2BB+0.3C+2.3BE+2S ≤ 12 (Iron)

BB,C,B,BE,S ≥ 0 ( Non-negativity)

(b) Use Solver to find the optimal solution and sensitivity report.

Minimum cost = 7.8 $