2. (50pts) You are in charge of loading ships at the port of Rotterdam and are currently preparing a loading plan for a freighter destined for Charleston. Philips Electronics wants to transport the following products aboard this ship. PRODUCT 1 2 3 4 AMOUNT AVAILABLE VOLUME PER TON PROFIT PER TON (TONS) (CUBIC FEET) (S) 4,800 40 70 2,500 25 50 1,200 60 1,700 60 55 80 You can elect to load any and/or all of the available products. However, the ship has three stowage areas with the following capacity restrictions: STOWAGE AREA FRONT MIDDLE BACK WEIGHT CAPACITY (TONS) 3,000 6,000 4,000 VOLUME CAPACITY (CUBIC FEET) 145,000 180,000 155,000 A stowage area can hold different products. However, for safety reasons, the weight of the front area must be within 10% of the weight in the back area and the middle area must hold between 40% and 60% of the total weight on board. a) Formulate a Linear Program that can be used to solve this problem. Clearly specify the decision variables, objective function, and constraints b) Create a spreadsheet model for this problem and solve it using Solver. c) What is the optimal solution? 2. (50pts) You are in charge of loading ships at the port of Rotterdam and are currently preparing a loading plan for a freighter destined for Charleston. Philips Electronics wants to transport the following products aboard this ship. PRODUCT 1 2 3 4 AMOUNT AVAILABLE VOLUME PER TON PROFIT PER TON (TONS) (CUBIC FEET) (S) 4,800 40 70 2,500 25 50 1,200 60 1,700 60 55 80 You can elect to load any and/or all of the available products. However, the ship has three stowage areas with the following capacity restrictions: STOWAGE AREA FRONT MIDDLE BACK WEIGHT CAPACITY (TONS) 3,000 6,000 4,000 VOLUME CAPACITY (CUBIC FEET) 145,000 180,000 155,000 A stowage area can hold different products. However, for safety reasons, the weight of the front area must be within 10% of the weight in the back area and the middle area must hold between 40% and 60% of the total weight on board. a) Formulate a Linear Program that can be used to solve this problem. Clearly specify the decision variables, objective function, and constraints b) Create a spreadsheet model for this problem and solve it using Solver. c) What is the optimal solution