Circular Reference - calculating average

RdenUijl
RdenUijl
in Modeling

Hi all,

 

I hope someone can help me, because I'm currently struggeling with a particular use case.

 

I'm creating an extrapolation for a client, and they want to use a combination of 3 extrapolation methods:

Month on Month (MoM), Year on Year (YoY) and a Manual Run Rate (Manual). Each of these methods are given a weight, which will determine what the end result should be (e.g. 50% MoM, 30% YoY and 20% Manual).

 

MoM is going ok, and is working as intended. The YoY uses a monthly delta and then increases/decreases the final end resul of 1 year ago with this delta, which is also going fine. Due to the lagged data used Anaplan accepts this.

 

The issue lies with the Manual part. This method uses a way to get to the End of year Runrate, which gives a baseline for the extrapolation. But then it uses a part of the YoY. It gives an additional weight to the YoY result (already calculated) and then looks for the difference of that month's value of the YoY and the average YoY result of the corresponding year. It will then add/detract this result from the baseline.

 

To give an example for this last part:

YoY:                              100, 100, 50, 100, 150

Difference to average       0,   0,   -50,   0,  50

Baseline of Manual       125, 120, 100, 120, 125

Manual end result         125, 120, 50, 120, 175

 

But when I try to calculate the average of the year using an average as summary and a lookup it will give me a circular reference error.

 

I hope that someone can help me.

 

Cheers,

René

Tagged:

Answers

  • VSekar
    VSekar

    Hi RDenUijl, 

     

    I am not sure that I got your query fully. But in general, to avoid the circular reference, you can use an import button. 

     

    For Example:

    Scenario 1:- without circular reference:

    A = 1

    B = 2

    C= A+B .i.e., 3

    Scenario 2:- with circular reference: Pass1

    A = C i.e., 3  (Referred from the first pass assuming the data as in Scenario 1)

    B = 2 

    C = A+B. i.e., 5.

    Scenario 2:- with circular reference: Pass2

    A = C i.e., 5  (Referred from the second pass assuming the revised data as in Scenario 2)

    B = 2 

    C = A+B. i.e., 7.

     

    So, in scenario 2, I will create an import process to import the data from line C to A. And then I will include that import to a process for the number of circular reference calculation. Generally keep it to 10 imports in a single process and that will suffice in most of the cases. 

     

    But this has an manual intervention and user need to run that import action once to see the calculated result. 

     

    I hope it might helps to some extent.  

  • RdenUijl
    RdenUijl

    Hi VSekar,

     

    Thanks for your reply. 

    I'm aware that an import is a solution, but that would be my last resort.

    The idea is that the client can change the weights and other parameters on the fly to do scenario analysis.

     

    So unfortunately this isn't a real viable option.

     

    Cheers,

     

    René

  • bram.kurstjens
    bram.kurstjens
    Hi René

    Im not completely sure where the circular reference comes from?
    It looks like your manual end result formula is:
    Baseline of Manual + YoY - YoY[LOOKUP: Year]
    How is the baseline of manual calculated?

    Perhaps it would be good to share a screenshot of the blueprint of your module + the formula that you try to insert that gives a circular reference for us to understand your issue better.


    KR,

    Bram Kurstjens
  • RdenUijl
    RdenUijl

    Hi Bram,

     

    I've included the excel export of the blueprint

    It is a test module to simplify the issue.

     

    The baseline (End of year runrate) itself doesn't matter as this calculation goes fine.

    The issue lies in the Manual line item and Total Value line item.

     

    The average YoY line item is just a reference to the line item above and is summarized as Average

    The line item below takes the year value in the months.

     

    I now want to add this result to the Manual line item, which give my error.

     

    The TEST - No Time module just has a bunch of percentages used for the weights.

     

    Thanks for you help!

     

    Cheers, René

  • DavidSmith
    DavidSmith

    If the end result is used to calculate the YoY which is then used to calculate the end result, then that is a true circular reference

    However, what I often try and do in cases like this is keep all of the component parts of the resulting calculation and put them in the base, rather than use end result.

     

    So you could calculate the YoY by using the Baseline + the adjustment, rather than the end result (which is base + adj)

     

    That way you effectively have two parallel calculations, that deliver the same result

     

    give that a go

    David

  • RdenUijl
    RdenUijl

    Hi David,

     

    Thanks for your suggestion!

     

    What I tried was creating a new line item which takes the End of Year Runrate and adds the difference of the average. I then tried to reference to that in the Manual line item, which still gives the same circular Reference.

     

    I attached a screenshot of the blueprint to this post. 

     

    Is this what you meant, or did I misunderstand?

     

    Cheers,

     

    René

  • DavidSmith
    DavidSmith

    Average uses YoY and is also used in Manual

    YoY uses Manual

    hence this ends up as a circular

     

    So my point it try and break the dependencies

     

    Instead of Average YoY using YoY, make it = LAG(Total Value, 13, 0) * (1 + YoY delta)

    In Manual try and replace Average YoY with LAG(Total Value, 13, 0) * (1 + YoY delta)

     

    Do you see what I'm getting at?  You have to break the dependencies and run the calculations in parallel

    See if you can produce the same results by using the initial components of the calculation rather than the resulting calculations

     

    David

Categories