Scheduling with Solver Microsoft Excel Part 2

Now Ill share the challenges I faced using Solver to create schedules for 24/7 set-ups, the solutions I thought of and the outcomes.

First, a summary of the problems that need to be solved when jumping from a 5 day service window to a 24/7 set-up:

1-Schedule Inflex.

2-Days off become part of the equation and need to be distributed

3- Higher demand for 1 of the 2 weekend days

4- Crossing Midnight Schedules impact 2 different days. The concept is easy to understand but it does represent a challenge when calculating and graphing Requirement VS Scheduled.

Then, lets start talking about the solutions:

1- Schedule Inflex is present in any Scheduling Process and it`s impossible to get rid of it completely but sure you can reduce it and the way to go is adding as many schedule possibilities as you can to your model.

2- Days Off This one requires a bit of work before running the model just as any scheduling tool I`ve used or heard of within the WFM world. If you don`t give enough options to distribute the days off throughout the week you`ll get unbalanced schedules.

3 Different demand on Weekend Days This one made me struggle big time until I actually got out of the my comfort zone and tried something new. Problem was that I could get good schedules for the weekdays but heavily overstaffed for weekends. At first I thought ok I can just replace some schedules on the weekends and after maybe 15 minutes you will have schedules ready to be sent but the goal was creating a model that won′t make you work 15 mins every time. Solution was I simply created a model exclusively for the weekends.

4 Crossing Midnight Schedules On this, I created many different models which would get me some schedules but nothing really perfect. In fact, this is the only thing that I couldn`t do with the 24/7 solver models until now. Definitely Ill keep trying and will let you know if I figure out a way of getting great schedules at 1 click.

Yet, I`m somewhat happy with the result because the Cross-midnight schedules would take only 5 minutes to add manually after running the model. Specially because the only ones missing are Monday-Friday. If you look at weekends full service window is covered.

A couple of conclusions on to this topic:

• 24/7 schedules can definitely be done with Solver.
• The resulting schedules aren`t perfect but can definitely work with them.

Please, let me know you thoughts and share your experience with Solver models.

Disclaimer: This article expresses my personal opinion on the topic discussed and not that of any company. Nor do I share any data from any company.