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:
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.
