AC-GL-RT4 - Cash Flow Statement

Created by Aljun Talle, Modified on Mon, 21 Oct at 10:59 PM by Damien Zwillinger

Applies to

  • Product version: Velixo NX
  • ERP: Acumatica
  • Functional area: Financials, Other
  • Template type: Production Report template


TABLE OF CONTENTS


Description

The Velixo AC-GL-RT4 Cash Flow Statement is a generic report for users to have a comprehensive view of their company’s cash inflows and outflows during a specific period. This report offers valuable insights into how cash is generated and utilized in Operating, Investing, and Financing activities. 


Please see Introduction to Report Templates for information about Samples, Demos, and Production Templates and the differences between them.  You many also want to refer to How to Modify Velixo Sample Reports.


Coverage

This workbook includes the following sheets:

  • Cash Flow Statement: the cash flow statement reflecting the operational, investment, and financing activities during a specific period of time.
  • Options: the template’s settings sheet that used for data validation lists and various lookups and configurations.
  • Information: the information sheet of the report, used for storing useful details about the report such as: code, version, article link to keep track of the origin of the report.


Typical audience

The typical users of this type of report are Finance professionals: Accountants, CFOs, Controllers and also Directors.


Features

The following features are used by this report. To use this report, please ensure that your Velixo license includes all of them, (or contact Support or Sales):

  • Financial functions
  • List functions


Preview



Download this template

Download


💡Having trouble downloading the file?

Simply right-click the link and select Save link as


Documentation

 

Navigating and utilizing the Velixo Cash Flow Statement is an intuitive process, designed for simplicity, genericity and efficiency. The template comes pre-configured with the most common line items, streamlining the reporting process with minimal user effort.


Filters

Located in the upper left of the sheet, the filters include:

  • Branch: lists all the Branches present in Acumatica Instance including any custom group defined in the options sheet. For ease of use, Velixo shows you the Branch ID that you may be familiar with along with the Branch name. This Display name is configurable in the Options sheet.
  • As of (Period): a date field. Please use the last date of a financial period/month
  • Ledger: the ledger to pull the balances from.
  • Include unposted transaction: an option for user to include unposted transactions

 

These filters are seamlessly linked to the data on the Options tab.




Cash Flow Statement Structure

 

Sections: Velixo has thoughtfully structured the Cash Flow statement using the most common sections that should be generic, by default but can be customized if needed. The following activities are used:

  • Operating Activities
  • Investing Activities
  • Financing Activities



Default line items were added as well per activities and are highly customizable depends on the user’s requirements. These are based on Acumatica built-in cash flow statement report category, making it as streamlined with the ERP as possible.



Time Period

 

The incorporated time periods encompass the following:

 

Month-to-date:

  • Provides the movement balance for the selected month on the As of date.
  • For example, if a user selects 06-2018, the returned balance spans from June 1st, 2018, to June 30th, 2018.

 

Year-to-date:

  • Provides the accumulated balance from the start of the fiscal year up to the indicated as-of-date.
  • To obtain the full year position, it is crucial to specify the last date of the fiscal year.

 

Prior Months MTD:

  • Velixo added prior months on the time period to provide a better view of the cash position for different periods. This can be used as well for comparison and analysis.
  • For example, if a user selects 12-2018, the Prior Months MTD will return the balance for November, and December 2018.



User Guide

 

Using the report

 

Now that we've covered the report overview, let's dive into a step-by-step guide on effectively utilizing the report.

 

  • Set the Connection Name on the Options Sheet - crucial for all processes, set the Connection Name on the Options sheet by updating cell A2. Ensure it matches the name used during Velixo application login.


  • Update the Options Sheet - As the foundation for filters on the Cash Flow Statement sheet, keep the Options sheet up to date. It should always and automatically capture the latest dimensions and structures from your Acumatica instance; however, you can also customize it.



  • Choose Desired Filters- After updating the Options sheet, select desired filters for the report:
    • Branch / Company
    • As of (MM-YYYY format)
    • Ledger
    • Include unposted transactions



  • Automatic Report Update - Selecting any filter will automatically update report balances. Begin analyzing the report with the latest data.



Understanding the Report Formula


Velixo created the template using a combination of Excel functions and Velixo functions for Cash Flow Statement report. The report incorporates two primary formulas on the Cash Flow Statement tab:

  • Account Class Validation
    1. In Column A, a validation formula was used to verify if the account class in Column B exists in Acumatica
    2. Invalid values (not in Acumatica) return as blank
    3. Ensure continuous use of this formula for any class additions or name changes; a correctly populated formula ensures accurate transaction amounts.



  • Transaction Amount formula
    • Used in all transaction amount columns, this formula’s primary objective is to extract balances for the account classes from Acumatica
    • Velixo function ACCOUNTTURNOVER power these columns
    • To facilitate flawless execution of Velixo functions, an IF statement is strategically placed.
    • In the account class validation formula, any invalid classes yield blanks. The IF statement distinguishes whether the class is valid; if not (blank in Column A), the transaction amount formula sets the amount to blank. On the other hand, if the class is valid, it extracts the balance using the Velixo function.



Customizing the template

 

Reviewing the Options sheet

 

The Options tab comprises Acumatica dimensions, and other attributes and structures used in the report. This information updates in real-time with Velixo app refresh, facilitating immediate integration of newly added ERP data into the report as filters.

 

This includes:

  • Connection Name
  • Branch
  • Company

 

 

 

The Connection Name is subject to variation depending on the user input provided through the Velixo login portal. For convenience, users may add the connection name used on cell A2. This cell has been designated as a named range (Connection), ensuring its automatic application to all formulas throughout the sheet.



Users can also customize Branch by adding their preferred custom values, using a list and ranges as described in this article.

 

To do so:

  1. collapse the column groups for the Branch dimension 
  2. then simply input the desired values in the Group formula and Custom group name columns, highlighted in light blue for quick identification.




Managing Account Structures

 

As outlined earlier in this document, users have the flexibility to tailor this report to their specific needs. This is especially useful when the pre-defined line items do not quite fit the requirements.

 

  • Adding a new Line Item

 

If users desire a structure beyond the provided default line items, they can leverage account classes set up in their ERP instance.

 

Users have two options when adding new line item:

 

1. Re-purposing an existing line item:

  • Rename an existing line item, replacing it with the desired custom account group.
  • Assign the corresponding account class or account codes to the new line item

 

2. Adding a Row in the required Cash Flow Activity section:

  • Use Excel functions to add a new row in the desired section.
  • Be cautious with formulas in newly added rows; copying from existing rows in the same section ensures accurate results.

 

  • Grouping Account Classes

 

If needed, the user can group multiple account classes into one and create a new line item. Velixo enabled this feature so that user can consolidate similar account classes into one and present them in the Cash Flow statement as one line item.

 

  • On Column B of the Cash Flow Statement tab, user can list all the account groups and using the semi-colon ( ; ) delimiter to grouped them
  • If needed, rename the line item on Column D showing the consolidation of the account groups listed on Column B

 

  • Using of Account Code/Range


As an additional feature, Velixo added the ability to use Account Code or Account Code ranges to extract the balance for the category.

 

  • On Column C of the Cash Flow statement tab, users can list all the account code or ranges they wish to use or consolidate. Same as account classes, the semi-colon delimiter should be used as well for the groupings.
  • If needed, rename the line item on Column D showing the consolidation of the account codes/range listed on Column B

 

Please note that Account Group and Account Code cannot be used at the same time. If both have values, it will result in calculations errors for the transaction balances. If a structure is not used, make sure to leave one of them at least blank to not cause any errors.


Version history

 

Version

Released on

Changes

1

January 18, 2024

N/A. Initial version of the template.

1.1June 10, 2024
  • Enhanced the "As of" field to accept both financial periods and specific dates.
  • Introduced support for non-calendar fiscal years.
1.2September 20, 2024
  • Updated the date logic to accurately support non-calendar fiscal years.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article