Advice for Solution: Rolling Update of Inventory based on conditions - MultiRow Multifield

[Kaseykreid]

Hi Community,

I'm stuck on a problem where I want to update the monthly Inventory for a number of products in the below dataset when the Inventory is below the Inventory Reorder Point in a given month. The Inventory value would be updated in the row that is X amount of months away (X = Lead Time (months) from the first instance the Inventory falls below the Inventory Reorder Point. 

Below is the example dataset with notes that further explain the problem.

Product	Demand	Lead Time (months)	Inventory Reorder Point	Inventory Reorder Quantity	Year Month	Inventory	Notes
Example Product 1	2400	6	33526	17190	2023_11	39004
Example Product 1	2400	6	33526	17190	2023_12	36248
Example Product 1	2400	6	33526	17190	2024_01	33492	Inventory falls below Inventory Reorder Point
Example Product 1	2400	6	33526	17190	2024_02	30736
Example Product 1	2400	6	33526	17190	2024_03	27980
Example Product 1	2400	6	33526	17190	2024_04	25224
Example Product 1	2400	6	33526	17190	2024_05	22468
Example Product 1	2400	6	33526	17190	2024_06	19712
Example Product 1	2400	6	33526	17190	2024_07	16956	I want the Inventory to be updated here based on what the lead time is ( 6 Months in this instance). The inventory would be updated with the Reorder Quantity: Inventory + Reorder Quantity
Example Product 1	2400	6	33526	17190	2024_08	14200	The rows afterwards would be updated based on this formula: Previous Row Inventory - Demand.
Example Product 1	2400	6	33526	17190	2024_09	11444
Example Product 1	2400	6	33526	17190	2024_10	8688
Example Product 1	2400	6	33526	17190	2024_11	5932
Example Product 1	2400	6	33526	17190	2024_12	3176
Example Product 1	2400	6	33526	17190	2025_01	420
Example Product 1	2400	6	33526	17190	2025_02	-2336
Example Product 1	2400	6	33526	17190	2025_03	-5092
Example Product 1	2400	6	33526	17190	2025_04	-7848
Example Product 1	2400	6	33526	17190	2025_05	-10604
Example Product 1	2400	6	33526	17190	2025_06	-13360
Example Product 1	2400	6	33526	17190	2025_07	-16116
Example Product 1	2400	6	33526	17190	2025_08	-18872
Example Product 1	2400	6	33526	17190	2025_09	-21628
Example Product 1	2400	6	33526	17190	2025_10	-24384
Example Product 1	2400	6	33526	17190	2025_11	-27140
Example Product 2	980	3	8744	2882	2023_11	13603	The logic restarts here when an new product is being evaluated.
Example Product 2	980	3	8744	2882	2023_12	12626
Example Product 2	980	3	8744	2882	2024_01	11649
Example Product 2	980	3	8744	2882	2024_02	10672
Example Product 2	980	3	8744	2882	2024_03	9695
Example Product 2	980	3	8744	2882	2024_04	8718
Example Product 2	980	3	8744	2882	2024_05	7741
Example Product 2	980	3	8744	2882	2024_06	6764
Example Product 2	980	3	8744	2882	2024_07	5787
Example Product 2	980	3	8744	2882	2024_08	4810
Example Product 2	980	3	8744	2882	2024_09	3833
Example Product 2	980	3	8744	2882	2024_10	2856
Example Product 2	980	3	8744	2882	2024_11	1879
Example Product 2	980	3	8744	2882	2024_12	902
Example Product 2	980	3	8744	2882	2025_01	-75
Example Product 2	980	3	8744	2882	2025_02	-1052
Example Product 2	980	3	8744	2882	2025_03	-2029
Example Product 2	980	3	8744	2882	2025_04	-3006
Example Product 2	980	3	8744	2882	2025_05	-3983
Example Product 2	980	3	8744	2882	2025_06	-4960
Example Product 2	980	3	8744	2882	2025_07	-5937
Example Product 2	980	3	8744	2882	2025_08	-6914
Example Product 2	980	3	8744	2882	2025_09	-7891
Example Product 2	980	3	8744	2882	2025_10	-8868
Example Product 2	980	3	8744	2882	2025_11	-9845

I would greatly appreciate any advice on the above. I tried using the Multirow and Multifield tools to achieve the solution I'm after but hitting a roadblock. 

[AndrewDMerrill]

There are some oddities in the data, that generate questions for me to better understand your use-case:

1. Where do your [Inventory] numbers come from because the formula you mention of subtracting the demand does not match for any of the rows, but what you are requesting would overwrite those values?
2. What happens (as is the case for Example Product 2) when the 3 month Lead time causes inventory to drop low enough that the reorder quantity is not enough to raise the inventory above the reorder point, does that month become the start of the next lead time calculation?
3. Does the lead time calculation need to be repeated until the end of the dates in the data set (i.e. there would be ~3 reorders for Example Product 1 before 2025_11)?

This looks to me like you would need a batch macro that feeds into an iterative macro.

[Kaseykreid]

Hi Andrew, 

Thanks for your response! Here are the answers to your questions. 

1.  The Inventory numbers were generated already from a separate process. This dataset is an example of the extracted data I'm working to transform and essentially correct the Inventory values. For more context, this dataset is meant to be an Inventory forecast.  What I'm requesting would overwrite the values and then continue overwriting the values after the first time the Inventory falls below the Inventory Reorder Point for each product.

2. Yes that's right. When the inventory drops below the Inventory Reorder Point again, that month will start the next lead time calculation. 

3. Yes the lead time calculation would need to be repeated until the end of the dates in the data set. 

[AndrewDMerrill · Accepted solution]

Here you are! A sample workflow that should do what you're looking for. Best wishes!

Main Workflow:

Batch Macro:

Iterative Macro:

[Kaseykreid]

Much appreciated Andrew! This helped me a lot! Thank you for your quick response!