Show/Hide Toolbars

DBA Help

(GL Analysis > Balance Accounts)

Use this screen to explore activity in your Balance Sheet Accounts in your General Ledger.  In the settings screen, you can define custom date range columns or a series of date range columns to view trends over time.  You can define data columns for either net change in transactions or a balance as of the period end date.  There are special columns for totals, averages, and comparisons of two columns. In the reporting grid, all of the column headers contain filtering capabilities and you can drag and drop for custom groupings of data.   For the net transactions cells, you can drill through to the GL Transactions Detail screen from fields highlighted in blue.

 

Menu_GL_Analysis_BalanceAccts_ReportGrid

Related Video:

Video_Link Analysis Balance Accounts Overview

Screen Details

Selection Filters

Use the selection filters in the upper panel, define your date columns in the Settings button, and select the Load button to generate a list of transactions in the reporting grid.

GL Account

Select One , Range of GL Accounts, or All Balance Accounts.   This report will only allow for reporting on balance accounts (Account Type = Assets, Liabilities, and Owners Equity).

NOTE: The Current Earnings and Retained Earnings GL Accounts are not included in this analysis screen.  These are calculated values and are best accessed via the GL > Reports > Balance Sheet.

Additional Settings

By default the report will only display GL Accounts that have transactions in one or more of the defined time frame report columns.   Check the box if you wish to see GL Accounts within your criteria that have no activity.

Report load times

We include the data pre-count and load times for this report.   For balance columns, there can be a significant load time.  Each balance column added requires a calculation of all transactions up to that date.  Net transaction columns will have much quicker load times.

 

Buttons  

Load

After you have defined your reporting columns in the Settings screen, click this button to populate the reporting grid.    

Settings

The settings button is where you define and edit the dynamic columns for your analysis (see details below).

Output

Click this button to output the grid contents using a variety of filtering and sorting options.   You can print this report or output to Excel.

Link:

Screen_Help   Output Grid Tool

Collapse, Expand  

The grid supports a drag and drop grouping of data.

Customize. Reset

You can customize the fields that display in the grid.  Reset will restore the default settings.

 

Settings Screen (Settings Button)

This Settings button will be used to add and edit the columns for your analysis.   You must first define your columns and then use the Load button to populate the report grid.

 

Menu_GL_Analysis_BalanceAccts_Settings

Analysis Title

This is the name of the report and output to Excel.

Settings Screen Buttons

Add Button

The Add button is used to define the columns for analysis. (See details below)

Edit Button

Select any column in the grid to edit.  

Delete Button

Select any column in the grid for deletion.   If the column is used in a calculation you may be prevented from deletion.   You can use the clear all and start over.

Clear All

This will clear all columns in the Settings grid for a quick start over.

 

Add Button

You must add a time period column(s) or a time period series of columns prior to loading data for analysis.  

 

Menu_GL_Analysis_BalanceAccts_Add

 

Add Time Period column - Net Transactions

This will allow you define a period of time for your report analysis.  This will calculate the total net change of value for the time specified.

 

Menu_GL_Analysis_BalanceAccts_Settings_NetDateRange

 

Column Type

This will describe the criteria for the data summary for the defined report column.

Column Title

This will be the heading of the column of your report.  You can edit this at any time and then Load the report to update in the report analysis grid.

Column Visible

If this is checked, the column will be visible in the report grid.  

Transaction Date

Select either a date range or accounting period range.

Add Time Period series of columns - Net Transactions

This will allow you define a sequential series of columns for your report analysis.  

 

Menu_GL_Analysis_BalanceAccts_Settings_Series_Net

 

Column Type

This will describe the criteria for the data summary for the defined report column.

Number of columns to create

This will set the number of sequential date columns to create.  You will select the grouping and last date in a timespan and it will create the trailing time period columns.  

Timespan of each column

You define the timespan of each column and select the final date in the range.  

By Fiscal Period

Report column groupings by period, quarter or year.  Choose the last accounting period in the range. Verify the number of columns to create.

By Calendar Date

Report column groupings by day, week, month, quarter or year.  Choose the last date in the range. Verify the number of columns to create.

Other Timespans

Define a custom range of time.  Choose the last date in the range. Verify the number of columns to create

 

Report Examples:

For a quarterly report for 2021, you choose the Quarter radio button, choose the last period of Q4 for 2021 or the last date of Q4 of 2021, and set the number of columns to create = 4.

For a monthly report for 2021, choose the Month radio button, choose the final period or last day of 2021, and set the number of columns to create = 12.

Title of each new column

Choose the format of the columns created.  Optionally you can add a prefix to your columns.

Display order of the new columns

This allows you to designate your preference of date order of your report columns from left to right.

Total Column

If selected, a column will be created that will be the total of all columns created with this tool.  You can customize the column header title.

Average Column

If selected, a column will be created that will be the average of all columns created with this tool.  You can customize the column header title.

Add balance as of date column

This will calculate a balance as of a date or the end of a period.  

NOTE: The calculation of the balance for each column added may take some time for companies with extensive history.

 

Menu_GL_Analysis_BalanceAccts_Settings_AsOfThisYear

 

Column Type

This will describe the criteria for the data summary for the defined report column.

Column Title

This will be the heading of the column of your report.  You can edit this at any time and then Load the report to update in the report analysis grid.

Column Visible

If this is checked, the column will be visible in the report grid.  

Transaction Date

Select either a date range or accounting period range.

Add Balance series of columns

This will allow you define a sequential series of columns for your report analysis.  This will display the balance as of the end date of each time grouping.  

NOTE: The calculation of the balance for each column may take some time for companies with extensive history.  

 

Menu_GL_Analysis_BalanceAccts_Settings_Series_Bal

 

Column Type

This will describe the criteria for the data summary for the defined report column.

Number of columns to create

This will set the number of sequential date columns to create.  You will select the grouping and last date in a timespan and it will create the trailing time period columns.  

Timespan of each column

You define the timespan of each column and select the final date in the range.  

By Fiscal Period

Report column groupings by period, quarter or year.  Choose the last accounting period in the range. Verify the number of columns to create.

By Calendar Date

Report column groupings by day, week, month, quarter or year.  Choose the last date in the range. Verify the number of columns to create.

Other Timespans

Define a custom range of time.  Choose the last date in the range. Verify the number of columns to create

 

Report Example:

For a quarterly report for 2021, you choose the Quarter radio button, choose the last period of Q4 for 2021 or the last date of Q4 of 2021, and set the number of columns to create = 4.

Title of each new column

Choose the format of the columns created.  Optionally you can add a prefix to your column titles..

Display order of the new columns

This allows you to designate your preference of date order of your report columns from left to right.

Total Column

If selected, a column will be created that will be the total of all columns created with this tool.  You can customize the column header title.

Average Column

If selected, a column will be created that will be the average of all columns created with this tool.  You can customize the column header title.

 

Comparison of 2 columns

After you have added two or more columns to the Settings grid you can compare any two columns of data.  You can calculate and display a total net change and/or a percentage change.  

 

Menu_GL_Analysis_BalanceAccts_Settings_CompareTwo

 

Column Type

This will describe the criteria for the data summary for the defined report column.

Column Visible

If this is checked, the column will be visible in the report grid.  

Columns to compare

Select the two columns for comparison.  Note that the second column selected is subtracted from the first column.

Net Change column

You can select to show Net Change and/or % Change columns.  You can customize the column title for each.

Sum of multiple columns

After you have defined multiple columns you can sum any two or more.   Note:  The series of columns has a built in total option.

 

Menu_GL_Analysis_P&L_TotalColumns

Column Type

This will describe the criteria for the data summary for the defined report column.

Column Title

This will be the heading of the column of your report.  You can edit this at any time and then Load the report to update in the report grid.

Column Visible

If this is checked, the column will be visible in the report grid.

Select Checkbox

Select all columns you wish to be included in the total calculation.

 

Average of multiple columns

After you have defined multiple columns you can average any two or more columns.   Note:  The series of columns has a built in average option.

 

Menu_GL_Analysis_P&L_AvgColumns

Column Type

This will describe the criteria for the data summary for the defined report column.

Column Title

This will be the heading of the column of your report.  You can edit this at any time and then Load the report to update in the report grid.

Column Visible

If this is checked, the column will be visible in the report grid.

Select Checkbox

Select all columns you wish to be included in the average calculation.

Analysis Grid Fields  

GL Account - Description

The hybrid value of the GL Account and Description was designed to be used for grouping transactions by GL account when looking across a range of accounts for a period of time

Account Type, Account Class, Account Group

We have added sort order numbering (From General Ledger Setup > Account Classes & Groups) to help preserve your preferred order when grouping data sets.

 

Menu_GL_Analysis_BalanceAccts_Groupings

X-Ref Code

This is the cross reference GL code for users that are on Financial Transfer.  If you would like to hide this column, go to the Customize button and un-check the box.

User defined data columns

The data columns are defined in the settings screen.  If you would like to modify a column header, you can go to the Settings button and edit the title.

Data drill through

For net transaction cell values in light blue, you can drill through to the GL Transactions Detail screen.

 

Menu_GL_Analysis_BalanceAccts_ReportGrid_DrillThrough

 

Output Screen

Click the Output button to output the grid contents using a variety of filtering and sorting options.  You can print this report or output to Excel.

Link:

Screen_Help   Output Grid Tool

 

Excel Formatting Tip:  Currency formatting does not pass through to Excel.   After you export a report to Excel, highlight all of the Excel Column Headers that are currency values and right click, select Format Cells, on Number tab select Category = Currency and choose preferred formatting.

 

ExcelFormatCells

 

ExcelFormatting