1
00:00:09,500 --> 00:00:12,580
So far, we've only considered
optimizing the fares

2
00:00:12,580 --> 00:00:14,610
for a single route.

3
00:00:14,610 --> 00:00:18,200
In this video, we'll change
our optimization formulation

4
00:00:18,200 --> 00:00:20,780
to include connecting flights.

5
00:00:20,780 --> 00:00:25,050
Now, instead of just being
able to go from JFK in New York

6
00:00:25,050 --> 00:00:29,280
to LAX in Los Angeles, let's
suppose that the plane stops

7
00:00:29,280 --> 00:00:33,150
in Dallas at the Dallas
Fort Worth airport.

8
00:00:33,150 --> 00:00:35,700
We still are just
using one plane,

9
00:00:35,700 --> 00:00:38,460
but the passengers can
now fly from New York

10
00:00:38,460 --> 00:00:42,080
to Dallas, Dallas
to Los Angeles,

11
00:00:42,080 --> 00:00:44,910
or from New York to
Los Angeles by just

12
00:00:44,910 --> 00:00:48,490
staying on the plane in Dallas.

13
00:00:48,490 --> 00:00:51,560
So how does our
optimization problem change?

14
00:00:51,560 --> 00:00:55,200
We now have six types of
seats that we can offer:

15
00:00:55,200 --> 00:00:58,380
the original two types,
regular and discount

16
00:00:58,380 --> 00:01:02,310
from New York to LA,
and four new types.

17
00:01:02,310 --> 00:01:05,630
We can sell both regular
and discount seats

18
00:01:05,630 --> 00:01:09,190
from New York to Dallas,
and regular and discount

19
00:01:09,190 --> 00:01:12,100
seats from Dallas
to Los Angeles.

20
00:01:12,100 --> 00:01:15,330
We know the price of
each type of ticket

21
00:01:15,330 --> 00:01:19,450
as well as the forecasted
demand for each type of ticket.

22
00:01:19,450 --> 00:01:21,730
We also know that
we have a capacity

23
00:01:21,730 --> 00:01:27,289
of 166 seats on our plane
for each leg of the trip.

24
00:01:27,289 --> 00:01:31,539
There's room for 166 passengers
on the plane from New

25
00:01:31,539 --> 00:01:34,960
York to Dallas, or the
first leg of the trip.

26
00:01:34,960 --> 00:01:38,250
Then the passengers with a
final destination of Dallas

27
00:01:38,250 --> 00:01:42,270
will get off the plane and the
passengers flying from Dallas

28
00:01:42,270 --> 00:01:45,140
to LA will get on the plane.

29
00:01:45,140 --> 00:01:49,410
On the second leg of the trip,
flying from Dallas to LA,

30
00:01:49,410 --> 00:01:54,380
we also have a
capacity of 166 seats.

31
00:01:54,380 --> 00:01:57,610
So we need to remember
that the passengers flying

32
00:01:57,610 --> 00:02:01,230
from New York to LA
will take up capacity

33
00:02:01,230 --> 00:02:05,750
on both legs of the trip, while
the other types of passengers

34
00:02:05,750 --> 00:02:10,120
will only take up capacity
on one leg of the trip.

35
00:02:10,120 --> 00:02:12,580
So what are our decisions now?

36
00:02:12,580 --> 00:02:16,829
They're the number of regular
tickets to sell for each type,

37
00:02:16,829 --> 00:02:20,690
and the number of discount
tickets to sell for each type.

38
00:02:20,690 --> 00:02:26,090
So in total, we have
six decisions to make.

39
00:02:26,090 --> 00:02:28,790
Now, let's define our objective.

40
00:02:28,790 --> 00:02:32,910
Like before, it's to
maximize the total revenue.

41
00:02:32,910 --> 00:02:36,810
This is the sum of the price
of the ticket times the number

42
00:02:36,810 --> 00:02:42,480
of seats of that type we
sell, for each type of ticket.

43
00:02:42,480 --> 00:02:46,860
And like before, we have two
types of constraints-- capacity

44
00:02:46,860 --> 00:02:49,780
constraints and
demand constraints.

45
00:02:49,780 --> 00:02:52,490
For the capacity
constraints, the airline

46
00:02:52,490 --> 00:02:55,870
shouldn't sell more seats than
the capacity of the plane,

47
00:02:55,870 --> 00:02:58,050
for each leg of the trip.

48
00:02:58,050 --> 00:03:00,970
So we need two capacity
constraints here:

49
00:03:00,970 --> 00:03:05,410
one for the New York to Dallas
leg and one for the Dallas

50
00:03:05,410 --> 00:03:07,150
to LA leg.

51
00:03:07,150 --> 00:03:10,150
Note that the New
York to LA passengers

52
00:03:10,150 --> 00:03:13,420
have to be counted on
both legs of the trip.

53
00:03:13,420 --> 00:03:16,620
So the first constraint
accounts for all passengers that

54
00:03:16,620 --> 00:03:20,960
need to be on the plane when it
flies from New York to Dallas,

55
00:03:20,960 --> 00:03:24,350
and the second constraint
accounts for all passengers

56
00:03:24,350 --> 00:03:25,870
that need to be
on the plane when

57
00:03:25,870 --> 00:03:29,880
it flies from Dallas to LA.

58
00:03:29,880 --> 00:03:32,840
We also need six
demand constraints,

59
00:03:32,840 --> 00:03:35,200
one for each type of ticket.

60
00:03:35,200 --> 00:03:37,140
The number of seats
sold should not

61
00:03:37,140 --> 00:03:41,400
exceed the forecasted
demand for each type.

62
00:03:41,400 --> 00:03:45,020
And lastly, we can't sell
a negative number of seats,

63
00:03:45,020 --> 00:03:47,770
so we have our
non-negativity constraints

64
00:03:47,770 --> 00:03:51,300
to prevent the variables
from being negative.

65
00:03:51,300 --> 00:03:55,240
Let's now go to LibreOffice
and adjust our formulation

66
00:03:55,240 --> 00:03:57,350
to solve this bigger problem.

67
00:03:57,350 --> 00:03:59,520
In LibreOffice, go
ahead and open the file

68
00:03:59,520 --> 00:04:00,820
Week9_AirlineRM_Connecting.ods.

69
00:04:05,650 --> 00:04:09,160
In this file, I've set up
our data, our decisions,

70
00:04:09,160 --> 00:04:12,080
our objective, and
our constraints.

71
00:04:12,080 --> 00:04:15,040
Our decisions, again, are
highlighted in yellow.

72
00:04:15,040 --> 00:04:20,170
We have a decision for each
type of seat on each flight.

73
00:04:20,170 --> 00:04:23,890
Our objective here
is the spot in blue.

74
00:04:23,890 --> 00:04:27,510
To build our objective, we'll
use the sumproduct function.

75
00:04:27,510 --> 00:04:32,840
So type = and then sumproduct,
and in parentheses,

76
00:04:32,840 --> 00:04:36,900
select all six prices,
type a semicolon,

77
00:04:36,900 --> 00:04:39,740
and then select
all six decisions.

78
00:04:39,740 --> 00:04:42,680
Close the parentheses
and hit Enter.

79
00:04:42,680 --> 00:04:44,909
We see here, like we
did before, that we

80
00:04:44,909 --> 00:04:47,960
have 0 in our objective,
because right now, we're

81
00:04:47,960 --> 00:04:50,740
not selling any seats.

82
00:04:50,740 --> 00:04:53,370
Now let's create
our constraints.

83
00:04:53,370 --> 00:04:56,810
The first constraints
are capacity constraints.

84
00:04:56,810 --> 00:05:01,290
The first is the capacity on
the leg from New York to Dallas.

85
00:05:01,290 --> 00:05:05,430
The left-hand side should be
equal to the seats from New

86
00:05:05,430 --> 00:05:13,500
York to LA plus the seats
from New York to Dallas.

87
00:05:13,500 --> 00:05:16,810
The sign is less than or
equals and the right-hand side

88
00:05:16,810 --> 00:05:21,330
is 166, the capacity
of our aircraft.

89
00:05:21,330 --> 00:05:24,360
Now we need to build the
capacity constraint from Dallas

90
00:05:24,360 --> 00:05:25,580
to LA.

91
00:05:25,580 --> 00:05:28,570
The left-hand side is equal
to the seats from New York

92
00:05:28,570 --> 00:05:32,870
to LA plus the seats
from Dallas to LA.

93
00:05:35,540 --> 00:05:37,930
Our sign is, again,
less than or equals

94
00:05:37,930 --> 00:05:41,470
and our right-hand side is 166.

95
00:05:41,470 --> 00:05:45,290
For the demand constraints and
the non-negativity constraints,

96
00:05:45,290 --> 00:05:47,580
because we have six
of each this time,

97
00:05:47,580 --> 00:05:51,120
we'll actually make them in a
more efficient way than before.

98
00:05:51,120 --> 00:05:52,700
So we just have
a note down there

99
00:05:52,700 --> 00:05:55,980
that we need to remember
to add these constraints.

100
00:05:55,980 --> 00:06:00,500
So now go ahead and in the
Tools menu, select Solver.

101
00:06:00,500 --> 00:06:02,980
We need to first fill in
the target cell, which

102
00:06:02,980 --> 00:06:05,220
should be the objective.

103
00:06:05,220 --> 00:06:08,420
Make sure that
Maximum is selected.

104
00:06:08,420 --> 00:06:14,650
Then, in the Changing Cells
box, select all six decisions.

105
00:06:14,650 --> 00:06:16,680
Down in the Limiting
Conditions, let's now

106
00:06:16,680 --> 00:06:18,440
build our constraints.

107
00:06:18,440 --> 00:06:20,790
For the Cell Reference
column, let's start

108
00:06:20,790 --> 00:06:23,760
by selecting the left-hand
side of the two capacity

109
00:06:23,760 --> 00:06:25,200
constraints.

110
00:06:25,200 --> 00:06:28,100
The Operator should
be less than or equals

111
00:06:28,100 --> 00:06:30,800
and the Value should be the
right-hand side of these two

112
00:06:30,800 --> 00:06:33,480
capacity constraints.

113
00:06:33,480 --> 00:06:36,190
Now let's make the
demand constraints.

114
00:06:36,190 --> 00:06:40,210
In Cell Reference, just
directly select the six decision

115
00:06:40,210 --> 00:06:44,680
variables, make sure the
Operator's less than or equals,

116
00:06:44,680 --> 00:06:48,810
and for the Value, select
the six demand constraints.

117
00:06:48,810 --> 00:06:51,630
This is a bit easier than what
we did before because we didn't

118
00:06:51,630 --> 00:06:54,760
have to type them all
out in our spreadsheet.

119
00:06:54,760 --> 00:06:58,460
Now let's do a similar thing for
the non-negativity constraints,

120
00:06:58,460 --> 00:07:02,020
where in Cell Reference, we
select the six decisions.

121
00:07:02,020 --> 00:07:05,590
The Operator this time should
be greater than or equals,

122
00:07:05,590 --> 00:07:08,710
and for the Value, just type 0.

123
00:07:08,710 --> 00:07:12,940
Make sure in Options that the
Linear Solver is selected,

124
00:07:12,940 --> 00:07:16,130
and go ahead and hit Solve.

125
00:07:16,130 --> 00:07:20,200
The solving result should say:
"Solving successfully finished.

126
00:07:20,200 --> 00:07:23,880
Result: 120,514."

127
00:07:23,880 --> 00:07:25,730
This is our total revenue.

128
00:07:25,730 --> 00:07:28,420
Go ahead and click
Keep Result, and let's

129
00:07:28,420 --> 00:07:30,540
take a look at our solution.

130
00:07:30,540 --> 00:07:33,060
So we see here that
the optimal solution

131
00:07:33,060 --> 00:07:37,570
is to sell 80 tickets for
the regular price from New

132
00:07:37,570 --> 00:07:42,590
York to LA, 0 of the discount
price from New York to LA,

133
00:07:42,590 --> 00:07:46,800
75 of the regular price
from New York to Dallas,

134
00:07:46,800 --> 00:07:50,690
11 of the discount price
from New York to Dallas,

135
00:07:50,690 --> 00:07:53,909
60 of the regular price
from Dallas to LA,

136
00:07:53,909 --> 00:07:58,680
and lastly, 26 of the discount
price from Dallas to LA.

137
00:07:58,680 --> 00:08:00,880
We saw here that we
could pretty easily

138
00:08:00,880 --> 00:08:04,510
solve a more complicated
problem in LibreOffice

139
00:08:04,510 --> 00:08:08,290
that we probably couldn't have
solved as easily by inspection.