While Excel is a powerful tool for many applications, it falls short when it comes to intricate demands of modern roster scheduling. This article will explore the reasons why scheduling on Excel spreadsheet is more spreadsheets is more difficult and less effective than using programmatic methods to schedule rosters.
Limited Automation Capabilities
There are 2 approaches to rostering in Excel: (1) Manual Scheduling; and (2) Solver-based scheduling.
Manual scheduling, being the most primitive method, takes a lot of time to complete, not to mention that you may spend hours trying to find a solution, and get totally stuck towards the end — when the configurations are all limited.
For the second way, it actually works for smaller wards, which fewer constrictions, and fewer factors to take into account. The drawback is that the configuration is extremely cumbersome — not only do you have to add in constraints one by one, but you also have an upper limit of 200 variable cells only.
To give some context, the latest client I’ve helped, required more than 2500 constraints to properly model the constraints to suit his ward’s particular needs.
Programmatic scheduling solutions, on the other hand, can automate a wide range of tasks, including:
- Shift assignment based on doctor/nurse availability, skills, and preferences.
- Compliance checks with roster rules.
- Generation of optimized schedules based on thousands of constraints, in the matter of minutes.
The level of automation provided by programmatic solution, in summary, is ahead of Excel by galaxies, allowing it to save staff precious time.
Difficulty in Handling Complex Constraints
Another client I helped had a very complex scenario where I used a lot of constraints to help him model the mathematics as close as real-life as possible. His ward had like 27 people over 3 seniorities.
Wanna guess how many constraints I used?
100?
500?
I used 36000+ constraints to fully fit the model to the his rostering problem.
Imagine having to type them one by one in an Excel solver. (You couldn’t, anyways, due to its limit on constraint counts).
Now, the reason we had to use so many constraints is because of the numerous complex constraints that can occur in real life:
- Doctor/nurse availability, requests, and preferences
- Skill requirements for different roles
- Fair distribution of desirable and less desirable shifts
- Compliance with rostering rules (and spacing shifts out so no one overworks in a short period of time)
- Staff number restrictions
- Preference to schedule, or avoid scheduling some people with others
Trying to manage all these constraints in an Excel spreadsheet quickly becomes overwhelming. Each constraint needs to be manually checked and balanced against each other.
Adding to that, there is an additional risk where you enter 2 conflicting constraints unknowingly, and have to find the conflicts by manually debugging.
As the number of constraints increase, the difficulty skyrockets in an exponential fashion.
Programmatic scheduling solutions, however, are designed to handle complex constraints effortlessly. The ability to name variables in the IDE allows us to code constraints in a much more understandable way.
Most importantly, with a few techniques, you can actually make the algorithm self diagnose the conflicting constraints when it does bump into infeasibility.
Ensuring Compliance
Now, let’s get back to manual scheduling in Excel. How would you check whether the roster complies with all rules of the ward? You probably need to check manually.
Here’s how I’d approach it:
- Ctrl + F to screen all the shifts by the same person, make sure they are not crammed together.
- Calculate the total number of shifts to see if they are equally distributed.
- Calculate the weekend shifts to see if they are equally distributed.
- And a deeper search against each rule to make sure no rule is broken.
It requires a lot of logical reasoning, and errors can go missed. Often, you will need manual review from peers and seniors to ensure compliance.
But you don’t need that with programs. You see, if you have set rules clearly, there are only 2 possibilities:
- The roster is feasible, and it gives you the solution.
- The roster is infeasible, you are forced to break some rules to make it work.
Keeping track of all these rules in a program is much easier than in an Excel. It is much easier to do it over program. With some techniques, you can even make it so the program reports to you which constraint has been broken.
Scalability Issues
As ward grow in size and complexity, Excel-based scheduling systems often struggle to keep up. The scalability issue manifests in several ways:
- Performance: as the number of staff members and shifts increase, Excel spreadsheet can become sluggish, taking longer to calculate and update. Notably, with the addition of each Boolean variable, you essentially double the size of the possible solution. Unless well optimised, solvers often struggle at scale, leading to frustration and wasted time for managers.
- Visual clutter: in excel, each variable requires a cell, and each constraint requires a column. Larger schedules require more rows and columns, making it difficult to view and manage the entire schedule at once. It also leads to errors and oversights in scheduling.
- Data management: with more staff comes more data — seniorities, ALs, on call and no call requests, number of assigned calls in their corresponding seniorities, leaves, and much more. Excel is not designed as a database and can become unwieldy when dealing with large amounts of interconnected data.
- Version control: Schedules are often shared and updated, which makes it challenging to keep track of the most current version, especially if multiple people are involved in the scheduling process.
Programmatic solutions offer a much cleaner interface. You can develop a clean, dedicated dashboard for managing these data, allowing efficient processing of vast amounts of data, providing clear visualisation regardless of scale, and offer robust data management capabilities.
Advanced Analytics and Reporting
In more advanced wards, we often want fine grain controls over several aspects of the wards constraints and objectives. To appropriately model these aspects, we often need to utilise what is called auxiliary variables. These are variables dedicated to the formulation of the problem, and are mathematically trivial.
Creating these auxiliary variables, however, is not trivial. Auxiliary variables require anywhere from 1 to 6-7 constraints to be formulated. And in an Excel, it is a big clutter to generate any meaningful report and insight from a big pile of auxiliary variables.
This is not the case with programmatic scheduling. As long we we can write clean code, we can effortlessly manage thousands of auxiliary variables (actually, in the 36000 constraints we mentioned above, around 23000 of them are auxiliary).
We can even achieve reporting of broken soft constraints, which serve as a great summary for the ward manager, as an indicator for the quality of the solve, and whether it fits the real life rostering rules.
Conclusion
While Excel has been a stalwart tool for many ward managers and associate consultants, it’s limitation become increasingly apparent as the complexity of healthcare scheduling grows. The challenges of limited automation, difficulty in handling complex constraints, compliance issues, scalability problem, advanced analytics all point to the need for more sophisticated solutions.
Programmatic scheduling methods offer a compelling alternative, providing powerful automation, robust constraint handling, built in compliance checking, and excellent scalability. As healthcare continues to evolve and become more complex, adopting these advanced scheduling solutions can help ward managers create more efficient, fair,and compliant schedules while saving time and reducing stress.
The transition from Excel to a programmatic scheduling solution may require an initial investment of time and resources, however, the long term benefits in terms of time savings, improved staff satisfaction, better compliance, and more informed decision making make it a worthwhile consideration for any healthcare facility looking to optimise its scheduling process.
Optimize Your Ward Scheduling with Expert Assistance
Our Scheduling Service
If you’re looking to implement an efficient, fair, and compliant ward roster scheduling system, we can help. We offer personalized algorithm development and web-based solutions tailored to your specific needs. Learn more about how we can transform your scheduling process at our home page.
Learn to Schedule with Program
If you want to try make your own rostering algorithm, you can learn it with our free e-book, “Programmatic Roster Scheduling”. This guide introduces you to the basics of using programming to create efficient and fair schedules. You will learn how to translate ward rules into code, automate repetitive tasks, and generate optimized rosters with ease. Because most doctors have little programming experience, I’ve written it for beginners in Python. Download it for free here and start learning now!