Question

To complete this comprehensive exercise, use the Excel spreadsheet program. In this exercise you will be using linear programming and Solver in Excel to solve the problem described in the following scenario. At Goodman Shipping, the load master wants to determine the mix of cargo to be carried on the next trip. The ship's volume limit for cargo is 100,000 cubic meters, and its weight capacity is 2,310 tons. The master has five different types of cargo from which to select and wishes to maximize the value of the selected shipment. However, to make sure that none of his customers are ignored, the load master would like to make sure that at least 20% of each cargo's available weight is selected. The specifications for the five cargoes are shown in the following table.

Cargo Type	Tons Available	Value Per Ton	Volume Per ton
A	970	1350	26
B	850	1675	54
C	1900	1145	28
D	2300	850	45
E	3600	1340	37

Part A: How should the load master use the mix of cargo for the ship’s next trip? What is the (highest) value that can be attained using the mix of cargo that you determined?

Part B: What constraints are binding constraints? What constraints have slack or surplus? How much slack or surplus is there?

Answer 1

	Max. volume	100,000
	Max. Tons	2,310
Cargo Type	Tons Available (1)	Value Per Ton (2)	Volume Per ton (3)	Total Volume (1*3)	Total Value (1*3)	Min. 20% of weight (1*20%) = (4)	Min. Volume Carried (4*3)	Value of Min. Volume Carried (4*2)	Remaining volume (1-4)*(3)	Allocation of remaining tons to Cargo B (Note 2) = (5)	Volume carried for Cargo B	Total weight carried (4+5) = (6)	Total value carried (2*6)
A	970	1,350	26	25,220	1,309,500	194	5,044	261,900	20,176	-	-	194	261,900
B	850	1,675	54	45,900	1,423,750	170	9,180	284,750	36,720	386	20,844	556	931,300
C	1,900	1,145	28	53,200	2,175,500	380	10,640	435,100	42,560	-	-	380	435,100
D	2,300	850	45	103,500	1,955,000	460	20,700	391,000	82,800	-	-	460	391,000
E	3,600	1,340	37	133,200	4,824,000	720	26,640	964,800	106,560	-	-	720	964,800
Total	9,620			361,020	11,687,750	1,924	72,204	2,337,550	288,816	386	20,844	2,310	2,984,100
	Note 1	Volume remaining after minimum carriage of cargo (100000 - 72204)						27,796
		Tons remaining after minimum carriage of cargo (2310-1924)						386
	Note 2	Cargo type B has maximum value per ton and master has an intent to maximise the value per ton. Hence, master should use entire remaining tons to carry Cargo B.
	Note 3	Remaining volume post allocations of weight to Cargo B ( 27,796 - 20,844)						6,952

Answer -
Part A	Cargo Type	Total weight carried	Total value carried
	A	194	261,900
	B	556	931,300
	C	380	435,100
	D	460	391,000
	E	720	964,800
	Total	2,310	2,984,100

Part B	The total weight that the master can carry is the binding constarint.
	Hence there is a surplus of 6,952 volume due to shortage of weight that the master is allowed to carry (Note 3).