Turbodata Analytics -Faster reports from Tally

 Purpose of Turbodata analytics Module

Contact details: Apoorv Chaturvedi

email: support@turbodatatool.com;support@mndatasolutions.com

phone:+91-8802466356

The ETL product developed in tally has been created for the following purposes:

·                   Tally stores the data in a tree based structure(heirarchial database). This database structure implkies that a child can have only oner parent. This results in the following issues for the end client:

o       Lot of data duplicacy

o       Need to transverse the tree according to the reporting requirements.

What does the Tuirbodata ETL tool do?

·                   It provides a fast and easy way to transverse the tree based structures.

·                   It provides an interface to convert the tree based structure to relational databases. This has been achieved using the following methods:

o       Flat file extraction

o       Extraction through JSON.

What does the product mean for the business user:

·                   Extensive data audit facilities: the data duplicacy implies that the end client needs to do extensive data audit. For example HSN details can be stored in stock group, stock item or the tacx classification object heirarchies. The data duplication implies that there can be data entry mistakes. An audit firm needs quick and easy way to analyze the data by tranversing the entire tree based structure with quickness and ease.

·                   Easy access to data at the database level and at the cloud

o       The ETL tool enables simple and complex reports to be extracted at the relational database level.

Attached is the benefits matrix for the same:

BENEFITS

Sample Sundry creditors and debtor report

From this report user will get the complete details of the ledger master in a single button click. The report helps extract the data faster from Tally by extracting the required data in a single instead of multiple clicks as shown below:

The extractor tool gives the following output:

The extracted field details are as follows

·                   Party Name: Creditor and debtor name

·                   Entity Type

·                   Party Code

·                   PAN

·                   Address

·                   Pin code

·                   State

·                   Country

·                   Contact Person

·                   Phone

·                   Mobile

·                   Email

·                   Entry Date

Bill Receivable and Bill Payable Reports

This particular report has been developed based on the following parameters:

·     The voucher types included sales, purchase, debit notes, credit

notes, payment and receipt only. The given report is only for those customers who maintain their sales and purchase details in inventory view only.

·     The bill id reference details for journal vouchers is Work in

progress

·     The ageing of the bills has been done based on the current fiscal

date only. The end user does not have the flexibility to change the date.

·     The report shall run across the entire Tally instance at one go

across all ledgers.

·     The report is database friendly. The report can easily be

imported to Powerbi, tableau and other BI tools.

·     The report excludes the collection status for on account entries

(those payment and receipt entries where the bill reference number has not been given).

·     The logic of the report includes the following:

o  Accounts receivable: sales, credit notes, stock journal

credit receipts

o  Accounts payable: purchase, debit notes, stock journal

debit and payments

Get overdue days by bill ID for Sundry Debtors and Sundry Creditors with a single button click. Incorporates Purchase, sales, payment and receipt logic.

Separate view for creditor and debtor report is also available on a single button click.

Fig: Creditor Report

HSN REPORT

HSN analysis: For most of the auditors, HSN analysis is complicated due to the fact that HSN details can be stored at multiple levels for inventory and ledger. Also Tally follows is own hierarchy for reporting for GST by HSN. The hierarchy is as follows:

1.)             Voucher

2.)             Ledger

3.)             Group

4.)             Stock group

5.)             Stock item

6.)             Company

Based on the given hierarchy and assuming that the end user has input GST applicability at each level of the hierarchy, Tally has built in functions $$GetInfoFromHeirarchy and $$GetNonEmptyValuefromHeirarchy

The limitations of these functions are as follows:

1.)             In case the end user has missed the GST applicability at any of the layers then those HSN details for the invoices shall be missed altogether

2.)             The report is as discrepancy with the “rate set up” report.

There are the other layer details:

In order to simplify the same, the ETL team has developed a flat report for HSN analysis.

Details are as follows:

1.)             The module covers only stock item and stock group hierarchies

2.)             The tax classification hierarchy is yet to be added.

3.)             Any level of hierarchies within the stock group can be added for the same. The code looks for the first non-empty HSN details in the hierarchy.

Transaction Reports

In order to extract transaction reports from Tally, we need to go through the daybook configuration process as follows:

For the HSN wise analysis, the attached report is as follows:

This report contains following features:

·                   More than 40 fields of voucher and ledger are available.

·                   Masters included: Company, ledger

·                   Transaction: Voucher.

·                   Business logic for Tax calculation, GST details are included.

o   Sales, Purchase, Credit notes and debit notes voucher types included.

This report is at the ledger level daybook analysis with a comprehensive set of fields for sales, purchase, debit notes and credit notes.

Separate view for sales and purchase report is also available on a single button click. Snapshots of these reports are attached below:

Fig: Sales Report

Fig: Purchase Report

BATCH ALLOCATION REPORT

Batch allocation report is a consolidation of sales, purchase, stock journal, credit notes and debit notes by batch id. In this particular report, purchase rate is used for the batch cost.

This report can be used to gauge batch profitability and stock loss analysis.

Above figure shows that to extract batch allocation report in tally, user have to go through these four clicks. But in our case, we can extract this in a single click as shown below.

GST Audit report-Find GST Errors with quickness and ease using Microsoft Powerbi

 The attached solution comes with readymade extractor for GST data. The extractor extracts the data at the voucher level and at the stock item level.

What is the purpose of the product?·         The product intends to give clear visibility to various chartered accountants on the filing errors by going through the Tally ERP screens in a minimal fashion.
·         It intends to give GST filing errors in an easy to understand and intuitive manner.
·         It intends to help the organization stakeholders know about the filing details in an easy to understand, prompt manner.
Why is the product required?Tally ERP 9.0 stores the data in a hierarchical fashion. This implies that a child entity can have only one parent. This also implies that there could a lot of data redundancies. That is a tool is required to jump over various tree like attributes to get the required information output.The ERP has various built in functions that help the end user extract the built in HSN details from the voucher from various levels in hierarchise. The tool shall extract the same.Requirements that the product meets:·         If there has been a data entry error at the voucher level or at the stock item master level(stock group, stock item, GST classification) then the given product gives visibility to the same.
·         Data entry errors at the voucher level: for the same the product gives the details of errors at 2 levels:
o   Wrong type of GST entry: in case the state codes of the bill state and company state do not match but the end client has put CGST entry or vice versa then the corresponding error shall be flagged.
o   Wrong type of GST entry input at the voucher level:  attached entry indicates the errors in the following types of scenarios:
o   In case the state codes match for the shipping address and the company address while the state code extract from the party GSTIN does not match with the state code of the company name then an error shall be indicated.
o   In case the state codes do not match for the shipping address and the company address while the state code extract from the party GSTIN matches with the state code of the company name then an error shall be indicated.
HSN Audit errors: in the following scenario the following errors are indicated for the end client:·         Tally ERP finds the HSN code using the default function $$GetInfofromHeirarchy or $$GetNonEmptyValueFromHeirarchy. The usage of the function indicates that all the levels for stock group, stock item are GST enabled. In case any of the levels is not GST enabled then the above functions give blank value.  Our solution helps resolve the error in the following way:
o   It flattens the stock item hierarchy for stock item HSN details, stock group(at the highest possible level) HSN details and the tax classification HSN details. From here on the end user can pick up the right tax value.
o   The report also gives any discprencies across various levels of hierarchy for HSN details for stock item.
BENEFITS

Purpose of Turease analytics Module

The ETL product developed in tally has been created for the following purposes:

·                   Tally stores the data in a tree based structure(heirarchial database). This database structure implkies that a child can have only oner parent. This results in the following issues for the end client:

o       Lot of data duplicacy

o       Need to transverse the tree according to the reporting requirements.

What does the Tuirbodata ETL tool do?

·                   It provides a fast and easy way to transverse the tree based structures.

·                   It provides an interface to convert the tree based structure to relational databases. This has been achieved using the following methods:

o       Flat file extraction

o       Extraction through JSON.

What does the product mean for the business user:

·                   Extensive data audit facilities: the data duplicacy implies that the end client needs to do extensive data audit. For example HSN details can be stored in stock group, stock item or the tacx classification object heirarchies. The data duplication implies that there can be data entry mistakes. An audit firm needs quick and easy way to analyze the data by tranversing the entire tree based structure with quickness and ease.

·                   Easy access to data at the database level and at the cloud

o       The ETL tool enables simple and complex reports to be extracted at the relational database level.

Attached is the benefits matrix for the same:

Sample Sundry creditors and debtor report

From this report user will get the complete details of the ledger master in a single button click. The report helps extract the data faster from Tally by extracting the required data in a single instead of multiple clicks as shown below:

The extractor tool gives the following output:

The extracted field details are as follows

·                   Party Name: Creditor and debtor name

·                   Entity Type

·                   Party Code

·                   PAN

·                   Address

·                   Pin code

·                   State

·                   Country

·                   Contact Person

·                   Phone

·                   Mobile

·                   Email

·                   Entry Date

Bill Receivable and Bill Payable Reports

This particular report has been developed based on the following parameters:

·     The voucher types included sales, purchase, debit notes, credit

notes, payment and receipt only. The given report is only for those customers who maintain their sales and purchase details in inventory view only.

·     The bill id reference details for journal vouchers is Work in

progress

·     The ageing of the bills has been done based on the current fiscal

date only. The end user does not have the flexibility to change the date.

·     The report shall run across the entire Tally instance at one go

across all ledgers.

·     The report is database friendly. The report can easily be

imported to Powerbi, tableau and other BI tools.

·     The report excludes the collection status for on account entries

(those payment and receipt entries where the bill reference number has not been given).

·     The logic of the report includes the following:

o  Accounts receivable: sales, credit notes, stock journal

credit receipts

o  Accounts payable: purchase, debit notes, stock journal

debit and payments

Get overdue days by bill ID for Sundry Debtors and Sundry Creditors with a single button click. Incorporates Purchase, sales, payment and receipt logic.

Separate view for creditor and debtor report is also available on a single button click.

Fig: Creditor Report

HSN REPORT

HSN analysis: For most of the auditors, HSN analysis is complicated due to the fact that HSN details can be stored at multiple levels for inventory and ledger. Also Tally follows is own hierarchy for reporting for GST by HSN. The hierarchy is as follows:

1.)             Voucher

2.)             Ledger

3.)             Group

4.)             Stock group

5.)             Stock item

6.)             Company

Based on the given hierarchy and assuming that the end user has input GST applicability at each level of the hierarchy, Tally has built in functions $$GetInfoFromHeirarchy and $$GetNonEmptyValuefromHeirarchy

The limitations of these functions are as follows:

1.)             In case the end user has missed the GST applicability at any of the layers then those HSN details for the invoices shall be missed altogether

2.)             The report is as discrepancy with the “rate set up” report.

There are the other layer details:

In order to simplify the same, the ETL team has developed a flat report for HSN analysis.

Details are as follows:

1.)             The module covers only stock item and stock group hierarchies

2.)             The tax classification hierarchy is yet to be added.

3.)             Any level of hierarchies within the stock group can be added for the same. The code looks for the first non-empty HSN details in the hierarchy.

Transaction Reports

In order to extract transaction reports from Tally, we need to go through the daybook configuration process as follows:

For the HSN wise analysis, the attached report is as follows:

This report contains following features:

·                   More than 40 fields of voucher and ledger are available.

·                   Masters included: Company, ledger

·                   Transaction: Voucher.

·                   Business logic for Tax calculation, GST details are included.

o   Sales, Purchase, Credit notes and debit notes voucher types included.

This report is at the ledger level daybook analysis with a comprehensive set of fields for sales, purchase, debit notes and credit notes.

Separate view for sales and purchase report is also available on a single button click. Snapshots of these reports are attached below:

Fig: Sales Report

Fig: Purchase Report

BATCH ALLOCATION REPORT

Batch allocation report is a consolidation of sales, purchase, stock journal, credit notes and debit notes by batch id. In this particular report, purchase rate is used for the batch cost.

This report can be used to gauge batch profitability and stock loss analysis.

Above figure shows that to extract batch allocation report in tally, user have to go through these four clicks. But in our case, we can extract this in a single click as shown below.

Contact details:

Apoorv Chaturvedi:support@mndatasolutions.com;support@turbodatatool.com, +91-8802466356

Website:https://mn-business-intelligence-india.business.site/