Question

A butchery prides itself in its ability to create three unique blends of meat: Speckled, Darted and Regent. It uses 4 types of niche meat to make the blends: Braised, Moose, Cologna and Milan. The success of the blends is entirely dependent on strictly adhering to the following requirements:

Blends	Requirements	Selling price/Pound
Speckled	At least 40% Cologna; at least 30% Moose	$13.00
Darted	At least 60% of Braised; no more than 10% Milan	$10.50
Regent	No more than 60% Milan; at least 30% Braised	$7.50

The cost of each is as follows: Braised is $4.00 per pound, moose is $5.50 per pound, Cologna $5.80 per pound and  Milan for $3.40 per pound. The shop has 220 pounds of Braised, 140 pounds of moose, 160 pounds of Cologna and 300 pounds of Milan meat available per week.

The owner wants to determine the optimal combination of blends to make to maximize profit.

A). Formulate a Linear programming model

A1: define the decision variables

A2: Use (A1) to express:

Pounds of Speckled:

Pounds of Darted:

Pounds of Regent:

Total use of Braised:

Total use of Moose:

Total use of Cologna:

Total use of Milan:

B). Use MS EXCEL to determine optimal blend quantities to maximize profit.

Answer 1

A)

LP model is as follows:

A1. Decision variables are quantity of ingredients used and pounds of each blend produced.

X11 = Pounds of Braised used to produce Speckled

X12 = Pounds of Moose used to produce Speckled

X13 = Pounds of Cologna used to produce Speckled

X14 = Pounds of Milan used to produce Speckled

X21 = Pounds of Braised used to produce Darted

X22 = Pounds of Moose used to produce Darted

X23 = Pounds of Cologna used to produce Darted

X24 = Pounds of Milan used to produce Darted

X31 = Pounds of Braised used to produce Regent

X32 = Pounds of Moose used to produce Regent

X33 = Pounds of Cologna used to produce Regent

X34 = Pounds of Milan used to produce Regent

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

A2:

X11+X12+X13+X14 = Pounds of Speckled produced

X21+X22+X23+X24 = Pounds of Darted produced

X31+X32+X33+X34 = Pounds of Regent produced

X11+X21+X31 = Pounds of Braised used

X12+X22+X32 = Pounds of Moose used

X13+X23+X33 = Pounds of Cologna used

X14+X24+X34 = Pounds of Milan used

===================================================================================

B)

Create EXCEL model as follows:

N16 ® fx =SUMPRODUCT(B16:M16,$B$18:$M$18) N O P Q B X11 C X12 D X13 E X14 F X21 G H X22X23 I X24 J K L X31 X32X33 M X34 1 -0.4 -0.3 -0.4 0.7 0.6 -0.3 -0.4 -0.3 O YYY 0 0.4 -0.1 -0.6 -0.1 -0.6 -0.1 -0.6 0.9 0 0 <= <= 0 0 -0.6 0.7 -0.6 -0.3 -0.6 -0.3 0.4 -0.3 2 0 <= 3 >=40% Cologna in Speckled 4 >=40% Moose in Speckled 5 >=60% Braised in Darted 6 <10% Milan in Darted 7 <=60% Milan in Regent 8 >=30% Braised in Regent 9 Total Braised 10 Total Moose 11 Total Cologna 12 Total Milan 13 14 Selling price: 15 Cost: 16 Profit: 17 18 Result: 140 160 300 0 13 4 9 13 5.5 7.5 13 5.8 7.2 13 3.4 9.6 10.5 4 6.5 10.5 5.5 5 10.5 5.8 4.7 10.5 3.4 7.1 7.5 4 3.5 7.5 5.5 7.5 5.8 .7 7.5 3. 4 4.1 0 2 1 0 19

Correction: In cell A4, it is ">=30% Moose in Speckled". by mistake, it is written as 40%. Calculation is correct.

EXCEL FORMULA:

N16 @ fx =SUMPRODUCT(B16:M16,$B$18:$M$18) А c E F G H к X12X13X14 X21X22X23X24 X31X32 O P M X33X34 X11 -0.4 -0.3 -0.4 0.7 0.6 -0.3 -0.4 -0.3 0.4 -0.1 -0.6 -0.1 -0.6 -0.1 -0.6 0.9 ^ ^ X X Y 3 >-40% Cologna in Speckled 4 >=40% Moose in Speckled 5 >=60% Braised in Darted 6 10% Milan in Darted 7 60% Milan in Regent 8 -30% Braised in Regent 9 Total Braised 10 Total Moose 11 Total Cologna 12 Total Milan -0.6 0.7 -0.6 -0.3 -0.6 -0.3 0.4 -0.3 =SUMPRODUCT(B3:M3,$B$18:$M$18) =SUMPRODUCT(B4:M4,$B$18:$M$18) =SUMPRODUCT(B5:M5,$B$18:$M$18) =SUMPRODUCT(B6:M6,$B$18:$M$18) =SUMPRODUCT(B7:M7,$B$18:$M$18) =SUMPRODUCT(B8:M8,$B$18:$M$18) =SUMPRODUCT(B9:M9,$B$18:$M$18) =SUMPRODUCT(B10:M10,$B$18:$M$18) =SUMPRODUCT(B11:M11,$B$18:$M$18) =SUMPRODUCT(B12:M12,$B$18:$M$18) 1 ^ ^ ^ ^ 1 13 14 Selling price: 15 Cost: 16 Profit 17 18 Result: 13 13 13 13 10.5 10.5 10.5 10.5 7.5 7.5 7.5 7.5 =SUMPRODUCT(B14:M14,$B$18:$M$18) - 5.5 5.8 3.4 4 5.5 5.8 3.4 4 5.5 5.8 3.4 -SUMPRODUCT(B15:M15,$B$18:$M$18) =B14-B15 =C14-C15 =D14-D15 =E14-E15 =F14-F15 =G14-G15 =H14-H15 =114-115 = 14-J15 =K14-K15 =L14-L15 EM14-M15 -SUMPRODUCT(B16:M16,$B$18:$M$18)

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

Enter Solver Parameters as follows:

S Solver Parameters Set Objective: SN$16 To: O Max Min Value Of: O By Changing Variable Cells: $B$18:SMS18 Subject to the Constraints: SN$3:SN$5 >= $P$3:5P55 SNS6:SN$7 <= SP56:5P57 SN$8 >= SP$8 SN$9:$N$12 <= $P$9:$P$12 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. After that, values appear automatically in yellow cells.

N16 @ fx =SUMPRODUCT(B16:M16,$B$18:$M$18) B C D E F G H I X11 X12 X13 X14 X21 22 23 24 N O P J X31 K X32 L 33 M 34 Q R S T U V W X = Solver Results -0.4 -0.3 -0.4 0.7 0.6 -0.3 -0.4 -0.3 43.2 0.4 -0.1 -0.6 -0.1 -0.6 -0.1 -0.6 0.9 ^ ^ Y Y Y 3 >40% Cologna in Speckled 4 -30% Moose in Speckled 5 >=60% Braised in Darted 6 <10% Milan in Darted 7 60% Milan in Regent 8 >=30% Braised in Regent 9 Total Braised 10 Total Moose 11 Total Cologna 12 Total Milan -0.6 0.7 -0.6 -0.3 -0.6 -0.3 0.4 -0.3 Solver found a solution. All constraints and optimality conditions are satisfied, Reports Answer O Keep Solver Solution Sensitivity Limits O Restore Original Values 7.6 220 140 160 300 ^ ^ 140 160 300 Return to Solver Parameters Dialog 13 OK | Cancel 14 Selling price: 15 Cost: 16 Profit 13 4 13 5.5 .5 13 5.8 7.2 13 3.4 9.6 10.5 4 6.5 10.5 5.5 5 10.5 5.8 4.7 10.5 3.4 7.1 7.5 4 3.5 7.5 5.5 7.5 5.8 1.7 7.5 3.4 4.1 8782 3598 5184 9 7 2 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. 17 18 Result: 0 120 160 120 129.6 0 0 14.4 90.4 20 0 165.6 ON NN

Click OK

Result is shown in yellow cells (B18:M18)

Pounds of Speckled produced = X11+X12+X13+X14 = 0+120+160+120 = 400 pounds

Pounds of Darted produced = X21+X22+X23+X24 = 129.6+0+0+14.4 = 144 pounds

Pounds of Regent produced = X31+X32+X33+X34 = 90.4+20+0+165.6 = 276 pounds

Pounds of Braised used = X11+X21+X31 = 0+129.6+90.4 = 220 pounds

Pounds of Moose used = X12+X22+X32 = 120+0+20 = 140 pounds

Pounds of Cologna used = X13+X23+X33 = 160+0+0 = 160 pounds

Pounds of Milan used = X14+X24+X34 = 120+14.4+165.6 = 300 pounds

Total profit = $ 5,184