1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Production Planning and schedule

Discussion in 'VBA Macros' started by Dhawal Sharma, Jan 4, 2019.

  1. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    Hi Gurus!

    I have a order position data here. What I wish to achieve is to create a datewise report for execution of orders with two prime objectives.

    - Ensure that priroities are met. No order (UID) overshoots its respective Delivery date.
    - Ensure the full utilization of capacities.


    The date wise plan (lets call it 'schedule') should be made considering capacity in terms of manhours. The schedule will assign work for all working days, excluding any sundays. The manhours needed for each UID is mentioned in data.
    It will first pick the UID with nearest Delivery date and assign for a day, then if the capacity permits, new UID with next nearest date will be assigned. If the new UID considered is having more manhours needed than balance capacity, UID qty need to be broken down in parts and only that much qty be assigned for the day which completes the capacity for day.

    There can a on UID order which has qty requiring manhours much more than capacity. such UIDs need to start a few days before the delivery date so that the last piece finishes before or on delivery date given.
    Our Schedule should cover these cases and inform the no. of pieces to finish date wise to finally ensure ontime delivery.

    In case the capacity is less for distributing high qty UIDs, we have a provision for calling on contract manpower and add a 20 manhours capacity.


    these are the logics and i feel this can be made. But! I am clueless about how to do it.. Closest reference work I have is this.. https://chandoo.org/wp/formula-forensics-no-031/
    Thanks Beforehand for your help!

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Dhawal Sharma
    Question:
    When those UIDs list has made?
    How many days before Del date should know?
    How many days could any UID last?
    ...
    I did one TST-sheet...
    It don't look like Your 'Output-template' ... but it's possible to do too.

    1) Open file
    2) Select tst-sheet
    3) Press [ Do It ]
    4) You'll see how those UIDs could share that max 280 manhrs per day.

    > Ideas? ... Questions? ... Answers?

    Would You like to get 'Output' as in Your example?
    ... someway 'tst'-sheet would show many things too ;)

    Attached Files:

  3. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    Thanks for your help! Much appreciated.

    Question:
    When those UIDs list has made?
    --> This is open Order position taken in 31st Oct End of day. The execution can start from 1st Nov.
    I See that the macro assigns work before 1st Nov also. I understand that the capacity do not allow the work to be completed if we start on 1st NOv.
    However from business point of view I would like to find out what is the capacity I should be aiming for for next few days... to ensure that (a) Delivery dates are met + (b) I can do that by starting work on 1st Nov (or whatever date I choose as starting point).

    How many days before Del date should know?
    --> The last piece of UID can be finished on the given Dellivery date. 1 day advance is not necessary.
    (I hope this is what you wanted to know)

    How many days could any UID last?
    - Any no. of days provided it does not cross Delivery date + we are able to start work on 1st Nov (or whatever date I choose as starting point)

    ...

    This worked like a charm! Thanks.

    Questions:
    1) As I mentioned that I need to plan execution from given stating point (1st NOv here), Can the solution provide me with a recommendation of capacity dispersed over days so that I can meet objectives.

    It can show capacity something like
    1st Nov: 280 + 20
    2nd NOv: 280 + 20
    3rd Nov: 280 + 20
    ... and so on till it normalizes back to 280.

    ABout capacity: It will difficult to suddenly increase capacity from 280 to say a 480 overnight. Hence I suggested to expand 20 each day and advise a plan accordingly.
    IF the 20 additional capacity do not help the situation, We can increase additional capacity to 60 but only after 7 days of starting point. ( it takes time to arrange contract force).

    and based on the new capacity a new plan can be suggested.

    2) Another point is that the execution team understands work in terms of pieces (while the manhours remains as basis of constraint). Can the plan be shown as pieces instead of manhours in Tst file. May be the manhours can then be prorated to give pieces with some rounding off?

    Thanks a Lot already for you help!

    Let me know if you have further query.
    Dhawal
  4. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Question:
    > When those UIDs list has made?
    ... any new UID to list means 'rush' somewhere ... means gotta calculate!
    > How many days before Del date should know?
    ... if new UID has 150Pcs / 147manhrs and del date is
    TOMORROW or next week ... humm?
    > How many days could any UID last?
    As these should solve backwards
    ... then even 1 ManHr per day would be okay ... hmm?
    = Del date 01-Jan-2020 / 350 ManHrs ... could last 350 days ... ouch?

    Answers:
    1) yes, from some day ... but gotta solve from the last day to some day
    ... I've an idea ... coming with next version...
    There could also add 'any number of ManHrs' for any days.
    2) How many ManHrs is one Pcs? eg 610Pcs ~ 744,2 ManHrs?
    So far normally 280ManHrs per day ... which will use for calculation.

    Question:
    Is that ManHrs like time ... like hours?
    If like 'normal hours' then, what kind of clocks do people in Mumbai use?
    I've used to use clock which has hours and minutes ... hmm?
  5. vletm

    vletm Excel Ninja

    Messages:
    4,636
    New:
    'tst'-sheet:
    1) You gotta select 'Your Target Date' below of [ Do It ]
    BEFORE press it!
    2) There is extra ManHrs-column for those extra hrs.
    You can add as many as needed! ;)
    You'll see needed number of those AFTER [ Do It ].
    3) You'll get sample 'report' after successful [ Do It ].

    Sample 'report'-sheet:
    If no date then check date above ( can see steps ).
    Psc ... shows those values 'my way' as long as I'll know how to solve those!

    My Missing Question:
    Are ONLY Sundays 'non-working-days'?
    ... sometimes somewhere are some other days too ...

    Attached Files:

  6. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    WOW! Thankyou!! I want to know & learn how you do all this so fast.

    Sample 'report'-sheet:
    Psc ... shows those values 'my way' as long as I'll know how to solve those!
    --> The report is already showing pieces in ratio of manhours. You have already covered this! or am i missing something here..

    My Missing Question:
    Are ONLY Sundays 'non-working-days'?
    You are spot on. There can be other holidays. It would be great if you can provide a column where I can fill those holiday dates and the Macro not assign any work on those days as well.
    (I did not mention that earlier to stick to core problem.)
  7. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    Responding to your questions:
    Question:
    > When those UIDs list has made?
    ... any new UID to list means 'rush' somewhere ... means gotta calculate!
    Yes if all capacity is utilized and new UID get added. It will be rush. Now the additional manhours feature will help me plan that! All thanks to you.
    > How many days before Del date should know?
    ... if new UID has 150Pcs / 147manhrs and del date is
    TOMORROW or next week ... humm?
    generally 2 days and above (upto 45 or so). if quantity is less it can be same day as well.
    Actually once I run this sheet. I would know where all I have scope to add new order. I ll fit the orders accordingly. If some urgent request by customer, I will now know how much manpower (and when) to add to accommodate that order.


    > How many days could any UID last?
    As these should solve backwards
    ... then even 1 ManHr per day would be okay ... hmm?
    = Del date 01-Jan-2020 / 350 ManHrs ... could last 350 days ... ouch?
    Theoretically YES. that is if My capacity was 01 only. and all my order will have super long lead time.
    So wont do that :)
    as of now the order can only last max of (X/280)+1 days. (X being the manhours needed for UID)



    2) How many ManHrs is one Pcs?
    Manhours vary style to style. A1 has different manhours conversion factor as compared to A2 or A3 and so on. Factors range from 0.8 to 1.3
    Do you find this relevant in any working? I ll share more info if required.


    Question:
    Is that ManHrs like time ... like hours?
    Yes Indeed. 280 Manhours is actually 35 people working for 8 hours every day.
    If like 'normal hours' then, what kind of clocks do people in Mumbai use?
    I've used to use clock which has hours and minutes ... hmm?[/quote]
    i guess we got the same clock!
  8. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Sample 'report'-sheet:
    Psc
    ... I guessed ...

    Are ONLY Sundays 'non-working-days'?
    ... guessed again ...
    (I did not mention that earlier to stick to core problem.)
    ... those would many times make huge challenges

    = one step forward, two steps backward, one step left, two steps right, two steps forward ...
    Could be solved as select(mark) 'non-working' dates ... gotta test!

    > When those UIDs list has made?

    ... but now, there should be ALWAYS extra ManHrs ... that won't be good!
    ... then 280 ManHrs is not enough!
    ... and there would be those other 'holidays' ... sick ... and so on!
    ... clear all those my marked and ... test Yourself!


    > How many days before Del date should know?
    With this 'solver' You cannot set order ... or only with Del date & UID!

    > How many days could any UID last?
    ...maybe this solutions would be more useful
    I could change it ... but then there would be ... hmm? ... many UIDs per day!


    2) How many ManHrs is one Pcs?
    If there would be CLEAR rule for that then it could calculate automatic needed ManHrs per UID ...
    but so far, there seems that every UID has own 'style' ... ouch!


    Is that ManHrs like time ... like hours?
    So why those ManHrs are not shown as 'normal time' with hours and minutes?
  9. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Dhawal Sharma
    'non-working-date'
    1) There is [Set Holidays]-cell.
    2) Select it - it'll change to 'GREEN'
    3) Select any dates one-by-one which are NOT working dates
    --- cell change to yellow
    4) to 'cancel' selection - select data again -- non-color-cell
    5) Repeat 3 as many times as needed
    6) Select [Set Holidays]-cell
    --- it'll change to non-color
    ( I tested to mark 05-Jan ... 11-Jan as 'non-working-dates' )

    Hint: You cannot set 'target-date' if 'GREEN' [Set Holidays]!

    Attached Files:

  10. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Dhawal Sharma
    ... still something won't match ... hmm?

    That previous version has some challenges ... You'll get fixed
    ... if You'll notice it ;)

    Question:
    Why count ManHours? ... 280hrs per day ...
    Why not count number of employees per day needed/used?
    > I tested ... and someway that would give values which I would need to see?
    Instead of add/get 1234 hrs ... add/get 154,25 employees (155) ;)
    ... as You've written each would do 8hrs per day.
    > Also ... there is possible to see those values as Hrs, Pcs or Days.

    OUCH! ... or how would I express this?

    I tested with those features ...
    Note: left side values are Man Work DAYS ( 1 day = 8hrs )

    Attached Files:

    Thomas Kuriakose likes this.
  11. vletm

    vletm Excel Ninja

    Messages:
    4,636
    I added 'Solver' ...
    Screen Shot 2019-01-06 at 19.01.28.png
    ... with it You could get 'automatic' those Extra Man Days.
    It gives 'the best' values ;)
    ... and after that
    it's possible to adjust those ... maybe a bit softer ... or not?
  12. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Dhawal Sharma
    with 'Solver',
    which can de/activate from cell behind [Do It]-button.

    Attached Files:

  13. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    Hi @vletm
    Thanks a lot!

    However, I get below error after pressing Do-IT in the last two versions.
    upload_2019-1-8_17-9-1.png

    Your sheet in #9 solved my requirements already. I wanted to see solver work but this error is coming.
    "Mismatch type"

    ---------------
    That previous version has some challenges ... You'll get fixed
    ... if You'll notice it ;)
    >> Well not able to notice o_O .. pls help me with what it is.

    Why count ManHours? ... 280hrs per day ...
    Why not count number of employees per day needed/used?
    >> We can have employees working for variable hours, not necessarily 8 hours every day, Half day, sick leave, extra time etc. So calculation in hours is much more convenient. Also the ops team is comfortable with this style.
    So its better to keep the manhours as validation and show in left most columns.

    How many days before Del date should know?
    With this 'solver' You cannot set order ... or only with Del date & UID!

    ->> I will try to see free manpower from the scheduler and allocate new order where ever i find space. made a column to show you in the attached file.
    Hope this address your question. Do you see a better way of placing new orders.

    >> manhours with hours and minute - We are used to decimal format of manhours but hours and minutes format is also fine.

    >> I require the report to be for all the dates and order in the data. (currently it comes for 7 to 8 days only.

    >> If the orders are less. I made data changes to create that scenario in attach file.
    Then the work will be assigned with gaps. and daily productivity will not be met.

    My suggestion: Keep the output as it is and create another report/ plan where if the capacity is not completely utilized it pulls order from future dates as per current output plan to make up for that days capacity target.

    Let me know for any queries.

    thanks again.

    Attached Files:

  14. vletm

    vletm Excel Ninja

    Messages:
    4,636
    'Type Mismatch' ...
    1) Did You select 'target date'?
    2) Did You get anything else information?


    That previous version has some challenges ...
    I would continue with newer version which has more features (after find typo).

    Why not count number of employees per day needed/used?
    Yes ... There should be possible to tell how many could work!
    not something 'fixed' value as Your files has been!
    Or at least, there should be 'MINUS something' is it Days/Hrs no matter!

    (( I have other solution for this too ... like calendar which would show those values ))
    ... You modified to show 'available manhours' ... that won't help! ...sorry!
    ... ... normally, there are nothing left! = all used!

    Also the ops team is comfortable with this style ... ;)
    ... then even those should learn something new!
    ... ... also those 'left side' values could shown with 'any format'
    ... ... ... even ManSeconds

    I will try to see free manpower from the scheduler and allocate new order where ever i find space. made a column to show you in the attached file.
    Hope this address your question. Do you see a better way of placing new orders.

    Not answer to my question ...
    ... but new orders should add in the end of 'Order data'-sheet and press [Do It].

    I require the report to be for all the dates and order in the data. (currently it comes for 7 to 8 days only.
    Hmm... hmm... really ALL?
    If there would be 'only' 20UIDs then maybe useful and 'new orders' would come 'only' every second year ;)
    ... but check PDFs...

    My suggestion: ... = Yours ... not mine!
    hmm ... I didn't get a bright visual or any image of that :(

    My suggestion: ...
    Try to give more information of that Error.
    Newer version ... would be better!
    eg then there even the ops team could select different views...
    >> 1st likes to see with HOURS...
    Screen Shot 2019-01-08 at 15.53.52.png
    >> 2nd likes to see with PCS
    Screen Shot 2019-01-08 at 15.54.07.png
    >> 3rd like to see with DAYS
    Screen Shot 2019-01-08 at 15.54.21.png

    ... I cannot 'fix', if I cannot get more details!

    Attached Files:

  15. vletm

    vletm Excel Ninja

    Messages:
    4,636
    Newer version ...
    I added some checks ...

    Attached Files:

    Last edited: Jan 8, 2019
  16. vletm

    vletm Excel Ninja

    Messages:
    4,636
    If You don't have 'system' which would give employees 'situation',
    then one used solution (now very draft) is below:
    Screen Shot 2019-01-08 at 19.33.43.png
    This would give employees 'working hours' for calculation!
    If 'empty cell' then normal hours = 8hrs
    If 'number' then number hours.
    if 'something else' then no hours.
    Left values are possible daily working hours...
  17. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    'Type Mismatch' ...
    1) Did You select 'target date'?
    -- yes I did.
    2) Did You get anything else information?
    - No other error.

    Vletm suggestion: ...

    Try to give more information of that Error.
    Error was straight forward dialog box- Type mismatch. Nothing else.
    eg then there even the ops team could select different views...
    This is great!
    The new file runs however. But the report do not give values of Pcs and hours in report sheet.

    Also I Insist on making the report for all dates.



    Why not count number of employees per day needed/used?
    1. My Manhours required is calculated after considering idle time as well (refreshment, bathroom breaks etc along with actual work hours). So i can aim for full utilization of available manhours against manhours of work needed.
    2. Its easier for me to know how much manhours should be present. Eg. based on work i need 16 manhours. This can be via 4 half day employees, or 2 full time employees. or single employee working 2 shifts.
    So for me manhours is absolute and no. of employees is a derived number.
    I hope i put my point clear here.



    ... You modified to show 'available manhours' ... that won't help! ...sorry!
    This i did to see if I have scope to add more work and Take more orders with shorter lead time.
    ... ... normally, there are nothing left! = all used!
    This depends on my season and order flow. I gave new data (off season simulation) in last post which suggests my capacity is more than orders received. In this case spare manhours would be available.

    ... ... also those 'left side' values could shown with 'any format'
    Which i again insist to be kept as Manhours :):):)


    Dhawal's suggestion:
    hmm ... I didn't get a bright visual or any image of that :(

    In attached sheet "TST" refer EE163. This work allocation will translate to below in report:
    upload_2019-1-9_10-28-37.png

    This means I ll utilize 119 manhours OUT of 280 manhours.
    IF I do so, my UID will get completed on time. No doubt.
    BUT... For that day I will not utilize my capacity.. (280 - 119) = Spare capacity.

    SO to utilize capacity, I will have to take up work which is assigned for 31st Nov in the TST sheet on 30th Itself. ( Pull Future orders)
    I am suggesting already create that plan for user.

    How i see it... Report get created for all dates and order --> Then A new "DO IT" button is available --> when we press a second separate report gets created taking first report as Basis. --> The second report will Pull future orders If there is any spare capacity in any of the work days.

    Was this helpful? Clear? :p :p
    Let me know.

    [/quote]

    Attached Files:

  18. Dhawal Sharma

    Dhawal Sharma New Member

    Messages:
    7
    Let me try to add context as well.

    As you would have guessed, we are manufacturers. SO ontime supply is of great importance. Currently everything is tracked using one final Delivery date.
    In case order is a bulk qty order, the order needs to start early. for that we need the date when the order working should start without fail (considering current capacity and other orders).
    THIS is what your solution is already providing.

    Operationally.
    We have 35 person on payroll. 38 actually, but we are modelling considering 35 because of the leaves, ad hoc work etc.
    Since I have 35 persons = 280 manhours. I want to use it completely or else my per piece cost will increase.

    Hence I want to pull future orders in case we observe spare capacity on certain days. For this I am requesting the "Second report".
    This will happen mostly in NON- season time. IN season time work will definitely be more and we will not have spare capacity.

    New order add
    When buyer approach us we try to see if we can meet his demands. No, of pieces to be provided by specific date.
    For This I need to have manhours available who will complete the work. WHich I was trying to see by creating a spare manhours column.

    IF spare capacity available - I take order
    > (Also if spare capacity available in 1-3 days of requirement, I go back to buyer for extension of that many days.)

    If not available - I can 1) Add manpower in plan (contract workers) or (2) Give order to my sister unit for execution.
    Of course option (1) is more beneficial for business.

    So I see the manhours "Booking" position every time order need to be added. orders come almost everyday, so i have to refer this everyday.

Share This Page