Cargo Loading You are in charge of loading cargo ships for International Cargo C
ID: 357431 • Letter: C
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:
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
Explanation / Answer
a) Profit Maximising loading plan
1) Calculation of total requirement of weight and volume-
Total volume requirement is 405000 cu fts.
Total Weight requirement is 10,000 tons
Equation 1
Required weight is 10000 tons whereas available weight is 5500 tons
Required volume is 405000 cu ft where as available volume is 317000 cu ft.
Hence Weight and volume both are constraints hence let us calculate volume required assuming weight is the only limiting factor and the commodity which gives maximum profit per ton will be loaded first. The priority will be as below-
Loading Plan Assuming Weight is the only limiting factor-
Conslusion-
Thus with the volume remaining balance with after weights in all cargos are utilised weight remains the only limiting factor and the maximum profit achieved with the above plan will be 390000.
b) Cargo Holds to be expanded
Commodity Tons (a) Volume per ton (cu ft) (b) Total Volume Required (a*b) 1 4000 40 160000 2 3000 25 75000 3 2000 60 120000 4 1000 50 50000 Total 10000 405000