Hi Anaplan Experts,
I'm trying to calculate the re-balancing of stocks from one store to another based on the logic below.
Step 1: Rank Supplying Stores based on their available stock to give (OUT)
Step 2: Rank Requesting Stores based on their needed stock to take (IN)
Step 3: Re-allocate available stocks from Supplying Stores (OUT) to Requesting Stores (IN).
We choose the Supplying Stores with the most stocks (Rank OUT =1) to give and send it to the Requesting Stores with the most demand (Rank IN = 1)
IF the amount is fulfilled for the first Requesting Store, THEN start fulfilling the second one until there's no more stores to send.
IF the amount of stocks from the first Supplying Store does not suffice, THEN get more stock from the next one until we have enough stocks or until there's no more Supplying Stores.
Please view my excel file for better visualization 😀
My question is:
Q1. How could we set up the logic in Anaplan for Step 3?
I struggle with matching the Rank OUT = Rank IN = 1, so they re-balance each other first before moving to the next accordingly.
Q2. If Q1 is successful, how could we calculate the how much each store needs to Take IN or Give OUT?
For example,
Store 1 with OUT = 10
Store 2 with IN = 5
Store 3 with IN = 3
Therefore,
Store 1 with Give OUT = 8, Take IN = 0
Store 2 with Give OUT = 0, Take IN = 5
Store 3 with Give OUT = 0, Take IN = 3
NOTE:
- IN & OUT were calculated beforehand from another module with Weeks as Time Dimension. Let's imagine we are considering only one specific week.
- For this module, dimension is CC List of SKU-Store. Therefore, Total IN and Total OUT of an SKU and Store were calculated in a line item (a workaround) instead of having a top level.
- For the excel file, all the columns headers are line items