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

CommunityMember82902
CommunityMember82902
in Modeling

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

 

 

 

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

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

     

Answers

  • srikanthkamath
    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
    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

     

    スクリーンショット 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
    CommunityMember82902

    Thanks for the extra tip to remove the intermediate module.

Categories