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/