1
00:00:04,720 --> 00:00:07,190
Here we are in
LibreOffice, and you'll

2
00:00:07,190 --> 00:00:09,350
see that we have all
the data we discussed

3
00:00:09,350 --> 00:00:13,450
in the previous videos loaded
into the spreadsheet here.

4
00:00:13,450 --> 00:00:15,710
Let's take a minute to
familiarize ourselves

5
00:00:15,710 --> 00:00:18,240
with how the data is set up.

6
00:00:18,240 --> 00:00:21,250
So here we see we've got
the number of surgical teams

7
00:00:21,250 --> 00:00:24,260
per department
per day, and we've

8
00:00:24,260 --> 00:00:28,140
got it listed by department--
all five of them are here--

9
00:00:28,140 --> 00:00:30,820
and by day of the week-- Monday,
Tuesday, Wednesday, Thursday,

10
00:00:30,820 --> 00:00:32,670
and Friday.

11
00:00:32,670 --> 00:00:34,050
Remember, for all
departments, we

12
00:00:34,050 --> 00:00:37,100
have the same number
of teams in per day,

13
00:00:37,100 --> 00:00:39,690
except for oral surgery,
because the oral surgeon is

14
00:00:39,690 --> 00:00:41,850
only present on
Tuesdays and Thursdays.

15
00:00:44,760 --> 00:00:48,030
Scrolling across, we see that
for all five departments,

16
00:00:48,030 --> 00:00:50,650
again, we've got the minimum
number of operating rooms

17
00:00:50,650 --> 00:00:53,630
that each one requires each day.

18
00:00:53,630 --> 00:00:56,470
This is simply 0.

19
00:00:56,470 --> 00:00:58,670
As we keep going, we
see the maximum number

20
00:00:58,670 --> 00:01:01,260
of operating rooms that each
department requires each day.

21
00:01:04,260 --> 00:01:06,580
Going down to the
next set of data,

22
00:01:06,580 --> 00:01:09,620
we've got the weekly
department requirements.

23
00:01:09,620 --> 00:01:12,039
We've got the minimum
and maximum number

24
00:01:12,039 --> 00:01:16,570
of operating rooms that each
department requires each week.

25
00:01:16,570 --> 00:01:19,120
We also have the weekly targets.

26
00:01:19,120 --> 00:01:22,140
This is data, because it
is set by the departments

27
00:01:22,140 --> 00:01:24,650
before the operating
room manager has a chance

28
00:01:24,650 --> 00:01:28,210
to schedule the operating rooms.

29
00:01:28,210 --> 00:01:30,260
So this is our problem
data, and now let's

30
00:01:30,260 --> 00:01:31,680
go down to our
decision variables.

31
00:01:36,259 --> 00:01:38,180
All right, so here are
our decision variables.

32
00:01:40,900 --> 00:01:42,810
You see that we've set
up a decision variable

33
00:01:42,810 --> 00:01:45,770
for each department, and
for each day of the week.

34
00:01:45,770 --> 00:01:48,200
Right now, they're
simply set to 0,

35
00:01:48,200 --> 00:01:52,729
but we've colored them yellow,
so they'll be easy to see.

36
00:01:52,729 --> 00:01:55,920
Let's now set up a
column for weekly totals.

37
00:01:55,920 --> 00:01:56,759
Let's put that here.

38
00:02:01,250 --> 00:02:01,820
All right.

39
00:02:05,820 --> 00:02:07,360
So let's fill this in.

40
00:02:07,360 --> 00:02:09,509
The weekly total
for ophthalmology

41
00:02:09,509 --> 00:02:13,610
should just be the sum of all
of the number of operating

42
00:02:13,610 --> 00:02:16,190
rooms assigned to ophthalmology
from Monday through Friday,

43
00:02:16,190 --> 00:02:17,820
so that's this entire row.

44
00:02:22,190 --> 00:02:23,740
Just like that.

45
00:02:23,740 --> 00:02:26,950
And we can drag this formula
down for every department.

46
00:02:32,110 --> 00:02:33,930
OK.

47
00:02:33,930 --> 00:02:36,940
So now, if you actually go
through each one of these,

48
00:02:36,940 --> 00:02:39,450
you can see up in
the Formula tab

49
00:02:39,450 --> 00:02:44,170
that we've got the formula
for the weekly totals here.

50
00:02:44,170 --> 00:02:46,750
So let's get started by
putting in our objectives

51
00:02:46,750 --> 00:02:49,000
and our constraints.

52
00:02:49,000 --> 00:02:52,560
So down here, I've set up
a box for the objective,

53
00:02:52,560 --> 00:02:55,810
but I haven't yet put
in a formula for it.

54
00:02:55,810 --> 00:03:00,230
So let's remind ourselves
what the objective is here.

55
00:03:00,230 --> 00:03:04,170
The objective is to maximize
the percent of target allocation

56
00:03:04,170 --> 00:03:09,040
hours that each department
is actually allocated.

57
00:03:09,040 --> 00:03:11,940
So how can we calculate that?

58
00:03:11,940 --> 00:03:15,830
Our decision variables up
here represent the number

59
00:03:15,830 --> 00:03:18,040
of operating room hours
that each department

60
00:03:18,040 --> 00:03:21,850
is assigned on each day.

61
00:03:21,850 --> 00:03:24,290
We calculated the
total over here,

62
00:03:24,290 --> 00:03:26,410
as the number of operating
rooms that a department

63
00:03:26,410 --> 00:03:29,980
is assigned over the
course of the week.

64
00:03:29,980 --> 00:03:32,680
To turn that into hours, we
need to multiply it by 8,

65
00:03:32,680 --> 00:03:36,200
because operating rooms are
staffed for eight hours.

66
00:03:36,200 --> 00:03:39,480
So let's create a column
that turns this into hours.

67
00:03:39,480 --> 00:03:42,680
We'll put it over here, and
we'll call it weekly hours.

68
00:03:46,710 --> 00:03:48,680
OK?

69
00:03:48,680 --> 00:03:53,450
And this will just be equal
to 8 times the weekly totals.

70
00:03:57,630 --> 00:03:58,590
Great.

71
00:03:58,590 --> 00:04:00,740
And we'll do this
for all departments.

72
00:04:00,740 --> 00:04:02,540
Here, this time I'll
actually write it out.

73
00:04:09,800 --> 00:04:10,300
OK.

74
00:04:22,870 --> 00:04:25,000
Great.

75
00:04:25,000 --> 00:04:27,090
So now we've got the
weekly hours here,

76
00:04:27,090 --> 00:04:30,500
but to calculate what
fraction of the weekly target,

77
00:04:30,500 --> 00:04:33,370
we've got to divide it by
the total weekly target.

78
00:04:33,370 --> 00:04:36,140
And the total weekly
target, we see, is up here.

79
00:04:38,690 --> 00:04:43,140
So let's set this up for
every department in a column

80
00:04:43,140 --> 00:04:45,520
called "Percent of Target".

81
00:04:53,550 --> 00:04:58,820
OK, so this will just be equal
to the weekly number of hours,

82
00:04:58,820 --> 00:05:02,400
divided by the weekly target.

83
00:05:05,630 --> 00:05:08,600
OK, so we'll just set that
up for each department.

84
00:05:25,560 --> 00:05:27,740
Certainly there are
faster ways of doing this,

85
00:05:27,740 --> 00:05:31,550
but I don't want to speed
ahead if you guys aren't yet

86
00:05:31,550 --> 00:05:32,590
LibreOffice wizards.

87
00:05:46,030 --> 00:05:46,530
OK.

88
00:05:50,340 --> 00:05:51,800
All right, so we've
got the percent

89
00:05:51,800 --> 00:05:54,380
of target reached
for every department.

90
00:05:54,380 --> 00:05:58,020
So our objective-- we
can go back to it now--

91
00:05:58,020 --> 00:06:02,050
it'll just be the sum of all
of these percent of targets.

92
00:06:02,050 --> 00:06:06,430
So let's add that
in-- equals sum,

93
00:06:06,430 --> 00:06:10,720
and then we'll add in all
of these percent of targets.

94
00:06:13,690 --> 00:06:14,250
Great.

95
00:06:14,250 --> 00:06:16,710
So right now, of course, we
haven't solved the problem yet,

96
00:06:16,710 --> 00:06:20,080
so we've just got
0 in our objective.

97
00:06:20,080 --> 00:06:21,990
But before we go
ahead and solve it,

98
00:06:21,990 --> 00:06:24,650
we need to add in
our constraints.

99
00:06:24,650 --> 00:06:27,840
So down here in
green, we have a list

100
00:06:27,840 --> 00:06:29,480
of all the constraints
we outlined

101
00:06:29,480 --> 00:06:31,700
in the previous videos.

102
00:06:31,700 --> 00:06:34,990
Let's review them quickly.

103
00:06:34,990 --> 00:06:37,120
So the first one is
just that the number

104
00:06:37,120 --> 00:06:40,880
of operating rooms assigned
each day is integer.

105
00:06:40,880 --> 00:06:43,320
We can't assign
ophthalmology 3/4

106
00:06:43,320 --> 00:06:45,300
of an operating
room on Thursday,

107
00:06:45,300 --> 00:06:49,740
or general surgery 6.5
operating rooms on Monday.

108
00:06:49,740 --> 00:06:53,450
We need to make sure that
these take integer values.

109
00:06:53,450 --> 00:06:56,060
The next constraint is
just that the total number

110
00:06:56,060 --> 00:07:01,130
of operating rooms each day has
to be less than or equal to 10.

111
00:07:01,130 --> 00:07:02,790
So while we're
here, let's just add

112
00:07:02,790 --> 00:07:04,960
in a line underneath
our decision variables,

113
00:07:04,960 --> 00:07:06,980
which calculates the
total number of operating

114
00:07:06,980 --> 00:07:08,990
rooms each day.

115
00:07:08,990 --> 00:07:11,470
Over here we'll
call it total number

116
00:07:11,470 --> 00:07:16,280
of operating rooms each
day, and this will just take

117
00:07:16,280 --> 00:07:22,670
the sum over all the operating
rooms we assign on Monday.

118
00:07:22,670 --> 00:07:25,540
Just like that.

119
00:07:25,540 --> 00:07:27,410
Great.

120
00:07:27,410 --> 00:07:29,920
And let's drag
this formula across

121
00:07:29,920 --> 00:07:31,460
to use it for the entire week.

122
00:07:38,960 --> 00:07:40,570
OK, so now we've
got that set up,

123
00:07:40,570 --> 00:07:44,510
so we'll be able to do
the second constraint.

124
00:07:44,510 --> 00:07:48,200
The third set of constraints
is the upper bound--

125
00:07:48,200 --> 00:07:50,740
that is, the upper bound on
the number of surgical teams

126
00:07:50,740 --> 00:07:54,190
that are available each day.

127
00:07:54,190 --> 00:07:57,080
We also have an upper bound on
the daily department operating

128
00:07:57,080 --> 00:08:00,520
room requirement, and a lower
bound on the daily department

129
00:08:00,520 --> 00:08:03,180
operating room requirement.

130
00:08:03,180 --> 00:08:06,320
Similarly, we have an upper
bound and a lower bound

131
00:08:06,320 --> 00:08:08,540
on the weekly department
operating room requirements.

132
00:08:11,140 --> 00:08:13,140
Our last set of
constraints has to do

133
00:08:13,140 --> 00:08:15,560
with departmental targets.

134
00:08:15,560 --> 00:08:18,140
We want to make sure we don't
give any department more

135
00:08:18,140 --> 00:08:21,570
operating room hours
than they actually want.

136
00:08:21,570 --> 00:08:23,090
So that means we
want to make sure

137
00:08:23,090 --> 00:08:25,760
that the number of hours
given to each department

138
00:08:25,760 --> 00:08:27,660
is less than or equal
to the targeted number

139
00:08:27,660 --> 00:08:30,340
that they asked for.

140
00:08:30,340 --> 00:08:33,190
All right, so let's go ahead
and add these constraints

141
00:08:33,190 --> 00:08:35,070
into the Solver.

142
00:08:35,070 --> 00:08:37,730
So we'll start by
putting our cursor

143
00:08:37,730 --> 00:08:42,450
on the objective function, and
going up to the Tools menu.

144
00:08:42,450 --> 00:08:43,690
And here we select Solver.

145
00:08:46,650 --> 00:08:49,260
OK.

146
00:08:49,260 --> 00:08:52,830
So because we started our cursor
on the objective function cell,

147
00:08:52,830 --> 00:08:56,750
it comes up properly with the
"Target cell" already labeled.

148
00:08:56,750 --> 00:09:01,110
We do want a maximum, so
we keep that the way it is.

149
00:09:01,110 --> 00:09:03,720
And then here, where it
says "By changing cells",

150
00:09:03,720 --> 00:09:06,120
we put in our
decision variables.

151
00:09:06,120 --> 00:09:09,360
So we just select
them all, like this.

152
00:09:09,360 --> 00:09:10,530
All right.

153
00:09:10,530 --> 00:09:12,570
Now, before we start
putting in our constraints,

154
00:09:12,570 --> 00:09:16,910
I want to remind you
to go into Options,

155
00:09:16,910 --> 00:09:20,380
and you'll need to change
this from DEPS Evolutionary

156
00:09:20,380 --> 00:09:25,760
Algorithm to LibreOffice
Linear Solver.

157
00:09:25,760 --> 00:09:28,300
OK.

158
00:09:28,300 --> 00:09:29,850
So you notice that
the settings--

159
00:09:29,850 --> 00:09:33,700
you can choose to assume
variables are integer,

160
00:09:33,700 --> 00:09:36,780
and assume variables
are non-negative.

161
00:09:36,780 --> 00:09:38,640
We'll do that here.

162
00:09:38,640 --> 00:09:41,510
In fact, this takes care of
our first constraint, which

163
00:09:41,510 --> 00:09:43,770
was to assume that the
variables are integer.

164
00:09:47,510 --> 00:09:49,770
OK.

165
00:09:49,770 --> 00:09:51,430
So we've got this set up.

166
00:09:51,430 --> 00:09:55,410
Now let's put in the
rest of our constraints.

167
00:09:55,410 --> 00:09:56,900
OK.

168
00:09:56,900 --> 00:09:59,260
So we've already taken care
of this first constraint,

169
00:09:59,260 --> 00:10:01,430
that the daily number of
operating rooms assigned

170
00:10:01,430 --> 00:10:05,080
is integer, and we've taken
care of that with our options.

171
00:10:05,080 --> 00:10:06,500
So we'll go down
to the next one--

172
00:10:06,500 --> 00:10:10,850
the total number of
operating rooms each day.

173
00:10:10,850 --> 00:10:14,960
Remember that we can't
assign more than 10,

174
00:10:14,960 --> 00:10:20,160
so we'll select this
whole row, which

175
00:10:20,160 --> 00:10:22,190
has the total number of
operating rooms assigned

176
00:10:22,190 --> 00:10:26,460
each day, and we'll say that
each element in this row

177
00:10:26,460 --> 00:10:30,470
has to be less than
or equal to 10.

178
00:10:30,470 --> 00:10:32,360
Great.

179
00:10:32,360 --> 00:10:35,610
So on to the upper bound
of surgical teams per day.

180
00:10:35,610 --> 00:10:41,090
Let's select our decision
variables, all of them at once.

181
00:10:41,090 --> 00:10:41,590
OK.

182
00:10:45,160 --> 00:10:47,040
And then we'll need to
scroll up to our data.

183
00:10:50,540 --> 00:10:51,040
OK.

184
00:10:53,960 --> 00:10:56,380
You'll notice here how I'm
inputting all these constraints

185
00:10:56,380 --> 00:10:56,970
all at once.

186
00:10:56,970 --> 00:11:00,710
We're actually putting in 25
different constraints, one

187
00:11:00,710 --> 00:11:03,350
for each day of the week, and
one for each surgical team.

188
00:11:03,350 --> 00:11:05,440
And we're just putting
them in with a single line,

189
00:11:05,440 --> 00:11:08,620
by saying that all our decision
variables need to be less than

190
00:11:08,620 --> 00:11:11,280
or equal to this
box of constraints.

191
00:11:14,200 --> 00:11:14,890
Just like that.

192
00:11:18,240 --> 00:11:20,640
OK.

193
00:11:20,640 --> 00:11:22,520
So our next set
of constraints is

194
00:11:22,520 --> 00:11:25,400
on the upper bound and lower
bound of the daily department

195
00:11:25,400 --> 00:11:26,810
OR requirements.

196
00:11:26,810 --> 00:11:29,680
So we'll put in our
decision variables again.

197
00:11:29,680 --> 00:11:30,700
Scroll down to those.

198
00:11:37,920 --> 00:11:40,560
And let's say that
these are less than

199
00:11:40,560 --> 00:11:45,000
or equal to--
sometimes we've got

200
00:11:45,000 --> 00:11:48,160
to move the solver around--
the maximum number of ORs

201
00:11:48,160 --> 00:11:49,330
per day per department.

202
00:11:52,390 --> 00:11:53,530
OK.

203
00:11:53,530 --> 00:11:56,150
And we'll do that again
for the lower-bound.

204
00:12:04,880 --> 00:12:07,410
This time we need to change
the operator to greater than.

205
00:12:14,900 --> 00:12:26,230
Greater than the minimum
number of operating rooms

206
00:12:26,230 --> 00:12:27,520
per department per day.

207
00:12:31,540 --> 00:12:34,290
You see here that we've got
0's in for the minimum number

208
00:12:34,290 --> 00:12:37,880
of operating rooms per day,
but you could conceivably

209
00:12:37,880 --> 00:12:39,690
expect that one
department might actually

210
00:12:39,690 --> 00:12:42,820
want a minimum number of
operating rooms per day.

211
00:12:42,820 --> 00:12:44,800
For example, general
surgery, which

212
00:12:44,800 --> 00:12:49,090
we see here has a weekly
target of 189 hours,

213
00:12:49,090 --> 00:12:53,360
might actually want to have more
than 0 operating rooms per day.

214
00:12:53,360 --> 00:12:57,200
They might want to guarantee
they've got at least one.

215
00:12:57,200 --> 00:12:59,820
We'll experiment with
changing some of these numbers

216
00:12:59,820 --> 00:13:02,090
after we solve the
initial problem.

217
00:13:02,090 --> 00:13:05,280
For now, let's get our
remaining constraints in.

218
00:13:05,280 --> 00:13:08,040
The next ones we have
are the weekly department

219
00:13:08,040 --> 00:13:09,820
OR requirements--
the upper-bound

220
00:13:09,820 --> 00:13:12,170
and the lower-bound.

221
00:13:12,170 --> 00:13:14,110
So let's just scroll
down here in the solver

222
00:13:14,110 --> 00:13:15,530
to give ourselves
a few more rows.

223
00:13:20,090 --> 00:13:22,000
OK.

224
00:13:22,000 --> 00:13:25,580
So here we'll put in
our weekly totals.

225
00:13:25,580 --> 00:13:28,420
Good thing we've
already got this set up.

226
00:13:28,420 --> 00:13:33,060
OK, so weekly totals--
this will have

227
00:13:33,060 --> 00:13:35,860
to be less than the maximum
required by any department

228
00:13:35,860 --> 00:13:38,090
in a given week.

229
00:13:38,090 --> 00:13:42,410
So in the Value column,
we go over here,

230
00:13:42,410 --> 00:13:45,310
and we put in the maximum
required each week.

231
00:13:48,620 --> 00:13:53,740
We'll do the same
for the lower-bound,

232
00:13:53,740 --> 00:13:56,480
again, changing the operator
to a greater-than or equal-to

233
00:13:56,480 --> 00:13:56,980
sign.

234
00:14:05,140 --> 00:14:06,500
OK.

235
00:14:06,500 --> 00:14:12,400
And we're on to our very last
set of constraints, which

236
00:14:12,400 --> 00:14:18,520
is the departmental
targets down here.

237
00:14:18,520 --> 00:14:21,120
So remember, we can't assign a
department more hours than they

238
00:14:21,120 --> 00:14:23,840
actually wanted to have,
because operating-room time is

239
00:14:23,840 --> 00:14:26,050
so expensive.

240
00:14:26,050 --> 00:14:27,850
So we'll select there
the weekly number

241
00:14:27,850 --> 00:14:33,300
of hours, which we already have
a column set up to calculate.

242
00:14:33,300 --> 00:14:37,760
So we'll put this in here that
the weekly number of hours

243
00:14:37,760 --> 00:14:42,180
must be less than or equal to
the target allocation hours.

244
00:14:42,180 --> 00:14:48,160
So scroll back up here to
the target allocation hours.

245
00:14:53,710 --> 00:14:54,970
OK, great.

246
00:14:54,970 --> 00:14:56,690
Now we've got all
our constraints in,

247
00:14:56,690 --> 00:14:58,720
and we're ready to go.

248
00:14:58,720 --> 00:14:59,570
So let's hit Solve.

249
00:15:03,690 --> 00:15:10,510
It may take a few
seconds-- up to 100.

250
00:15:10,510 --> 00:15:13,780
In the next video, we'll look
at the results that we get,

251
00:15:13,780 --> 00:15:15,120
and interpret them.

252
00:15:15,120 --> 00:15:18,290
We'll also see how they change,
if we experiment with changing

253
00:15:18,290 --> 00:15:20,880
some of the numbers
in the problem.