WEBVTT
00:00:04.770 --> 00:00:07.050
In this video, we'll
solve our sports
00:00:07.050 --> 00:00:10.250
scheduling problem
in LibreOffice.
00:00:10.250 --> 00:00:14.500
We can use the solver to solve
integer optimization problems
00:00:14.500 --> 00:00:19.140
using the same process as for
linear optimization problems.
00:00:19.140 --> 00:00:21.010
The only difference
is that we need
00:00:21.010 --> 00:00:23.820
to add extra constraints
to define variables
00:00:23.820 --> 00:00:26.640
as integer or binary.
00:00:26.640 --> 00:00:29.460
Let's go ahead and solve our
small tournament scheduling
00:00:29.460 --> 00:00:30.830
problem.
00:00:30.830 --> 00:00:33.630
In LibreOffice, or in
the spreadsheet software
00:00:33.630 --> 00:00:36.220
you're using, go ahead
and open the spreadsheet
00:00:36.220 --> 00:00:37.100
SportsScheduling.ods.
00:00:39.940 --> 00:00:41.740
At the top of the
spreadsheet, I've
00:00:41.740 --> 00:00:45.070
created a spot for our
decision variables.
00:00:45.070 --> 00:00:48.150
We have a decision
variable for each week,
00:00:48.150 --> 00:00:51.490
weeks one through four,
and for each team pair:
00:00:51.490 --> 00:00:57.410
A and B, A and C, A and
D, B and C, B and D,
00:00:57.410 --> 00:01:01.730
and C and D. This gives
us a total of 24 decision
00:01:01.730 --> 00:01:04.769
variables highlighted in yellow.
00:01:04.769 --> 00:01:06.790
Below the decision
variables, there's
00:01:06.790 --> 00:01:10.570
a spot for our objective,
highlighted in blue.
00:01:10.570 --> 00:01:14.430
Let's go ahead and
construct our objective.
00:01:14.430 --> 00:01:17.690
Start by typing an
equals sign, and then we
00:01:17.690 --> 00:01:22.490
want it to be equal to 1 times
the decision variable for A
00:01:22.490 --> 00:01:27.890
and B in week 1, plus 2 times
the decision variable for A
00:01:27.890 --> 00:01:34.990
and B in week 2, plus 4 times
the decision variable for A
00:01:34.990 --> 00:01:39.220
and B in week 3, plus
8 times the decision
00:01:39.220 --> 00:01:41.880
variable for A and B in week 4.
00:01:41.880 --> 00:01:45.170
Now we want to repeat
this for teams C and D.
00:01:45.170 --> 00:01:50.320
So now we have 1 times the
decision variable for C and D
00:01:50.320 --> 00:01:55.520
in week 1, plus 2 times the
decision variable for C and D
00:01:55.520 --> 00:02:01.170
in week 2, plus 4 times the
decision variable for C and D
00:02:01.170 --> 00:02:06.740
in week 3, plus 8 times the
decision variable for C and D
00:02:06.740 --> 00:02:08.440
in week 4.
00:02:08.440 --> 00:02:10.419
Go ahead and hit Enter.
00:02:10.419 --> 00:02:12.580
Our objective has
value 0 for now
00:02:12.580 --> 00:02:16.370
because we haven't filled in
our decision variables yet.
00:02:16.370 --> 00:02:19.280
Now, let's construct
our constraints.
00:02:19.280 --> 00:02:23.690
The first constraint is teams
A and B should play twice.
00:02:23.690 --> 00:02:27.970
So our left hand side should
be equal to the sum-- we'll
00:02:27.970 --> 00:02:30.980
use the sum function
here, which just adds up
00:02:30.980 --> 00:02:33.200
everything inside
the parentheses.
00:02:33.200 --> 00:02:36.850
So type =sum, and
then in parentheses,
00:02:36.850 --> 00:02:41.230
select the four A and
B decision variables.
00:02:41.230 --> 00:02:43.990
The sign should be equals
and the right hand side
00:02:43.990 --> 00:02:45.579
should be 2.
00:02:45.579 --> 00:02:48.810
Now let's just repeat
this for teams C and D.
00:02:48.810 --> 00:02:53.050
So the left hand side is the
sum of the C and D decision
00:02:53.050 --> 00:02:59.480
variables, the sign is equals,
and the right hand side is 2.
00:02:59.480 --> 00:03:02.100
Now we want to add the
constraint that teams A
00:03:02.100 --> 00:03:07.350
and C should play once, so this
left hand side is very similar.
00:03:07.350 --> 00:03:12.360
It should be the sum of the
A and C decision variables,
00:03:12.360 --> 00:03:15.390
the sign should be equals,
and the right hand side here
00:03:15.390 --> 00:03:17.490
should be 1.
00:03:17.490 --> 00:03:20.550
Now let's repeat this
for teams A and D.
00:03:20.550 --> 00:03:25.650
The left hand side is the sum of
the A and D decision variables,
00:03:25.650 --> 00:03:29.660
the sign is equals, and
the right hand side is 1.
00:03:29.660 --> 00:03:32.600
We'll repeat it again
for teams B and C.
00:03:32.600 --> 00:03:36.150
So the left hand side is the
sum of the B and C decision
00:03:36.150 --> 00:03:41.440
variables, the sign is equals,
and the right hand side is 1.
00:03:41.440 --> 00:03:43.410
The last of this
type of constraint
00:03:43.410 --> 00:03:46.760
is that teams B and
D should play once,
00:03:46.760 --> 00:03:50.430
so the left hand side is the
sum of the B and D decision
00:03:50.430 --> 00:03:56.170
variables, the sign is equals,
and the right hand side is 1.
00:03:56.170 --> 00:03:57.880
Now we want to add
the constraints
00:03:57.880 --> 00:04:01.500
that each team should only
play once in each week.
00:04:01.500 --> 00:04:05.780
So the first is that team A
should play once in week one.
00:04:05.780 --> 00:04:09.740
So the left hand side should be
the sum of all of the decision
00:04:09.740 --> 00:04:12.800
variables where A
plays in week one.
00:04:12.800 --> 00:04:17.540
So this should be equal to
A playing B in week one,
00:04:17.540 --> 00:04:24.310
plus A playing C in week one,
plus A playing D in week one.
00:04:24.310 --> 00:04:28.770
The sign is equals, and the
right hand side is again 1.
00:04:28.770 --> 00:04:30.900
Now let's repeat
this for week two.
00:04:30.900 --> 00:04:34.720
So the left hand side is equal
to A playing B in week two,
00:04:34.720 --> 00:04:41.310
plus A playing C in week two,
plus A playing D in week two.
00:04:41.310 --> 00:04:45.360
The sign is equals and
the right hand side is 1.
00:04:45.360 --> 00:04:47.930
Now let's repeat this
for weeks three and four.
00:04:47.930 --> 00:04:50.870
So for week three,
the left hand side
00:04:50.870 --> 00:04:56.270
is just equal to the variables
of A playing with week three:
00:04:56.270 --> 00:05:01.920
A and B, A and C, A and
D. The sign is equals
00:05:01.920 --> 00:05:04.400
and the right hand side is 1.
00:05:04.400 --> 00:05:07.010
For week four,
the left hand side
00:05:07.010 --> 00:05:12.690
is just the A variables in
week four, A and B, A and C,
00:05:12.690 --> 00:05:16.330
and A and D. The
sign should be equals
00:05:16.330 --> 00:05:19.010
and the right hand side is 1.
00:05:19.010 --> 00:05:22.400
I went ahead and filled in the
rest of the constraints for you
00:05:22.400 --> 00:05:26.390
since they're just repeating
the same thing for team B,
00:05:26.390 --> 00:05:30.000
team C, and team D.
00:05:30.000 --> 00:05:32.600
Now we're ready to
solve our problem.
00:05:32.600 --> 00:05:38.070
So let's go to the "Tools"
menu and select "Solver".
00:05:38.070 --> 00:05:42.010
In the Solver window, let's
first pick our target cell.
00:05:42.010 --> 00:05:45.550
So with the cursor in
the "Target cell" box,
00:05:45.550 --> 00:05:47.760
go ahead and select
the objective cell,
00:05:47.760 --> 00:05:49.600
or the blue cell.
00:05:49.600 --> 00:05:51.820
Make sure that
"Maximum" is selected,
00:05:51.820 --> 00:05:55.250
since we're trying to
maximize preferences.
00:05:55.250 --> 00:05:59.100
Then, with the cursor in
the "By changing cells" box,
00:05:59.100 --> 00:06:04.190
go ahead and select all
24 decision variables.
00:06:04.190 --> 00:06:06.650
Now, let's add in
our constraints.
00:06:06.650 --> 00:06:09.630
Since all of our constraints
have an equals sign,
00:06:09.630 --> 00:06:12.100
we can add them all in together.
00:06:12.100 --> 00:06:14.740
So in the first
Cell Reference box,
00:06:14.740 --> 00:06:17.380
go ahead and select all
of the left hand sides.
00:06:23.560 --> 00:06:28.150
In the Operator
box, select equals.
00:06:28.150 --> 00:06:32.000
Then, in the Value box, select
all of the right hand sides.
00:06:40.590 --> 00:06:43.290
Since this is an integer
optimization problem,
00:06:43.290 --> 00:06:46.710
there's one more thing we need
to do in the constraint area.
00:06:46.710 --> 00:06:49.300
In the Cell Reference
box, go ahead
00:06:49.300 --> 00:06:51.440
and select all of the
decision variables.
00:06:55.530 --> 00:06:59.720
Then, in the operator pull
down menu, select Binary.
00:06:59.720 --> 00:07:02.850
This will make all of our
decision variables binary.
00:07:02.850 --> 00:07:06.420
We don't need to put
anything in the Value column.
00:07:06.420 --> 00:07:09.460
The last thing we need
to do is in Options,
00:07:09.460 --> 00:07:14.640
make sure that we're using the
linear solver, and click OK.
00:07:14.640 --> 00:07:18.000
Now, go ahead and hit Solve.
00:07:18.000 --> 00:07:20.140
The solving results says:
solving successfully
00:07:20.140 --> 00:07:22.400
finished, result 24.
00:07:22.400 --> 00:07:25.130
Go ahead and pick Keep
Result, and now let's
00:07:25.130 --> 00:07:26.900
look at our solution.
00:07:26.900 --> 00:07:31.820
We can see that teams A
and B and teams C and D
00:07:31.820 --> 00:07:34.800
both play during weeks 3 and 4.
00:07:34.800 --> 00:07:38.860
This makes sense, since we're
trying to maximize preferences,
00:07:38.860 --> 00:07:41.060
and the preference for
teams in the same division
00:07:41.060 --> 00:07:44.000
is to play later on.
00:07:44.000 --> 00:07:47.550
In the next video, we'll see the
different types of constraints
00:07:47.550 --> 00:07:51.700
that we can add to an
integer optimization model.