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

CommunityMember82902

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.

 

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.

 

Below is the blue print for the source module

 

Here is the blue print for the target module.

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

 

 

 

ChrisAHeathcote

@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;

 

 

 

 

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.

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.

 

srikanthkamath

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

Amaya

I basically agree with @ChrisAHeathcote'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

 

Use the CUMULATE function below. Very useful.

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

 

I hope it helps,

Taichi

CommunityMember82902

Thanks for the extra tip to remove the intermediate module.