Production Schedule as per Available Manpower

Can you show a full calculation: how does manpower come into the equation? Is output per day per person (or total output per day)? So first product has production run of 114 days?

Are we to assume 7 day working and no holidays?

Dear JohnTopley,

Thank you so much for your immediate response, Please find below my updates!

Can you show a full calculation: I have updated a one more excel file, where manually updated complete schedule

how does manpower come into the equation? Is output per day per person (or total output per day)? 7.25 Hours is available hours * Total available Manpower we are considering

So first product has production run of 114 days? Yes, You are absolutely right!

Are we to assume 7 day working and no holidays? My Apology, I should have mentioned it before itself, We have to factor Sat, Sun & Declared Holiday eliminated from Schedule alignment!

Thanks & Regards,
Rajeshkumar R

Dear JohnTopley,

Please my updates!

Where is holiday schedule?

Range (I7:BP7): I have updated with a Day code. Wherever it is highlighted as “W” it’s a working day and rest all Days (like B, H &L) we must eliminate it from scheduling!

And I suspect this will need a VBA-based solution rather then using formulae.

The Reason I was looking for formulae is due to my lack of knowledge in VBA. If you suggest VBA would be the better solution for this; Please suggest me the same!!!

Since we are going with VBA Solutions (Would need your extended support):

In this attachment, “Column F” I have modified a little bit, i.e. instead of Priority Number, I have mentioned the Production Start Date. Our Production schedule must begin from the start date mentioned, subjected to the Available Man Power availability!

The Priority Sequence remains Top to Bottom subjected to Start Date.

Note:

Though attached Sample sheet having 8 Product and 1 Segment, my live file is having more than 25 Segment (will be one below other) with 1000+ Product (But, the Template Remains same).

Thanks, in advance for your valuable feedback!!!

Regards,
Rajeshkumar R

Hi AliGW, Yes Please!

Regards,
Rajeshkumar R

Dear JohnTopley,

My Sincere Thanks to you for your efforts on my query and continues appropriate response too!

Thank you very much for the updates and clear explanation.

I see yo have added a table with all the dates "coded": thank you. If required, the "B","L","W", "H" could be added by code. All that would be required are holiday dates so your table could be much shorter. Noted!

EDIT: holiday table provided!

In "Date_Input" column G could be simplified:

=IF(WEEKDAY(B2)=7,"B",IF(WEEKDAY(B2)=1,"L",IFERROR(IF(VLOOKUP(B2,$I$2:$I$17,1,0),"H",""),"W"))) Your suggested formula is Really simple and gives the exact output which I got it with a very big length of Formula! Thank You So Much for this Suggestion!!!

And how do you want incorporate the "buffer" day: could data be added to these days at some point? No…. Always it has to be Eliminated from Scheduling!

My thinking would be exclude holidays (using a table of holiday dates) and all Saturdays/Sundays i.e. I would not differentiate between "B" and "L". At ideal state your assumption is right, However; Internally we have different set of Rules to Manage Sat & Sun. That is the Reason I have differentiated it like this!

my live file is having more than 25 Segment (will be one below other) with 1000+ Product

Can you please add a sample file with more than one segment to i (all) understand how this works. In particular, how does this realate to "Available MP" and "Loaded MP" data: are there multiple lines? As Requested, Here I have updated with 16 Different Segment with 500 different Products in it.

Range (I2:BP17): Contains available Manpower by segment on each day!

Range (I20:BP35): Contains a Formula which will calculates Loaded Manpower based on schedule alignment

Range (A40:F539): Contains All Product code, Segment and Other necessary Inputs

Range (I40:BP539): Auto Production Schedule to be aligned as per criteria mentioned on post #5, In the attachment I have updated complete production schedule manually (That is the exact output which I am looking for), (One additional Note here is, If a Product Code has “Zero” as To Be Schedule Quantity, Then particular Row to be eliminated from production scheduling)


With a coding solution, it is important to clearly define what is required to avoid unnecessary re-work: your "specification" is an excellent start so thank you again.

Thank you so much for your Feedback!

Although I can do VBA, I am not an expert so I suggest you ask a moderator to re-assign the post to the VBA/Macro forum where the VBA expertise is better than mine..

Yes, It is Already Done!

Meanwhile, I will start looking at a VBA solution.

FYI: see the attached (from a previous post) which is a combination of formulae and VBA is a similar requirement to yours.

I just downloaded and trying to understand the same! Paralley, As you requested, here I have updated a New Excel file with Multiple Segment!

Looking forward a positive valuable feedback to accomplish my subjected objective!

Thanks & Regards,
Rajeshkumar R

Thanks AliGW for your support in moving the Query to VBA Group

Dear JohnTopley

Suggest VBA When I am trying to execute in attached File, at code "Dim ***_prod As Long" I am getting an Error and it is not moving to next...

Regards,
Rajeshkumar R

The original code will NOT work with your latest file: as I said in previous posts, it needs updating to reflect the new layout.

More questions:

1). How many days (maximum) production should I allow for i.e how many days in row 39?

2). What is the maximum number of segments (more beyond "P") ?

Dear JohnTopley,

More questions:

1). How many days (maximum) production should I allow for i.e how many days in row 39?

As shown in template always 60 Days (From least date (in this case it is 14 Aug 2017)!

2). What is the maximum number of segments (more beyond "P") ?

I will restrict my number of Segments to 16 Only i.e, up to Segment "P" only!

Regards,
Rajeshkumar R

Please Login or Register  to view this content.

NOTES: I moved the "Check Calculations" to columns I & J as this makes it easier to add additional dates.

I have added many more dates than you require: just delete where you require the last date.

As before click RUN to execute macro.

If the number of segments doesn't change then the macro (when you have checked the results!) should not require change.

It is possible to allow for a dynamic set of segments but this requires additional coding.

Dear JohnTopley ,

Thank You So much for your valuable time in providing me a solution to my query!

I am sure it will help me a lot!

One small concern I found in the file is, when we amend (Reduce or Increase) the available Man power for a Day or Week, VBA Daily scheduling alignment not referring those changes and aligning the schedule. Could you please help me resolving the same!

For Example: -

Segment A,

On 21 Aug 2017 to 24 Aug 2017 I reduced the available Manpower to 50, But still the schedule was getting aligned for 65,65,59 & 74 Accordingly!

Likewise,

On 21 Aug 2017 to 24 Aug 2017 I increased the available Manpower to 100, But still the schedule was getting aligned for 64,65,59 & 74!!!

Looks like auto schedule alignment (VBA) is not factoring available Manpower into consideration! (I may be wrong, since I don't have knowledge of VBA)

Could you please help me getting this fixed?

Thanks & Regards,
Rajeshkumar R

There is a flaw in my logic: I was using "in-core" arrays to speed the calculation but now realise this will not work as the matrix in K40:Mx600 must be filled in as we do the calculations so the "Loaded MP" gets calculated each time.

I will look again tomorrow,

Dear JohnTopley,

Noted, Thanks!

Regards,
Rajeshkumar R

Please Login or Register  to view this content.

Dear JohnTopley,

Once again Thank you So much to you and Excel Forum Too!

I am happy to be a part this Forum! It gives a lot of opportunities for beginner like me to learn something New!!!

I just review this new File. In case of any doubt or Concern, I shall revert Back!

Thanks & Regards,
Rajeshkumar R

Dear JohnTopley,

Suggested Coding working amazingly! Once again Thank You So Much!

Regards,
Rajeshkumar R