WEBVTT

00:00:04.500 --> 00:00:08.010
In this video, we'll solve our
linear optimization problem

00:00:08.010 --> 00:00:10.980
in the software LibreOffice.

00:00:10.980 --> 00:00:14.280
LibreOffice is similar
to Microsoft Excel,

00:00:14.280 --> 00:00:16.239
but it's an open
source software,

00:00:16.239 --> 00:00:19.300
and is available for
free on the internet.

00:00:19.300 --> 00:00:22.630
Another option we could
use is OpenOffice.

00:00:22.630 --> 00:00:26.180
You're welcome to use Excel,
OpenOffice, or LibreOffice

00:00:26.180 --> 00:00:29.750
in this course, and whenever
we mention LibreOffice,

00:00:29.750 --> 00:00:31.400
keep in mind that
you could be using

00:00:31.400 --> 00:00:34.070
one of the other
softwares instead.

00:00:34.070 --> 00:00:36.450
For more information
about the options,

00:00:36.450 --> 00:00:39.980
see the download
instructions on edX.

00:00:39.980 --> 00:00:42.580
You should have already
downloaded and installed

00:00:42.580 --> 00:00:43.960
LibreOffice.

00:00:43.960 --> 00:00:46.640
If not, follow the
instructions on edX

00:00:46.640 --> 00:00:50.400
before continuing
with this video.

00:00:50.400 --> 00:00:52.600
Go ahead and open the
file, Week9_AirlineRM.ods.

00:00:56.550 --> 00:00:59.440
I've already set up the data
for our problem and places

00:00:59.440 --> 00:01:02.520
for us to build our
decisions, our objective,

00:01:02.520 --> 00:01:05.060
and our constraints.

00:01:05.060 --> 00:01:07.680
The decisions are
highlighted in yellow.

00:01:07.680 --> 00:01:10.100
These are the number of
regular seats to sell,

00:01:10.100 --> 00:01:12.600
and the number of
discount seats to sell.

00:01:12.600 --> 00:01:14.850
We'll just leave these
cells blank for now,

00:01:14.850 --> 00:01:19.000
since the solver will be
finding the optimal values.

00:01:19.000 --> 00:01:22.320
Our objective, which we
saw in the previous video,

00:01:22.320 --> 00:01:24.820
is to maximize total revenue.

00:01:24.820 --> 00:01:28.530
Let's go ahead and build the
objective in this blue cell.

00:01:28.530 --> 00:01:31.900
It should equal the
price of regular seats,

00:01:31.900 --> 00:01:34.020
times the number
of regular seats

00:01:34.020 --> 00:01:38.490
we sell, plus the price
the discount seats,

00:01:38.490 --> 00:01:42.220
times the number of
discount seats we sell.

00:01:42.220 --> 00:01:44.330
Go ahead and hit Enter.

00:01:44.330 --> 00:01:46.830
You should see 0 in this cell.

00:01:46.830 --> 00:01:49.870
That's because right now,
we're not selling any seats.

00:01:49.870 --> 00:01:52.979
Our decision cells are blank.

00:01:52.979 --> 00:01:54.840
This could be a
little tedious if we

00:01:54.840 --> 00:01:56.850
had more than two decisions.

00:01:56.850 --> 00:01:59.690
To make it easier, we
can use a nice function

00:01:59.690 --> 00:02:02.840
called sumproduct to
build our objective.

00:02:02.840 --> 00:02:05.970
So go ahead and
clear the objective.

00:02:05.970 --> 00:02:08.979
Now in the objective
cell, let's type

00:02:08.979 --> 00:02:14.710
equals, and then sumproduct,
and then in parentheses,

00:02:14.710 --> 00:02:19.030
select the two prices,
type a semicolon,

00:02:19.030 --> 00:02:21.250
and then select the two seats.

00:02:21.250 --> 00:02:24.710
This will multiply the first
price times the first decision

00:02:24.710 --> 00:02:27.740
variable, and the second price
times the second decision

00:02:27.740 --> 00:02:30.160
variable, and add them up.

00:02:30.160 --> 00:02:32.850
Close the parentheses
and hit Enter.

00:02:32.850 --> 00:02:34.850
Note that if you're
using Excel, you

00:02:34.850 --> 00:02:38.720
should use a comma
instead of a semicolon.

00:02:38.720 --> 00:02:41.280
We should again see
0 in our objective.

00:02:41.280 --> 00:02:45.860
This is going to have the
exact same value it did before.

00:02:45.860 --> 00:02:48.790
Now let's construct
our constraints.

00:02:48.790 --> 00:02:52.280
The first constraint is
the capacity constraint.

00:02:52.280 --> 00:02:55.050
The green table here allows
us to easily write out

00:02:55.050 --> 00:02:58.810
our constraints in terms of
what's on the left-hand side,

00:02:58.810 --> 00:03:03.680
LHS, what the sign is,
like equals, less than

00:03:03.680 --> 00:03:06.160
or equals, or greater
than or equals,

00:03:06.160 --> 00:03:08.880
and what's on the
right-hand side, or RHS,

00:03:08.880 --> 00:03:10.550
of the constraint.

00:03:10.550 --> 00:03:14.010
So for the capacity
constraint, the left-hand side

00:03:14.010 --> 00:03:17.110
is equal to the number
of regular seats

00:03:17.110 --> 00:03:21.030
plus the number
of discount seats.

00:03:21.030 --> 00:03:25.300
The sign is less than or
equals, and the right-hand side

00:03:25.300 --> 00:03:30.030
is 166, the capacity
of our aircraft.

00:03:30.030 --> 00:03:33.620
The regular demand constraint
should be the regular number

00:03:33.620 --> 00:03:36.200
of seats, which
should be less than

00:03:36.200 --> 00:03:41.500
or equal to the regular
demand, which equals 100.

00:03:41.500 --> 00:03:43.640
The discount demand
should be the number

00:03:43.640 --> 00:03:45.770
of discount seats,
which should be

00:03:45.770 --> 00:03:51.430
less than or equal to the
demand, which is equal to 150.

00:03:51.430 --> 00:03:54.720
Note here that whenever I
pick the seats or the demand,

00:03:54.720 --> 00:03:57.170
I pick those cells
up on the top.

00:03:57.170 --> 00:03:59.480
That's because if we want
to change our demand,

00:03:59.480 --> 00:04:01.430
we could easily change
it up at the top,

00:04:01.430 --> 00:04:05.010
and all of our constraints
will change too.

00:04:05.010 --> 00:04:08.300
Now, let's add in our
non-negativity constraints.

00:04:08.300 --> 00:04:10.980
So the number of
regular seats should

00:04:10.980 --> 00:04:14.760
be greater than or equal
to 0, and the number

00:04:14.760 --> 00:04:17.779
of discount seats should be
greater than or equal to 0.

00:04:20.990 --> 00:04:23.450
Now we're ready to
solve our problem.

00:04:23.450 --> 00:04:26.100
To do this, we just
go to the Tools menu

00:04:26.100 --> 00:04:29.800
in LibreOffice
and select Solver.

00:04:29.800 --> 00:04:33.080
Now we need to fill in the
information about our problem.

00:04:33.080 --> 00:04:35.840
The "Target cell"
should be the objective.

00:04:35.840 --> 00:04:38.550
So with the blinking
cursor in the target cell,

00:04:38.550 --> 00:04:41.590
select the objective cell.

00:04:41.590 --> 00:04:43.650
We should also be
selecting "Maximum",

00:04:43.650 --> 00:04:46.690
since we're trying to
maximize the total revenue.

00:04:46.690 --> 00:04:49.320
The area called
"By changing cells"

00:04:49.320 --> 00:04:51.190
should be our
decision variables,

00:04:51.190 --> 00:04:54.240
so go ahead and select
that blank area,

00:04:54.240 --> 00:04:57.860
and select the
decision variables.

00:04:57.860 --> 00:05:01.020
The "Limiting conditions"
are our constraints.

00:05:01.020 --> 00:05:03.560
The "Cell reference" should
be the left-hand side

00:05:03.560 --> 00:05:06.880
of the constraint, the
"Operator" is the sign,

00:05:06.880 --> 00:05:10.010
and the "Value" is
the right-hand side.

00:05:10.010 --> 00:05:12.640
For constraints with the same
sign, if they're in a row,

00:05:12.640 --> 00:05:15.730
we could select them at
once to be more efficient.

00:05:15.730 --> 00:05:18.600
So first, let's select
the first three less than

00:05:18.600 --> 00:05:20.140
or equal to constraints.

00:05:20.140 --> 00:05:22.410
We want to make sure the
operator is less than

00:05:22.410 --> 00:05:26.390
or equal to, the integer and
binary options you see here,

00:05:26.390 --> 00:05:29.840
we'll explain next
week, and the value

00:05:29.840 --> 00:05:33.400
should be the right-hand
side of these constraints.

00:05:33.400 --> 00:05:35.550
Then we need to add in the
greater than or equal to

00:05:35.550 --> 00:05:36.550
constraints.

00:05:36.550 --> 00:05:39.520
So select the two
left-hand sides.

00:05:39.520 --> 00:05:42.600
The operator should be
greater than or equal to,

00:05:42.600 --> 00:05:46.580
and the value should be
the two right-hand sides.

00:05:46.580 --> 00:05:49.550
The last thing we want
to do is in Options,

00:05:49.550 --> 00:05:53.930
make sure that the LibreOffice
Linear Solver is selected.

00:05:53.930 --> 00:05:57.870
Click OK, and then hit Solve.

00:05:57.870 --> 00:06:02.130
The solving result should say:
"Solving successfully finished.

00:06:02.130 --> 00:06:05.740
Result: 77,408".

00:06:05.740 --> 00:06:08.720
This is the objective
of our optimal solution,

00:06:08.720 --> 00:06:11.440
and is the total revenue we get.

00:06:11.440 --> 00:06:13.770
Go ahead and click Keep Result.

00:06:13.770 --> 00:06:15.800
And now back in
our spreadsheet, we

00:06:15.800 --> 00:06:21.410
can see that our solution is to
sell 100 regular seats and 66

00:06:21.410 --> 00:06:23.500
discount seats.

00:06:23.500 --> 00:06:25.550
You may be thinking that
you could have done this

00:06:25.550 --> 00:06:26.940
without the Solver.

00:06:26.940 --> 00:06:29.560
But when the problems
become more complicated,

00:06:29.560 --> 00:06:32.000
it's very difficult
and often impossible

00:06:32.000 --> 00:06:34.010
to solve them by hand.

00:06:34.010 --> 00:06:35.909
We'll make our problem
more complicated

00:06:35.909 --> 00:06:39.250
later in the lecture, and
solve it in LibreOffice.