Stock Re-balancing between Stores

Nam Dao
Nam Dao
in Modeling

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
    Stock Re-balancing.xlsx
    Stock Re-balancing.png

Tagged:

Best Answer

  • gaurav
    gaurav
    Answer ✓

    Hi @Nam Dao, You need to add two variables, first which will help you give the stocks to the requesting stores and second is just to set the count with Max value as 1(which will help you prioritizing the transfers from one supplying store then look for the other store).

    Create a module to set the objective and bring the value line item only from Variable module(Your objective will be minimize the Value line item).

    Cheers!

Answers

  • gaurav
    gaurav

    Hi @Nam Dao I feel like you would need a lot of hardcoded formula's here to get the solution. I would suggest you to use Anaplan optimizer for your problem, it can be solved and you will not compromise with the performance.

  • Nam Dao
    Nam Dao

    Hi @GauravKumar, I tried to look into Excel Solver before looking into Anaplan Optimizer. However, it seems to me that the objective is not to get the optimal result, but instead, this problem requires how the stock is re-distributed across the stores, fulfilling one store before moving on to the next based on their priority (Ranking).

    Whether it is being set up with Excel Solver/Anaplan Optimizer or with hard-coding workarounds, could I pick up your brain (or anyone's) on this? Appreciate your help!😃

Categories