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

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