Transactions by period - remapped to transaction list with no time period.
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.
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.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA