Problem 2 (35 points): A cargo plane has three compartments for storing cargo: front, center, and back. These compartments have capacity limits on both weight and space, as summarized below. Note: An Excel file will have to be created for 2b (one tab).

Compartment	Weight Capacity (tons)	Space capacity (cubic feet)
Front	12	7000
Center	18	9000
Back	10	5000

Furthermore, the weight of the cargo in the respective compartments must be the same proportion of that compartments weight capacity to maintain the balance of the airplane. That is, the ratio of total weight put into the front to the weight capacity of the front should be equal to the ratio of total weight put into the center to the weight capacity of the center, and these should be equal to the ratio of total weight put into the back to the weight capacity of the back. (Hint: two equality constraints will suffice, e.g., front ration is equal to center ratio, and center ration is equal to back ratio). The following four cargoes have been offered for shipment on an upcoming flight as space is available.

Cargo	Weight (Tons)	Volume (cubic feet per ton)	Profit ($ per ton)
1	20	500	320
2	16	700	400
3	25	600	360
4	13	400	290

Any portion of these cargoes can be accepted. The goal is to determine how much (if any) of each cargo should be accepted and how to distribute each among the compartments to maximize the total profit for the flight. (Hint: total of 12 decision variables, portion of cargo i to be loaded to compartment j).

a) (20 points) Formulate the algebraic model for the above problem. Clearly define your decision variables and then use them for formulating the objective function and the constraints. Then present the formulation. b) (15 points) Formulate a spreadsheet model for the problem and solve it using Excel solver. Use colors and titles in your cells for better representation.