Capacity planning shift rota Help or miracle!!!

**GordonKy42** · 10-10-2019, 05:36 AM

Hello Good Sirs/Lady's,

Looking for some help(perhaps a miracle

) / a point in the right direction. Any and all help greatly welcomed

So i am looking to create a Shift Rota for our Machine shop based on capacity hours required. I want to have something that when i put in how many planned hours there are for set machines i know how many shifts are required, (i have this part created), it will then pull in the Operator names capable of running these machines from the Manning tabs (Dayshift, Backshift and Nightshift) to satisfy the planned hours for that week. So if it says i need two shifts it will pull in two operators probably one Dayshift and one Backsfift for that Hierarchy.

I need to also have it so that if their name is already assigned to a machine for that day they can not be used else where as without this it would not work.( i have just popped in made up names for the time being.)

Right now i have created a Rota Tab where i am looking to have the names pulled to based on the Hierarchy of the machines column B:B, and will fill the work centers, column A:A as more than one machine may be required to satisfy demand.
I have added 3 other tabs one for Dayshift, Backshift and Nightshift, where the operators names are to come from and tie into the Dayshift, Backshift and Nightshift columns in the Rota Tab column C:C

In addition, but not a must would be, if there are not enough operators available it would come up with a message saying that the capacity cover is not achieved, i would be keen to know how to do this

I fear i may be asking too much, but i am excited to see if this is possible and expand my excel skills

Attached is a copy of the workbook

Any Help will be greatly received

**dflak** · 10-10-2019, 02:27 PM

Yes, this can be done, but it will require reorganizing the data. The biggest issue will be equitable distribution of the workforce.

**dflak** · 10-11-2019, 04:24 PM

I got one function working: whether a person is available for a certain date.

I'm going to need some business rules for assigning people. The easiest thing to do is to go down the list of people available for a certain machine and fill the slot with the first person found. This will lead to some people getting a lot of hours and some people getting few hours.

I also notice that some people are qualified on multiple machines. This puts one more wrinkle in the equation. Suppose Joe is qualified for machine X and Y but is the only person qualified for machine Y. If I assign him to X then Y doesn't have coverage.

On the capacity sheet, are these the hours per day for the whole week? You are showing 2 operators for the LONGBAY E90. Does this mean that you want two names per shift in each slot?

The rules seem to be very complex, but what I can do is give you a listing in each slot a to who is available for that slot. I'll shoot for that as a first attempt.

**dflak** · 10-11-2019, 05:01 PM

This is the best I can do for the moment. It is a bit slow, but it shows who is available for what machines, for what shifts on a given day.

**GordonKy42** · 10-15-2019, 11:11 AM

Hey Dflak,

amazing thanks for looking at this!! sorry on the delay i was off on holiday for a long weekend.
So regarding what you asked above, regarding the hours distribution i am not overlay concerned on that at the moment if that is a difficult one to fix.
Regarding what you stated in relation to the coverage - yes that is correct some individuals have multiple competencies how that is worked in i am not sure, how possible that will be/ how big a headache. That may have to be a manual fix if not possible.
Regarding the capacity sheet, so column B:B would be how many hours i need that week, and Column C:C is how many shifts i need over that week. (not the numbers shown, would usually be a lot more but i have just put in random numbers as well as random names to get the logic proven if possible).

Hope that helps,

Again really appreciate your help and time

Capacity planning shift rota Help or miracle!!!

LinkBack

Thread Tools

Rate This Thread

Display

Capacity planning shift rota Help or miracle!!!

Re: Capacity planning shift rota Help or miracle!!!

Re: Capacity planning shift rota Help or miracle!!!

Re: Capacity planning shift rota Help or miracle!!!

Re: Capacity planning shift rota Help or miracle!!!

Thread Information

Users Browsing this Thread

Similar Threads

rough cut capacity planning

Is this capacity planning tool errenous

Capacity Planning

Capacity Planning

Production Capacity Planning; required hrs per week per department

Capacity Planning for Work Cell

capacity planning: 5-day rolling basis

Bookmarks

Bookmarks

Posting Permissions