none
Formula to help production planning - Number of machines RRS feed

  • Question

  • Hello,

    I have a problem in a file about production planning.

    I have only 8 machines and a 5 days week to plan my production.

    When I plan the first two orders (point 1 in the picture) I am using the 8 machines but I am only using 3.57 days. So, I can still put some more orders to fill the remaining days...

    And the problem is here. As you can see in point number 2 of the picture I am planing the next order 200 with 2 machines but the formula in cell T9 is assuming that I dont have any more machines. That is not true, I have 8 machines because the machines from point 1 in the picture already finished their order.

    This is my problem I want to know the number of machines that I can use until i fill the 5 day week.

    In this case when I was going to start the 3rd order I would have 8 machines available to plan because the first and second order were finished already.

    Did I make myself clear?

    https://1drv.ms/x/s!AnTTDTm5U5RshRD2EeUsUY6Xkfmo?e=Ywchcy

    Thank you

    JPMR


    Jose

    Friday, July 19, 2019 4:15 PM

All replies

  • Hi Jose,

    Did the 8 machines work at the same time? For example Nike 1234 and 4567 need 4 machines for each lotes, did the 8(4+4) machines work at the same time? Or you will finish Nike 1234 first and then 4567 second? Or they are based on package date?

    In this case, it is recommended that you can uses this formula of cell T9 “=IF(8-SUMIF(R12:R610,">0",T12:T610)<0,"No more machine",8-SUMIF(R12:R610,">0",T12:T610))”. 

     

    In this formula you can use the value of Days to deliver to determine if the order is complete and calculate the number of machines available. If the value of Days to deliver greater than 0, then the order is finished.
     


    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Monday, July 22, 2019 8:43 AM
    Moderator
  • Hello Emi Zhang,

    First of all thank you for your answer and the time that you spent with my problem.

    I have some questions about what you said.

    - What happens when I have days to deliver under 0 (this means that they are late in production)?;

    - When I have days to deliver equal to 0 what happens? (with your formula I can put as many machines as I want until fill the 5 days);

    Answering your questions:
    Did the 8 machines work at the same time?
    For example Nike 1234 and 4567 need 4 machines for each lotes, did the 8(4+4) machines work at the same time? Or you will finish Nike 1234 first and then 4567 second? Or they are based on package date?

    - All the machines are independent from each other. Nike 1234 and 4567 can work at the same time because is 4 machines for each lotes and we have 8 machines package date is important and the time that it takes to produce each lotes is also importante. 

    Can you just clarify this to me please?

    Thank you!

    JPMR


    Jose


    • Edited by JPMR Monday, July 22, 2019 3:26 PM
    Monday, July 22, 2019 2:01 PM
  • Hello Emi Zhang,

    I really liked your solution to my problem.

    Can you just answer the questions that I made you on the reply above, please?

    Thank you

    JPMR


    Jose

    Tuesday, July 23, 2019 7:59 AM
  • Hi,

    First I want to explain the purpose of using Days to deliver: it is used to determine whether the order is completed. If the order is completed, the machines used for the completed orders can be used to produce new orders. Then the number of available machines will change, even become 8.

    If you look at it from your actual situation, the Deliver day is not really an order completion time, then the modification of formula for cell T9 is invalid.

    Then I answer the questions you asked, I hope I can help you:

    Q:< - - - What happens when I have days to deliver under 0 (this means that they are late in production)?
    A: It means the orders were finished, such as -175, the orders has been delivered for 175 days.
    The 4 machines used for producing Nike 1234 and the 2 machines used for producing Adidas 7890 can be used for new orders.

    Q:< - - - When I have days to deliver equal to 0 what happens (with your formula I can put as many machines as I want until fill the 5 days)?
    A:It also means the order is finished, like the value under 0. Then the number of available machines will be increased.

    If necessary, the formula of cell T9 can be ” =IF(8-SUMIF(R12:R610,">=0",T12:T610)<0,"No more machine",8-SUMIF(R12:R610,">=0",T12:T610))” .  When the Days to deliver equal to 0 ,the order is not completed, the number of available machines remains the same. 

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, July 30, 2019 9:04 AM
    Moderator