90.2K

Verified Solution

Link Copied!

Global Air-Cargo Freight Ltd. is a world class freight forwarder which provides the highest standard of air cargo delivery services. Once they receive orders from customers, they will arrange its two cargo planes to deliver the cargo to destinations all over the world. The two cargo planes are different in sizes; therefore they have different weight capacity and space capacity. Each cargo plane has three compartments for storing cargo: front, centre and rear. Table 1 shows the weight and space capacity of each compartment in cargo plane 1 and 2.

Table 1. Weight and Space Capacity of each Compartment in Cargo Plane 1 and 2

	Weight Capacity (Tonnes)		Space Capacity (m3)
Compartment	Cargo Plane 1	Cargo Plane 2	Cargo Plane 1	Cargo Plane 2
Front	20	18	6,800	6,000
Centre	26	22	8,700	8,200
Rear	16	10	5,300	5,000

Furthermore, the weight of the cargo in the respective compartments must be the same proportion of that compartment's weight capacity to maintain the balance of the plane, i.e. Front compartment weight % = Centre compartment weight % = Rear compartment weight %).

Currently, Global Air-Cargo Freight Ltd. receives the following 10 cargos for shipment. Table 2

shows the cargo details. The company can receive different profits when using cargo plane 1 and cargo plane 2.

Table 2. Weight, Volume and Profit (for Cargo Plane 1 and Cargo Plane 2) of the 10 Cargos

	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10
Weight (tonnes)	8	10	14	8	12	11	11	13	15	11
Volume (m3/tonne)	435	370	379	380	327	432	314	338	461	454
Profit: Plane 1 ($/tonne)	3,100	3,800	3,500	3,200	2,850	2,600	3,700	4,000	2,800	3,200
Profit: Plane 2 ($/tonne)	3,800	3,000	4,500	4,800	4,000	3,250	3,000	3,400	3,000	4,000

Any proportion of these cargoes can be accepted, the cargo can be split into plane 1 and plane 2 for delivery, and each cargo can be split between two or more compartments in the two cargo planes.

What to do:

1. [Excel] Add more functions to your system to allow decision making for different scenarios.

****Please use excel to finish it****

Answer & Explanation Solved by verified expert

See Answer