WEBVTT

00:00:09.500 --> 00:00:12.580
So far, we've only considered
optimizing the fares

00:00:12.580 --> 00:00:14.610
for a single route.

00:00:14.610 --> 00:00:18.200
In this video, we'll change
our optimization formulation

00:00:18.200 --> 00:00:20.780
to include connecting flights.

00:00:20.780 --> 00:00:25.050
Now, instead of just being
able to go from JFK in New York

00:00:25.050 --> 00:00:29.280
to LAX in Los Angeles, let's
suppose that the plane stops

00:00:29.280 --> 00:00:33.150
in Dallas at the Dallas
Fort Worth airport.

00:00:33.150 --> 00:00:35.700
We still are just
using one plane,

00:00:35.700 --> 00:00:38.460
but the passengers can
now fly from New York

00:00:38.460 --> 00:00:42.080
to Dallas, Dallas
to Los Angeles,

00:00:42.080 --> 00:00:44.910
or from New York to
Los Angeles by just

00:00:44.910 --> 00:00:48.490
staying on the plane in Dallas.

00:00:48.490 --> 00:00:51.560
So how does our
optimization problem change?

00:00:51.560 --> 00:00:55.200
We now have six types of
seats that we can offer:

00:00:55.200 --> 00:00:58.380
the original two types,
regular and discount

00:00:58.380 --> 00:01:02.310
from New York to LA,
and four new types.

00:01:02.310 --> 00:01:05.630
We can sell both regular
and discount seats

00:01:05.630 --> 00:01:09.190
from New York to Dallas,
and regular and discount

00:01:09.190 --> 00:01:12.100
seats from Dallas
to Los Angeles.

00:01:12.100 --> 00:01:15.330
We know the price of
each type of ticket

00:01:15.330 --> 00:01:19.450
as well as the forecasted
demand for each type of ticket.

00:01:19.450 --> 00:01:21.730
We also know that
we have a capacity

00:01:21.730 --> 00:01:27.289
of 166 seats on our plane
for each leg of the trip.

00:01:27.289 --> 00:01:31.539
There's room for 166 passengers
on the plane from New

00:01:31.539 --> 00:01:34.960
York to Dallas, or the
first leg of the trip.

00:01:34.960 --> 00:01:38.250
Then the passengers with a
final destination of Dallas

00:01:38.250 --> 00:01:42.270
will get off the plane and the
passengers flying from Dallas

00:01:42.270 --> 00:01:45.140
to LA will get on the plane.

00:01:45.140 --> 00:01:49.410
On the second leg of the trip,
flying from Dallas to LA,

00:01:49.410 --> 00:01:54.380
we also have a
capacity of 166 seats.

00:01:54.380 --> 00:01:57.610
So we need to remember
that the passengers flying

00:01:57.610 --> 00:02:01.230
from New York to LA
will take up capacity

00:02:01.230 --> 00:02:05.750
on both legs of the trip, while
the other types of passengers

00:02:05.750 --> 00:02:10.120
will only take up capacity
on one leg of the trip.

00:02:10.120 --> 00:02:12.580
So what are our decisions now?

00:02:12.580 --> 00:02:16.829
They're the number of regular
tickets to sell for each type,

00:02:16.829 --> 00:02:20.690
and the number of discount
tickets to sell for each type.

00:02:20.690 --> 00:02:26.090
So in total, we have
six decisions to make.

00:02:26.090 --> 00:02:28.790
Now, let's define our objective.

00:02:28.790 --> 00:02:32.910
Like before, it's to
maximize the total revenue.

00:02:32.910 --> 00:02:36.810
This is the sum of the price
of the ticket times the number

00:02:36.810 --> 00:02:42.480
of seats of that type we
sell, for each type of ticket.

00:02:42.480 --> 00:02:46.860
And like before, we have two
types of constraints-- capacity

00:02:46.860 --> 00:02:49.780
constraints and
demand constraints.

00:02:49.780 --> 00:02:52.490
For the capacity
constraints, the airline

00:02:52.490 --> 00:02:55.870
shouldn't sell more seats than
the capacity of the plane,

00:02:55.870 --> 00:02:58.050
for each leg of the trip.

00:02:58.050 --> 00:03:00.970
So we need two capacity
constraints here:

00:03:00.970 --> 00:03:05.410
one for the New York to Dallas
leg and one for the Dallas

00:03:05.410 --> 00:03:07.150
to LA leg.

00:03:07.150 --> 00:03:10.150
Note that the New
York to LA passengers

00:03:10.150 --> 00:03:13.420
have to be counted on
both legs of the trip.

00:03:13.420 --> 00:03:16.620
So the first constraint
accounts for all passengers that

00:03:16.620 --> 00:03:20.960
need to be on the plane when it
flies from New York to Dallas,

00:03:20.960 --> 00:03:24.350
and the second constraint
accounts for all passengers

00:03:24.350 --> 00:03:25.870
that need to be
on the plane when

00:03:25.870 --> 00:03:29.880
it flies from Dallas to LA.

00:03:29.880 --> 00:03:32.840
We also need six
demand constraints,

00:03:32.840 --> 00:03:35.200
one for each type of ticket.

00:03:35.200 --> 00:03:37.140
The number of seats
sold should not

00:03:37.140 --> 00:03:41.400
exceed the forecasted
demand for each type.

00:03:41.400 --> 00:03:45.020
And lastly, we can't sell
a negative number of seats,

00:03:45.020 --> 00:03:47.770
so we have our
non-negativity constraints

00:03:47.770 --> 00:03:51.300
to prevent the variables
from being negative.

00:03:51.300 --> 00:03:55.240
Let's now go to LibreOffice
and adjust our formulation

00:03:55.240 --> 00:03:57.350
to solve this bigger problem.

00:03:57.350 --> 00:03:59.520
In LibreOffice, go
ahead and open the file

00:03:59.520 --> 00:04:00.820
Week9_AirlineRM_Connecting.ods.

00:04:05.650 --> 00:04:09.160
In this file, I've set up
our data, our decisions,

00:04:09.160 --> 00:04:12.080
our objective, and
our constraints.

00:04:12.080 --> 00:04:15.040
Our decisions, again, are
highlighted in yellow.

00:04:15.040 --> 00:04:20.170
We have a decision for each
type of seat on each flight.

00:04:20.170 --> 00:04:23.890
Our objective here
is the spot in blue.

00:04:23.890 --> 00:04:27.510
To build our objective, we'll
use the sumproduct function.

00:04:27.510 --> 00:04:32.840
So type = and then sumproduct,
and in parentheses,

00:04:32.840 --> 00:04:36.900
select all six prices,
type a semicolon,

00:04:36.900 --> 00:04:39.740
and then select
all six decisions.

00:04:39.740 --> 00:04:42.680
Close the parentheses
and hit Enter.

00:04:42.680 --> 00:04:44.909
We see here, like we
did before, that we

00:04:44.909 --> 00:04:47.960
have 0 in our objective,
because right now, we're

00:04:47.960 --> 00:04:50.740
not selling any seats.

00:04:50.740 --> 00:04:53.370
Now let's create
our constraints.

00:04:53.370 --> 00:04:56.810
The first constraints
are capacity constraints.

00:04:56.810 --> 00:05:01.290
The first is the capacity on
the leg from New York to Dallas.

00:05:01.290 --> 00:05:05.430
The left-hand side should be
equal to the seats from New

00:05:05.430 --> 00:05:13.500
York to LA plus the seats
from New York to Dallas.

00:05:13.500 --> 00:05:16.810
The sign is less than or
equals and the right-hand side

00:05:16.810 --> 00:05:21.330
is 166, the capacity
of our aircraft.

00:05:21.330 --> 00:05:24.360
Now we need to build the
capacity constraint from Dallas

00:05:24.360 --> 00:05:25.580
to LA.

00:05:25.580 --> 00:05:28.570
The left-hand side is equal
to the seats from New York

00:05:28.570 --> 00:05:32.870
to LA plus the seats
from Dallas to LA.

00:05:35.540 --> 00:05:37.930
Our sign is, again,
less than or equals

00:05:37.930 --> 00:05:41.470
and our right-hand side is 166.

00:05:41.470 --> 00:05:45.290
For the demand constraints and
the non-negativity constraints,

00:05:45.290 --> 00:05:47.580
because we have six
of each this time,

00:05:47.580 --> 00:05:51.120
we'll actually make them in a
more efficient way than before.

00:05:51.120 --> 00:05:52.700
So we just have
a note down there

00:05:52.700 --> 00:05:55.980
that we need to remember
to add these constraints.

00:05:55.980 --> 00:06:00.500
So now go ahead and in the
Tools menu, select Solver.

00:06:00.500 --> 00:06:02.980
We need to first fill in
the target cell, which

00:06:02.980 --> 00:06:05.220
should be the objective.

00:06:05.220 --> 00:06:08.420
Make sure that
Maximum is selected.

00:06:08.420 --> 00:06:14.650
Then, in the Changing Cells
box, select all six decisions.

00:06:14.650 --> 00:06:16.680
Down in the Limiting
Conditions, let's now

00:06:16.680 --> 00:06:18.440
build our constraints.

00:06:18.440 --> 00:06:20.790
For the Cell Reference
column, let's start

00:06:20.790 --> 00:06:23.760
by selecting the left-hand
side of the two capacity

00:06:23.760 --> 00:06:25.200
constraints.

00:06:25.200 --> 00:06:28.100
The Operator should
be less than or equals

00:06:28.100 --> 00:06:30.800
and the Value should be the
right-hand side of these two

00:06:30.800 --> 00:06:33.480
capacity constraints.

00:06:33.480 --> 00:06:36.190
Now let's make the
demand constraints.

00:06:36.190 --> 00:06:40.210
In Cell Reference, just
directly select the six decision

00:06:40.210 --> 00:06:44.680
variables, make sure the
Operator's less than or equals,

00:06:44.680 --> 00:06:48.810
and for the Value, select
the six demand constraints.

00:06:48.810 --> 00:06:51.630
This is a bit easier than what
we did before because we didn't

00:06:51.630 --> 00:06:54.760
have to type them all
out in our spreadsheet.

00:06:54.760 --> 00:06:58.460
Now let's do a similar thing for
the non-negativity constraints,

00:06:58.460 --> 00:07:02.020
where in Cell Reference, we
select the six decisions.

00:07:02.020 --> 00:07:05.590
The Operator this time should
be greater than or equals,

00:07:05.590 --> 00:07:08.710
and for the Value, just type 0.

00:07:08.710 --> 00:07:12.940
Make sure in Options that the
Linear Solver is selected,

00:07:12.940 --> 00:07:16.130
and go ahead and hit Solve.

00:07:16.130 --> 00:07:20.200
The solving result should say:
"Solving successfully finished.

00:07:20.200 --> 00:07:23.880
Result: 120,514."

00:07:23.880 --> 00:07:25.730
This is our total revenue.

00:07:25.730 --> 00:07:28.420
Go ahead and click
Keep Result, and let's

00:07:28.420 --> 00:07:30.540
take a look at our solution.

00:07:30.540 --> 00:07:33.060
So we see here that
the optimal solution

00:07:33.060 --> 00:07:37.570
is to sell 80 tickets for
the regular price from New

00:07:37.570 --> 00:07:42.590
York to LA, 0 of the discount
price from New York to LA,

00:07:42.590 --> 00:07:46.800
75 of the regular price
from New York to Dallas,

00:07:46.800 --> 00:07:50.690
11 of the discount price
from New York to Dallas,

00:07:50.690 --> 00:07:53.909
60 of the regular price
from Dallas to LA,

00:07:53.909 --> 00:07:58.680
and lastly, 26 of the discount
price from Dallas to LA.

00:07:58.680 --> 00:08:00.880
We saw here that we
could pretty easily

00:08:00.880 --> 00:08:04.510
solve a more complicated
problem in LibreOffice

00:08:04.510 --> 00:08:08.290
that we probably couldn't have
solved as easily by inspection.