Payment every year

manuebilly
manuebilly
in Modeling

Capture.PNGDear Anaplaner,

I would like to forecast the payments of supplier contracts.
Example:
-a contract should be paid every year starting Nov 19.
-so next payment would be, Nov 20, Nov 21, Nov 22....

I would like to use boolean to indicate which month should be paid ?

Thank you for your help.

Tagged:

Best Answer

  • manuebilly
    manuebilly
    Answer ✓

    Thank you Rob & Dameyer.

     

    I've found a solution :

     

    A- payment ?= '1st payment ?' OR Next payments ?

    B- next payments ?:

     

    IF Payment frequency = Payment frequency.Yearly AND Payment # - 1 = 12 THEN TRUE ELSE IF Payment frequency = Payment frequency.Yearly AND Payment # - 1 = 24 THEN TRUE ELSE IF Payment frequency = Payment frequency.Yearly AND Payment # - 1 = 36 THEN TRUE ELSE IF Payment frequency = Payment frequency.Yearly AND Payment # - 1 = 48 THEN TRUE ELSE IF Payment frequency = Payment frequency.Yearly AND Payment # - 1 = 60 THEN TRUE FALSE

     

     

    Emmanuelle

     

    Capture.PNG

Answers

  • rob_marshall
    rob_marshall

    @manuebilly 

     

    How many payments will be needed or does that change?  If it is fixed, then the Next Payment? formula would be '1st payment ?' + 12.  and then you can have another line time for 3rd payment which would be '1st Payment' +24

  • manuebilly
    manuebilly

    Dear Rob,

     

    I've thought about this solution but I have other types of payments (quarterly, semesterly...)
    I would like it to be automatic: if I add 5 years in my model I will need to add many line items.

  • dameyer01
    dameyer01
    Could something like this work:
    MONTH('Payment 1st month by item') = MONTH(PERIOD(START()))

    Then you're comparing the model month with the month set in your line item, regardless of the year. If you wanted to just flag the future payment period, you could add:

    AND PERIOD(START()) <> 'Payment 1st month by item'
  • dameyer01
    dameyer01
    Glad you were able to find a solution that works for you! I have two additional thoughts on your conclusion.

    1) You should be able to put the 'Payment frequency = Payment frequency.Yearly' clause in front of the IF statements, so it's not being evaluated every time. So it would look like this:
    Payment frequency = Payment frequency.Yearly AND IF Payment # - 1 = 12 THEN TRUE ELSE IF Payment # - 1.....

    2) Although your formula does what you need it to, it could be shortened so that the period in question is being evaluated against the period in the 'Payment 1st month by item' line item.
    'Next Payments ?':
    Payment frequency = Payment frequency.Yearly AND MONTH('Payment 1st month by item') = MONTH(PERIOD(START())) AND PERIOD(START()) > 'Payment 1st month by item'

    That said, it matters most that your solution works, which it clearly does!

Categories