Cargo Loading
You are in charge of loading cargo ships for International CargoCompany (ICC) at a major East Coast port. You have been asked toprepare a loading plan for an ICC freight ship bound for Africa. Anagricultural commodities dealer would like to transport thefollowing 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 availablecommodities. However, the ship has three cargo holds with thefollowing 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 cargohold. However, because of balance considerations, the weight in theforward cargo hold must be within 10 percent of the weight in therear cargo hold, and the center cargo hold must be between 40percent and 60 percent of the total weight on board.
(a) Determine a profit-maximizing loading plan for thecommodities. What is the maximum profit and the loading plan thatachieves it?
(b) Suppose each one of the cargo holds could beexpanded. Which holds and which forms of expansion (weight orvolume) would allow ICC to increase its profits on this trip, andwhat is the marginal value of each form of expansion?
(Please use EXCEL Solver and kindly provide thescreenshot and equations for constraints, objective and variables!BIG Thanks)
Show your work with Excel and Solver:
