Q2 (LP - EXCEL SOLVER SOLUTION)

An airlines uses a double decker jumbo aircraft that can carry a maximum of 350 passengers and has four different classes of seats. Executive and business class passenger are in the upper deck, while the lower deck is for premium economy and traveller classes. A profit of 3,500 is made on each executive class; 2,000 business class; 1,500 for premium economy; and 800 is made on each traveller class ticket. The airline reserves at least 25 seats for executive class and another 30 for business. However, at least 3 times as many passengers prefer to travel by traveller class than by the executive class. Also, 2 times as many passengers prefer to travel by traveller class than by the premium economy class. Space capacity in the upper deck cannot exceed 100 seats. Executives are allowed 50kg of luggage; business is 35kg; premium economy 30kg and economy 23kg. Each passenger is also allowed to carry a maximum of 10kg hand luggage. For safety reasons the total luggage the aircraft can hold cannot exceed 14,000kg. The airline wants to determine how many tickets of each type must be sold in order to maximise their profit

(a) Formulate a linear programming model for this problem by writing out the equations for the objective function and all constraints. Ensure to clearly label each equation.

(b) Use Excel Solver to solve this model

(c). Determine how many tickets of each type must be sold in order to maximise the profit for the airline?. What is the maximum profit?