Blog

Cash Flow and OneStream - An Introduction

  • , Consultant

The Cash Flow Statement provides information about how changes in balance sheet accounts affect cash. There are two different methods to prepare the Cash Flow Statement; the Direct and Indirect method. The Indirect is most commonly used by companies using accrual accounting and is less complex to prepare. It reconciles the Net Income to cash from operating, financing, and investing activities. The Direct method uses the cash receipts and payments to reconcile to cash provided by operating activities. This method is used by companies that do cash-based accounting.

The Cash Flow Statement document is required for public companies when filing their 10Q and 10K. Both public and privately held companies often include it as regularly prepared board package.

The Cash flow statement is a complex statement to build, maintain, and automate. Each change in a balance sheet account must be accounted for on a line within the Cash Flow Statement. Often base level accounts cannot be easily mapped or accounted for on a single cash flow line item. Schedules to allow the user to input detailed information such as additions, depletions, depreciation, amortization, payments, write-offs, and other activity must be captured on separate schedules. It is not unusual to have adjustments on top of this process to allow for minor “tweaks.”

Often automation of the Cash Flow Statement is part of the consolidation reporting requirements. However, as it is complex, it often falls off the task list. Or if it completed, there is still a large percentage of manual adjustments.

Most multidimensional systems have the ability with multiple hierarchies or business rules to generate a Cash Flow Statement.

Overall Cash Flow Reporting Approach

The approach to building cash flow utilizes the account and flow dimensions. A standard chart of accounts includes a Balance Sheet, Income Statement, and Statistical accounts. An addition, account hierarchy is created to capture Cash Flow Statement line items.

The following discussion is based on OneStream as a consolidation tool, but the process can be applied to other multi-dimensional consolidation systems.

OneStream account hierarchy

Two general approaches to achieving cash flow reporting are discussed here. Which approach a company utilizes depends on its understanding of its own cash flows as well as the availability of data to populate either approach.

OneStream Approach #1

Approach #1 utilizes the basic account structure as noted in the overall approach above.

Cash flow line items contained member formulas. The basic formula is:

Target Account Cash Flow Provision for Bad Debts = Source Accounts (Allowance for doubtful accounts1 + Allowance for doubtful accounts2)

Api Data Calculate

We created flow members to hold Ending balance less the Beginning balance (Activity) and an adjustment bucket (Adj).

Creating Cash flow members

In addition, we created several schedules to collect roll forward detail. For example, for Property Plant and Equipment we created buckets for additions, disposals, gain/loss, transfers, and other members.

Creating Schedules to Collect Roll Forward Details

We created several schedules to collect detail from buckets such as additions, payments, amortization, etc., We split out the debt by long term and short term to further assist building the member formulas on the cash flow statement.

Collect details from buckets

For example, in the Financing section, we have the Cash Flow account called Long Term Debt Principal payments. We want to pick up the activity in two debt accounts 22405 and 23007. But we do not want to pick up the entire activity balance (End Bal – Beg Bal = Activity). So, we pick up just the payments piece of the activity.

Long Term Debt Principal

OneStream Approach #2

In this approach to cash flow, detailed rollforwards are not captured for all balance sheet items to ensure completeness. This approach works better if there are specific cash flow line items that need to be called out for any given balance sheet category. This reduces the need to "rollforward" accounts in the balance sheet to identify the activity needed for the cash flow.

Approach #2 also uses the account and flow dimensions.

  • Accounts are represented by the same types of cash flow line items as in Approach #1. Shown here is an example of a full listing of accounts needed for a cash flow report.
  • Flow members, in the example shown below and to the right, represent Balance Sheet categories that will be easier to group adjustments into.

Balance Sheet Categories

Cash flow

Activity

The premise of Approach #2 is:
  • To identify line items into which the general change in balance is captured – e.g., Accounts (CF_AcctsRec) Receivable into the Accounts Receivable (CF_AR) cash flow line item most usually identified in the Operating Activity section.
  • Other cash flow line items are available for input – these would represent adjustments to any change in balance – e.g., A/R changes attributable to the Acquisition of businesses
  • A calculation is included in the line item into which the general change in balance is captured that represents the periodic change in the Balance Sheet less adjustments entered into any other line item.

Calculations to capture General Change in Balance

Shown below is how Approach #2 occurs conceptually – with accounts represented in the rows and flow dimension members in the columns.

  • The change in balance at the top is represented in cash flow impact – i.e., an increase in an accounts receivable balance represents a “use” of cash flow (“-)”.
  • Calculations that capture activities that can be pulled from elsewhere – e.g., depreciation expense into PPE activity are represented in one set of green cells.
  • Calculations to capture the residual change in balance is shown in a different set of green cells. Where this calculation is captured depends on which cash flow line item the activity of a given balance sheet category is typically noted.

Calculations to capture residual change in balance

Summary

There are a couple of different approaches that can be utilized to capture cash flow activity for a company's cash flow reporting. The approach used will be dependent on a given company’s experiences in cash flow reporting as well as the availability of the data needed to call out the activity needed.

Again, the approaches shown here have been built based on OneStream Software as a consolidation tool, but the process can be applied to other multi-dimensional consolidation systems.

Contact MindStream Analytics

Want to learn more about Cash Flow 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

Built using best in class hardware, market- leading business intelligence software and in-memory database technology, Oracle Exalytics is an optimized system that delivers speed of thought analysis with unmatched intelligence, simplicity and manageability. For customers evaluating Oracle Exalytics, MindStream Analytics houses an Oracle Exalytics machine dedicated to 'sandbox' environment testing.

Oracle Profile

Share: