1
00:00:04,770 --> 00:00:07,050
In this video, we'll
solve our sports

2
00:00:07,050 --> 00:00:10,250
scheduling problem
in LibreOffice.

3
00:00:10,250 --> 00:00:14,500
We can use the solver to solve
integer optimization problems

4
00:00:14,500 --> 00:00:19,140
using the same process as for
linear optimization problems.

5
00:00:19,140 --> 00:00:21,010
The only difference
is that we need

6
00:00:21,010 --> 00:00:23,820
to add extra constraints
to define variables

7
00:00:23,820 --> 00:00:26,640
as integer or binary.

8
00:00:26,640 --> 00:00:29,460
Let's go ahead and solve our
small tournament scheduling

9
00:00:29,460 --> 00:00:30,830
problem.

10
00:00:30,830 --> 00:00:33,630
In LibreOffice, or in
the spreadsheet software

11
00:00:33,630 --> 00:00:36,220
you're using, go ahead
and open the spreadsheet

12
00:00:36,220 --> 00:00:37,100
SportsScheduling.ods.

13
00:00:39,940 --> 00:00:41,740
At the top of the
spreadsheet, I've

14
00:00:41,740 --> 00:00:45,070
created a spot for our
decision variables.

15
00:00:45,070 --> 00:00:48,150
We have a decision
variable for each week,

16
00:00:48,150 --> 00:00:51,490
weeks one through four,
and for each team pair:

17
00:00:51,490 --> 00:00:57,410
A and B, A and C, A and
D, B and C, B and D,

18
00:00:57,410 --> 00:01:01,730
and C and D. This gives
us a total of 24 decision

19
00:01:01,730 --> 00:01:04,769
variables highlighted in yellow.

20
00:01:04,769 --> 00:01:06,790
Below the decision
variables, there's

21
00:01:06,790 --> 00:01:10,570
a spot for our objective,
highlighted in blue.

22
00:01:10,570 --> 00:01:14,430
Let's go ahead and
construct our objective.

23
00:01:14,430 --> 00:01:17,690
Start by typing an
equals sign, and then we

24
00:01:17,690 --> 00:01:22,490
want it to be equal to 1 times
the decision variable for A

25
00:01:22,490 --> 00:01:27,890
and B in week 1, plus 2 times
the decision variable for A

26
00:01:27,890 --> 00:01:34,990
and B in week 2, plus 4 times
the decision variable for A

27
00:01:34,990 --> 00:01:39,220
and B in week 3, plus
8 times the decision

28
00:01:39,220 --> 00:01:41,880
variable for A and B in week 4.

29
00:01:41,880 --> 00:01:45,170
Now we want to repeat
this for teams C and D.

30
00:01:45,170 --> 00:01:50,320
So now we have 1 times the
decision variable for C and D

31
00:01:50,320 --> 00:01:55,520
in week 1, plus 2 times the
decision variable for C and D

32
00:01:55,520 --> 00:02:01,170
in week 2, plus 4 times the
decision variable for C and D

33
00:02:01,170 --> 00:02:06,740
in week 3, plus 8 times the
decision variable for C and D

34
00:02:06,740 --> 00:02:08,440
in week 4.

35
00:02:08,440 --> 00:02:10,419
Go ahead and hit Enter.

36
00:02:10,419 --> 00:02:12,580
Our objective has
value 0 for now

37
00:02:12,580 --> 00:02:16,370
because we haven't filled in
our decision variables yet.

38
00:02:16,370 --> 00:02:19,280
Now, let's construct
our constraints.

39
00:02:19,280 --> 00:02:23,690
The first constraint is teams
A and B should play twice.

40
00:02:23,690 --> 00:02:27,970
So our left hand side should
be equal to the sum-- we'll

41
00:02:27,970 --> 00:02:30,980
use the sum function
here, which just adds up

42
00:02:30,980 --> 00:02:33,200
everything inside
the parentheses.

43
00:02:33,200 --> 00:02:36,850
So type =sum, and
then in parentheses,

44
00:02:36,850 --> 00:02:41,230
select the four A and
B decision variables.

45
00:02:41,230 --> 00:02:43,990
The sign should be equals
and the right hand side

46
00:02:43,990 --> 00:02:45,579
should be 2.

47
00:02:45,579 --> 00:02:48,810
Now let's just repeat
this for teams C and D.

48
00:02:48,810 --> 00:02:53,050
So the left hand side is the
sum of the C and D decision

49
00:02:53,050 --> 00:02:59,480
variables, the sign is equals,
and the right hand side is 2.

50
00:02:59,480 --> 00:03:02,100
Now we want to add the
constraint that teams A

51
00:03:02,100 --> 00:03:07,350
and C should play once, so this
left hand side is very similar.

52
00:03:07,350 --> 00:03:12,360
It should be the sum of the
A and C decision variables,

53
00:03:12,360 --> 00:03:15,390
the sign should be equals,
and the right hand side here

54
00:03:15,390 --> 00:03:17,490
should be 1.

55
00:03:17,490 --> 00:03:20,550
Now let's repeat this
for teams A and D.

56
00:03:20,550 --> 00:03:25,650
The left hand side is the sum of
the A and D decision variables,

57
00:03:25,650 --> 00:03:29,660
the sign is equals, and
the right hand side is 1.

58
00:03:29,660 --> 00:03:32,600
We'll repeat it again
for teams B and C.

59
00:03:32,600 --> 00:03:36,150
So the left hand side is the
sum of the B and C decision

60
00:03:36,150 --> 00:03:41,440
variables, the sign is equals,
and the right hand side is 1.

61
00:03:41,440 --> 00:03:43,410
The last of this
type of constraint

62
00:03:43,410 --> 00:03:46,760
is that teams B and
D should play once,

63
00:03:46,760 --> 00:03:50,430
so the left hand side is the
sum of the B and D decision

64
00:03:50,430 --> 00:03:56,170
variables, the sign is equals,
and the right hand side is 1.

65
00:03:56,170 --> 00:03:57,880
Now we want to add
the constraints

66
00:03:57,880 --> 00:04:01,500
that each team should only
play once in each week.

67
00:04:01,500 --> 00:04:05,780
So the first is that team A
should play once in week one.

68
00:04:05,780 --> 00:04:09,740
So the left hand side should be
the sum of all of the decision

69
00:04:09,740 --> 00:04:12,800
variables where A
plays in week one.

70
00:04:12,800 --> 00:04:17,540
So this should be equal to
A playing B in week one,

71
00:04:17,540 --> 00:04:24,310
plus A playing C in week one,
plus A playing D in week one.

72
00:04:24,310 --> 00:04:28,770
The sign is equals, and the
right hand side is again 1.

73
00:04:28,770 --> 00:04:30,900
Now let's repeat
this for week two.

74
00:04:30,900 --> 00:04:34,720
So the left hand side is equal
to A playing B in week two,

75
00:04:34,720 --> 00:04:41,310
plus A playing C in week two,
plus A playing D in week two.

76
00:04:41,310 --> 00:04:45,360
The sign is equals and
the right hand side is 1.

77
00:04:45,360 --> 00:04:47,930
Now let's repeat this
for weeks three and four.

78
00:04:47,930 --> 00:04:50,870
So for week three,
the left hand side

79
00:04:50,870 --> 00:04:56,270
is just equal to the variables
of A playing with week three:

80
00:04:56,270 --> 00:05:01,920
A and B, A and C, A and
D. The sign is equals

81
00:05:01,920 --> 00:05:04,400
and the right hand side is 1.

82
00:05:04,400 --> 00:05:07,010
For week four,
the left hand side

83
00:05:07,010 --> 00:05:12,690
is just the A variables in
week four, A and B, A and C,

84
00:05:12,690 --> 00:05:16,330
and A and D. The
sign should be equals

85
00:05:16,330 --> 00:05:19,010
and the right hand side is 1.

86
00:05:19,010 --> 00:05:22,400
I went ahead and filled in the
rest of the constraints for you

87
00:05:22,400 --> 00:05:26,390
since they're just repeating
the same thing for team B,

88
00:05:26,390 --> 00:05:30,000
team C, and team D.

89
00:05:30,000 --> 00:05:32,600
Now we're ready to
solve our problem.

90
00:05:32,600 --> 00:05:38,070
So let's go to the "Tools"
menu and select "Solver".

91
00:05:38,070 --> 00:05:42,010
In the Solver window, let's
first pick our target cell.

92
00:05:42,010 --> 00:05:45,550
So with the cursor in
the "Target cell" box,

93
00:05:45,550 --> 00:05:47,760
go ahead and select
the objective cell,

94
00:05:47,760 --> 00:05:49,600
or the blue cell.

95
00:05:49,600 --> 00:05:51,820
Make sure that
"Maximum" is selected,

96
00:05:51,820 --> 00:05:55,250
since we're trying to
maximize preferences.

97
00:05:55,250 --> 00:05:59,100
Then, with the cursor in
the "By changing cells" box,

98
00:05:59,100 --> 00:06:04,190
go ahead and select all
24 decision variables.

99
00:06:04,190 --> 00:06:06,650
Now, let's add in
our constraints.

100
00:06:06,650 --> 00:06:09,630
Since all of our constraints
have an equals sign,

101
00:06:09,630 --> 00:06:12,100
we can add them all in together.

102
00:06:12,100 --> 00:06:14,740
So in the first
Cell Reference box,

103
00:06:14,740 --> 00:06:17,380
go ahead and select all
of the left hand sides.

104
00:06:23,560 --> 00:06:28,150
In the Operator
box, select equals.

105
00:06:28,150 --> 00:06:32,000
Then, in the Value box, select
all of the right hand sides.

106
00:06:40,590 --> 00:06:43,290
Since this is an integer
optimization problem,

107
00:06:43,290 --> 00:06:46,710
there's one more thing we need
to do in the constraint area.

108
00:06:46,710 --> 00:06:49,300
In the Cell Reference
box, go ahead

109
00:06:49,300 --> 00:06:51,440
and select all of the
decision variables.

110
00:06:55,530 --> 00:06:59,720
Then, in the operator pull
down menu, select Binary.

111
00:06:59,720 --> 00:07:02,850
This will make all of our
decision variables binary.

112
00:07:02,850 --> 00:07:06,420
We don't need to put
anything in the Value column.

113
00:07:06,420 --> 00:07:09,460
The last thing we need
to do is in Options,

114
00:07:09,460 --> 00:07:14,640
make sure that we're using the
linear solver, and click OK.

115
00:07:14,640 --> 00:07:18,000
Now, go ahead and hit Solve.

116
00:07:18,000 --> 00:07:20,140
The solving results says:
solving successfully

117
00:07:20,140 --> 00:07:22,400
finished, result 24.

118
00:07:22,400 --> 00:07:25,130
Go ahead and pick Keep
Result, and now let's

119
00:07:25,130 --> 00:07:26,900
look at our solution.

120
00:07:26,900 --> 00:07:31,820
We can see that teams A
and B and teams C and D

121
00:07:31,820 --> 00:07:34,800
both play during weeks 3 and 4.

122
00:07:34,800 --> 00:07:38,860
This makes sense, since we're
trying to maximize preferences,

123
00:07:38,860 --> 00:07:41,060
and the preference for
teams in the same division

124
00:07:41,060 --> 00:07:44,000
is to play later on.

125
00:07:44,000 --> 00:07:47,550
In the next video, we'll see the
different types of constraints

126
00:07:47,550 --> 00:07:51,700
that we can add to an
integer optimization model.