Calculation Inquiry

Hi,

 

PLease see details in the attachment.

 

Regards,

Best Answers

  • Misbah
    Answer ✓

    @Jsdeloria21 

     

    You will have to create a SYS TIME module and have a mapping between Native Time and Counter Time. For Example Jun 2020 will be Month 10 something like that. Once done you can easily use LOOKUP based on the mapping and pull the values. Let me know if you want me to create an example for you

  • Jsdeloria21
    Answer ✓

    Hi Misbah,

     

    Thank you for the response. I kinda figured that this is going to use the LOOKUP function. Is it ok if you can show me how to do this approach?

     

    Regards, 

  • prabhu
    Answer ✓

    Hi @Jsdeloria21 - 

    Follow these steps

     

    41.JPG42.JPG43.JPG44.JPG45.JPG

     

    Thanks,

  • andre.lie
    Answer ✓

    Hi @Jsdeloria21 ,

     

    I think we need to create a staging module with Payment Scheme, Time, and Counter Time as lists.  This will enable us to calculate the installment for each principal amount. The final module will summarize this staging module.

     

    Principal.JPG

     

    From your example, I notice that percentage split is always input from Month 1, no matter the real Month when the principal amount is issued

     

    Percent.JPG

     

    The first staging module is to perform the calculation for each principal

     

    Principal Staging.JPG

    Principal Staging-Formula.JPG

     

    The second staging module is to summarize the payment for each Counter Time using TIMESUM

    Principal Staging 2.JPG

    The result module needs to convert the Counter Time to Anaplan Time using SUM.  Here is shown the Oct 19 - Dec 19 (which corresponds to Oct 18 - Dec 18 in your example)

    Result.JPG

    In addition, we need the mapping module that @prabhu showed, and for my example the reverse mapping module which are derived by formula from the original mapping

     

    Reverse Mapping.JPG 

     

    Regards,

    Andre

     

  • andre.lie
    Answer ✓

    Hi @Jsdeloria21 

     

    No problem.

     

    I have a Counter Time Mapping module to assign Counter Time to Anaplan period manually.  Month 1 should be mapped to the first period in the Time Settings.  In my case, Time Settings starts from Jan 19

     

    Counter Time Mapping.JPG

    Counter Time Mapping - blueprint.JPG

     

    Then I have the reverse mapping in Time - Counter Time Mapping module, which is derived from Counter Time Mapping module.  

     

    Time - Counter Time Mapping - blueprint.JPG

     

    Here is Principal Staging formula, for 2 line items in question

     

    Principal Staging - blueprint.JPG

     

    Data from Principal Staging goes to Principal Staging 2

    Principal Staging 2.JPG

     

    Principal Staging 2 - blueprint.JPG

    Finally from Principal Staging 2 to Payment module

    Payment.JPG

     

    Let me know if you have further questions.

     

    Regards

    Andre

  • andre.lie
    Answer ✓

    Hi @Jsdeloria21 ,

     

    It looks like the FINDITEM in Percent Counter Time Lookup could not find the list item.  I think it is because you missed a space in the formula i.e. "Month ", not "Month" as what you have

     

    Andre

  • andre.lie
    Answer ✓

    @Jsdeloria21 ,

     

    For question 1 and 2, no payment will be calculated if the period is not mapped.  For question 3, the sample payment calculation works on assumption the period is mapped sequentially in the mapping module.  

     

    Regards,

    Andre

Answers

  • @Jsdeloria21 

     

    @prabhu  has created an example for you already. The only thing I would add is if you have a logic you can automate the mapping between Native Time and Counter Time, if not then you can manually map these two

     

    Hope that helps

    Misbah

  • Hi Andre,

     

    My apologies for the late reply on your response. And I want to say thank you for taking time on answering the query. I would like to ask something about your solution particularly on the table below. I can see that the Counter Time column on the list was already set to Month 1 - Jan 19. I already had the Column Counter time no ready. Just want to ask, did you set the Month 1 as Jan 19 on the list.? I mean did you assign it manually on this table below? What if there is a value on "Principal" which is Sep 16, does this mean Sep 16 should be tagged as Month 1? 

     

    I am currently applying your solution to the set up that we have already. Just need some clarifications on this.

     

    Jsdeloria21_1-1593409033211.png

    Hope to hear from you

     

    Regards,

     

     

  • Hi Andre,

     

    My apologies again I think I was wrong on analyzing the screenshots. I just want to clarify my first reply to you.

     

    Is this a module where Row - Time and Counter Time = Counter Dimension ? If so, is it possible to view the formula of this module so I can compare it with what the client.

     

     

    Jsdeloria21_1-1593410169835.png

     

    Thanks

     

  • Hi Andrei,

     

    Sorry for disturbing you. I was able to replicate the solution that you sent me. See below:

     

    Jsdeloria21_0-1593411867532.png

     

    I only have 2 missing line items that I need to clarify with you. What is the complete formula for Cumulate count and in the Counter Time Month line item, what is Counter time mapping.Month, is it a module or a list? I think I am missing that part as well. Plus the question that I have with  Time - Counter Mapping table.

     

    Appreciate your response

     

    Regards,

     

  • Hi Andrei,

     

    Thank you for the response it really helped a lot.

     

    Here is that I have so far. This is what my Principal Staging looks like now.

     

    As you can see on the Percent Counter Time Lookup line Item I only have the "Month 10" while on your example it counts Month 1... Month 2.. etc.

     

    Did I do something wrong or am I missing something?

    Jsdeloria21_2-1593418401449.png

     

     

    Jsdeloria21_1-1593418159723.png

     

    Regards,

  • Hi Andrei,

     

    Thank you so much for answering the queries yesterday I was able to build the calculation. I would like to clarify some stuff if it alright.

     

    Particularly on this table. I can assume that this table will be the first approach that a user/client will have to set before the calculation starts. Now as you can see below you set Month 1 - Jan 19, in my case I started with Month 1 - Jan 18 and it calculated the Oct 18-Dec 18. Now I have this questions to ask.

     

    1. Let us say that client has 24 mos spread on the Counter dimension and I set Month 1 - Jan 18 so meaning it should cover Jan 18 - Dec 2020. If ever I have a computation on Jan 21 which is not covered on this table. Does this mean there will be no computation present on the Payment Module since it is not mapped on the Counter Time Mapping module? 

     

    2. Also this one is related to the Number 1 question, in my actual Run I have a value to be computed on Source Module dated Sep 16, since it is not mapped on this table does this also mean that there will be no computation present on the Payment Module? 

     

    3. I am assuming that in this module Counter Time Mapping you manually input the Months based on the number of spread Month 1 - 24. Is there a way to automate the mapping? Meaning this table will map only the Months on the Source module with values in it?

     

    Jsdeloria21_0-1593501860013.png

     

    Since I already build the solution you presented I am currently applying the scenario in the actual model itself.

    Appreciate your response. And again thank you.

     

    Regards,