Circular Reference - calculating average

Options
RdenUijl
RdenUijl Member, ALL USERS, Partner, Certified Master Anaplanner, Certified Model Builder Posts: 5 Certified Master Anaplanner
in Planning & 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:

Comments

  • RdenUijl
    RdenUijl Member, ALL USERS, Partner, Certified Master Anaplanner, Certified Model Builder Posts: 5 Certified Master Anaplanner

    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é

  • RdenUijl
    RdenUijl Member, ALL USERS, Partner, Certified Master Anaplanner, Certified Model Builder Posts: 5 Certified Master Anaplanner

    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é

  • RdenUijl
    RdenUijl Member, ALL USERS, Partner, Certified Master Anaplanner, Certified Model Builder Posts: 5 Certified Master Anaplanner

    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é

Categories