WEBVTT

00:00:04.720 --> 00:00:07.190
Here we are in
LibreOffice, and you'll

00:00:07.190 --> 00:00:09.350
see that we have all
the data we discussed

00:00:09.350 --> 00:00:13.450
in the previous videos loaded
into the spreadsheet here.

00:00:13.450 --> 00:00:15.710
Let's take a minute to
familiarize ourselves

00:00:15.710 --> 00:00:18.240
with how the data is set up.

00:00:18.240 --> 00:00:21.250
So here we see we've got
the number of surgical teams

00:00:21.250 --> 00:00:24.260
per department
per day, and we've

00:00:24.260 --> 00:00:28.140
got it listed by department--
all five of them are here--

00:00:28.140 --> 00:00:30.820
and by day of the week-- Monday,
Tuesday, Wednesday, Thursday,

00:00:30.820 --> 00:00:32.670
and Friday.

00:00:32.670 --> 00:00:34.050
Remember, for all
departments, we

00:00:34.050 --> 00:00:37.100
have the same number
of teams in per day,

00:00:37.100 --> 00:00:39.690
except for oral surgery,
because the oral surgeon is

00:00:39.690 --> 00:00:41.850
only present on
Tuesdays and Thursdays.

00:00:44.760 --> 00:00:48.030
Scrolling across, we see that
for all five departments,

00:00:48.030 --> 00:00:50.650
again, we've got the minimum
number of operating rooms

00:00:50.650 --> 00:00:53.630
that each one requires each day.

00:00:53.630 --> 00:00:56.470
This is simply 0.

00:00:56.470 --> 00:00:58.670
As we keep going, we
see the maximum number

00:00:58.670 --> 00:01:01.260
of operating rooms that each
department requires each day.

00:01:04.260 --> 00:01:06.580
Going down to the
next set of data,

00:01:06.580 --> 00:01:09.620
we've got the weekly
department requirements.

00:01:09.620 --> 00:01:12.039
We've got the minimum
and maximum number

00:01:12.039 --> 00:01:16.570
of operating rooms that each
department requires each week.

00:01:16.570 --> 00:01:19.120
We also have the weekly targets.

00:01:19.120 --> 00:01:22.140
This is data, because it
is set by the departments

00:01:22.140 --> 00:01:24.650
before the operating
room manager has a chance

00:01:24.650 --> 00:01:28.210
to schedule the operating rooms.

00:01:28.210 --> 00:01:30.260
So this is our problem
data, and now let's

00:01:30.260 --> 00:01:31.680
go down to our
decision variables.

00:01:36.259 --> 00:01:38.180
All right, so here are
our decision variables.

00:01:40.900 --> 00:01:42.810
You see that we've set
up a decision variable

00:01:42.810 --> 00:01:45.770
for each department, and
for each day of the week.

00:01:45.770 --> 00:01:48.200
Right now, they're
simply set to 0,

00:01:48.200 --> 00:01:52.729
but we've colored them yellow,
so they'll be easy to see.

00:01:52.729 --> 00:01:55.920
Let's now set up a
column for weekly totals.

00:01:55.920 --> 00:01:56.759
Let's put that here.

00:02:01.250 --> 00:02:01.820
All right.

00:02:05.820 --> 00:02:07.360
So let's fill this in.

00:02:07.360 --> 00:02:09.509
The weekly total
for ophthalmology

00:02:09.509 --> 00:02:13.610
should just be the sum of all
of the number of operating

00:02:13.610 --> 00:02:16.190
rooms assigned to ophthalmology
from Monday through Friday,

00:02:16.190 --> 00:02:17.820
so that's this entire row.

00:02:22.190 --> 00:02:23.740
Just like that.

00:02:23.740 --> 00:02:26.950
And we can drag this formula
down for every department.

00:02:32.110 --> 00:02:33.930
OK.

00:02:33.930 --> 00:02:36.940
So now, if you actually go
through each one of these,

00:02:36.940 --> 00:02:39.450
you can see up in
the Formula tab

00:02:39.450 --> 00:02:44.170
that we've got the formula
for the weekly totals here.

00:02:44.170 --> 00:02:46.750
So let's get started by
putting in our objectives

00:02:46.750 --> 00:02:49.000
and our constraints.

00:02:49.000 --> 00:02:52.560
So down here, I've set up
a box for the objective,

00:02:52.560 --> 00:02:55.810
but I haven't yet put
in a formula for it.

00:02:55.810 --> 00:03:00.230
So let's remind ourselves
what the objective is here.

00:03:00.230 --> 00:03:04.170
The objective is to maximize
the percent of target allocation

00:03:04.170 --> 00:03:09.040
hours that each department
is actually allocated.

00:03:09.040 --> 00:03:11.940
So how can we calculate that?

00:03:11.940 --> 00:03:15.830
Our decision variables up
here represent the number

00:03:15.830 --> 00:03:18.040
of operating room hours
that each department

00:03:18.040 --> 00:03:21.850
is assigned on each day.

00:03:21.850 --> 00:03:24.290
We calculated the
total over here,

00:03:24.290 --> 00:03:26.410
as the number of operating
rooms that a department

00:03:26.410 --> 00:03:29.980
is assigned over the
course of the week.

00:03:29.980 --> 00:03:32.680
To turn that into hours, we
need to multiply it by 8,

00:03:32.680 --> 00:03:36.200
because operating rooms are
staffed for eight hours.

00:03:36.200 --> 00:03:39.480
So let's create a column
that turns this into hours.

00:03:39.480 --> 00:03:42.680
We'll put it over here, and
we'll call it weekly hours.

00:03:46.710 --> 00:03:48.680
OK?

00:03:48.680 --> 00:03:53.450
And this will just be equal
to 8 times the weekly totals.

00:03:57.630 --> 00:03:58.590
Great.

00:03:58.590 --> 00:04:00.740
And we'll do this
for all departments.

00:04:00.740 --> 00:04:02.540
Here, this time I'll
actually write it out.

00:04:09.800 --> 00:04:10.300
OK.

00:04:22.870 --> 00:04:25.000
Great.

00:04:25.000 --> 00:04:27.090
So now we've got the
weekly hours here,

00:04:27.090 --> 00:04:30.500
but to calculate what
fraction of the weekly target,

00:04:30.500 --> 00:04:33.370
we've got to divide it by
the total weekly target.

00:04:33.370 --> 00:04:36.140
And the total weekly
target, we see, is up here.

00:04:38.690 --> 00:04:43.140
So let's set this up for
every department in a column

00:04:43.140 --> 00:04:45.520
called "Percent of Target".

00:04:53.550 --> 00:04:58.820
OK, so this will just be equal
to the weekly number of hours,

00:04:58.820 --> 00:05:02.400
divided by the weekly target.

00:05:05.630 --> 00:05:08.600
OK, so we'll just set that
up for each department.

00:05:25.560 --> 00:05:27.740
Certainly there are
faster ways of doing this,

00:05:27.740 --> 00:05:31.550
but I don't want to speed
ahead if you guys aren't yet

00:05:31.550 --> 00:05:32.590
LibreOffice wizards.

00:05:46.030 --> 00:05:46.530
OK.

00:05:50.340 --> 00:05:51.800
All right, so we've
got the percent

00:05:51.800 --> 00:05:54.380
of target reached
for every department.

00:05:54.380 --> 00:05:58.020
So our objective-- we
can go back to it now--

00:05:58.020 --> 00:06:02.050
it'll just be the sum of all
of these percent of targets.

00:06:02.050 --> 00:06:06.430
So let's add that
in-- equals sum,

00:06:06.430 --> 00:06:10.720
and then we'll add in all
of these percent of targets.

00:06:13.690 --> 00:06:14.250
Great.

00:06:14.250 --> 00:06:16.710
So right now, of course, we
haven't solved the problem yet,

00:06:16.710 --> 00:06:20.080
so we've just got
0 in our objective.

00:06:20.080 --> 00:06:21.990
But before we go
ahead and solve it,

00:06:21.990 --> 00:06:24.650
we need to add in
our constraints.

00:06:24.650 --> 00:06:27.840
So down here in
green, we have a list

00:06:27.840 --> 00:06:29.480
of all the constraints
we outlined

00:06:29.480 --> 00:06:31.700
in the previous videos.

00:06:31.700 --> 00:06:34.990
Let's review them quickly.

00:06:34.990 --> 00:06:37.120
So the first one is
just that the number

00:06:37.120 --> 00:06:40.880
of operating rooms assigned
each day is integer.

00:06:40.880 --> 00:06:43.320
We can't assign
ophthalmology 3/4

00:06:43.320 --> 00:06:45.300
of an operating
room on Thursday,

00:06:45.300 --> 00:06:49.740
or general surgery 6.5
operating rooms on Monday.

00:06:49.740 --> 00:06:53.450
We need to make sure that
these take integer values.

00:06:53.450 --> 00:06:56.060
The next constraint is
just that the total number

00:06:56.060 --> 00:07:01.130
of operating rooms each day has
to be less than or equal to 10.

00:07:01.130 --> 00:07:02.790
So while we're
here, let's just add

00:07:02.790 --> 00:07:04.960
in a line underneath
our decision variables,

00:07:04.960 --> 00:07:06.980
which calculates the
total number of operating

00:07:06.980 --> 00:07:08.990
rooms each day.

00:07:08.990 --> 00:07:11.470
Over here we'll
call it total number

00:07:11.470 --> 00:07:16.280
of operating rooms each
day, and this will just take

00:07:16.280 --> 00:07:22.670
the sum over all the operating
rooms we assign on Monday.

00:07:22.670 --> 00:07:25.540
Just like that.

00:07:25.540 --> 00:07:27.410
Great.

00:07:27.410 --> 00:07:29.920
And let's drag
this formula across

00:07:29.920 --> 00:07:31.460
to use it for the entire week.

00:07:38.960 --> 00:07:40.570
OK, so now we've
got that set up,

00:07:40.570 --> 00:07:44.510
so we'll be able to do
the second constraint.

00:07:44.510 --> 00:07:48.200
The third set of constraints
is the upper bound--

00:07:48.200 --> 00:07:50.740
that is, the upper bound on
the number of surgical teams

00:07:50.740 --> 00:07:54.190
that are available each day.

00:07:54.190 --> 00:07:57.080
We also have an upper bound on
the daily department operating

00:07:57.080 --> 00:08:00.520
room requirement, and a lower
bound on the daily department

00:08:00.520 --> 00:08:03.180
operating room requirement.

00:08:03.180 --> 00:08:06.320
Similarly, we have an upper
bound and a lower bound

00:08:06.320 --> 00:08:08.540
on the weekly department
operating room requirements.

00:08:11.140 --> 00:08:13.140
Our last set of
constraints has to do

00:08:13.140 --> 00:08:15.560
with departmental targets.

00:08:15.560 --> 00:08:18.140
We want to make sure we don't
give any department more

00:08:18.140 --> 00:08:21.570
operating room hours
than they actually want.

00:08:21.570 --> 00:08:23.090
So that means we
want to make sure

00:08:23.090 --> 00:08:25.760
that the number of hours
given to each department

00:08:25.760 --> 00:08:27.660
is less than or equal
to the targeted number

00:08:27.660 --> 00:08:30.340
that they asked for.

00:08:30.340 --> 00:08:33.190
All right, so let's go ahead
and add these constraints

00:08:33.190 --> 00:08:35.070
into the Solver.

00:08:35.070 --> 00:08:37.730
So we'll start by
putting our cursor

00:08:37.730 --> 00:08:42.450
on the objective function, and
going up to the Tools menu.

00:08:42.450 --> 00:08:43.690
And here we select Solver.

00:08:46.650 --> 00:08:49.260
OK.

00:08:49.260 --> 00:08:52.830
So because we started our cursor
on the objective function cell,

00:08:52.830 --> 00:08:56.750
it comes up properly with the
"Target cell" already labeled.

00:08:56.750 --> 00:09:01.110
We do want a maximum, so
we keep that the way it is.

00:09:01.110 --> 00:09:03.720
And then here, where it
says "By changing cells",

00:09:03.720 --> 00:09:06.120
we put in our
decision variables.

00:09:06.120 --> 00:09:09.360
So we just select
them all, like this.

00:09:09.360 --> 00:09:10.530
All right.

00:09:10.530 --> 00:09:12.570
Now, before we start
putting in our constraints,

00:09:12.570 --> 00:09:16.910
I want to remind you
to go into Options,

00:09:16.910 --> 00:09:20.380
and you'll need to change
this from DEPS Evolutionary

00:09:20.380 --> 00:09:25.760
Algorithm to LibreOffice
Linear Solver.

00:09:25.760 --> 00:09:28.300
OK.

00:09:28.300 --> 00:09:29.850
So you notice that
the settings--

00:09:29.850 --> 00:09:33.700
you can choose to assume
variables are integer,

00:09:33.700 --> 00:09:36.780
and assume variables
are non-negative.

00:09:36.780 --> 00:09:38.640
We'll do that here.

00:09:38.640 --> 00:09:41.510
In fact, this takes care of
our first constraint, which

00:09:41.510 --> 00:09:43.770
was to assume that the
variables are integer.

00:09:47.510 --> 00:09:49.770
OK.

00:09:49.770 --> 00:09:51.430
So we've got this set up.

00:09:51.430 --> 00:09:55.410
Now let's put in the
rest of our constraints.

00:09:55.410 --> 00:09:56.900
OK.

00:09:56.900 --> 00:09:59.260
So we've already taken care
of this first constraint,

00:09:59.260 --> 00:10:01.430
that the daily number of
operating rooms assigned

00:10:01.430 --> 00:10:05.080
is integer, and we've taken
care of that with our options.

00:10:05.080 --> 00:10:06.500
So we'll go down
to the next one--

00:10:06.500 --> 00:10:10.850
the total number of
operating rooms each day.

00:10:10.850 --> 00:10:14.960
Remember that we can't
assign more than 10,

00:10:14.960 --> 00:10:20.160
so we'll select this
whole row, which

00:10:20.160 --> 00:10:22.190
has the total number of
operating rooms assigned

00:10:22.190 --> 00:10:26.460
each day, and we'll say that
each element in this row

00:10:26.460 --> 00:10:30.470
has to be less than
or equal to 10.

00:10:30.470 --> 00:10:32.360
Great.

00:10:32.360 --> 00:10:35.610
So on to the upper bound
of surgical teams per day.

00:10:35.610 --> 00:10:41.090
Let's select our decision
variables, all of them at once.

00:10:41.090 --> 00:10:41.590
OK.

00:10:45.160 --> 00:10:47.040
And then we'll need to
scroll up to our data.

00:10:50.540 --> 00:10:51.040
OK.

00:10:53.960 --> 00:10:56.380
You'll notice here how I'm
inputting all these constraints

00:10:56.380 --> 00:10:56.970
all at once.

00:10:56.970 --> 00:11:00.710
We're actually putting in 25
different constraints, one

00:11:00.710 --> 00:11:03.350
for each day of the week, and
one for each surgical team.

00:11:03.350 --> 00:11:05.440
And we're just putting
them in with a single line,

00:11:05.440 --> 00:11:08.620
by saying that all our decision
variables need to be less than

00:11:08.620 --> 00:11:11.280
or equal to this
box of constraints.

00:11:14.200 --> 00:11:14.890
Just like that.

00:11:18.240 --> 00:11:20.640
OK.

00:11:20.640 --> 00:11:22.520
So our next set
of constraints is

00:11:22.520 --> 00:11:25.400
on the upper bound and lower
bound of the daily department

00:11:25.400 --> 00:11:26.810
OR requirements.

00:11:26.810 --> 00:11:29.680
So we'll put in our
decision variables again.

00:11:29.680 --> 00:11:30.700
Scroll down to those.

00:11:37.920 --> 00:11:40.560
And let's say that
these are less than

00:11:40.560 --> 00:11:45.000
or equal to--
sometimes we've got

00:11:45.000 --> 00:11:48.160
to move the solver around--
the maximum number of ORs

00:11:48.160 --> 00:11:49.330
per day per department.

00:11:52.390 --> 00:11:53.530
OK.

00:11:53.530 --> 00:11:56.150
And we'll do that again
for the lower-bound.

00:12:04.880 --> 00:12:07.410
This time we need to change
the operator to greater than.

00:12:14.900 --> 00:12:26.230
Greater than the minimum
number of operating rooms

00:12:26.230 --> 00:12:27.520
per department per day.

00:12:31.540 --> 00:12:34.290
You see here that we've got
0's in for the minimum number

00:12:34.290 --> 00:12:37.880
of operating rooms per day,
but you could conceivably

00:12:37.880 --> 00:12:39.690
expect that one
department might actually

00:12:39.690 --> 00:12:42.820
want a minimum number of
operating rooms per day.

00:12:42.820 --> 00:12:44.800
For example, general
surgery, which

00:12:44.800 --> 00:12:49.090
we see here has a weekly
target of 189 hours,

00:12:49.090 --> 00:12:53.360
might actually want to have more
than 0 operating rooms per day.

00:12:53.360 --> 00:12:57.200
They might want to guarantee
they've got at least one.

00:12:57.200 --> 00:12:59.820
We'll experiment with
changing some of these numbers

00:12:59.820 --> 00:13:02.090
after we solve the
initial problem.

00:13:02.090 --> 00:13:05.280
For now, let's get our
remaining constraints in.

00:13:05.280 --> 00:13:08.040
The next ones we have
are the weekly department

00:13:08.040 --> 00:13:09.820
OR requirements--
the upper-bound

00:13:09.820 --> 00:13:12.170
and the lower-bound.

00:13:12.170 --> 00:13:14.110
So let's just scroll
down here in the solver

00:13:14.110 --> 00:13:15.530
to give ourselves
a few more rows.

00:13:20.090 --> 00:13:22.000
OK.

00:13:22.000 --> 00:13:25.580
So here we'll put in
our weekly totals.

00:13:25.580 --> 00:13:28.420
Good thing we've
already got this set up.

00:13:28.420 --> 00:13:33.060
OK, so weekly totals--
this will have

00:13:33.060 --> 00:13:35.860
to be less than the maximum
required by any department

00:13:35.860 --> 00:13:38.090
in a given week.

00:13:38.090 --> 00:13:42.410
So in the Value column,
we go over here,

00:13:42.410 --> 00:13:45.310
and we put in the maximum
required each week.

00:13:48.620 --> 00:13:53.740
We'll do the same
for the lower-bound,

00:13:53.740 --> 00:13:56.480
again, changing the operator
to a greater-than or equal-to

00:13:56.480 --> 00:13:56.980
sign.

00:14:05.140 --> 00:14:06.500
OK.

00:14:06.500 --> 00:14:12.400
And we're on to our very last
set of constraints, which

00:14:12.400 --> 00:14:18.520
is the departmental
targets down here.

00:14:18.520 --> 00:14:21.120
So remember, we can't assign a
department more hours than they

00:14:21.120 --> 00:14:23.840
actually wanted to have,
because operating-room time is

00:14:23.840 --> 00:14:26.050
so expensive.

00:14:26.050 --> 00:14:27.850
So we'll select there
the weekly number

00:14:27.850 --> 00:14:33.300
of hours, which we already have
a column set up to calculate.

00:14:33.300 --> 00:14:37.760
So we'll put this in here that
the weekly number of hours

00:14:37.760 --> 00:14:42.180
must be less than or equal to
the target allocation hours.

00:14:42.180 --> 00:14:48.160
So scroll back up here to
the target allocation hours.

00:14:53.710 --> 00:14:54.970
OK, great.

00:14:54.970 --> 00:14:56.690
Now we've got all
our constraints in,

00:14:56.690 --> 00:14:58.720
and we're ready to go.

00:14:58.720 --> 00:14:59.570
So let's hit Solve.

00:15:03.690 --> 00:15:10.510
It may take a few
seconds-- up to 100.

00:15:10.510 --> 00:15:13.780
In the next video, we'll look
at the results that we get,

00:15:13.780 --> 00:15:15.120
and interpret them.

00:15:15.120 --> 00:15:18.290
We'll also see how they change,
if we experiment with changing

00:15:18.290 --> 00:15:20.880
some of the numbers
in the problem.