Scheduling Jobs to Factory Sections based on Capacity remaining and Precedence constraints

[Zee1984]

Hi,

I'm trying to come up with a formula that will help me determine the End times (Column Z) and Start times (Column Y) for Activities/Jobs (Column K).

• Each activity is done in a certain section (Column I).
• Each section has a maximum No. of Operators/ Headcount (Column S). This determines the section's capacity.
• I have worked out the Headcount required to carry out each activity (Column T)
• The Activities are sorted in descending order of the Activity Order (Column L). I wanted to plan the last activity first as I want to make sure we will meet the delivery deadline) and work my way backward.
• The Products are listed in Column J

The desired output:
The column Z should be populated such that

• Each preceding activity (based on the Activity Order) should be completed before the next one would start. For example. Activity 24 needs to end before Activity 25 can start.
• The aim is to assign multiple Jobs to a section during the same timeslot (while respecting the Activity Order) as long as the section capacity is fully utilized (in terms of headcount or manhours remaining in that timeslot). If this is not possible assign it to the next available EARLIER timeslot.

I tried it but ended up with a set of tasks arranged End to End while ignoring the available capacity. This causes the Makespan (Time it takes to manufacture) too long. I am not able to check for available capacity against the capacity of the job to be assigned when the timeslots will overlap.

My attempt is formulated in Columns Z and Y. I am stumped and would appreciate any help.

Thanks

Zee

 

[vletm]

Zee1984
Questions base Your file:
Do You use Activity Cycle Time/ Unit (mins) as it has written? ... or always that is total used time?
Is there 'break' out of shift time ( one shift per day ) ... or how?
Do Si No to be ready before or same day ( BOQ Item Required on Site Date )?
... if previous day then 'my samples' the last moment should be in the end of previous day ...
I tried to get some kind of idea only based 3 - Production Tracker -sheet ...
... many things are possible, but I would need at least time to figure - what do You really has written? ... and Your needs.
Note: I used 'raw values' instead those rounded values.
>> Later ... I did 'some' modifications with my sample - now, without those formulas ... but with comments <<

 

[Zee1984]

Zee1984
Questions base Your file:
Do You use Activity Cycle Time/ Unit (mins) as it has written? ... or always that is total used time?
Is there 'break' out of shift time ( one shift per day ) ... or how?
Do Si No to be ready before or same day ( BOQ Item Required on Site Date )?
... if previous day then 'my samples' the last moment should be in the end of previous day ...
I tried to get some kind of idea only based 3 - Production Tracker -sheet ...
... many things are possible, but I would need at least time to figure - what do You really has written? ... and Your needs. Currently
Note: I used 'raw values' instead those rounded values.
>> Later ... I did 'some' modifications with my sample - now, without those formulas ... but with comments <<

Hi vletm,

Thanks for the reply. I will review this and respond. Using my phone right now. Need to use my laptop.

Kind regards
Zee

 

[Zee1984]

Hi Vletm.

Thank you for the attempt. Your code seems to be quite efficient. However, I think I need to provide more information in order to solve this. The current solution does not address the capacity constraints for each section. I will provide more details below.

Details
Imagine a factory that manufactures different products.

• The factory has different sections. For ex. Solid, Veneer, Machinery, Assembly, Painting etc.
• Each Product will ‘travel’ through different sections via activities performed in them, and may spend different duration in each section, depending on the product type.

So, Product 1 may follow

Section	Solid	Machinery	Assembly	Painting	Dispatch
Duration	2hrs	2.5hrs	1hr	5hrs	0.5hrs

And Product 2

Section	Veneer	Machinery	Assembly	Dispatch
Duration	5hrs	3hrs	1.5hr	0.5hrs

• Within each section, the product goes through Activities (‘Column K’).
• The Order in which the activities need to be performed is indicated in ‘Column L’
  • The activities cannot be performed out of order. i.e. Activity 1 must be complete before Activity 1.1/ Activity 24 must be performed before Activity 25. This is a constraint.
• ‘Column N’ indicates the Minimum time required to finish an activity for a single unit. This is the fastest you can do this activity.
  • You cannot speed this up by adding more resources. For ex. baking a cake requires 30 mins of baking and this time cannot be sped up beyond this.
  • Take Sl No.1 for example.
    This activity will take a minimum of 231 mins/ 3.85 hrs to complete. I can then assign this to 1 person for 3.85 hrs (rounded to 4 hours) or 2 people for 2 hours (this is what you see in 'Column T' as 'Allocated Headcount'. However, the Dispatch section ('Column I') only has a total of 8 men working (indicated in 'Column S'). This is the maximum capacity of the section at any given time.
• 'Column M' has the required number of units.
  Sl No. 1 represents the Dispatch of 2 units of Product 1.
• Each Section has its own set of manpower. The Total men per shift in each section is the Section Headcount (Column S).
  • So if the section has a capacity of 10 people, it is 10 manhours available for every 1 hour in that section.
• Each product can have its own Delivery dates (‘Column R’). So the last activity under that product must be completed before this date. This is another constraint.

The challenge is to schedule work to each section in such a way that,

• the activity for each product is done in the right order,
• when assigning the work to each section, it spends the minimum required duration in the section (this can be more than minimum, but not less),
• the allocated headcount should not exceed the maximum capacity/ headcount of the section.

Example:

• The Dispatch section has a Section headcount of 8 (max capacity).
• Imagine, I have assigned some work for Product 1 previously to the Dispatch section, say, 4PM to 5PM. This required 2 headcounts in this section. (2 manhours).
• Now, I have to assign a task called ‘Dispatch – Packing’ (Dispatch section) for Product 2. Based on the minimum time required, I determined that this activity will take 3 hours by 4 headcounts. (Total 12 manhours)
• I see that Dispatch section still has balance capacity during 4PM to 5 PM. (2 headcounts used for Product 1, and 6 remaining without work).
• I want to assign Product 2 between 2PM to 5PM. So now, the unused capacity between 4PM and 5PM will also be utilized. 4 headcounts for 3 hours.
• So during the first 2 hours for Product 2, the section will have 4 working men. 2PM to 4 PM.
• From 4PM to 5PM, 2 more people will start working in this section for Product 1.
• During these 3 hours my peak active headcount (6 headcounts between 4PM and 5PM) is still within the capacity limit for the section (8 headcounts).
• So, while assigning tasks to a sections its current workload (for all jobs) during that timeslot needs to be checked against the Total section headcount/ capacity. If, by adding the new task, the combined headcount will exceed the section capacity, then do not assign it and find the next earlier/ available slot.

I will try also to address your questions below.

Do You use Activity Cycle Time/ Unit (mins) as it has written? ... or always that is total used time?
This is the Minimum time required to finish this activity for a single unit. You cannot speed this up by adding more resources. However, this time can be increased to adjust the work according to available capacity.
For ex. baking a cake requires 30 mins of baking and this time cannot be sped up beyond this.


Is there 'break' out of shift time ( one shift per day ) ... or how?
I didn’t consider breaks in this. But I like the idea of being able to specify break times and scheduling work around that.

Do Sl No to be ready before or same day?
I added an option in cell ‘H2’ to specify the Notice period you need to give for delivery. So this time needs to be subtracted from the On site date/ delivery date (‘Column R’).

I tried to get some kind of idea only based 3 - Production Tracker -sheet ...
... many things are possible, but I would need at least time to figure - what do You really has written? ... and Your needs.
Note: I used 'raw values' instead those rounded values.
You are correct in using that sheet. I hope I have provided you the details you require.

Later ... I did 'some' modifications with my sample - now, without those formulas ... but with comments
Adding the comments/notes to provide more information is nice.

I like the VBA based approach for this. I checked your code and did not see anything that checks if the capacity of the section is exceeded or not. Other than that, the sequencing is maintained.

Hope you can help me out with a solution.

Regards
Zee

 

[vletm]

Zee1984
... hmm? a lot of writings, but did You read my writings or answer to my questions those?
... You tried ... to write ...

Do You use Activity Cycle Time/ Unit (mins) as it has written? ... or always that is total used time?
How long time takes Si No. 1 ? Needs two units and 231min/unit .... 3:50 or 7:41? Your explain something about 4hrs /2hrs... hmm?
...hmm? do You mean that if You could assign 10 persons ... then this would take ~23minutes ...hmm?
For ex. baking a cake requires 30 mins of baking and this time cannot be sped up beyond this.

Is there 'break' out of shift time ( one shift per day ) ... or how?
You have given when shift starts and ends ... other times are 'breaks'.
Do those work 24/7 or shift time?

Do Sl No to be ready before or same day?
Yes, I got an answer.
If all has same 'the last date' then - why there have to give same date 37 times?

Note: I used 'raw values' instead those rounded values.
eg I won't use 'rounding'!

You noted:
I checked your code and did not see anything that checks if the capacity of the section is exceeded or not.
Yeah ... that's true - based Your writings - would it be possible to figure what do You really need?

You're using terms ... imagine ... hope.
Can You imagine that my own file size is now about 48kB ( from Yours ~391kB)?

Many things are possible, but then there should be clear procedure - what really should happen?
I can try to figure - how to modify my file - but based Your writings ... it will be hard!

 

[Zee1984]

Zee1984
... hmm? a lot of writings, but did You read my writings or answer to my questions those?
... You tried ... to write ...

Do You use Activity Cycle Time/ Unit (mins) as it has written? ... or always that is total used time?
How long time takes Si No. 1 ? Needs two units and 231min/unit .... 3:50 or 7:41?
The duration should be a minimum of 3:50. If 2 people work on the activity for 3:50, the Total manhours will be 7:41. But the duration will be 3:50 (for ex. 2 people working from 2PM to 5:50 PM )


Your explain something about 4hrs /2hrs... hmm?
This was me rounding the hours. You are accurate when you say 3:50.


...hmm? do You mean that if You could assign 10 persons ... then this would take ~23minutes ...hmm?
For ex. baking a cake requires 30 mins of baking and this time cannot be sped up beyond this.
No. You cannot assign more people to shorten the Activity Cycle Time/ Unit (mins). It will take always take the minimum time,3:50.
But you can increase the time to adjust for available capacity.



Is there 'break' out of shift time ( one shift per day ) ... or how?
You have given when shift starts and ends ... other times are 'breaks'.
Do those work 24/7 or shift time?
We work 24/6 over 2 shifts. One day Off. But this was not part of my initial plan while doing the worksheet.


Do Sl No to be ready before or same day?
Yes, I got an answer.
If all has same 'the last date' then - why there have to give same date 37 times?
I agree this is not required for all activities for the same product. This was added for reference and to make it easy to use in the formula I was initially trying to come up with.

Also, Items may have different delivery dates. I am treating each Product separately. My way might not be efficient. I am open to suggestions.
The aim of the file was to get a lot of orders and plan for the items so that they are always scheduled backward from the individual delivery date.


Note: I used 'raw values' instead of those rounded values.
eg I won't use 'rounding'!
Noted.


You noted:
I checked your code and did not see anything that checks if the capacity of the section is exceeded or not.
Yeah ... that's true - based Your writings - would it be possible to figure what do You really need?
I have tried to explain how the scheduling is done manually in my post. Do you require additional information?

You're using terms ... imagine ... hope.
Can You imagine that my own file size is now about 48kB ( from Yours ~391kB)?
My apologies. No offense meant.


Many things are possible, but then there should be clear procedure - what really should happen?
I can try to figure - how to modify my file - but based Your writings ... it will be hard!

I have tried to explain the process in my post. Please find the excerpt below

Example Process

• The Dispatch section has a Section headcount of 8 (max capacity).
• I have assigned some work for Product 1 previously to the Dispatch section, say, 4PM to 5PM. This required 2 headcounts in this section. (2 manhours).
• Now, I have to assign a task called ‘Dispatch – Packing’ (Dispatch section) for Product 2. Based on the minimum time required, I determined that this activity will take 3 hours by 4 headcounts. (Total 12 manhours)
• I see that Dispatch section still has balance capacity during 4PM to 5 PM. (2 headcounts used for Product 1, and 6 remaining without work).
• I want to assign Product 2 between 2PM to 5PM. So now, the unused capacity between 4PM and 5PM will also be utilized. 4 headcounts for 3 hours.
• So during the first 2 hours for Product 2, the section will have 4 working men. 2PM to 4 PM.
• From 4PM to 5PM, 2 more people will start working in this section for Product 1.
• During these 3 hours my peak active headcount (6 headcounts between 4PM and 5PM) is still within the capacity limit for the section (8 headcounts).
• So, while assigning tasks to a sections its current workload (for all jobs) during that timeslot needs to be checked against the Total section headcount/ capacity. If, by adding the new task, the combined headcount will exceed the section capacity, then do not assign it and find the next earlier/ available slot.

In my first file, I was trying to come up with a solution via formula to determine the start and end times. The only condition is that all the constraints are met. (Delivery date, Activity order, and Section headcount capacity,). In my attempt, I was able to meet the delivery date and Activity order. But I couldn't check against the section capacity.

Kindly let me know if you require more details.