Blog - Balance Sheet and Income Statements

Balance Sheet and Income Statement accounts in the same hierarchy?

  • , Consultant

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.

Client Funds from Operation

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
      Flow Dimension End Balance
    • 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.
      Formula Editor - BegBal
  • 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.

Contact MindStream Analytics

Want to learn more about the Flow dimension and OneStream Software? Please complete the form below and we'll get back to you shortly.


Partner SpotLight

OneStream Diamond Partner

OneStream CPM

OneStream aligns to your business needs and changes more quickly and easily than any other product by offering one platform and one model for all financial CPM solutions. OneStream employs Guided Workflows, validations and flexible mapping to deliver data quality confidence for all collections and analysis while reducing risk throughout the entire auditable financial process.

OneStream Profile