Blog - Balance Sheet and Income Statements

If you need to put balance sheet and income statement accounts into the same hierarchy and then report on the movements across periods, have you tried this?

Requirement

Client calculates FFO (Funds from Operations) and AFFO (Adjusted Funds from Operations) by adding the movements of various trial balance accounts, i.e., both balance sheet and income statement accounts.

Historical Consolidation & Reporting

Legacy consolidation is done in PBCS which does not handle data based on financial reporting account types the way that a consolidation system such as OneStream does.

Reporting is done via Excel.

OneStream

• Consolidation tools like OneStream XF include financial intelligence such that account types are assigned based on the nature of seeing balances based on traditional financial statement presentation line items. The key differences between certain trial balance account types are that:
  • Balance Sheet accounts (Assets / Liabilities and Equity), by definition, are point-in-time account types whose balances do not naturally flow (or are evaluated) from period to period, unlike
  • Movements in Income Statement accounts (Revenue / Expense) accounts are typically viewed (whether it be daily / weekly / monthly / quarterly) as they accumulate to a year-to-date value. The most common presentation is to see how monthly values aggregating to a current year-to-date position.
• To enable an account hierarchy of both balance sheet and income statement accounts to "flow" in this application:
  • Accounts
    • Put accounts as needed into the proper hierarchy as noted above
  • Flow
    • All data is loaded into the Flow dimension member called, EndBal_Input
    • Create new flow Hierarchy with Activity_Calc as a parent to EndBal_Input and BegBal
    
  • Formula - set a member formula on the BegBal member.
    • The formula below is set to pull the prior period's Ending Balance, which when used in conjunction with the current ending balance for a balance sheet account, will return only the change in the balance sheet account balance between the two periods within the Activity_Calc flow dimension member.
    • Time constraints
      • For period 1, the BegBal formula is set to pull the prior year-end ending balance and is run only on the base level members of the balance sheet account hierarchy.
      • For all other periods, the BegBal formula is set to pull the prior period ending balance and is run only on the base level members of the balance sheet account hierarchy.
    • There is a statement to clear any BegBal that might calculate for any income statement base accounts - this statement is not needed.
    
• Reporting on the types of hierarchies that require combining the movements between both balance sheet and income statement accounts can now be achieved by using the Activity_Calc flow dimension member for reporting.

Summary

Legacy reporting tools often present challenges when implementing a replacement system/tool. Clients can be satisfied with the reporting that was available and might not understand the differences in functionality of the replacement vs the legacy. The solution noted here is not overly complex, but it did require time and understanding of the client's history and needs to develop a solution that would work for them. Thankfully, OneStream XF has a great deal of flexibility within its functionality to integrate this solution into the implemented application.