Forum Discussion

jbrem's avatar
jbrem
New Contributor III
3 days ago

Member formula to allocate bal sheet by UD

Hi experts, hoping someone can guide me.  

Our P&L is recorded by Line of Business (UD2), however our balance sheet is recorded to UD2 None.  FPA has a requirement that they currently handle manually to "allocate" the balance sheet to the lines of business based on the percent of revenue.  Our partner initially achieved getting closer by using a dynamic formula on UD8.  This does not ultimately provide the accurate results at the consolidated level (due to nature of dynamic formula).  We believe we need to change this to a formula pass but it is proving to be challenging.  

We are open to suggestions, using UD7 (tracking) or UD6 (reporting).  I am also open to creating a mini alt acct hierarchy since it really only needs to apply for a handful of summary level accounts.  (Coding examples would be so very appreciated!)  Thank you! 

Admin
Finance
  • FredLucas's avatar
    FredLucas
    Contributor III
    2 days ago

    Hi jbrem ,

    This allocation step would typically be done by executing a finance rule as part of the planning process.

    Depending on the process, it could be done at a push of a button, or at the completion of a specific Worflow step, etc.

    This would allow for users to review the allocated data at base level and potentially update it if required (e.g.: the allocation could be done in O#Import and accept manual updates in O#Forms) prior to consolidate.

    This type of logic is typically achieved by using an api.data.calculate (often using the multiply unbalance for the driver) or using data buffers in more complex cases. It is also important to ensure that the input data (recorded at UD2 None) uses a separate POV so it does not add up at U2#Top together with the allocated data.

    Note: the allocation result should be stored as durable data to ensure it's not cleared during the consolidation.

    • jbrem's avatar
      jbrem
      New Contributor III
      2 days ago

      Hi FredLucas thx for the response.  This helped me get things extremely close!  Once I got data, I realized I need to have a value in No LOB for any entity that does not have revenue.  I'm attempting this with the statement in bold below but I can't get it to validate.  Any more wisdom for me?  Thx!

       

      If Not api.Entity.HasChildren And api.Cons.IsLocalCurrencyForEntity Then
       
      api.data.Calculate("A#LOB_Pct_BURev:U1#None:U6#Local:U7#RLE_LOB:U8#None = DivideUnbalanced(A#BU_REV:U1#Top:U7#Top,A#BU_Rev:U1#Top:U7#Top:U2#Top, U2#Top)")
       
      Dim valueCheck As Double
       
      valueCheck = api.Data.Calculate("A#LOB_Pct_Rev:u1#Top:U2#LOB")
       
       
      If valueCheck<> 1 Then
      Api.Data.Calculate("A#LOB_Pct_BURev:U1#None:U2#No_LOB:U6#Local:U7#RLE_LOB:U8#None = 1")
      End If
       
       
       
      End If

       

      • FredLucas's avatar
        FredLucas
        Contributor III
        11 hours ago

        I believe the problem is here:

        valueCheck = api.Data.Calculate("A#LOB_Pct_Rev:u1#Top:U2#LOB")

        Api.data.Calculate is a sub i.e.: does not return any value.

        You'll probably want to use the api.Data.GetDataCell instead:

        valueCheck = api.Data.GetDataCell("A#LOB_Pct_Rev:u1#Top:U2#LOB .... ").CellAmount

        Note: Make sure to specify the full POV