Automatic Inventory Allocation Based on Customer BBD Requirements

[BartH]

Hi Team,

I was hoping that you can help me with a problem I am trying to solve. I would like to be able to match my forecast with the current on hand inventory position to see if future orders will fail based on a minimum best before date (BBD) per client and see what stock doesn’t meet the BBD and will be written off.

I am working with two simple data sets Forecast data and inventory data:

Item	Forecast Date	Order Quantity	Min Required Shelf life	Min BBD on ordering
A	1-Feb	100	220	09-Sep-21
A	8-Feb	0	220	16-Sep-21
A	15-Feb	100	220	23-Sep-21
A	22-Feb	50	220	30-Sep-21
A	1-Mar	0	220	07-Oct-21
A	8-Mar	80	220	14-Oct-21

This example I look at 6 weeks of forecast, the client requires to have 220 days of shelf life upon ordering therefore the Min BBD ordering is forecast date plus 220 days.

The second data set is an inventory ledger, each batch will have an BBD.

Item	Inventory	On hand	BBD
A	Batch 00002	50	29-Jul-21
A	Batch 00006	100	08-Aug-21
A	Batch 00003	60	07-Sep-21
A	Batch 00001	40	26-Nov-21
A	Batch 00004	70	15-Jan-22
A	Batch 00005	90	30-Jan-22

The end result should be two tables,

Item	Forecast Date	Order	Min Required Shelf life	Min BBD on ordering	Inventory	On hand	BBD
A	1-Feb	100	220	09-Sep-21	Batch 00001	40	26-Nov-21
A	1-Feb	100	220	09-Sep-21	Batch 00004	60	15-Jan-22
A	15-Feb	100	220	23-Sep-21	Batch 00004	10	15-Jan-22
A	15-Feb	100	220	23-Sep-21	Batch 00005	90	30-Jan-22
A	22-Feb	50	220	30-Sep-21
A	8-Mar	80	220	14-Oct-21

The end result should be the table above, where the on hand batches are allocated based on the lowest BBD that meets the Minimum BBD.

For the orders of 1-feb and 15-feb this shouldn’t pose a problem, but for the orders of 22-feb and 8th of march it should flag a out of stock.

Inventory	On hand	BBD
Batch 00002	50	29-Jul-21
Batch 00006	100	08-Aug-21
Batch 00003	60	07-Sep-21

The second table should indicate what stock can’t be allocated to orders and will need to be written off.

Thanks,

Bart

[clmc9601]

Hi @BartH,

I built an iterative macro which I believe outputs your desired results-- please see attached. I hope it helps!

[BartH]

Hi @clmc9601, 

Thank you very much for your help! I dived into the iterative macro and this is indeed what i was looking for.

One more question that i hope you can help me with. When I run the workflow the iterative macro creates a column "Iteration" in the inventory table which is joined with the "Order_OrderID" from the Orders Table.

This way the batches are linked to the order and the remaining is excluded, the macro stops after running 1 iteration therefore only the first order is checked. Is it possible to Continue with the second iteration (Order for 8-feb), run the sequence again and continue with the third iteration (Order for 15-feb) until the list is completed. Essentially, restarting the same macro with the output (Updated Inventory) that we just created?

Example of how Second and third iteration would run:

OrderID	Forecast Date	Order Quantity	Min Required Shelf life	Min BBD on ordering	Item	Inventory	BBD	Days to spare	On hand
1	1-Feb	100	220	9-Sep-21	A	Batch 00001	26-Nov-21	78	40
1	1-Feb	100	220	9-Sep-21	A	Batch 00004	15-Jan-22	128	60
2	8-Feb	0	220	16-Sep-21	A	Remains Blank since order Quantity is 0
3	15-Feb	100	220	23-Sep-21	A	There are still 10 units on hand from batch 4, and 90 from Batch 5 that meet the min BBD
4	22-Feb	50	220	30-Sep-21	A	Blank due to Out of stock
5	1-Mar	0	220	7-Oct-21	A

Thanks,

Bart

[clmc9601]

Hi @BartH,

One tricky thing about macros is that they only run as intended when outside the macro file itself. You'll have to run it from the yxmd file if you want all iterations. From within the yxmc (macro) file, it doesn't even run an iteration-- it just flows data through the tools. It coincidentally works due to the way I set up the data. The real macro won't run until you click run in the workflow. I joined based on OrderID so that each iteration will take the next order "in line". 

Can you try this (running it in the workflow) and confirm it works the way you want?

[BartH]

Hi @clmc9601,

Alright, so the macro does not loop the data it's just 1 flow and not an iteration. 

No sure if am understanding it correctly but I tried a couple of things without the desired result:

1. I ran the workflow as you initially sent just the yxmd file but his has the same outcome as going into the yxmc file and running the macro and than running the workflow.

2. Copied at work flow from the yxmc macro file into the workflow yxmd and connected it as if it where the macro.

Any suggestions more than welcome i have the feeling that we are pretty close to the answer.

Thanks,

bart

[clmc9601 · Accepted solution]

Hi @BartH,

Not quite-- from within the macro, it's just a data flow based on the templates (and because I set the templates the same as the data, it looks like it's working from within the macro. It actually does nothing different than a workflow when you're inside). From outside the macro, it does iterate! So extracting the tools removes the iterative capability. However, the iterative capability is necessary here. 

From a programming perspective, we are defining the function within the yxmc file. We are calling the function when we place the macro tool on a workflow yxmd file. The real power is in calling the function.

I found the issue-- it had to do with how I was ordering and recalling the batches. I removed the filter and it should work now! You'll want to run it from this screen in order to get the desired result:

 

[BartH]

Hi @clmc9601 

Thanks for the support here much appreciated.

I have some experience with VBA in excel so i get what your saying with calling the macro and that being two separate programs. 

The workflow runs perfectly now. I see that the filter has been removed, so this was blocking the irritations from happening 

Thanks for your support i am going to try to recreate this and other macro's to get the hang of it but this really helped. 

Cheers,

bart