Allocate Route and Fare-Class Tickets Lets say that FOPA advises the board to make Kansas City a hub that forms a local network covering: Saltlake City, Chicago, Dallas, and Atlanta. This local network is serviced by two

planes: 1. Airbus A220-100 with a total seating capacity of 135 passengers 2. Airbus A320 with total seating capacity of 150 passengers

Since Kansas City is the hub for this network, every destination must be served non-stop to and from Kansas. In order to achieve this goal in an efficient manner the two planes are parked overnight at Saltlake City and Atlanta (which might serve as hubs for other regions). Therefore each morning the Saltlake City based plane flies to Dallas with a stopover at Kansas City and the Atlanta based plane flies towards Chicago also stopping over at Kansas City. The diagram below illustrates the logistics of this arrangement. BAL sells two ticket types: basic and regular coach. The basic fare restricts carry on, does not allow passengers to select their seat in advance, and does not allow for any changes to be made to the itinerary. Regular coach ticket holders can select their seats in advance, can bring one carry on item onboard, can change their ticket free of charge once (and pay the ticket price difference if larger than zero), and receive a complimentary beverage upon boarding the plane. Every day, the pricing engine (that you do not have to develop as part of this use case) spits out a set of prices and expected demand for all future flight days in a 6 month time window, for each fare class and destination.

FOPA is tasked with developing an algorithm that returns the allocated number of seats for each class and itinerary. The algorithm output is used by ticket agents and queried by various online vendors that sell tickets on BALs behalf.

1. Determine how many seats to allocate to each route (you can check that total demand exceeds capacity) with the goal to maximize revenue. Model output should contain metrics used by agents or travel site engines that assist in guiding whether tickets should still be allocated if full allocated capacity is reached for a given route and class 2. Advise the board at which location to station the A220 and A320 (one in Saltlake, the other in Atlanta)

Please set this all up in Excel.

Saltlake City Chicago Kansas City (Hub) Dallas Atlanta Route Ticket Price Forecasted Seats 1 185 35 2 3 277 235 47 43 4 390 16 462 5 5 6 565 12 7 24 8 9 Departure Salt Lake City SaltLake City SaltLake City Salt Lake City Salt Lake City Salt Lake City Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Kansas City Kansas City Kansas City Kansas City Destination Kansas City Dallas Chicago Kansas City Dallas Chicago Kansas City Dallas Chicago Kansas City Dallas Chicago Dallas Dallas Chicago Chicago Class Basic Coach Basic Coach Basic Coach Regular Coach Regular Coach Regular Coach Basic Coach Basic Coach Basic Coach Regular Coach Regular Coach Regular Coach Basic Coach Regular Coach Basic Coach Regular Coach 206 249 350 56 38 10 388 15 11 8 454 585 12 12 13 187 61 14 387 6 15 232 46 16 590 11 Saltlake City Chicago Kansas City (Hub) Dallas Atlanta Route Ticket Price Forecasted Seats 1 185 35 2 3 277 235 47 43 4 390 16 462 5 5 6 565 12 7 24 8 9 Departure Salt Lake City SaltLake City SaltLake City Salt Lake City Salt Lake City Salt Lake City Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta Kansas City Kansas City Kansas City Kansas City Destination Kansas City Dallas Chicago Kansas City Dallas Chicago Kansas City Dallas Chicago Kansas City Dallas Chicago Dallas Dallas Chicago Chicago Class Basic Coach Basic Coach Basic Coach Regular Coach Regular Coach Regular Coach Basic Coach Basic Coach Basic Coach Regular Coach Regular Coach Regular Coach Basic Coach Regular Coach Basic Coach Regular Coach 206 249 350 56 38 10 388 15 11 8 454 585 12 12 13 187 61 14 387 6 15 232 46 16 590 11