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.

Turbo Analytics-Batch wise, Bill wise Reports

Turbo Tally Reports

The given product is for the following set of end clients:
a.) Companies looking to give out loans to other firms
b.) Companies looking to factor invoices for other firms
c.) Companies looking for financial advisory services

Background:
The given product has been developed in collaboration with top end financial consultants from Deloitte, Schlumberger. The given product has been developed to provide ease of reporting using Powerbi from desktop solutions such as Tally.

The details of the solution:
o All the reports shall be embedded in Tally
o All the reports have been designed in such a manner that direct integration with databases using
json, xml can be done with ease.
o All the reports can be exported to MS Excel
o Direct integration with PowerBI and Tableau shall be done for ease of analysis.
o The preliminary solution contains 5(five) different reports entailing master details, transaction
details and the payment/receivable ageing details.

For the same the ETL team has devised a solution that entails the following:
 Get complete details of sundry debtors and sundry creditors.
Difference from Tally report:
o The given output shall be a consolidated list of sundry debtors and sundry creditors. The
output can be directly interfaced with any database (cloud or on site relational database).
 Get the complete list of sales, purchase, stock journal, credit notes and debit note outputs. In the
first version the journal details (used for fixed asset purchase) have been excluded.
o Difference from Tally:
 The end client does not need to use multiple reports of sales register, purchase
register and journal register

 Calculate the bill receivable and bill payable ageing details. This is required because the end client
should know the following:
o Outstanding bill payments
o Outstanding bill receipts.

Bill receivable and bill payable ageing report: this particular report captures the following movement
types:
o Sales
o Purchase
o Stock journal debit
o Stock journal credit
o Journal debit
o Journal credit
o Payment
o Receipt
For the given report, the bill reference number is required. The entries that do not have the bill
reference number shall be excluded from the report.
The formulae for the Bill payable ageing as per the product is as follows:
o Total payable outstanding ageing: Total purchase + Total journal debit + Total stock journal debit-
payment - Total debit notes
o Total receivable outstanding specification: Total sales + Total journal credit + Total stock journal
credit - receipt - Total credit notes
Level of granularity of the report: this report has been generated at voucher, allledgerentries and bill
allocations level from the Tally solution. The ageing aggregate has been generated for the consolidated
time period for which Tally has been opened by the end user.

Ageing formulae: the ageing formulae has been devised as the difference between the current fiscal
date as set by the system variable ##SVCurrentdate and the bill due date. The bill due date is the bill
date in case the bill credit period for the sundry debtor or sundry creditor is 0, else it adjusts the bill due
date as per the bill credit period.

In case of On Account entry the following shall be the entries:

·         Due date of payment shall be the same as Bill credit period

·         The number of due days shall be 43830.

·         ‘Name’ in the audit report refers to the bill reference number.

The final report looks as follows:

Attached is the explanation of the above report:

·         DATE: For the sales, purchase invoice

·         BILLID: this is the reference number between sales and receipt, purchase and payment.

·         PARTYLEDGERNAME: self-explanatory

·         TOTAL  OUTSTANDING RECEIVABLES: to date total receivables, that is total sales minus total receipt

·         TOTAL  OUTSTANDING PAYABLES: to date total payables, that is total purchase minus total payments

·         Ageing: this is the number of days between current date and the date of sales/purchase.

Note:

·         This report includes logic for credit notes, debit notes, stock journal and journal also.

The given report has consolidated the following Tally reports:

Also the given report helps give the total ledger outstandings across all ledgers. Within Tally the end client is supposed to extract total outstandings ledger wise

Sundry creditors and Sundry debtors reports:

o   The report shall include all sundry creditors and sundry debtors by looking at whether the ledger entries belong to group sundry debtors or group sundry creditors.

o   The report shall include all outputs as in ledger details for sundry debtors and sundry creditors.

Explanation of each field is as follows:

·         PARTY NAME: the party to which sales, purchase, credit note or debit note has been issued

·         ENTITY TYPE:  Select Regular from Registration Types.

·         PAN: Self-explanatory from above screenshot.

·         ADDRESS: Self-explanatory from above screenshot.

·         PINCODE: Self-explanatory from above screenshot.

·         STATE: Self-explanatory from above screenshot.

·         COUNTRY: Self-explanatory from above screenshot.

·         All the other details are coming from ledger master.

Sample Movement analysis report:

This report is built at Voucher and All Ledger Entries details.

In this report, the following is the business logic that is given.

o   The total amount is the first collection field of amount by all ledger entries

o   The taxable amount is the second collection field by all ledger entries

o   For the IGST amount the software checks for the IGST entry in 3^rd, 4^th and 5^th ledgers from voucher details.

o   For the SGST amount the software checks for the 3^rd, 4^th and 5^th ledgers from the voucher details

o   For the CGST amount, the software checks for the 3rd, 4^th and 5^th ledgers from the voucher details.

This given report consolidates 5 different Tally reports as follows:

Explanation of each field is as follows:

·         Partyledgername: the party to which sales, purchase, credit note or debit note has been issued

·         GST registration type: this is the registration type for the second ledger in the voucher as per inventory view.

·         Invoice number

·         Invoice date

·         Ship to: if the voucher has ship to details then the same shall be given

·         Bill to: if the voucher has ‘bill to’ details then the same shall be given

·         PARTYGSTIN: the GST number for the party

·         Document number: the reference document number of the voucher. It could be sales, purchase etc. this is the parent of the voucher type name

·         Currency

·         Note no: to be extensively used for the credit notes and debit notes

·         Total: The total invoice amount

·         Taxable: the total taxable amount

·         IGST, SGST, CGST and CESS: self-explanatory

·         GST supply: specifies whether goods or services

·         Ledger: the second ledger entry in the voucher

·         BasicPurchaseorderno: specifies the basic purchase order based on which the sales invoice was generated

·         GST nature: specifies whether ‘taxable’, ‘exempt’ etc.

·         Basicshipvesselno

·         Basicorderref

·         Basicshippingdate

·         Dispatch date

·         Good receipt date

                                                      

Points to note:

o   The given module does not have HSN details

o   The fields GST Nature of Return, Taxability and nature of services have been set as from the ledger object and associated collections. For the same the collections GST details, State wise details and Rate details have been extensively used.

Quick points to note:

o   If the value are empty then a default value of “NA” has been put into it.

Batch Allocation report: This particular report gives the batch wise profitability across all movement types: sales, purchase, journal debit, journal credit, debit notes and credit notes

This report helps consolidate the different movement type reports from sales, purchase into one single report.

For example instead of a filtered report as given below:

The end client shall get a consolidate batch movement report.

The explanation for the report is as follows:

·         Batch ID: Self explanatory

·         Batch name: Self explanatory

·         Billedqty_purchase: Total quantity purchased for the given batch to date

·         Billedqty_sale: Total quantity sale for the given batch and given item to date

·         Batchqty_stock: Total stock journal debit for the batch and given item up to the given date

·         Batchqty_stock_Jrnl: Total stock journal credit for the batch  and item up to the given date

·         Debit_Note: Total debit note quantity for the item and batch up to the given date

·         Credit_Note: Total credit note quantity for the item and batch up to the given date

·         Net_Difference:Billedqty_purchase-Billedqty_sale+Batchqty_stock-Batchqty_stock_jrnl-debit_Note+Credit_Note

·         Batch_Rate: This is the purchase rate of the item for the given batch

·         Net_Amount: Multiplication of Batch_rate with Net_Difference

     Prepared by:

     Contact: Apoorv Chaturvedi(8802466356)

    email:mndatasolutionsindia@gmail.com,support@mndatasolutions.com;support@turbodatatool.com

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