SI-GL-RT1 - Profit and Loss summary

Created by Damien Zwillinger, Modified on Tue, 09 Apr 2024 at 11:43 PM by Aljun Talle

Applies to

  • Product version: Velixo NX
  • ERP: Sage Intacct
  • Functional area: Financials
  • Industry: General, Services, Not-For-Profit
  • Plan: Free, Essentials, Professional, Advanced
  • Template type: Production Report template


TABLE OF CONTENTS


Description

The SI-GL-RT1 Profit and Loss summary report is considered a key component in the month-end reporting process and is often used by managers and executives to analyze revenues, expenses and profitability across the business. 

This report provides monthly, quarterly and year-to-date variances to both budget and last year’s actuals, enabling the user to easily compare amounts across time. 


The chart of accounts structure of this report is based on Account categories which make sure the report is generic, dynamic and consistent with Sage Intacct. 


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).


Users can drill down on any cell of the report to analyze the value and see the accounts and transactions that make up the number of each account category cell.


The current version of this template is v1.2


Coverage

This workbook includes the following sheets:

  • Profit & Loss: the Profit & Loss report, at the granularity of account categories, and reporting on Actuals and Budget data across MTD, QTD, YTD and Prior Year YTD.

  • Control: a control report that can be used to confirm that the data is accurate and balances.

  • Options: the report'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 in order 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 Velixo 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 if not):

  • Financial functions
  • List functions


Preview


Download this template


(General / Services QuickStart version)
(Not-For Profit QuickStart version)




Documentation


Navigating and utilizing the Velixo SI-GL-RT1 Profit and Loss summary (or SI-GLRT1.NFP Statement of Activities) report is an intuitive process, designed for simplicity, genericity and efficiency. The template comes pre-configured with QuickStart account categories, 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
  • Budget ID: for usability purposes, Velixo lists all of your Budget Ids in a dropdown list. Should you want to select more than one, you can also concatenate them in the Filter value field.

 

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

 

 

P&L structure


Sections: Velixo has thoughtfully structured the P&L using multiple common sections that should be generic, by default. 

The following sections are used by default in the report:

  • Revenue
  • Cost of Revenue
  • Operating Expenses
  • Non-Operating Expenses
  • Other Income
  • Other Expenses
  • Tax

 

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. 


Note: Only minor naming adjustments may have to be considered, see: Understanding Account categories' use in Templates.


 

Note: Auto Hide Zero Rows in Selected Range has been configured on the YTD Actuals column. This means that even if some of Sage Intacct's standard Account categories are not mapped to any of your accounts or if there is no value in YTD Actuals column, then those account categories will remain hidden and the report will work as expected. 



Metrics: The report includes the following metrics :

  • On rows
    • Gross Profit Margin %
    • EBITDA / EBITDA Margin %
    • Net Profit Margin %
  • on columns:
    • MTD / QTD / YTD / Prior Year YTD
      • Actual:  the turnover from the beginning of the period up to the date specified in the "As of" filter
      • % of Rev: the share of revenue of the current row (compared to Total (Gross) revenue).
      • Budget: the budget amount from the beginning of the period up to the date specified in the "As of" filter.
    • MTD/QTD/YTD Variance 
      • Var $: The amount difference between the Budget amount and the Actual amount for the period.
      • Var %: The variance ratio between the Budget amount and the Actual amount for the period.
    • Prior YTD YTD
      • Var $: The amount difference between the Prior Year YTD Actual amount and the YTD Actual amount
      • Var %: The variance ratio between the Prior Year YTD Actual amount and the YTD Actual amount 

 


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 Profit and Loss report 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.


  • 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)
  • Budget ID



  • Automatic Report Update - Selecting any filter will automatically update report balances. Begin analyzing the report with the latest data.
  • Validation and analysis: ensure accuracy by verifying balances in the Control sheet. Confirm that differences for each total are zero.
  • Variances Investigation: if discrepancies arise between data extracted at the account category and account code levels, conduct a thorough investigation to resolve any differences.


Using the As of (Period) filter and understanding Time calculations


MTD (Month-To-Date) amounts:

  • Returns the movement for the current month up to the specified as-of-date.
  • For example, if a user selects January 31st, the returned balance is from January 1st to January 31st.

QTD (Quarter-To-Date) amounts:

  • Returns the movement for the current quarter up to the specified as-of date.
  • For instance, if a user selects February 28th, the balance returned spans from January 1st to February 28th.
  • To obtain the full quarter balance, one would specify the last date of the quarter (e.g., 3/31, 6/30, 9/30, 12/31).

YTD (Year-To-Date) amounts:

  • Returns the movement for the current year up to the specified as-of date, using the Fiscal start of year as defined in the Options sheet.
  • 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, one would specify the last date of the fiscal year.

Prior Year YTD amounts:

  • Returns the YTD movement for the corresponding period in the prior year.
  • For instance, if a user selects 6/30/2019 with a Fiscal Start Month set to January, the balance returned spans from 1/1/2018 to 6/30/2018.
  • Similar to the current month, this does not represent a year-to-date balance but captures only the movement for the specified month.



Using the Control report


Committed to upholding data integrity across the report and Sage Intacct, the Control sheet serves as a supplementary component to the primary Profit and Loss sheet. Its purpose is to validate the data extracted by Velixo on an account category basis by cross-referencing it with the extracted balances at the account code level.

 

Given that the balances of the account codes aggregate to the account category, the Control sheet ensures the absence of discrepancies in the total for each account type. This verification process enhances the reliability and accuracy of the overall financial reporting template.

 

The Control sheet is divided into three sections:

 

  • Account Code Balance Check: Extracting balances at the account code level to guarantee accurate capture and reconciliation of P&L balances with the account categories listed on the Profit and Loss tab. The Account code series is highly flexible, and users may adjust the settings to match their specific requirements at any time.
  • P&L Balances: Displays total balances from the Profit and Loss tab for reconciliation with the Account Code balance section.
  • Differences: Highlights reconciliatory differences between the Account code and account category sections, marked in red font for investigation

 

 

In this P&L summary report, it is crucial that the total balance matches the closing retained earnings for the chosen period. Also, we check for any differences at the overall P&L level, comparing it to the detailed account codes.

 

If any discrepancies in the report are displayed, it could be caused by a custom structure on the Profit and Loss tab, missing account categories, renamed account categories or any user customizations. 


To troubleshoot further, you could redownload and reconnect the report to your instance, verify that no account category row returns any error, and perform data validations by comparing this report with Sage Intacct's out-of-the-box financial reports. If you believe the report contains an error, please report it to Velixo Support.


Understanding the Report Formula


Velixo created the template using a combination of Excel functions and Velixo functions for Balance Sheet report. The report incorporates three primary formulas on the Balance Sheet tab:


  • Account Group 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 both Current Year and Prior year columns, this formula’s primary objective is to extract balances for the account categories from Sage Intacct ERP
    2. Velixo functions, specifically SI.TURNOVER 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
  • Location
  • Department
  • Quarter
  • Budget ID
  • Existing Account Groups
  • Books


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

 

The calculated transaction amounts are determined solely by the account codes mapped to the designated QuickStart category in Column C. This ensures the report is fully ready for use by anyone, 100% out-of-the-box, provided the account codes are accurately linked to the QuickStart category in Sage Intacct.


However, as outlined earlier in this document, users have the flexibility to tailor this report to their specific needs. This is especially useful when the default QuickStart categories don't quite fit the requirements.

 

  • Adding New Account Section

 

If users desire a structure beyond the provided account categories, 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 Account Category:

  • Rename an existing account category line, replacing it with the desired custom account group.
  • Ensure exact naming consistency with the ERP instance for Velixo to accurately return amounts.

2. Adding a Row in the Required P&L 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.

 

  • Removing an Account category

 

While users have the freedom to remove an account category, it's strongly discouraged as the report makes use of Velixo’s automatic hiding of rows with zero values, which has been configured on the Year-to-Date (YTD) Actual values. 

This feature eliminates the need for manual deletions of account category rows and ensures the report remains fully dynamic should usage of account categories change over years or transactions get created against new account categories.

 

However, if an account category should really be deleted:

  • Utilize Excel's delete row function for removal, followed by thorough data validation and reconciliation on the report.
  • Always validate data accuracy using the Control sheet.


  • Grouping Account Groups


The user can group multiple account groups into one to create a summary account. Velixo enabled this feature so that user can consolidate similar account groups and present them in the Profit and Loss report as one.

  • On column C of the Profit and Loss tab, user can list all the account groups and using the semi-colon ( ; ) delimiter to group them
  • If needed, rename the account category on column E showing the consolidation of the account groups listed on column C


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 Profit and Loss 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 account category on column E showing the consolidation of the account codes listed on column D.


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


VersionReleased onChanges
1January 18, 2024N/A. Initial version of this template.
1.1March 08, 2024
  • Reviewed Account categories in column B and ensured that every row used the default Sage Account categories and no Account groups.
  • Highlighted cells in Column C for those Account categories that do not have a corresponding 1:1 Account group. This caused the Template not to return any amount. For this scenario, users are required to enter a concatenated list of the accounts mapped to the Account Category.
  • Updated the Template documentation and added a new Configuring the Template section.
1.2April 10, 2024
  • Implemented the latest argument for the function SI.EXPANDACCOUNTRANGE to extract mapped accounts linked to the QuickStart category.
  • Revised the Template documentation, removing the Configuring the Template section.


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 atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article