AC-GL-BT9 Balance Sheet Forecast (budget-based)

Created by Aljun Talle, Modified on Thu, 07 Mar 2024 at 04:39 AM by Aljun Talle

Applies to

  • Product version: Velixo NX
  • ERP: Acumatica
  • Functional area: Financials, Budgeting, Other
  • Plan: Free, Essentials, Professional, Advanced
  • Template type: Production Report template


TABLE OF CONTENTS


Description

The AC-GL-BT9 Balance Sheet Forecast Report (budget-based) is a Velixo financial tool that can project a company's assets, liabilities, and shareholders' equity over a future period. It provides valuable insights into a company's financial health and can be used as well in predicting future financial positions based on historical data and anticipated changes in business operations.


Coverage

This workbook includes the following sample reports:

  • Balance Sheet Forecast: The Balance Sheet Forecast Report comprises pre-defined account classes, assigned to various sections for a comprehensive financial overview.
  • Control: a control report that can be used to confirm that the data is accurate and balanced.
  • Options (hidden): the template’s settings sheet that is used for data validation lists and various lookups and configurations.
  • Information (hidden): the information sheet of the report, used for storing useful details about the report such as code, version, and 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


Documentation

 

Navigating and utilizing the AC-GL-BT9 Balance Sheet Forecast Report (budget-based) is a very straightforward process, designed for simplicity, genericity and efficiency. The template comes pre-configured with generic account classes 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 the user to include unposted transactions

 



BS structure

 

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

The following sections are used by default:

  • Current Assets
  • Non-Current Assets
  • Current Liabilities
  • Long-Term Liabilities
  • Capital Stock
  • Other Comprehensive Income



Generic Account Classes: To enhance the report's usability, Velixo has incorporated a set of predefined account classes. Users have the flexibility to either utilize them as is or tailor them to align with their specific reporting needs.



Metrics: The report includes the following metrics :

    • YTD (Actuals) vs Full Year (Actuals + Budget) vs Full Year Forecast
      • Col F: the turnover from the beginning of the period up to the date specified in the "As of" filter
      • Col G: the total of the Actual and Budget balance from Col K to V
      • Col H: the budget turnover balance for the full year
    • Variance (YTD Actuals vs Full Year (Actuals + Budget)) 
      • Variance $: the amount difference between the YTD Actuals and the Full Year Actual + Budget totals
    • Monthly Movement (Actuals or Budget)
      • Col K to V: the turnover for the period and can be the Actuals or Budget based on the date specified on the "As of" filter.




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 Balance Sheet Forecast tab, 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
    • 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.



  • 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 class and account code levels, conduct a thorough investigation to resolve any differences.


Using the Control report

 

Committed to upholding data integrity across the report and Acumatica, the Control sheet serves as a supplementary component to the primary Balance sheet Forecast tab. Its purpose is to validate the data extracted by Velixo on an account class 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 class, 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: Extract balances at the account code level to guarantee accurate capture and reconciliation of BS balances with the account classes listed on the Balance Sheet Forecast tab. The Account code series is highly flexible, and users may adjust the settings to match their specific requirements at any time.
  • BS Balances: Displays total balances from the Balance Sheet Forecast tab for reconciliation with the Account Code balance section.
  • Differences: Highlights reconciliatory differences between the Account code and account class sections, marked in red font for investigation

 

 

In a balance sheet, it is crucial that the Total Assets match the sum of the Total Liabilities and Total Equity for the chosen period. Also, we check for any differences at the overall Balance sheet 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 Balance Sheet Forecast tab, missing account classes, renamed account classes or any user customizations.


Understanding the Report Formula


Velixo created the template using a combination of Excel functions and Velixo functions for the Balance Sheet Forecast report. The report incorporates two primary formulas on the Balance Sheet Forecast 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
    1. Used in all the transaction columns, this formula’s primary objective is to extract balances for the account classes from Acumatica
    2. Velixo functions, specifically ACCOUNTENDINGBALANCE, ACCOUNTBEGINNINGBALANCE and ACCOUNTTURNOVER, power these columns
    3. To facilitate flawless execution of Velixo functions, an IF statement is strategically placed. 
    4. In the account class validation formula, any invalid class 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 of 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 account classes do not quite fit the requirements.


  • Adding New Account classes


If users desire a structure beyond the pre-defined account classes, they can leverage account classes set up in their ERP instance. 


Users have two options when adding new account classes:

  1. Re-purposing an existing pre-defined account class:
    • Rename an existing account class, replacing it with the desired custom account class.
  2. Adding a Row in the Required BS 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 Class

 

Removing a pre-defined account class is not really necessary 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 class rows and ensures the report remains fully dynamic should usage of account classes change over years or transactions get created against new account classes.


However, if an account class 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 Accounts Classes

 

If needed, the user can group multiple account classes into one and create a summary account class. Velixo enabled this feature so that user can consolidate similar account classes into one and present them in the Balance Sheet as one.

  • On Column B of the Balance Sheet Forecast tab, user can list all the account groups and using the semi-colon ( ; ) delimiter to grouped them.
  • If needed, rename the pre-defined account class on Column E showing the consolidation of the account classes 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 account class.


  • On Column C of the Balance Sheet Forecast tab, users can list all the account code or ranges they wanted to use or consolidate. Same as account classes, the semi-colon delimiter should be us as well for the groupings.
  • If needed, rename the pre-defined account class on Column E showing the consolidation of the account codes listed on Column C.

 

Please note that Account Class and Account Code cannot be used at the same time. Doing so will result in calculations errors for the transaction balances. If a structure is not used, make sure to leave them blank to not cause any errors.


Version history

 

Version

Released on

Changes

1

12 Feb 2024

N/A. Initial version of the template.


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