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.
No comments:
Post a Comment