Question

Use the following information to answer the next few questions:

The production manager for the Coory soft drink company is considering the production of 2 kinds of soft drinks: regular (R) and diet (D). Two of her limited resources are production times (8 hours = 480 minutes per day) and syrup (1 of her ingredients) limited to 675 gallons per day. To produce a regular case requires 2 minutes and 5 gallons of syrup, which a diet case needs 4 minutes and 3 gallons of syrup. Profits for regular soft drink are $3 per case and profits for diet soft drink are $2 per case. Set this up as a linear programming model and solve.

The total profit for the above model's diet drink only alternative non-optimal solution is:

Question 5 options:

120

96

480

135

240

Answer 1

Cases of Let ex and eys be the number of and regular broduced diet soft drink to be As regular case requires 2 minutes while cliet Case requires u mins so total time taken as man soi syrup used T- 2x + 4y As total time 480 day. 2x+44 2480 = x +2y 2240 a As begular Case requires 5 gallons of syrup while diet case requires 3 gallows of syrup, so amount of per day es :- S: 5x + 3y As total syrup is limited to 675 gallons per day, so:- 5x + 3y = 675 2 Profit for regular and diet case is $3 and $2 per case respectively, to total profit is? z = 3x +27 So, this can be formulated as linear programming model :- Marrimize Z = 3x +2y subject to X+ 2y = 240 5x+34 2 675 X, Y 7,0

We can use excel solvar to set up the linear programming problem as shown -

Then set up the solver as shown -

E12 : X A B с F 1 х Y Condition Value 2 3 4. Profit =3*B3 =B3 5 T =2*C3 =2* C3 =3*C3 =SUM(B4:04) =SUM(B5:C5) =SUM(B6:06) 240 6 S =5* B3 <= 675 7 8 9

File Home Insert Draw Page Layout Formulas Data Review View Help Power Pivot Share LÈ From Text/CSV E Queries & Connections Group WN Recent Sources La Existing Connections AL JA Clear Reapply + Data Analysis - ? Solver From Web Get Data From Table/Range Ungroup Stocks 紐反% Geography Sort Filter Properties Refresh All Edit Links Queries & Connections Text to Columns Co Advanced GO Subtotal What-If Forecast Analysis Sheet Forecast Get & Transform Data Data Types Sort & Filter Data Tools Outline Analyze D4 fic Solver Parameters А B с D E F R S T U V 1 Set Objective: $D$4 1 х Y Condition Value To: O Max O Min O Value of: 0 2 3 4. Profit 5 T 0 0 0 0 <= 0 0 240 By Changing Variable Cells: $B$3:$C$3 1 6 S 0 0 0 <= 675 7 8 Subject to the Constraints: $D$5 <= $F$5 $D$6 <= $F$6 Add 9 10 Change 11 Delete 12 13 14 Reset All 15 Load/Save 16 17 Make Unconstrained variables Non-Negative 18 Select a Solving Method: GRG Nonlinear Options 19 20 21 Solving Method Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 22 23 24 25 26 27 Help Solve Close

Note that you can access 'Solver' from 'Data' tab as shown above.

This should give you following output -

X = 90 and Y = 75.

Thus, maximum profit = Z = 3(90) + 2(75)

= $420

Now, if you want only diet alternative profit, so set X = 0, to set up the LP as  -

D4 X fr B с D E F 0 Solver Parameters Х X Y Condition Value А. 1 2 3 4 Profit 5 T 0 Set Objective: $D$4 4 0 0 oi 0 0 To: 0<%3D O Max O Min O Value of: 240 675 S 0 0 0 <= 6 7 8 By Changing Variable Cells: $C$3 1 9 10 Subject to the Constraints: $D$5 <= $F$5 $D$6 <= $F$6 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Now, this would give you the output -

	X	Y
	0	120
Profit	0	240	240
T	0	240	240
S	0	360	360

So, you produce 120 cases of diet coke which gives you a maximum profit of -

Z = 240

Thus, last option is correct.

_____________________________