Circular Reference - Supply Planning

HendrikDeCuyper
HendrikDeCuyper
in Modeling

Hi,

 

For a customer of ours we're building a Supply Planning model on daily basis. 

 

When trying to build the core module I'm running into a circular reference which I can not seem to solve.

 

Any help would be greatly appreciated. 

 

Relevant line items are shown in the screenshot below. 

 

HendrikDeCuyper_0-1584539296572.png

 

For the Suggested Reorder Quantity the formula should be to take the Reorder point - Ending inventory - MOVINGSUM(Scheduled receipts, 1, Supplier Lead Time) - MOVINGSUM(Planned Receipts, 1, Supplier Lead Time -1).

 

However the Planned Receipts are calculated based on the Suggested Reorder Quantity, which you will theoretically order. Formula goes as follows: OFFSET(Suggested reorder quantity, -Supplier Lead Time, 0).

 

I've tried a myriad of ways to workaround it but can't seem to make it work.

 

Hope anyone can help, glad to jump on a Teams or provide extra information where necessary.

 

Thank you!

 

Hendrik

 

 

 

Tagged:

Answers

  • anirudh
    anirudh
    Hi Hendrik,

    Can you please show the 'Referenced By' for the Line Item - Suggested reorder quantity
  • HendrikDeCuyper
    HendrikDeCuyper

    Hi,

     

    It's referenced by the Planned Receipts.

     

    HendrikDeCuyper_0-1584540235643.png

     

  • anirudh
    anirudh

    Great!

    But I'm still a little confused, what is the formula for Planned Receipts? Is it the one in the original screenshot or is it: OFFSET(Suggested reorder quantity, -Supplier Lead Time, 0)

     

    Can you first change the formula in Planned Receipts to OFFSET(Suggested reorder quantity, -Supplier Lead Time, 0)

    and then apply

     

    Reorder point - Ending inventory - MOVINGSUM(Scheduled receipts, 1, Supplier Lead Time) - MOVINGSUM(Planned Receipts, 1, Supplier Lead Time -1) to Suggested Reorder Qty

     

     

     

  • HendrikDeCuyper
    HendrikDeCuyper

    Hi,

     

    The full formula for Planned Receipt is as followed.

     

    IF OFFSET(M Supply Plan Custom Days.'Days you Order this SKU 0/1'[SUM: M Custom Day Mapping.Standard Day Running], -'M SYS - SP7 - SKU / DC / Supplier'.lead time, 0) = 1 THEN OFFSET(Suggested reorder quantity, -'M SYS - SP7 - SKU / DC / Supplier'.lead time, 0) ELSE 0

     

    As in the model we have an Ordering Calendar which is the 'Days you Order this SKU', therefore you have to look back if Today - Supplier Lead Time would be an Ordering Day than we would have Planned Receipts. 

     

    Hope this clarifies, thanks for helping!

  • anirudh
    anirudh

    Thanks, I'm guessing Planned Receipts isn't what is causing the circular reference.

    Can you share the formula and referenced by for Scheduled Receipts?

     

  • HendrikDeCuyper
    HendrikDeCuyper

    Hi,

     

    This comes from out of a data module. 

     

    Good to know is that the Supply Plan is on Dynamic time (List created with only working days)

     

    HendrikDeCuyper_0-1584543037203.png

     

    Would you prefer jumping on a WebEx or how can I help you to solve this together?

     

    Thanks!

     

     

     

     

Categories