Developing automated consolidated trial balance for 36 companies in the trading domain

Developing automated consolidated trial balance for 36 companies in the trading domain

Problem: the end client required consolidated ledger balances and balance sheet details across 36 companies. With the given software that the end client had the process was taking a lot of time. The system would hang during the process of consolidation and generation of the required reports.

Methodology of the ETL team: the ETL team consolidated data ledger data from all the 36 companies. In order for the end client to generate balance sheet/trial balance details on any fiscal date the ETL team did the following activities:
·         Perpetual ledger balance details were stored by partyledgername and ledgername.
·         The associated cost center details for the ledger were also stored. The Profit and Loss statements could be generated according to the cost center details.
·         The ETL team was able to generate the balance sheet details, trial balance details across all the companies.
·         The end client could get the access to the balance sheet details across multiple companies.

The following system was used to match the trial balance details:
·         Data audit: the ETL team used the perpetual ledger balance details to arrive at the closing ledger balance details on the given fiscal date. The closing ledger balance on the given fiscal date was matched with the trial balance details from the software. The software was able to handle the cases where opening ledger balance was non zero.

Final result:
·         The audit numbers of the resulting output were matching with the software output.
·         The report refresh times was crashed by more than 90%(ninety) percent
·         The software did not hang during the process of initial and incremental data load and during the process of report generation.
Other benefits to the end client:
·         Better scope of cash flow availability: since the end client is having the cash flow balances on each fiscal date, hence the end client is able to capture the variances in payments across all ledgers. This helps the end client at better planning of the cash flows.
For the process of data consolidation, the following actions were done:
·         Data cleansing
·         Data consolidation
·         Report generation using C#/.net interface.

       Further ledger analysis was done as given in the following link:
       Ledger analysis link

    Partner details:

Prepared by :

Shubham Singh
Phone: +91-0124-4365845

Popular posts from this blog

Inventory optimization through Turbodata

Capturing Data Entry errors for audit purposes

Predictive analytics and forecasting at lower costs through data compression and sql reduction