Cash Flow Reporting and Planning is a foundational process within all companies. Cash Flow is the net cash and cash equivalents transferred in and out of the company. Being able to report these numbers is critical for analysis to understand the health of the business and potential metrics to understand trends like Days Sales Outstanding (DSO), Days Payable Outstanding (DPO), and Quick Ratio, for example.
FP&A Software like Cube is extremely helpful for performing exercises like Cash Flow Reporting and Planning, as Cube handles these calculations within the tool to generate the values. One issue with doing this manually within Excel is the potential for errors with missing accounts.
This article will cover topics around Cash Flow Reporting and Planning best practices and commonly used Cube formulas. After setting up your formulas, check out our Cash Flow template.
Cash Flow Reporting Best Practices
Cash Flow Reporting is reporting an increase/(decrease) in cash flow for a time period. The Cash Flow statement is driven by activities within the Income Statement and Balance Sheet and is usually divided into three sections:
- Cash Flows from Operating Activities
- Cash Flows from Investing Activities
- Cash Flows from Financing Activities
When writing the formulas to bring the data over from the Income Statement or Balance Sheet, we recommend using parent dimensions to pull data. Child dimensions can be grouped under parent dimensions to ensure the data rolls up and flows properly.
Learn more about dimensions.
Cash Flows from Operating Activities
Operating Activities for a company are based on the money directly used to produce and sell products/services. Operating Activities are produced by taking the Net Income from operations, adjustments to reconcile net income to net cash (Depreciation & Amortization, Stock-Based Compensation, Unrealized (gains) and losses), and changes to working capital.
Cube can manage the formulas for these calculations rather than managing them with spreadsheet formulas. Add the formula in Cube, and the values will update whenever the associated dimension values change. Learn more about using formulas.
While pulling this information from the Income Statement and Balance Sheet may vary, below are some common examples of best practice formulas:
Formula Name | Formula |
Net Income (Loss) | =”Net Income” |
Depreciation & Amortization | =”Depreciation & Amortization” |
Changes in Accounts Receivable | =<MONTHS_AGO(“Accounts Receivable”,1)>-”Accounts Receivable” |
Changes in Inventory | =<MONTHS_AGO(“Inventory”,1)>-”Inventory” |
Changes in Accounts Payable | =”Accounts Payable”-<MONTHS_AGO(“Accounts Payable”,1)> |
Changes in Accrued Liabilities | =”Accrued Liabilities”-<MONTHS_AGO(“Accrued Liabilities”,1)> |
Dimension names vary, so you may need to adjust these formulas to reference your dimensions and ensure they have unique names from existing dimensions.
Cash Flows from Investing Activities
Investing Activities for a company are related to the money spent on investment activities for a period. Very common examples of this could be purchases of different assets (Property, Plant, and Equipment), Investments in Subsidiaries, and potential Sales of certain investments. These can be tricky to track from a cash flow perspective as it may be certain transactions within an Account dimension within Cube. The best practice for situations like these would be to create an Account dimension to publish the value if certain transactions are required for Cash Flow Reporting.
Below are some common examples:
Formula Name | Formula |
Change in PPE | =<MONTHS_AGO("PPE", 1)> - "PPE" |
Change in Investments | =<MONTHS_AGO("Investments", 1)> - "Investments" |
Cash Flows from Financing Activities
Financing Activities shows the net flows of cash used to fund the company and its capital. This section mainly focuses on the activities with the Balance Sheet's Long-Term Liabilities and Equity section. Depending on the industries, this can be very light on the volume of activity and is usually isolated to a handful of accounts.
Below are some common examples:
Formula Name | Formula |
Change in Long Term Debt | ="Long Term Debt"- <MONTHS_AGO("Long Term Debt", 1)> |
Change in Common Stock | ="Common Stock"- <MONTHS_AGO("Common Stock", 1)> |
Cash Flow Planning Best Practices
Just as Cash Flow Reporting is important to a business, Cash Flow Planning as a Business is critical for making business decisions. Use Cube to easily forecast your Cash Flow within the spreadsheet. Below are some of the best practices for forecasting Cash Flow within Cube:
- Because Cash Flow is driven off the Balance Sheet and Income Statement, the same formulas currently being used for Cash Flow Reporting can help drive Cash Flow Planning.
- When an account grouping (Ex. Accounts Receivable) has many accounts under the parent, choose one account to use consistently or create an input account specifically for your forecasting purposes.
- For ease of use and formula capabilities within Excel, simply publish the projected forecasts from your spreadsheets back into Cube.