How to aggregate the "same" month data from different years (Seasonality Calculation)

PopCC
PopCC
in Modeling

Dear all,

 

We have a scenario which is to calculate the seasonality % based on the past 3 years' actual, and the seasonality is applied for the planing of the next four years equally. For example:

 

FY18, January (Seasonality %) = FY19, January (Seasonality %) = FY20, january  (Seasonality %) 

= ( FY14, January + FY 15, January + FY16, January ) / FY14 + FY15 + FY16

 

And another requirement is that users can exclude certain year (e.g. exclude FY 15 due to irregular data) for the seasonality calculation.

 

I found it very difficult because I couldn't find any functions to addup all the same month from different years and also define from/to (3 years ago to last year). I implemented it by using functions like LAG, TIMESUM, and adding lots of lineitems, but not easy for maintainance. Is there any easy method or idea to achieve this?

 

Thanks in advance

 

I attached the calculation image, please refer to it.

 

 

Tagged:

Answers

  • PaulRitner
    PaulRitner

    Hi,

     

    The attached file is password protected.

     

    Notwithstanding, if there are many line items you want to calculate historical seasonality % for (for example, multiple revenue lines or certain functional expenses/headcount), I'd consider using a line item subset from the source modules(s) and then calculate seasonality using a single line item in a subsequent module.  If all of the data for the target years is stored in native anaplan time, then perhaps 3 line items can do the trick:  First line item to Collect(), a 2nd line item would aggregate your 3 years of data, and a 3rd line item could calculate seasality based on the 2nd line item as... 3YrAmount/YearValue(3YrAmount).  

     

    You can include multiple source modules in the Line Item Subset definition, if the data you need seasonality for is located in more than one module (for example, Revenue in some level of detail, as well as OPEX in some level of detail).  This way, all seaonality drivers are in once place.  ...or you could separate them if it makes more sense.

     

    Please let me know where you end up!

     

    Cheers!

  • DavidSmith
    DavidSmith

    Hi

    Assuming the calcs are run on lists (not line items), the attached is a solution based on time mappings

    I've also assumed that the Current Period is utilised to determine the first forecast period

    1. Time Mappings Module

    I used this type of module all of the time; yes some of the calcs are a little involved, but it's a "one stop shop" for all sorts of mappings that you can use throughout the model.  The trick is to make the forecast months always use Jan17 - Dec17.  This is done through the dynamic variable for the Year Lag

     

    2017-12-13_10-05-42.png

    2017-12-13_10-07-09.png

     

    2. Calculate the Seasonality

    You use the various month lags from the time mappings module to add Jan -1, Jan -2, Jan -3 etc.  The total of the 3 January's can then be used as the denominator.  I've broken out of of the component parts for clarity

    2017-12-13_10-09-23.png

     2017-12-13_10-25-56.png

    The formula for Average Seasonaility is:

    Data[LOOKUP: Time Mapping.'Months -1 Yr'] + Data[LOOKUP: Time Mapping.'Months -2 Yrs'] + Data[LOOKUP: Time Mapping.'Months -3 Yrs']

     

    3. Use this seasonality to forecast 

     2017-12-13_10-16-09.png

     

    I hope this helps

    David

     

  • PopCC
    PopCC

    Hi, David

     

    Thanks for the explanation, this is really a creative way to solve the problem and also easy to understand/maintanance!

     

    Actually I was doing it by similar formulars, but I tried to put them in the same place instead of creating a Time-mapping module, which results in complexity. I'll try to apply the time-mapping way in this scenario.

     

    P.S

    The only risk is that we are discussing about that we may abandon Time, instead apply time list for our model, which will make those formulars unvailable after the change.... The reason that why we want to change it from anaplan standard Time to customized time list is, we are lack of workspace size allowancethe due to that we have to apply max 10 years' range (5 years for actual, and 4 years for forecast) on product number level. Since anaplan applies the same time range to all time applied modules though in some modules we actually only need to keep 2 years' data, it will make the model very huge and heavy. So we want to apply standard anaplan time (2 years) for monthly report, and also time list for acutual storage and future forecasting (10 years).

     

    Thanks!

  • PopCC
    PopCC

    Hi, Paul,

     

    Thanks for the option which sounds very helpful. And I attached the file again without password (sorry for the mistake...)

     

    I'll try to apply the lineitem subset as you said. Luckily the actual data is saved in the same module.

     

    The only risk is that we are about to apply Time List instead of anaplan native time due to workspace size problem.

     

    Thanks, I'll let you know what problem I meet.

Categories