Question

A shipping freighter has space for two more shipping containers, but the combined weight cannot go over 20 tons Four shipping containers are being considered. The following table provides details on the weight (in tons) and value of the contents of each container Container Weight of container (tons) Value / Container 97 5 $6,000 $5,500 $7,500 $6,000 Develop a binary integer model (write all the constraints) that will determine the two containers, solve by SOLVER that will maximize the value of the shipment

Answer 1

Formulating the problem

Decision variables

X1= Container 1 will be selected

X2= Container 2 will be selected

X3= Container 3 will be selected

X4= Container 4 will be selected

Constraints

X1, X2, X3, X4 = Binary variable {0.1}

X1+X2+X3+X4=2

5X1+6X2+9X3+7X4<=20

Objective function

Objective is to maximize total value of the shipment

MAX 6000X1+5500X2+7500X3+6000X4

We will now solve this problem using Solver as shown in screenshots below:

Optimal solution as per model is to choose Container 1 and Container 3 with maximum value of shipment is 13,500.

Answer 2

Formulating the problem

Decision variables

X1= Container 1 will be selected

X2= Container 2 will be selected

X3= Container 3 will be selected

X4= Container 4 will be selected

Constraints

X1, X2, X3, X4 = Binary variable {0.1}

X1+X2+X3+X4=2

5X1+6X2+9X3+7X4<=20

Objective function

Objective is to maximize total value of the shipment

MAX 6000X1+5500X2+7500X3+6000X4

We will now solve this problem using Solver as shown in screenshots below:

- === Wrap Text General Calibri BI U - 1. -11 -Á A - - E AutoSum - A L O 2 x P EX 11 Insert Delete Format at w ? Fill ŻY O Paste Merge & Center, Merge & Center $ - % Clear * Cut Copy - Format Painter Clipboard : A 60,0 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter - Select Editing Font Alignment Number Cells G18 fix X B C D E F G H I J K L M N O P Q R S T A 2 3 4 2 Container 1 3 Weight tons 4 Value/ container $6,000 $5,500 $7,500 $6,000 7 Setup table for Solver 8 9 1. Decision variable (Binary, 1 - selected, 0- not selected) 10 Container | 1 | 2 3 4 2. Constraints Selected / Not 0 SUM(B11:E11) = 2 11 Selected 12 13 0 SUMPRODUCT(B11:E11, B3:53) 14 & (Ctrl) 15 16 3. Objective function 17 MAX O SUMPRODUCT(B11:E11, B4:E4)

A ZA Clear E Flash Fill |-- Consolidate Group - + ng LÀ From Access ccesso Te From Web From Other Le From lext S ources Get External Data show Show Queries From Table Existing New Connections Query - Lo Recent Sources Get & Transform A Connections E Properties Refresh All Edit Links Connections Il Sort Filter Reap Solver Parameters V. Adva Sort & Filter Set Objective: $C$17 C17 fic X B To: O Max Min value of: A C D E F G H J By Changing Variable Cells: $B$11:$E$11 2 3 4 2 Container 1 3 Weight tons 4 Value/ container $6,000 $5,500 $7,500 $6,000 Subject to the Constraints: $B$11:$E$11 = binary $F$11 = $1$11 $F$13 <= $K$13 Add 7 Setup table for Solver Change 8 Delete 9 1. Decision variable (Binary, 1 - selected, O- not selected) 10 Container 1 2 3 4 Selected / Not 11 Selected O 2. Constraints SUM(B11:E11) = 2 Reset All 12 Load/Save 0 SUMPRODUCT(B11:E11, B3:E3) 13 14 Make Unconstrained Variables Non-Negative 15 Select a Solving Method: GRG Nonlinear Options | 16 3. Objective function 17 Max CO SUMPRODUCT(B11:E11,84:E4) 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

Clear tions 21 14 EDHE - Consolidate Relationships - Show Queries From Table Existing New Connections Querv - Lo Recent Sources Get & Transform Connections 3 Properties Refresh All L Edit Links Connections Reapply LA From Access Le From Web From Other La From Text Sources Get External Data H14 x 2 A B Group Ungroup Subtotal Ād Sort Filter B E Flash Fill 5-Remove Duplicates Text to Data Validation Columns Data Validation - Data Tools Advanced Sort & Filter What-If Forecast Analysis - Sheet Forecast Outline ✓ fix C D E F G H I J K L M N O P Q R S T A 1 2 Container 1 3 Weight tons 4 Value/ container $6,000 5 2 3 4 6 97 $5,500 $7,500 $6,000 7 Setup table for Solver 8 9 1. Decision variable (Binary, 1 - selected, 0- not selected) 10 Container | 1 2 3 4 Selected / Not 11 Selected 12 13 14 1 0 1 0 2. Constraints SUM(B11:E11) 2 = 2 14 SUMPRODUCT(B11:E11,B3:E3) <= 20 15 16 17 3. Objective function MAX 13500 SUMPRODUCT(B11:E11,B4:E4)

Optimal solution as per model is to choose Container 1 and Container 3 with maximum value of shipment is 13,500.

Please give thumbs up/ likes if you find this answer helpful. Thank you!