Question

MUST USE EXCEL TO COMPLETE

Paul Bergey is in charge of loading cargo ships for International Cargo Company (ICC) at the port in Newport News, Virginia. Paul is preparing a loading plan for an ICC freighter destined for Ghana. An agricultural commodities dealer wants to transport the following products aboard this ship.

Commodity	Amount Available (tons)	Volume per Ton (cubic feet)	Profit per Ton ($)
A	4,800	40	70
B	2,500	25	50
C	1,200	60	60
D	1,700	55	80

Paul can elect to load any and/or all of the available commodities. However, the ship has three cargo holds with the following capacity restrictions.

Cargo Hold	Weight Capacity (tons)	Volume Capacity (cubic feet)
Forward	3,000	145,000
Center	6,000	180,000
Read	4,000	155,000

More than one type of commodity can be placed in the same cargo hold. However, because of balance considerations, the weight in the forward cargo hold must be within 10% of the weight in the rear cargo hold, and the center cargo hold must be between 40% and 60% of the total weight on board.

1) Formulate the LP model for this problem.

2) Create the spreadsheet model and use Solver to solve the problem.

Answer 1

(1): Formulation as a linear programming model of this problem.

Choice factors are the amounts of various items to be delivered in various Cargo hold (Forward, Center, Rear} with the goal to have Maximum Profit subject to imperatives by virtue of availibity, weights and volume limitations.

Let I represents the commodity and j represents the cargo hold and X_{ij is the quantity in terms of tons of i}^t_commodity placed in the j^th cargo hold. Forward (j=1), Center (j=2), Rear (j=3)

Total quantity of 1st commodity = X₁₁ + X₁₂ + X13 (C1)

Similarly Total quantity of 2nd commodity = X₂₁ + X₂₂ +X₂₃ (C2)

Total quantity of 3rd commodity = X₃₁₊ X₃₂ + X₃₃   (C3)

And Total quantity of 4th commodity = X₄₁ + X₄₂ + X₄₃ (C4)

Therefore the Objective function of maximizing profit is Maximize 70*C1 + 50*C2 + 60*C3 + 80*C4

Constraints on account of available quantities are C1 <= 4800, C2 <= 2500, C3 <= 1200 & C4 <=1700

In order to write weight and volume constraints of cargo hold, let us represents weights in Forward, Center and Rear as W1, W2 & W3 respectively, therefore W1 <= 3000, W2 <= 6000 and W3 <= 4000

Whereas W1 = X₁₁ + X₂₁ + X₃₁ + X₄₁           W2 = X₁₂ + X₂₂ + X₃₂ + X₄₂       W3 = X₁₃ + X₂₃ + X₃₃ + X₄₃  

The total weight of the cargo = W1 + W2 + W3 (say W)

W1 <= .10*W3 (condition forward load within 10% of rear)

.40W <=   W2 <= .60*W (condition center cargo between 40% and 60%)

For volume capacity constraints we are required to find the capacities of the commodities in each cargo hold

40*X11 + 25*X21 +60*X31 +55*X41 <= 145000 (capacity in forward)

40*X12 + 25*X22 +60*X32 +55*X42 <= 180000 (capacity in center)

40*X13 + 25*X23 + 60*X33 + 55*X43 <= 155000 (capacity in Rear)

X_ij >= 0 Non-negativity constraint being quantity in tons

(2). Answer:

Total Weights of commodities = 4800+2500+1200+1700 =10200 whereas capacity = 3000+6000+4000 =13000