Register

Transactions by period - remapped to transaction list with no time period.

CommunityMember82902
Occasional Contributor

Transactions by period - remapped to transaction list with no time period.

HI 

I have a line item which contains all the cashflows I require for the IRR calculation, but I need to have the cashflows against the last day of each period to get the correct IRR result.

 

In order to do this I have created a transactions list "Trans 01, Trans 02, etc" and then for each of the projects created a line item which enters the relevant transactions list item against beach cashflow, as shown in screen shot below.

 

CommunityMember82902_0-1619554981095.png

I now want to create a transactions module which has no time dimension and pulls through the cashflows and period end dates for each project against the transactions list based on the entry in the source module line item "Transaction No. Mapping" as shown below. I can then use the IRR with dates calculation I need.

CommunityMember82902_2-1619555504870.png

 

Below is the blue print for the source module

 

CommunityMember82902_1-1619555455624.png

Here is the blue print for the target module.

CommunityMember82902_3-1619555615198.png

In excel this is a simple V or H lookup to the transaction no.

But I just cant seem to get it right in Anaplan, any help appreciated.

Thanks

 

 

 

4 REPLIES 4
SrikanthKamath07
Contributor

Re: Transactions by period - remapped to transaction list with no time period.

Hi @CommunityMember82902 

 

I will suggest you to use below approach, which may not be the best approach but see if its work for you.

 

1) Create a intermediate module with Fake month ( Create a list for fake month) and bring the cashflow values from calendar month module to fake month module using lookup( Create a mapping module).

 

2)Now map your "Transaction No. Mapping" in Fake month Module.

 

3) Use Sum by "Transaction No. Mapping" in target Module.

 

Hope this approach will work.

 

Regards,

Srikanth

ChrisHeathcote
Community Boss

Re: Transactions by period - remapped to transaction list with no time period.

@CommunityMember82902 

In essence what you are trying to achieve is re-mapping the data held in a module dimensioned by month into another dimensioned by transaction number. 

There a few steps required.

1. You need to create a bridge will allows you to allocate the end date to each transaction. Therefore, create a mapping bridge module dimensioned by your transaction list and month time;

 

ChrisHeathcote_0-1619624011045.png

 

 

ChrisHeathcote_5-1619621386937.png

 

Set summary to LASTNONBLANK

2. Create a Transaction list properties module dimensioned by transactions and projects list and add end date, time period and transaction value as a line items.

ChrisHeathcote_7-1619621543400.png

ChrisHeathcote_8-1619621567117.png

Use SELECT or LOOKUP against a full year to pull the end date from the mapping bridge, use this to derive the time period and in turn use this in a LOOKUP to pull the transaction value through from the original source module. 

 

You can now use this to calculate the IRR. 

 

 

 

 

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
Amaya
Certified Master Anaplanner

Re: Transactions by period - remapped to transaction list with no time period.

I basically agree with @ChrisHeathcote's idea.

 

Let's use VALUE[LOOKUP: Time Period] in the target module.
In addition, I think the intermediate module is not needed.

 

You have 'Deal Header Information input.Start Date'.

It can automatically indicate which Time Period should be looked up.

LOOKUP Period = START PERIOD + Month No

 

スクリーンショット 2021-05-01 12.54.23.png

Use the CUMULATE function below. Very useful.

Month No = CUMULATE(Count, FALSE, 'Pseudo-Timeline') - 1

 

I hope it helps,

Taichi

CommunityMember82902
Occasional Contributor

Re: Transactions by period - remapped to transaction list with no time period.

Thanks for the extra tip to remove the intermediate module.