Question

Cargo Loading

You are in charge of loading cargo ships for International Cargo Company (ICC) at a major East Coast port. You have been asked to prepare a loading plan for an ICC freight ship bound for Africa. An agricultural commodities dealer would like to transport the following products aboard this ship:

Commodity	Tons Available	Volume Per Ton (cu ft.)	Profit Per Ton ($)
1	4,000	40	70
2	3,000	25	50
3	2,000	60	60
4	1,000	50	80

You 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 (cu ft.)
Forward	3,000	100,000
Center	5,000	150,000
Rear	2,000	120,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 percent of the weight in the rear cargo hold, and the center cargo hold must be between 40 percent and 60 percent of the total weight on board.

(a) Determine a profit-maximizing loading plan for the commodities. What is the maximum profit and the loading plan that achieves it?

(b) Suppose each one of the cargo holds could be expanded. Which holds and which forms of expansion (weight or volume) would allow ICC to increase its profits on this trip, and what is the marginal value of each form of expansion?

(Please use EXCEL Solver and kindly provide the screenshot and equations for constraints, objective and variables! BIG Thanks)

Show your work with Excel and Solver:

Answer 1

(a) Profit maximizing loading plan is determined using Linear programming as follows

Formula:

E3 =SUM(B3:D3) copy to E3:E6

B7 =SUM(B3:B6) copy to B7:D7

B8 =SUMPRODUCT(B3:B6,$H$3:$H$6) copy to B8:D8

B12 =D7*0.9

B13 =D7*1.1

C12 =0.4*SUM(B7:D7)

C13 =0.6*SUM(B7:D7)

E16 =SUMPRODUCT(E3:E6,I3:I6)

(b)

Refer constraints sensitivity report as under

The binding constraints are Volume capacity of Forward and Center Cargo hold, and Weight Capacity of Rear Cargo hold. These Capacity expansions will increase the total profit. The marginal value of each expansion is indicated by the shadow price of particular constraint.

Shadow price of Loaded weight (tons) Rear is maximum (126) . Therefore, expansion of weight capacity of rear cargo hold will provide the maximum increase in total profit, with a marginal value of 126 per ton expansion of weight capacity.