GST Audit report-Find GST Errors with quickness and ease using Microsoft Powerbi

 The attached solution comes with readymade extractor for GST data. The extractor extracts the data at the voucher level and at the stock item level.

What is the purpose of the product?·         The product intends to give clear visibility to various chartered accountants on the filing errors by going through the Tally ERP screens in a minimal fashion.
·         It intends to give GST filing errors in an easy to understand and intuitive manner.
·         It intends to help the organization stakeholders know about the filing details in an easy to understand, prompt manner.
Why is the product required?Tally ERP 9.0 stores the data in a hierarchical fashion. This implies that a child entity can have only one parent. This also implies that there could a lot of data redundancies. That is a tool is required to jump over various tree like attributes to get the required information output.The ERP has various built in functions that help the end user extract the built in HSN details from the voucher from various levels in hierarchise. The tool shall extract the same.Requirements that the product meets:·         If there has been a data entry error at the voucher level or at the stock item master level(stock group, stock item, GST classification) then the given product gives visibility to the same.
·         Data entry errors at the voucher level: for the same the product gives the details of errors at 2 levels:
o   Wrong type of GST entry: in case the state codes of the bill state and company state do not match but the end client has put CGST entry or vice versa then the corresponding error shall be flagged.
o   Wrong type of GST entry input at the voucher level:  attached entry indicates the errors in the following types of scenarios:
o   In case the state codes match for the shipping address and the company address while the state code extract from the party GSTIN does not match with the state code of the company name then an error shall be indicated.
o   In case the state codes do not match for the shipping address and the company address while the state code extract from the party GSTIN matches with the state code of the company name then an error shall be indicated.
HSN Audit errors: in the following scenario the following errors are indicated for the end client:·         Tally ERP finds the HSN code using the default function $$GetInfofromHeirarchy or $$GetNonEmptyValueFromHeirarchy. The usage of the function indicates that all the levels for stock group, stock item are GST enabled. In case any of the levels is not GST enabled then the above functions give blank value.  Our solution helps resolve the error in the following way:
o   It flattens the stock item hierarchy for stock item HSN details, stock group(at the highest possible level) HSN details and the tax classification HSN details. From here on the end user can pick up the right tax value.
o   The report also gives any discprencies across various levels of hierarchy for HSN details for stock item.
BENEFITS

Purpose of Turease analytics Module

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:

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.

Contact details:

Apoorv Chaturvedi:support@mndatasolutions.com;support@turbodatatool.com, +91-8802466356

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

Weighted average valuation from Tally

 The attached module has  developed the weighted average module from Tally. The required collections are available for analysis in the Turbodata datawarehouse  by the end client.

The required numbers impact the following:

Balance sheet entries including the closing stock valuations.

The weighted average periodic valuations have been calculated on

a daily basis. These numbers shall be used to calculate the perpetual cost of goods sold. This shall further impact the gross profit calculations for the end client.

Methodology for solving the problem:

·         For the opening stock numbers and opening stock valuations the extraction from item masters was done from Tally.

·         For stock movements the extraction from the voucher captured the inward and outwards movements as debit and credit entries (independent of the voucher types).

·         After the extraction the data was taken to the cloud instance where the running sum for inward movements(quantity and value) was calculated for each stock item.

o   Quick points: the calculations can also be done at other levels of granularity such as categories, stock groups and at lower levels of granularities such as batch wise allocations. These are custom requirements that shall be handled on a case by case basis.

o   The development team has currently excluded ‘sales order’, ‘purchase order’, delivery notes’ and ‘receipt notes’ from the valuations

o   The data has been input in the Tally datawarehouse. The masters and the transactions of the Tally ERP have not been updated by the team. The option of doing the same rests with the end client and the Tally partner.

o   In the current release only the numbers for the stock items where the closing stock numbers are matching have been released. At places where the closing stock numbers are not matching a separate report shall be released.

Method for adjusting the closing stock numbers:

·         Run the Tally datawarehouse report:

Get perpetual cost of goods sold in a  plug and play basis

The difference between the Closing Value and Closing Value(Tally) is the adjustment to be done for the closing stock value.

 

Method for calculating the running total:

Attached are the steps for calculating the same:

a.)    Calculate the debit quantity, debit value and credit quantity from tally keeping the voucher type filter details as above into consideration.

b.)    Calculate the running debit quantity, running total debit amount.

c.)     The periodic weighted average rate is calculated accordingly.

d.)    The periodic weighted average rate for the given fiscal date multiplied with the closing stock number for the given fiscal date gives the weighted average valuation.

e.)    The closing stock valuation on the last date gives the closing stock value for the comparison purposes.

 

 

 

Flowchart for implementation:

 

Benefits of doing the analysis on cloud:

a.)    The speed of the calculations goes up significantly without blocking the functioning of Tally ERP .

b.)    The connector can be scheduled to run automatically(no manual intervention is required). The connector can open Tally at a prescheduled time and run the extraction. Thereafter on pressing the required button in Tally the data shall be updated in the Tally datawarehouse.

c.)     For each company a separate database shall be created. This is been done to ensure data security and flexibility in giving offerings

 

 

 

Method of implementation:

 

a.)    Give the company name for which the stock valuation is to be done.

 

a.)    The development team shall configure the details for the end client for stock valuation.

b.)    Load the TCP file.

 Open Tally ERP >Configure (Shortcut key: F12) > Product & Features (shortcut key: T) > Manage Local TDL (shortcut Key:  F4) >Paste the Location of TCP File

c.) Load the Tally connector

Fetch Tally Data to fetch data from Tally into cloud

 

 

a.)    Refresh the report data.

 Gateway of Tally>COGS and Weighted Avg

WA: For Weighted Average Report

WA (Excel Export): Export Weighted Average Report into Excel sheet

Cogs: For Cost of goods sold Report

Cogs (Excel Export): Export Cost of goods sold Report into Excel

Location of Exported Excel Report is: C:\Excel Export\WA and COGS

 

 

Other activities:

·         Automation of extraction: the following inputs shall be required for the end client:

o   The time at which extractor is to run.

o   Multiple locations consolidation: in case then the consolidation logic shall need to handled on a case by case basis.

o   In the current product the stock journal entry values have been taken as is. In case of customization required for stock journal values then the logic coding needs to be handled separately.

Contact:Apoorv Chaturvedi

Phone: +91-8802466356

Email: support@mndatasolutions.com;apoorv@mnnbi.com;support@tureasesolutions.com

Resolving Running Sum problem using DAX from Tally

  The given product helps resolve the following problems with regards to valuations across large number of ERP installations.

Attached videos explains the demo:

Copy of the demo:

https://youtu.be/5XIlNLABCIY

The video below gives the following:

Testing the collections and linking the dashboards to the cloud:

https://www.youtube.com/watch?v=1k2IZLRtuhw&feature=youtu.be

Powerbi app  trial login:

Username: guest@mndatasolutions.com.microsoft.com

Password: Bhaiya80$

Download of the sample dashboards-through dropbox:

Dropbox login: powerbimnnbitrial@gmail.com

Password: Bhaiya78$

Client problem: The end client is looking for perpetual inventory and ledger valuations on the date and year month granularity.  This is required for calculating the perpetual gross profit and the perpetual trial balance for the end client.

The issue to be resolved is as follows:

Problems to be resolved for the end client:

a.) Get right inventory valuations by weighted average method for the end client. this shall impact the closing stock value for the end client.

b.) Find the historical accounts receivable ageing for the ledgers at various levels of granularity.  the granularity in the current extract is on daily and year-month basis.

To calculate the running sum in sql or the other languages like TDL is quite complicated in terms of the code. Hence the developer has used the Powerbi DAX coding for simplifying the coding process.

One normally gets the ledger balanaces as a snapshot from various ERPs like Tally( as given below):

However there is a requirement to do the ledger analysis on the basis of dynamic time entries. That is the time framework is not a snapshot but dynamic. 

In order to get the ledger balances in a dynamic fashion one needs to combine the opening ledger balance along with the historical ledger entries

The opening ledger balance is obtained from the ledger snapshot as shown below:

The historical ledger entries is obtained by walking the voucher across allledger entries as shown below:

The issue is that the snapshot and the historical ledger entries need to be combined to get the consolidated running ledger entries.

Thereafter consolidation needs to be done based on time intelligence aggregates.

In order to do the consolidation one needs to either do a join or develop a custom function for walking the collection.

The above process is replaced by DAX code. This DAX code shall ultimately be executed in the cloud.

The method of aggregation is to combine ledger snapshot(with filter conditions for the parent ledger type been "Sundry Debtors" and "Sundry Creditors") and the voucher-allledgerentries to be filtered by parent vouchertypename as "Sales", "Journal" and "Receipt". The value of total credit minus the total debit is the net amount for the given fiscal date. This is then combined with the opening ledger entries to give the consolidated ledger balances.

The join conditions entails joining between a snapshot(Ledger) entity and a cumulative(Voucher-allledgerentries) entity.

The running total is calculated in DAX using the cumulative entries and the date dimensions.

Other dashboards for the product.

This set of dashboards gives the comprehensive Profit and loss analysis based on the dynamic date filter.

The clients should get gross profit, net profit, cost of goods sold, revenue aggregates at various levels of heirarchy(year to date, month to date and quarter to date). Also the clients shall get store wise sales numbers at various time granularities.

Historical inventory analysis; this dashboard gives the inventory profitabiluty analysis at voucher-allinventoryentries level. The granularity of analysis could year, year-quarter and year month. The clients  shall get profitability analysis by items.

The above dashboard is ledger wise profit and analysis sales dashboard. There are readymade objects for sales, gross profit, net profit and cost of sales.

Accounts receivable analysis: this gives the analysis of accounts receivable by ledger snapshot and ledger historical entries.

In case one desires top buy the product then the following are the payment details:

Payment details:

Pre requisites for the package are as follows: