Replenishment modeling with circular reference

tingtingxia

Dear Anaplan Community,

I am facing a circular reference issue modeling replenishment calculation. For this specific client, their trigger condition to place an order is to evaluate below condition:

If I do not place an order today, will my projected end on hand (EOH) on this Item's ETA date fall below this item's safety stock? If yes, then I do need to place an order today to avoid such situation.

For example, if I have an item with lead time 3 days, then I am evaluating its projected end on hand in 3 days on daily basis to see whether I need to place an order on a specific day.

I have attached an Excel example for your reference.

The formula go like:

Beginning on Hand (BOH) = Loaded on hand (if it is Today) or previous EOH

On order from Oracle: loaded data from Oracle

New on order = Offset(Net Reorder Qty, Lead Time)

EOH = BOH + On order from Oracle (Orders placed and expected to arrive) + New on order (Anaplan suggested new orders offset by its lead time) - Demand

ETA EOH < SS? : Order triggering condition stated above

Net Reorder Qty (Net ROQ) = SUM of demand over lead time days after its arrival + ABS gap between BOH and SS on ETA date

You can see that there is a circular reference here where by my Net ROQ goes into on hands then goes into its recalculation again. In excel I am separating the steps so that I won't get circular reference error. But how I can model this efficiently in Anaplan? Any expert can shed light on this one? Much appreciated!

Replenishment Engine Logic.xlsx

ManjunathKN

Hi @tingtingxia

Can you show me an example (Blueprint and lineitems) in anaplan and also the error message.

Regards,
Manjunath

tingtingxia

Hi @ManjunathKN

Thanks so much for reaching out. Please refer to our blueprint export from the calculation module and those line items highlighted in yellow are the ones involved in the calculation.

In essence, the circular is caused by below flow and I also screenshot the error message for your reference if I offset calculated net re-order qty directly to become planned receipts.

RPL03.01 Replenishment Calculations - Tier1 (1).xlsx

Let me know if you have further questions around this and I'm looking forward to hearing from you. :)

Cheers

Tingting

ManjunathKN

Hi @tingtingxia

Let me know if the below solution helps you.

Firstly, I am a bit stunned of inventory replenishment at the day level. I have changed a couple of line item names as per my understanding of Materials requirements planning.

Net Reorder qty - Shortfall on inventory
New on Order - Reorder qty.

I had split a lot of line items just for clarity.. There is no need to split line items so much..

Grid view -

Blue print view -

Formulas that are not visible in blue print view

Beginning on Hand - IF Current Beginning inventory date boolean THEN Beginning Inventory current day ELSE PREVIOUS(EOH)

Reorder qty - IF Replenishment day THEN ABS(PREVIOUS(Beginning on Hand) + PREVIOUS('On Order/Open PO') + PREVIOUS(Reorder qty) - PREVIOUS(Demand) - Safety Stock) + MOVINGSUM(Demand, 1, 3) ELSE 0

Shortfall on Inventory - IF 'ETA EOH < Safety stock' THEN ABS(LEAD(Beginning on Hand, Lead Time, 0) - Safety Stock) + LEAD(Demand, Lead Time + 1, 0) + LEAD(Demand, Lead Time + 2, 0) + LEAD(Demand, Lead Time + 3, 0) ELSE 0

Let me know if you have questions.

Regards,
Manjunath

ManjunathKN

Hi @tingtingxia

Please find the below solution automating the ETA EOH.
Solution is bit complex, I need to redesign the process to eliminate the BOH looping.
Let me know if you need any explanation.

MRP - TingTing.xlsx

Regards,
Manjunath