BC-GL-RT1 Profit and Loss Summary

Applies to

• Product version: Velixo NX
• ERP: Dynamics 365 Business Central
• Functional area: Financials, Other
• Plan: All
• Template type: Production Report template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Filters
  • P&L Structure
• User Guide
  • Configuring the report
  • Using the report
  • Using the As of (Period) filter and understanding Time calculations
  • Using the Control report
  • Managing Account Structures
• Version History

Description

The BC-GL-RT1 Profit & 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 generic account subcategories which make sure the report is generic, dynamic and consistent with Dynamics 365 Business Central. However, you may have changed those account subcategories or created new ones, and they must be reviewed.

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

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 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
• Query functions

Preview

Download this template

Template will be available for download soon

💡Having trouble downloading this file?

Simply right-click on the link and select Save Link As

Documentation

Navigating and utilizing the BC-GL-RT1 Profit and Loss summary report is an intuitive process, designed for simplicity, genericity and efficiency.

Filters

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

• Dimensions: lists all dimensions present in Dynamics 365 Business Central instance
• As of (Period): a date field. Please use the last date of a financial period/month 
• 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.

Note: The dimension list updates dynamically based on the number of dimensions configured in your instance. However, the template can process a maximum of 10 dimensions per company. If your instance includes more than 10 dimensions, please contact Velixo Support for assistance.

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:

• Income
• Cost of Goods Sold
• Operating Expenses
• Non-Operating Expenses

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

Metrics: The report includes the following metrics :

• On rows
  • Gross Profit 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

Configuring the report

A quick setup is needed to get the most out of the report. These settings control how balances are pulled from the system and displayed in the report.

Setting Up Account Subcategories or Account Codes/Ranges

Each statement line in the report should be mapped to the corresponding account subcategories or account codes. This mapping defines how Velixo retrieves data from the system. To set this up:

• Expand the grouped columns on the leftmost side of the report

• Doing so will display several configuration columns. The Account Subcategory and Account Code/Range columns can be found in columns D and E

• The report includes default subcategories based on the Velixo demo instance. These may not match your company’s configuration. Update the subcategories or account codes as needed to align with your instance setup.

Note: By default, the Account Code takes priority over the Account Subcategory. If both are specified in the configuration, the template will ignore the subcategory and extract balances based solely on the account code.

Setting Up Dimensions per Statement Line (Optional)

To provide greater customization and flexibility, users can apply dimension filters at the statement line level. The dimension columns span from columns F to O.

The template can process up to a maximum of 10 dimensions per instance. If your system is configured with fewer than 10 dimensions, some columns in the template will remain blank - this is a current template UI limitation and does not affect how the report extracts balances.

Note: The global report filter setup takes priority over statement line level filters when the same dimension is used in both. For example, if the Area dimension is selected at both the global and statement line levels, the value specified in the global filter will take precedence and be used by the report when extracting data.

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 Dynamics 365 Business Central instance.

• Choose Desired Filters: after updating the Options sheet, select desired global filters for the report:
  • Dimensions
  • As of (Period)
  • Budget ID

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

• Variances Investigation: if discrepancies arise between data extracted at the account subcategory 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 Dynamics 365 Business Central, 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

Managing Account Structures

The calculated transaction amounts are determined solely by the account codes mapped to the designated Account subcategory in Column D or the specified account code or range in Column E. This ensures the report is fully ready for use by anyone, 100% out-of-the-box.

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 subcategories don't quite fit the requirements.

 Adding New Account Section

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

 Users have two options when adding new account structure:

    1. Re-purposing an existing Account subcategory:

• Rename an existing account subcategory 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 subcategories change over years or transactions get created against new account subcategories.

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.

Version History