Shift Pattern Creator - Excel
- or -
Post a project like this2457
£100(approx. $125)
- Posted:
- Proposals: 4
- Remote
- #1630238
- Awarded
Description
Experience Level: Expert
General information for the business: Call Centre Planning
Description of requirements/functionality: I am trying to create a scenario builder in excel for shift patterns
I have two pots of people - those that are on fixed patterns and those that i can move to a new pattern.
The excel doc v4 already achieve some of what i think is needed. You can see the demand (ie how many people do we need in each time interval) is on the demand tab.
Moving to the WhatIf tab you can see it creates a set of shift patterns if you run the Whatif2 macro. These are just random patterns created in Excel by playing around with the start/stop times, days off etc. There are some rules it must adhere to and you can change the various parameters in the yellow boxes. You can see that as it works it gives a score on the left which is where it is working to get as close to covering all the demand spots it can. If it got to 0 it would mean there would be enough people to work that the whole of the demand profile.
The output of the patterns are under the 'supply' tab which counts up how many people would be in that slot. The 'create supply' button on the tab will show you the workings of that. You take supply minus demand to come up with the variance i.e the score.
Now that works great for a new team. The second sheet (new needed on top of bespoke) tries to do something similar but takes into account the bespoke people who i cant move.
The 'full v bespoke' tab is a comparison of the two
I am trying to get the rotations to be balanced out so i have taken the average we have on each day and added to the 'average' tab. You can see on the full v bespoke tab we have some low numbers on a Wednesday and Thursday morning at 8am v the other days, although they are pretty well balanced i have manually increased to 35 people on the average tab so that when we create patterns we work to fill those slots with people.
On the 'new colleagues per week' tab i have then taken the average needed minus those already in on bespoke patterns so we know how many 'new patterned' people we need.
Then on the 'patterns' tab there is opportunity to run some simulations to get to the best score we can. This is slightly different to the first sheet as it will be a view over 8 weeks so we take into consideration all the bespoke imbalances over that period
Again, there are some rules that need to be adhered to and you can see those in the attached sheet and in the yellow boxes.
The patterns it create must adhere to the rules described.
All the yellow boxes should be dynamic and the results should change accordingly depending on what you input in - e.g change the number of colleagues, number of teams etc..
Extra notes:
Description of requirements/functionality: I am trying to create a scenario builder in excel for shift patterns
I have two pots of people - those that are on fixed patterns and those that i can move to a new pattern.
The excel doc v4 already achieve some of what i think is needed. You can see the demand (ie how many people do we need in each time interval) is on the demand tab.
Moving to the WhatIf tab you can see it creates a set of shift patterns if you run the Whatif2 macro. These are just random patterns created in Excel by playing around with the start/stop times, days off etc. There are some rules it must adhere to and you can change the various parameters in the yellow boxes. You can see that as it works it gives a score on the left which is where it is working to get as close to covering all the demand spots it can. If it got to 0 it would mean there would be enough people to work that the whole of the demand profile.
The output of the patterns are under the 'supply' tab which counts up how many people would be in that slot. The 'create supply' button on the tab will show you the workings of that. You take supply minus demand to come up with the variance i.e the score.
Now that works great for a new team. The second sheet (new needed on top of bespoke) tries to do something similar but takes into account the bespoke people who i cant move.
The 'full v bespoke' tab is a comparison of the two
I am trying to get the rotations to be balanced out so i have taken the average we have on each day and added to the 'average' tab. You can see on the full v bespoke tab we have some low numbers on a Wednesday and Thursday morning at 8am v the other days, although they are pretty well balanced i have manually increased to 35 people on the average tab so that when we create patterns we work to fill those slots with people.
On the 'new colleagues per week' tab i have then taken the average needed minus those already in on bespoke patterns so we know how many 'new patterned' people we need.
Then on the 'patterns' tab there is opportunity to run some simulations to get to the best score we can. This is slightly different to the first sheet as it will be a view over 8 weeks so we take into consideration all the bespoke imbalances over that period
Again, there are some rules that need to be adhered to and you can see those in the attached sheet and in the yellow boxes.
The patterns it create must adhere to the rules described.
All the yellow boxes should be dynamic and the results should change accordingly depending on what you input in - e.g change the number of colleagues, number of teams etc..
Extra notes:
Mark L.
0% (0)Projects Completed
-
Freelancers worked with
-
Projects awarded
67%
Last project
26 Apr 2024
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies