Using Solver to Make a Dynamic Staff Rota

  • Hi there everyone!

    I come to you in a moment of desperation :(

    I'm fairly adept in Excel, but I just can't seem to figure this one out and I need your help!

    On a weekly basis, I have a document that I update with several Call Stats and it provides me with a Staff Forecast, broken down by 15-minute intervals. (See the 'Forecast' worksheet of the document attached)

    I've also managed to Solver use that data to look at how many agents are needed overall on a Daily Basis when looking at a generic Weekly View. (See the 'Shift Allocation' worksheet of the document attached)

    I've ascertained that the 6 shift patterns needed to keep the staff numbers at a minimum are:

    • 09:00AM-05:00PM
    • 10:30AM-06:30PM
    • 12:00PM-08:00PM
    • 01:30PM-09:30PM
    • 03:00PM-11:00PM
    • 04:30PM-12:30AM

    What I'm attempting to do now (and been failing at for the past couple of days, and I'm now at the end of my tether...) is use the Forecast data to provide me with the minimum number of Agents needed, based on:

    • The Number of Agents Forecast, by Day, by 15 Minute Segment (in the 'Forecast' worksheet'
    • The 6 shift patterns above
    • If possible, have the Shift Pattern maintained in the 'Shift Allocation' worksheet (if not, have a shift pattern where staff have at least 2 days off in a row)

    The document is attached.

    Your help is much appreciated, everyone :D

    Document attached:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!