Formula Help Required :-)

lcapon
lcapon
in Modeling

Hi,

 

I've recently been contact by our finance team with the below query - I wondered if someone could help point out where in the existing formula I can tweak to resolve.

 

OLD Formula: 

lcapon_0-1636033808880.png

NEW Formula:

lcapon_1-1636033861875.png

 

This has resulted in incorrect output in the field known as 'B6', please could you help me work out what i need to amend in the below formula?

OLD Output:

lcapon_3-1636034074090.png

 

New Output:

lcapon_2-1636034034065.png

 

 

 

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @lcapon 

    Interesting formula change.

    • At first glance all that you seem to need is a relationship between the TARGET and SOURCE of the data. In this case your SOURCE is appears to be related to values coming off a PO (PO Import.value). Make sure your SOURCE and TARGET have a meaningful relationship that doesn't require a LOOKUP or SUM because your formula appears to be taking the value directly whether the TYPE is a PO or a Subcontractor.
    • The other idea to consider is what happens when you use the "OR" function. You are asking Anaplan to take the PO value if the TYPE is a purchase order or a subcontractor which suggests to me that you need a SUM function (SUMIF in Excel terms). This can be easily managed in a system module that possibly relates your TARGET module with the different types.
    • Lastly, keep in mind when using the "OR" function you can open yourself up to double counting.

    Not sure why you're getting a large negative number. Would need more context and details about what you're trying to do.

  • ankit_cheeni
    ankit_cheeni

    Hi @lcapon 

     

    Can you help me understand why do you think the result is wrong. Your B6 formula is E - (B1+B2+B3+B4). If you are changing your B4 formula and thus the value(List item 107's B4 value went from 0 to 248128), your B6 would get reduced by 248128, which is what i see happening (B6 goes from 0 to -248128). I would say this is what you've arithmetically tried to do. 

     

    If you feel the calculation is incorrect, maybe you need to check with Finance whether B6 calculation for subcontractor is different from POs. 

     

    This is my best understanding from the information presented 🙂 

     

Categories