SI-GL-RT4 - Cash Flow Statement / SI-GL-RT4.NFP Statement of Cash Flows

Applies to

• Product version: Velixo NX, Velixo Classic
• ERP: Sage Intacct
• Functional area: Financials
• Plan: Free, Essentials, Professional, Advanced
• Template type: Production Report template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Filters
  • Cash Flow Statement Structure
  • Time Period
• User Guide
  • Using the report
  • Understanding the Report Formula
• Customizing the template
  • Reviewing the Options sheet
  • Managing Account Structures
• Version history

Description

The SI-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.

Note: Two versions of this Report Template are available - one for organizations that use either the General and Services QuickStart template, and one for Not-For-Profit organizations).

The current version of this template is v1.4

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

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 (General / Service QuickStart version)	Download - Direct Method (Not-for Profit QuickStart version)	Download - Indirect Method (Not-for Profit QuickStart version)

💡Having trouble downloading the file?

Simply right-click the link and select Save link as

Documentation

Navigating and utilizing the SI-GL-RT4 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:

• Location: lists all entities present in Sage Instance (excluding groups) including any custom group defined in the options sheet. For ease of use, Velixo shows you the Entity id that you may be familiar with along with the entity name. This Display name is configurable in the Options sheet.
• Department: lists all departments present in Sage Instance (excluding groups) including any custom group defined in the options sheet. For ease of use, Velixo shows you the Department id that you may be familiar with along with the department 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 
• Books: lists every type of books defined in your Sage Instance in a dropdown list. The report defaults to the Accrual book, but user can add more than one book by concatenating books in the filter value. For example: Accrual;GAAP will return amounts for those two books

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.

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 Sage Intacct categories, making it as streamlined with the ERP as possible.

QuickStart Account Categories: Built on the foundation of Sage Intacct out-of-the-box account categories, the template is ready for use post-ERP implementation.

These QuickStart Account Categories are itemized and grouped on Column C as default.

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 6/30/2019, the returned balance spans from June 1st, 2019, to June 30th, 2019.

Year-to-date:

• Provides the accumulated balance from the start of the fiscal year (depending on the user input for the fiscal start month on the Options tab) up to the indicated as-of-date.
• For example, if a user selects 6/30/2019, and the specified fiscal start month is April, the returned balance spans from April 1st, 2019, to June 30th, 2019.
• To obtain the full year position, it is crucial to specify the last date of the fiscal year.

Prior Months YTD:

• 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 6/30/2019, the Prior Months YTD will return the balance for May, April, and March 2019.

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 Sage Intacct instance; however, you can also customize it.

• If your fiscal calendar doesn't start on the January 1st, then specify the Fiscal Start Month on the Options sheet, as it will be the basis of the calculation for the YTD balances. 

• Setting the Location Configuration Option: to enable further customization, the template includes an option for users to choose the location level (Parent, Child, or both). By default, the template is set to extract the balances at Parent level.
  • Navigate to the Cash Flow Statement tab and expand the grouped rows by clicking the plus sign (+) on the upper left corner of the tab.
  • Go to cell F4 and select your preferred location level from the dropdown menu:

• Choose Desired Filters: after updating the Options sheet, select desired filters for the report:
  • Location
  • Department
  • As of (Period) in MM/dd/YYYY format
  • Books (single select or multiple using semi-colon as delimiter)

• 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 the Cash Flow Statement report. The report incorporates three primary formulas on the Cash Flow Statement tab:

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

• Account Code Validation
  1. Column B contains a validation formula designed to retrieve mapped account codes to the corresponding QuickStart category in Column C. If no account codes are mapped to the specified QuickStart category in the ERP, it will return a blank value.
  2. The retrieved account codes serve as the foundation for the Transaction Amount calculation formula.
  3. This process exclusively applies to QuickStart categories. If an account group is specified in Column B instead, it will return a blank value, prompting the Transaction Amount formula to reference Column A for calculation purposes.

• Transaction Amount formula
  1. Used in all transaction amount columns, this formula’s primary objective is to extract balances for the account categories from Sage Intacct ERP
  2. Velixo functions, specifically SI.TURNOVER, SI.OPENINGGBABALANCE, and SI.CLOSINGBALANCE power these columns
  3. To facilitate flawless execution of Velixo functions, an IF statement is strategically placed
  4. The validation formulas for account codes and account groups ensure that any invalid inputs result in blanks. The IF statement discerns the validity of the input: if both columns A and B are blank, the transaction amount formula sets the amount to zero. Conversely, if the category is valid and has mapped accounts associated with it, or if the account group is valid within the ERP system, the balance is extracted using the Velixo function.

Customizing the template

Reviewing the Options sheet

The options tab comprises Sage Intacct 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
• Locatino
• Department
• Books
• Fiscal Year Setup

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 Location and Department dimensions 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 either the Department or Location.
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. It is not required but may be useful if the default QuickStart categories don't quite fit the requirements.

• Adding a new line item

If users desire a structure beyond the provided default line items, they can leverage account groups set up in their ERP instance. However, caution is crucial to avoid overlap between account groups, preventing inaccuracies. Opting for a granular account category list ensures data is accurate.

Users have two options when adding new account structure:

1. Re-purposing an existing line item:
   • Rename an existing line item, replacing it with the desired custom account group
   • Assign the corresponding account group 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 the newly added rows; copying from existing rows in the same section ensures accurate results

• Grouping Account Groups

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

• On Column C 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 E showing the consolidation of the account groups listed on Column B

Note: User also have the ability to group QuickStart categories to form custom ones. However, currently, merging QuickStart categories with Account Groups is not feasible. This functionality will only be available if the QuickStart category aligns precisely with an account group. Although this is not recommended as it may lead to data inaccuracies.

• 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 D of the Cash Flow statement tab, users can list all the account code or ranges they wish to use or consolidate. Same as account groups, the semi-colon delimiter should be used as well for the groupings.
• If needed, rename the line item on Column E showing the consolidation of the account groups listed on Column C

Please note that Account Group and Account Code cannot be used at the same time. If both have values, it will result into 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