Question

PLEASE SOLVE USING EXCEL

You are in charge of loading cargo ships for ICC at a major east coast port. You have been asked to prepare a loading plan for an ICC freighter 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.)40 Profit per Ton ($) 1 2 3 4 3,0004,0001,0003,000 25 60 50 70 50 60 80 You can elect to load any 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) ForwardCenter 5,000 3,000 100,000 ear 2,000 130,000 160,000 More than one type of commodity can be placed in the same cargo hold. However, because of balance considerations, the weight in the rear cargo hold must be between 45% and 55% of the total weight on board Determine a profit-maximizing loading plan for the commodities. What is the maximum profit and the loading plan that achieves it. a. 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?

Answer 1

a. Let,

C1F = number of commodity 1 to be loaded in forward position of Cargo ship

C1C = number of commodity 1 to be loaded in center position of Cargo ship

C1R = number of commodity 1 to be loaded in rear position of Cargo ship

C2F = number of commodity 2 to be loaded in forward position of Cargo ship

C2C = number of commodity 2 to be loaded in center position of Cargo ship

C2R = number of commodity 2 to be loaded in rear position of Cargo ship

C3F = number of commodity 3 to be loaded in forward position of Cargo ship

C3C = number of commodity 3 to be loaded in center position of Cargo ship

C3R = number of commodity 3 to be loaded in rear position of Cargo ship

C4F = number of commodity 4 to be loaded in forward position of Cargo ship

C4C = number of commodity 4 to be loaded in center position of Cargo ship

C4R = number of commodity 4 to be loaded in rear position of Cargo ship

Objective is to maximize profit so objective function = Max 70(C1F+C1C+C1R) + 50(C2F+C2C+C2R) + 60(C3F+C3C+C3R) + 80(C4F+C4C+C4R)

Subject to

C1F+C1C+C1R <= 3000 (3000 tons available to be shipped for commodity 1)

C2F+C2C+C2R <= 4000   (4000 tons available to be shipped for commodity 2)

C3F+C3C+C3R <= 1000 (1000 tons available to be shipped for commodity 3)

C4F+C4C+C4R <= 3000   (3000 tons available to be shipped for commodity 4)

C1F+C2F+C3F+C4F <= 3000 (weight capacity is 3000 tons in Forward hold)

C1C+C2C+C3C+C4C <= 5000 (weight capacity is 5000 tons in center hold)

C1R+C2R+C3R+C4R <= 2000 (weight capacity is 2000 tons in rear hold)

40C1F+25C2F+60C3F+50C4F <= 100000 (Volume capacity is 100000 in Forward hold)

40C1C+25C2C+60C3C+50C4C <= 160000 (Volume capacity is 160000 in center hold)

40C1R+25C2R+60C3R+50C4R <= 130000 (Volume capacity is 130000 in rear hold)

C1R+C2R+C3R+C4R >= 0.45(C1R+C2R+C3R+C4R+C1C+C2C+C3C+C4C+C1F+C2F+C3F+C4F)

C1R+C2R+C3R+C4R <= 0.55(C1R+C2R+C3R+C4R+C1C+C2C+C3C+C4C+C1F+C2F+C3F+C4F)

All variables greater than 0

Solving in excel solver we get,

Loading plan

C1F = number of commodity 1 to be loaded in forward position of Cargo ship = 0

C1C = number of commodity 1 to be loaded in center position of Cargo ship = 0

C1R = number of commodity 1 to be loaded in rear position of Cargo ship = 1444.444

C2F = number of commodity 2 to be loaded in forward position of Cargo ship = 0

C2C = number of commodity 2 to be loaded in center position of Cargo ship = 0

C2R = number of commodity 2 to be loaded in rear position of Cargo ship = 0

C3F = number of commodity 3 to be loaded in forward position of Cargo ship = 0

C3C = number of commodity 3 to be loaded in center position of Cargo ship = 0

C3R = number of commodity 3 to be loaded in rear position of Cargo ship = 0

C4F = number of commodity 4 to be loaded in forward position of Cargo ship = 1269.841

C4C = number of commodity 4 to be loaded in center position of Cargo ship = 1174.603

C4R = number of commodity 4 to be loaded in rear position of Cargo ship = 555.5556

and maximum profit = $341111.1

b. Sensitivity report

Variable Cells Final Value Reduced Objective Allovable Allov able Cost oefficien Increase Decrease 1E+30 1E+30 Cell Name $C$2 C1C $02 C1R 70 70 70 1444.4444 10 20 20 20 10 10 20 1Е-30 1E+30 1E+30 1E+30 1E+30 1E+30 $F$2 C2C 20 10 $H$2 C3F $I$2 C3C $J$2 C3R $K$2 C4F $L$2 C4C 60 60 10 80 6.023E-14 1269.8413 1174.6032 555.55556 80 10 Constraints Final Value 86666.667 Cell $N$10 Volume -Front hold $N$11 Volume -Centre hold $N$12 Volume -Rear hold $N$13 $N$14 $NS3 Aavailble tons of commodity 1 $N$4 Aavailble tons of commodity 2 $N$5 Aavailble tons of commodity 3 $N$6 Aavailble tons of commodity 4 $NS7 Weight-Front hold $NS8 Weight -Centre hold $N$9 Weight-Rear hold Shado Constrainl Allov able Allovable Price R.H. Side Increase Decrease 1E+30 13333.333 160000 130000 1.721E-15 130000 44444.444 41111.111 100 1E+30 444.44444 1E+30 1555.5556 4000 1000 3000 822.22222 222.22222 1Е-30 1730.1587 1E+30 3825.3968 650 Name 0 100000 0 160000 1E+30 0-155.5556 0 363.63636 -444.4444 1444.4444 3000 4000 1000 1E+30 1E+30 1269.8413 1174.6032 3000 5000 2000 2000 155.55556

We can see from sensitivity report, only Volume capacity and Weight capacity of Rear hold have been fully utilized. Shadow price of Weight capacity of Rear hold is higher than volume capacity of rear hold. So expanding Weight capacity of rear hold would have maximized effect on Profit. 1 unit of Weight capacity increase of rear hold will fetch $155.55556 additional profit

Marginal value of Volume capacity - rear hold expansion is $1.721E-15

Marginal value of Weight capacity - rear hold expansion is $155.55556

Marginal value of other expansions is 0 as their existing capacities are not utilized