So far, we’ve only considered
optimizing the fares for a single route. In this video, we’ll change
our optimization formulation to include connecting flights. Now, instead of just being
able to go from JFK in New York to LAX in Los Angeles, let’s
suppose that the plane stops in Dallas at the Dallas
Fort Worth airport. We still are just
using one plane, but the passengers can
now fly from New York to Dallas, Dallas
to Los Angeles, or from New York to
Los Angeles by just staying on the plane in Dallas.
So how does our
optimization problem change? We now have six types of
seats that we can offer: the original two types,
regular and discount from New York to LA,
and four new types. We can sell both regular
and discount seats from New York to Dallas,
and regular and discount seats from Dallas
to Los Angeles. We know the price of
each type of ticket as well as the forecasted
demand for each type of ticket. We also know that
we have a capacity of 166 seats on our plane
for each leg of the trip.
There’s room for 166 passengers
on the plane from New York to Dallas, or the
first leg of the trip. Then the passengers with a
final destination of Dallas will get off the plane and the
passengers flying from Dallas to LA will get on the plane. On the second leg of the trip,
flying from Dallas to LA, we also have a
capacity of 166 seats. So we need to remember
that the passengers flying from New York to LA
will take up capacity on both legs of the trip, while
the other types of passengers will only take up capacity
on one leg of the trip. So what are our decisions now? They’re the number of regular
tickets to sell for each type, and the number of discount
tickets to sell for each type. So in total, we have
six decisions to make. Now, let’s define our objective.
Like before, it’s to
maximize the total revenue. This is the sum of the price
of the ticket times the number of seats of that type we
sell, for each type of ticket. And like before, we have two
types of constraints– capacity constraints and
demand constraints. For the capacity
constraints, the airline shouldn’t sell more seats than
the capacity of the plane, for each leg of the trip. So we need two capacity
constraints here: one for the New York to Dallas
leg and one for the Dallas to LA leg. Note that the New
York to LA passengers have to be counted on
both legs of the trip. So the first constraint
accounts for all passengers that need to be on the plane when it
flies from New York to Dallas, and the second constraint
accounts for all passengers that need to be
on the plane when it flies from Dallas to LA. We also need six
demand constraints, one for each type of ticket. The number of seats
sold should not exceed the forecasted
demand for each type. And lastly, we can’t sell
a negative number of seats, so we have our
non-negativity constraints to prevent the variables
from being negative.
Let’s now go to LibreOffice
and adjust our formulation to solve this bigger problem. In LibreOffice, go
ahead and open the file Week9_AirlineRM_Connecting.ods. In this file, I’ve set up
our data, our decisions, our objective, and
our constraints. Our decisions, again, are
highlighted in yellow. We have a decision for each
type of seat on each flight. Our objective here
is the spot in blue. To build our objective, we’ll
use the sumproduct function. So type = and then sumproduct,
and in parentheses, select all six prices,
type a semicolon, and then select
all six decisions. Close the parentheses
and hit Enter. We see here, like we
did before, that we have 0 in our objective,
because right now, we’re not selling any seats. Now let’s create
our constraints. The first constraints
are capacity constraints. The first is the capacity on
the leg from New York to Dallas. The left-hand side should be
equal to the seats from New York to LA plus the seats
from New York to Dallas.
The sign is less than or
equals and the right-hand side is 166, the capacity
of our aircraft. Now we need to build the
capacity constraint from Dallas to LA. The left-hand side is equal
to the seats from New York to LA plus the seats
from Dallas to LA. Our sign is, again,
less than or equals and our right-hand side is 166. For the demand constraints and
the non-negativity constraints, because we have six
of each this time, we’ll actually make them in a
more efficient way than before. So we just have
a note down there that we need to remember
to add these constraints. So now go ahead and in the
Tools menu, select Solver. We need to first fill in
the target cell, which should be the objective. Make sure that
Maximum is selected. Then, in the Changing Cells
box, select all six decisions. Down in the Limiting
Conditions, let’s now build our constraints. For the Cell Reference
column, let’s start by selecting the left-hand
side of the two capacity constraints. The Operator should
be less than or equals and the Value should be the
right-hand side of these two capacity constraints.
Now let’s make the
demand constraints. In Cell Reference, just
directly select the six decision variables, make sure the
Operator’s less than or equals, and for the Value, select
the six demand constraints. This is a bit easier than what
we did before because we didn’t have to type them all
out in our spreadsheet. Now let’s do a similar thing for
the non-negativity constraints, where in Cell Reference, we
select the six decisions. The Operator this time should
be greater than or equals, and for the Value, just type 0. Make sure in Options that the
Linear Solver is selected, and go ahead and hit Solve. The solving result should say:
“Solving successfully finished. Result: 120,514.” This is our total revenue. Go ahead and click
Keep Result, and let’s take a look at our solution. So we see here that
the optimal solution is to sell 80 tickets for
the regular price from New York to LA, 0 of the discount
price from New York to LA, 75 of the regular price
from New York to Dallas, 11 of the discount price
from New York to Dallas, 60 of the regular price
from Dallas to LA, and lastly, 26 of the discount
price from Dallas to LA.
We saw here that we
could pretty easily solve a more complicated
problem in LibreOffice that we probably couldn’t have
solved as easily by inspection..
Call Now for Discount Airline Tickets
