Steps to convert a Trial Balance into a Financial Statement Grouping Schedule in IDEA

Brian Element

Forums

Hi,

 

MIS and Accounts Reconciliation Teams often need to download Trial Balances and convert the same into Financial Statement Grouping Schedules for internal and external stakeholder presentation.

 

So as an example if the Trial Balance contains -

 

Account Code                      Account Description                      Grouping Code                 Amount

 

5600001                               Freight Outward                            5600000                            350

5600002                               Trade Discounts                            5600000                            250

5600003                               Volume Incentives                         5600000                            200

 

In the Financial Statement Grouping Schedule the 3 items sampled above may appear as

 

Grouping Code                     Grouping Description                 Grouping Amount

 

5600000                                Selling Expenses                       800

 

The Trial Balance can be taken as the Primary Database and joined to the Grouping Schedule as the Secondary Database on the common matching key field Grouping Code.

 

Then by applying a Summarization on the joined file with Fields to Summarize being Grouping Code then Grouping Description and Numeric Fields to Total being Trial Balance Amount one can arrive at -

 

Grouping Code                     Grouping Description                 Grouping Amount

 

5600000                                Selling Expenses                       800

 

using IDEA Software.

 

This activity may seem a breeze in MS-Excel whilst dealing with a single Trial Balance for a single reporting unit.

 

But the moment the environment changes to multi-unit reporting it is prudent to use IDEA given its limited outcome related key strokes and possibility to convert the process into a Visual Script - Macro.

 

Kind Regards

 

Jairam                          

 

ebmansoor Sun, 09/26/2021 - 04:53

Hi Brian
 
Thanks for the insightful script.
 
I am looking for a script that reconciles the Journal Entries to the Trial Balance.
 
I found a script in IDEA Lab however based on my understanding of the script - the script does not include an IF statement based on account type.
 
In accounting standards, the reconciliation is done as follows:
 
If Account Type = Balance Sheet then Opening Trial Balance + Journal Entries = Ending Trial Balance
Else If Account Type = Income Statement then Journal Entries = Ending Trial Balance
 
Whilst I am in the process of attempting to create the script; I was wondering if you already have a script that caters for the above? As this will save me tons of time!
 
Thank you in advance.
 

Brian Element Mon, 09/27/2021 - 07:04

Good day ebmansoor,

Unfortunately I don't have a script that does what you are looking for.