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

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.

Seasonality_Calculation_Image_26690.xlsx

Accepted answers

All comments

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

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

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

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

I hope this helps

David

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

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.

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…