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.