Help in determining fulfillment date & remaining inventory

[yogi_789]

I have been trying to solve this but hitting a wall.

I have order data that has order date as well as SKU's produced each week in columns as dates.

I want to find out the order fulfillment date i.e. if the Qty needed was 20 and the production in 2nd week (5/23/2022) was 18 and 6/6/2022 was 45 then qty 18 was fulfilled from 5/23/2022 and remaining 2 from 6/6/2022. Therefore, 6/6/2022 is the fulfillment date. Also, the remaining inventory is total production on all dates - qty needed.

Note - Different orders can have similar SKUs

See attached

Thanks!

[MatthewO]

@yogi_789 I believe you could get to a solution by transposing the data and using running totals. See the attached workflow for reference.

[FilipR]

Please see my solution attached.

[kathleenmonks]

Hi @yogi_789 see the attached workflow and screenshot to find both the fulfillment date as well as the remaining quantity in stock. 

[yogi_789]

Thanks all! The solutions posted do not reduce the reserves size. e.g., if n units were allocated to SKU 1 on xyz date then those n units should not be available to the next SKU. So what will happen eventually is that if 200 units for a SKU in one order are needed and 200 are available on 5/16/2022, then the fulfillment date would be 5/16/2022. If next order requires 50 units of same SKU then the next available date should be next date on which 50 or less production is available (as 200 produced on 5/16/2022 are already used in a previous order)

[kathleenmonks]

Hi @yogi_789,

See the attached workflow for an additional few tools that create a running total of the stock after an order is fulfilled.