Schedule Model with Queing

[whitneybrook]

I am trying to build a scheduling model for a clinic. Right now I am focused on building the basic model (random variation elements will be added later). Eventually, I am hoping that I can show wait times for patients and resource utilization patterns based upon different scheduling templates.

The first sheet is a list of appointments and the time each patient will spend during different phases of the appointment. I am hoping to make certain columns (e.g. C & E below) dependent upon resource availability. So if a patient arrives at 8:05 am, they will start check-in right away, but if all registrars are busy, the patient will wait for the next available registrar. I used an if function below to show my logic, I'm not assuming that an if function will work.

This tab is called "Appointments":

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Patients[/TD]
[TD]Arrival Time[/TD]
[TD]Check-In Start[/TD]
[TD]Check-In Finish[/TD]
[TD]Rooming Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Patient 1[/TD]
[TD]8:00 am[/TD]
[TD]8:00 am[/TD]
[TD]8:05 am[/TD]
[TD]8:05 am[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Patient 2[/TD]
[TD]8:05 am[/TD]
[TD]=if(Registrar Available, Arrival Time, Next Available Time)[/TD]
[TD]=C3+Times$B$2[/TD]
[TD]=if(Nurse available, Check-in finish time, Next available time)[/TD]
[/TR]
</tbody>[/TABLE]

In order to determine whether or not a resource is available, I have created a table that counts how much of each resource is currently used (by minute).

This tab is called "Resource Table":

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Time[/TD]
[TD]Registrars[/TD]
[TD]Nurses[/TD]
[TD]Exam Rooms[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8:01 am[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8:02 am[/TD]
[TD]=countifs(appointments!C:C,"<="&A3,appointments!D:D,">="&A3[/TD]
[TD]similar to B3, but for nurse timepoints[/TD]
[TD]similar to B3, but for room timepoints[/TD]
[/TR]
</tbody>[/TABLE]


Each resource has a separate capacity restraint. I imagine I will put those in another table, but for now, I think it may be good enough to assume that limits will be hard coded with the Registrar constraint = 5; Nurses = 4, Exam Rooms = 8.

Ideally, C3 of Appointments will return 8:05 am if there are 4 or fewer registrars available when Patient 2 arrives (this creates a circular reference problem because Resource Table B3 counts Appointments C:C). If there are 5 registrars in use, a time will be returned when the number of registrars drops. I don't know how to accomplish that last step.

I suspect if I know how to get Excel to look for the next available time slot, then I can probably fiddle to get rid of the circular reference problem, but an integrated solution that considers both problems is probably best.

I have no VBA skills (other than I once "borrowed" a script and it worked).

 

[Rijnsent]

Hi Whitney,

what about this to start you off:
https://www.dropbox.com/s/melwn4nhdifplxm/waiting_rooms_planning.xlsx?dl=0

What I did:
-create a series of patients coming in
-for times: Excel sees a time as a decimal number, so 12:00 = 0,5 and 18:00 = 0,75. The nasty bit about that is the rounding, if you keep adding 1 minute (= +1/(24*60) ), then minor rounding differences will pop up, causing formulas not to work as expected. So I "translate" the times to a rounded whole number, 12:00 is then 720, 06:00 is 360 etc.
-the patients come in in intervals of a couple of minutes, see B60 for a formula that can be dragged up and down, having a patient entering every 2-5 minutes.
-the same goes for the check-in time in minutes (column F), which depends on the variables in RESOURCES, using two named cells
-first calculation step is in column D to check how many Registrars are busy
-next is column E, that tells the time when somebody can go and see a registrar. The LARGE function is key there. Another named range was used for the number of registrars.

The nurse block is essentially the same as the registry block.

Hope that gets you started,

Koen

 

[whitneybrook]

Thanks! And here I was trying to teach myself Visual Basic in the hopes that I would stumble on a potential solution. That's still on the To Do list, but this is a much easier approach. Thanks for putting so much effort into this.

 

[Rijnsent]

Hi Whitney,
if you run into VBA issues, please give me a poke, I'd be happy to help this model a step further .
Cheers,
Koen