1
00:00:04,500 --> 00:00:08,010
In this video, we'll solve our
linear optimization problem

2
00:00:08,010 --> 00:00:10,980
in the software LibreOffice.

3
00:00:10,980 --> 00:00:14,280
LibreOffice is similar
to Microsoft Excel,

4
00:00:14,280 --> 00:00:16,239
but it's an open
source software,

5
00:00:16,239 --> 00:00:19,300
and is available for
free on the internet.

6
00:00:19,300 --> 00:00:22,630
Another option we could
use is OpenOffice.

7
00:00:22,630 --> 00:00:26,180
You're welcome to use Excel,
OpenOffice, or LibreOffice

8
00:00:26,180 --> 00:00:29,750
in this course, and whenever
we mention LibreOffice,

9
00:00:29,750 --> 00:00:31,400
keep in mind that
you could be using

10
00:00:31,400 --> 00:00:34,070
one of the other
softwares instead.

11
00:00:34,070 --> 00:00:36,450
For more information
about the options,

12
00:00:36,450 --> 00:00:39,980
see the download
instructions on edX.

13
00:00:39,980 --> 00:00:42,580
You should have already
downloaded and installed

14
00:00:42,580 --> 00:00:43,960
LibreOffice.

15
00:00:43,960 --> 00:00:46,640
If not, follow the
instructions on edX

16
00:00:46,640 --> 00:00:50,400
before continuing
with this video.

17
00:00:50,400 --> 00:00:52,600
Go ahead and open the
file, Week9_AirlineRM.ods.

18
00:00:56,550 --> 00:00:59,440
I've already set up the data
for our problem and places

19
00:00:59,440 --> 00:01:02,520
for us to build our
decisions, our objective,

20
00:01:02,520 --> 00:01:05,060
and our constraints.

21
00:01:05,060 --> 00:01:07,680
The decisions are
highlighted in yellow.

22
00:01:07,680 --> 00:01:10,100
These are the number of
regular seats to sell,

23
00:01:10,100 --> 00:01:12,600
and the number of
discount seats to sell.

24
00:01:12,600 --> 00:01:14,850
We'll just leave these
cells blank for now,

25
00:01:14,850 --> 00:01:19,000
since the solver will be
finding the optimal values.

26
00:01:19,000 --> 00:01:22,320
Our objective, which we
saw in the previous video,

27
00:01:22,320 --> 00:01:24,820
is to maximize total revenue.

28
00:01:24,820 --> 00:01:28,530
Let's go ahead and build the
objective in this blue cell.

29
00:01:28,530 --> 00:01:31,900
It should equal the
price of regular seats,

30
00:01:31,900 --> 00:01:34,020
times the number
of regular seats

31
00:01:34,020 --> 00:01:38,490
we sell, plus the price
the discount seats,

32
00:01:38,490 --> 00:01:42,220
times the number of
discount seats we sell.

33
00:01:42,220 --> 00:01:44,330
Go ahead and hit Enter.

34
00:01:44,330 --> 00:01:46,830
You should see 0 in this cell.

35
00:01:46,830 --> 00:01:49,870
That's because right now,
we're not selling any seats.

36
00:01:49,870 --> 00:01:52,979
Our decision cells are blank.

37
00:01:52,979 --> 00:01:54,840
This could be a
little tedious if we

38
00:01:54,840 --> 00:01:56,850
had more than two decisions.

39
00:01:56,850 --> 00:01:59,690
To make it easier, we
can use a nice function

40
00:01:59,690 --> 00:02:02,840
called sumproduct to
build our objective.

41
00:02:02,840 --> 00:02:05,970
So go ahead and
clear the objective.

42
00:02:05,970 --> 00:02:08,979
Now in the objective
cell, let's type

43
00:02:08,979 --> 00:02:14,710
equals, and then sumproduct,
and then in parentheses,

44
00:02:14,710 --> 00:02:19,030
select the two prices,
type a semicolon,

45
00:02:19,030 --> 00:02:21,250
and then select the two seats.

46
00:02:21,250 --> 00:02:24,710
This will multiply the first
price times the first decision

47
00:02:24,710 --> 00:02:27,740
variable, and the second price
times the second decision

48
00:02:27,740 --> 00:02:30,160
variable, and add them up.

49
00:02:30,160 --> 00:02:32,850
Close the parentheses
and hit Enter.

50
00:02:32,850 --> 00:02:34,850
Note that if you're
using Excel, you

51
00:02:34,850 --> 00:02:38,720
should use a comma
instead of a semicolon.

52
00:02:38,720 --> 00:02:41,280
We should again see
0 in our objective.

53
00:02:41,280 --> 00:02:45,860
This is going to have the
exact same value it did before.

54
00:02:45,860 --> 00:02:48,790
Now let's construct
our constraints.

55
00:02:48,790 --> 00:02:52,280
The first constraint is
the capacity constraint.

56
00:02:52,280 --> 00:02:55,050
The green table here allows
us to easily write out

57
00:02:55,050 --> 00:02:58,810
our constraints in terms of
what's on the left-hand side,

58
00:02:58,810 --> 00:03:03,680
LHS, what the sign is,
like equals, less than

59
00:03:03,680 --> 00:03:06,160
or equals, or greater
than or equals,

60
00:03:06,160 --> 00:03:08,880
and what's on the
right-hand side, or RHS,

61
00:03:08,880 --> 00:03:10,550
of the constraint.

62
00:03:10,550 --> 00:03:14,010
So for the capacity
constraint, the left-hand side

63
00:03:14,010 --> 00:03:17,110
is equal to the number
of regular seats

64
00:03:17,110 --> 00:03:21,030
plus the number
of discount seats.

65
00:03:21,030 --> 00:03:25,300
The sign is less than or
equals, and the right-hand side

66
00:03:25,300 --> 00:03:30,030
is 166, the capacity
of our aircraft.

67
00:03:30,030 --> 00:03:33,620
The regular demand constraint
should be the regular number

68
00:03:33,620 --> 00:03:36,200
of seats, which
should be less than

69
00:03:36,200 --> 00:03:41,500
or equal to the regular
demand, which equals 100.

70
00:03:41,500 --> 00:03:43,640
The discount demand
should be the number

71
00:03:43,640 --> 00:03:45,770
of discount seats,
which should be

72
00:03:45,770 --> 00:03:51,430
less than or equal to the
demand, which is equal to 150.

73
00:03:51,430 --> 00:03:54,720
Note here that whenever I
pick the seats or the demand,

74
00:03:54,720 --> 00:03:57,170
I pick those cells
up on the top.

75
00:03:57,170 --> 00:03:59,480
That's because if we want
to change our demand,

76
00:03:59,480 --> 00:04:01,430
we could easily change
it up at the top,

77
00:04:01,430 --> 00:04:05,010
and all of our constraints
will change too.

78
00:04:05,010 --> 00:04:08,300
Now, let's add in our
non-negativity constraints.

79
00:04:08,300 --> 00:04:10,980
So the number of
regular seats should

80
00:04:10,980 --> 00:04:14,760
be greater than or equal
to 0, and the number

81
00:04:14,760 --> 00:04:17,779
of discount seats should be
greater than or equal to 0.

82
00:04:20,990 --> 00:04:23,450
Now we're ready to
solve our problem.

83
00:04:23,450 --> 00:04:26,100
To do this, we just
go to the Tools menu

84
00:04:26,100 --> 00:04:29,800
in LibreOffice
and select Solver.

85
00:04:29,800 --> 00:04:33,080
Now we need to fill in the
information about our problem.

86
00:04:33,080 --> 00:04:35,840
The "Target cell"
should be the objective.

87
00:04:35,840 --> 00:04:38,550
So with the blinking
cursor in the target cell,

88
00:04:38,550 --> 00:04:41,590
select the objective cell.

89
00:04:41,590 --> 00:04:43,650
We should also be
selecting "Maximum",

90
00:04:43,650 --> 00:04:46,690
since we're trying to
maximize the total revenue.

91
00:04:46,690 --> 00:04:49,320
The area called
"By changing cells"

92
00:04:49,320 --> 00:04:51,190
should be our
decision variables,

93
00:04:51,190 --> 00:04:54,240
so go ahead and select
that blank area,

94
00:04:54,240 --> 00:04:57,860
and select the
decision variables.

95
00:04:57,860 --> 00:05:01,020
The "Limiting conditions"
are our constraints.

96
00:05:01,020 --> 00:05:03,560
The "Cell reference" should
be the left-hand side

97
00:05:03,560 --> 00:05:06,880
of the constraint, the
"Operator" is the sign,

98
00:05:06,880 --> 00:05:10,010
and the "Value" is
the right-hand side.

99
00:05:10,010 --> 00:05:12,640
For constraints with the same
sign, if they're in a row,

100
00:05:12,640 --> 00:05:15,730
we could select them at
once to be more efficient.

101
00:05:15,730 --> 00:05:18,600
So first, let's select
the first three less than

102
00:05:18,600 --> 00:05:20,140
or equal to constraints.

103
00:05:20,140 --> 00:05:22,410
We want to make sure the
operator is less than

104
00:05:22,410 --> 00:05:26,390
or equal to, the integer and
binary options you see here,

105
00:05:26,390 --> 00:05:29,840
we'll explain next
week, and the value

106
00:05:29,840 --> 00:05:33,400
should be the right-hand
side of these constraints.

107
00:05:33,400 --> 00:05:35,550
Then we need to add in the
greater than or equal to

108
00:05:35,550 --> 00:05:36,550
constraints.

109
00:05:36,550 --> 00:05:39,520
So select the two
left-hand sides.

110
00:05:39,520 --> 00:05:42,600
The operator should be
greater than or equal to,

111
00:05:42,600 --> 00:05:46,580
and the value should be
the two right-hand sides.

112
00:05:46,580 --> 00:05:49,550
The last thing we want
to do is in Options,

113
00:05:49,550 --> 00:05:53,930
make sure that the LibreOffice
Linear Solver is selected.

114
00:05:53,930 --> 00:05:57,870
Click OK, and then hit Solve.

115
00:05:57,870 --> 00:06:02,130
The solving result should say:
"Solving successfully finished.

116
00:06:02,130 --> 00:06:05,740
Result: 77,408".

117
00:06:05,740 --> 00:06:08,720
This is the objective
of our optimal solution,

118
00:06:08,720 --> 00:06:11,440
and is the total revenue we get.

119
00:06:11,440 --> 00:06:13,770
Go ahead and click Keep Result.

120
00:06:13,770 --> 00:06:15,800
And now back in
our spreadsheet, we

121
00:06:15,800 --> 00:06:21,410
can see that our solution is to
sell 100 regular seats and 66

122
00:06:21,410 --> 00:06:23,500
discount seats.

123
00:06:23,500 --> 00:06:25,550
You may be thinking that
you could have done this

124
00:06:25,550 --> 00:06:26,940
without the Solver.

125
00:06:26,940 --> 00:06:29,560
But when the problems
become more complicated,

126
00:06:29,560 --> 00:06:32,000
it's very difficult
and often impossible

127
00:06:32,000 --> 00:06:34,010
to solve them by hand.

128
00:06:34,010 --> 00:06:35,909
We'll make our problem
more complicated

129
00:06:35,909 --> 00:06:39,250
later in the lecture, and
solve it in LibreOffice.