Formula to add line item from Monthly set up to QTY

Jsdeloria21
Jsdeloria21
in Modeling

Hi Community,

 

I would like to ask if this is possible. There are 3 modules done but 1 module is set to monthly and the other is set to quarterly. See below for the illustration:

I had to create an illustration since I cannot provide screenshot from the model due to confidentiality.

This is the revenue module.

 

Jsdeloria21_0-1642751229963.png

 

And then they created another module set to quarterly for the recon.

 

Jsdeloria21_2-1642751344674.png

 

 

The idea is to add both yellow cells on th Mar 19 column of the Final Revenue module. So the mar 19 formula in the final Revenue Module would be Revenue Module.Revenue mar19 + Recon to AFS module. Recon to AFS Revenue Mar19. Currently we are not getting the values or getting a circular reference as an error.

Jsdeloria21_3-1642751358468.png

Not so sure why they build the model with different modules instead of a single one.

 

Appreciate if you can provide some ideas on how to execute this one.

 

Thanks and Regards,

 

 

Tagged:

Best Answers

  • ManjunathKN
    ManjunathKN
    Answer ✓

    Hi

    Bring 2 line items(LI), one with boolean and another with number. now add number formatted line item to your revenue line item and name it as "Revenue Final".
    formula for
    LI 1 : Quartervalue(Revenue). - Numeric
    LI 2: Next(Parent(item(time)))<>Parent(item(time)) - Boolean
    LI 3(Revenue final): if not LI2 then Revenue else (LI 1+Revenue) - Numeric

    Thanks,
    Manjunath

  • ManjunathKN
    ManjunathKN
    Answer ✓

    Hi

    line item 1 in your revenue module(from where you are getting initial quarter data) - keeping month as time period.
    Line item 2 keep in your time setting module and bring one more line item in your revenue module.(refer from time setting module)
    Line item 3 in your revenue module.

    I would suggest to keep all 3 line item in your revenue module.

    Thanks

    Thanks.


  • Jsdeloria21
    Jsdeloria21
    Answer ✓

    hi,

     

    Is there a way you can provide some screenshot on how this is executed. My apologies for my request.

     

    Thanks in advance,

     

  • ManjunathKN
    ManjunathKN
    Answer ✓

    Hi 

    Below SS will help you
    Module 1: quarter data in recon to AFS module.

     

    ManjunathKN123_0-1642756174762.png

    ManjunathKN123_1-1642756215564.png

     

    Module 2: Revenue module.

    ManjunathKN123_2-1642755783824.png

    ManjunathKN123_2-1642756291135.png

     


    Thanks,
    Manjunath

Answers

  • Jsdeloria21
    Jsdeloria21

    hi ManjunathKN123,

     

    Appreciate your response, I would like to just clear somethings. like where should I add the 2 line item? on the revenue module, recon with AFS module or the final Revenue module.?

    also on the formula if not LI2 then Revenue else (LI 1+Revenue) - Numeric shall I put this on the Final Revenue module?

     

    Thanks for the response,

     

     

  • Jsdeloria21
    Jsdeloria21

    Hi Manjunath,

     

    THank you very much.

     

    Regards,

  • Jsdeloria21
    Jsdeloria21

    hi,

     

    THis issue has been resolved already. THanks for all your input. Appreciate it very much.

     

    Thanks and Regards,

Categories