How to calculate within specific tenure?

taqyyudin

Dear All,

I am looking for the best solution how calculate/distribute an amount within specific tenure once it was triggered by a variable.

Example:

1 million of revenue will be recognized equally over 24 months once the project is completed.

The 1st month of the recognition starts on the following month once the project completed (the variable).

The revenue recognition will ends after the 24th month from the 1st month of recognition. Or it will end once cumulative revenue is 1 million

Your suggestion if much appreciated. 

Thank you

DavidSmith

Hi

Yes, the profile function will work here.

1. Set up a Profile Allocation module for as many months forward as you need (you can also set up different profile methods if needed)

2. Use the profile function from to allocate the amount forward.  The assumes the first line item from 1 is the first month of the allocation, so you need to "move" or "reference" the cost to allocate into that month.  I have mocked up some simple logic below to illustrate

This will automatically stop the allocation after 24 months

Further details can be found here:

https://community.anaplan.com/t5/Calculation-Functions/PROFILE/ta-p/17425

I hope that helps

David

taqyyudin

Hi David,

Thanks alot for your suggestion. 

taqyyudin

Hi David,

How can I have multiple % Allocation for different type of products that may have 12 mths tenure?

My solution: based on you suggestion, I will need to create multiple profile to cater for different mth and use if formula to select which profile it should refer to.

Any other better suggestion my solution above?

 

Thank you

taqyyudin

Hi David,

How can I have multiple % Allocation for different type of products that may have 12 mths tenure?

My solution: based on you suggestion, I will need to create multiple profile to cater for different mth and use if formula to select which profile it should refer to.

Any other better suggestion my solution above?

 Thank you

DavidSmith

Hi

You can use an IF statement, but there is a better, more effficient way using Line Item Subsets.  

Using Line Item Subsets is a little complicated, but once mastered it is a very useful technique.

Also, I prefer to keep the calculations in multiple modules as it is clearer to understand when you have different dimensionality

I will outline the steps now.

1. Create more line items in the Profile model to account for the different phasing profiles

2. Set up a line item subset from this module

3. Set up a Projects Profile Choice module, dimensioned by project with a single line item formatted as the newly created line item subset.  This is where you will assign the profile to the project

4. Create a Projects profile module with two line items.  The first Collect(), utilises the Line Item Subset functionality to concert the line items into "list items". The dimensons should be the the 24 months list and the Line Item Subset.  The second line item uses a lookup from the Projects Profile Choice module to pull in the correct profile

5. Point the Recognition profile line item to this line

Voila!

David

nicole.johnson

I know you wrote this solution over 2 years ago, but it is really going to come in handy for me in my latest project. Thank you!!

HendrikDeCuyper

Hi @DavidSmith 

Browsing the community to find the best practice to set up inventory consumption through the FIFO method, where the supply plan is calculated on the Product (parent level) but the inventory is kept at the Lot (child level). @JaredDolich pointed me to this article as an inspiration, however I'm still a bit in doubt how to proceed.

 

Was wondering if you could provide input on the common way of setting up the logic in Anaplan as my current method would require a large iteration of line items to make it work.

 

Thanks in advance!

 

Hendrik

DavidSmith

@HendrikDeCuyper 

Can you provide more information of the problem and the logic?

thanks

David

HendrikDeCuyper

Hi David,

 

Thanks for your answer.

On the level of P2 - Products we'll have a forecast in monthly buckets, e.g Sep 20 - 100 | Oct 20 - 100 | Nov 20 - 100 | Dec 20 - 100. 

We want to calculate based on the forecast if we would have any inventory that would expire and by what date based on the FEFO logic, which translates to 'First Expired First Out'. 

 

Below the P2 - Products level we'll have the child level P3 - Lot, where the inventory is stored in lots with specific expiration dates. 

e.g Lot 1 - Quantity: 50 - Exp: 20/10/2020 | Lot 2 - Quantity: 100 - Exp: 30/10/2020 | Lot 3 - Quantity: 150 - Exp: 15/11/2020

At the moment I've created two ways of calculating the inventory consumption by ranking the lots, based on their expiration date, consuming the inventory from the lot with the highest rank first. 

 

The first is an iterative approach where I'll need to add an indefinite amount of line items higher than the maximum number of lots one product can have - which is quite sparse and not very manageable. However this approach would theoretically work.

 

The second is an approach where I reach a circular reference. I'm adding a List 'Rank' which is a list with numbers from 1 to 25. From the moment I want to include the already used inventory in the planned consumption similar to the previous approach with 'Remaining Consumption' I'm reaching a circular reference error.

 

I've added both the formulas in Excel for your reference.

 

Hope this clarifies.

 

Thanks in advance!

HendrikDeCuyper

Hi @DavidSmith ,

 

Just tagging you - as else I was afraid this might get lost in the wilderness of notifications.

 

Looking forward to hear from you!

 

Have a great weekend.

 

Hendrik