1 Reply Latest reply: Oct 31, 2017 9:21 PM by Luis Madriz RSS

    How can I subtract from a daily total sorted by a running total?

    Tiffani Leslie

      I need to create an application that has to do the following:  Allow the end user to simulate projected inventory levels by date and by part number if a machine or machines were taken down for a period of time.

       

      Here are some challenges that I have ran into:


      • Consumption by part number is available, but supply is not. 
      • Capacity on the machines is not infinite.  There are X amount of available hours each day based on hours per shift and number of shifts.  I was not given a manufacturing schedule of the parts, so I am to assume the part numbers will be made based the greatest need.

       

      So sum it up, I have to create a model that has these assumptions built in:

       

      1. If the Part Number belongs to  Program 'X', its takes 2.5 hours to make 1 piece. Otherwise it's 2.0 hours. Let's call it vManuTime.
      2. The supplier is not going to make over than what is demanded in total.
      3. The supplier is going to make the parts based on the greatest need which can be interpreted as: Part Numbers with the lowest inventory levels required  in the near future.
      4. The supplier is going to make the parts in set lot/batch sizes.
      5. Not all of the hours on the machines have to be used but you cannot use more than the available machine hours each day.

       

      I gave up on enabling a user to be able to select a machine and date range to "take down" to simulate inventory levels in the UI, so I created an excel file with dates and machines hours that they can zero out the values and reload the app if needed.

      Capture.PNG 

       

      I have a table created that has, Date, Part Number, and Demand Qty.  I am stuck on how to assign machine hours per part based on the assumptions above.  I am essentially trying to do what the example excel table is doing by allocating machine hours(Supply=Machine Hours *vManuTime) to each part number based on lowest to greatest inventory levels (Column G) each day (Column A) without making more than needed in total (example Row 8).

       

      Capture.PNG