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.


Latest testimonial

MindStream Analytics is a leading consulting firm focused on helping clients improve business understanding and decision making. With years of experience in the analytics and Business Performance Management area, MindStream offers customers services ranging from software selection and implementation to best practices for financial planning. MindStream will work with customers towards a solution that enhances value and offers more insight into their data. MindStream believes in the power of technology combined with new procedures to give customers better analytic capabilities


Partner SpotLight

Oracle Partner

Oracle

Oracle has the most comprehensive suite of integrated, global business applications that enable organizations to make better decisions, reduce cost

Oracle