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 manual...
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
      15 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