Contact details of blog writer:
Name: Apoorv Chaturvedi
Email: apoorv@mnnbi.com
Phone: +91-8802466356
website: www.mnnbi.com
Problem:
The ETL team
intends to solve the following problems regarding GST filing:
·
Wrong
data entries
Solve the problems of under filings
for the government: As per the Times of
India article, the government is dealing with under recovery for GST
collections in India.
o
Help
the end clients meet the statutory requirements
·
Manual
processes in filing taxes
·
Reconciliation
of taxes: the ETL team believes that tax refund should be the ultimate target: As per
the changed government norms, the buyers and suppliers could have to
reconcile the entries before filing the GST Returns.
http://www.business-standard.com/article/economy-policy/infosys-chief-nandan-nilekani-plan-on-gst-invoice-matching-may-be-tweaked-118022300073_1.html
http://www.business-standard.com/article/economy-policy/infosys-chief-nandan-nilekani-plan-on-gst-invoice-matching-may-be-tweaked-118022300073_1.html
·
Work
across multiple systems: plug and play module
Solution: Automated GST Filing using Turbodata and GSP Partners
Are you facing the following issues with regards to GST filing?
- · Delay in filing
- · Concern regarding the changing regulations from the government
- · Concern regarding reconciliation: specially for customers using MS Excel upload.
- · Have a manual process for GSTR filing. The manual process is prone to error
- · Have high manpower costs related with GST filing.
Turbodata shall help your firm with faster, easier and more convenient GST filing.
How is Turbodata different?
- · All the reports for the end client shall be developed on the cloud installation. Only a minimal extract for all the vouchers and masters shall be done from the end client location. The ETL team shall commit to usage of maximum amount of RAM for the same(say 1 GB for incremental data extract)
- · The end client can do the prior change of the data. The system shall automatically take care of the same. This is enabled through incremental data load process using data normalization.
- · No reports shall be developed at the client location. All the reporting work shall be done at the server location.
- · Initial and incremental transaction data extract shall be done from the end client location.
- · The end client need not worry about re filing the GST reports since it shall be done by the GSP partner automatically.
- · The package is very easy to deploy, deliver and maintain. No high end software are required. The system can extract data from SAP, Tally and other source systems with ease.
- · Dependence on MS Excel for tax filing purposes is taken away since it could result in data errors and discrepancies.
Current system:
Why is the Turbodata system better?
Turbodata system:
· Turbodata system is inspired by ‘The Deming Way’, ‘The Goal’ and the Toyota production system and the Inmon methodology. In a nutshell the following are the features copied from the above systems by Turbodata:
o No error prone data should be passed for the reporting purposes. The data needs to be cleansed, audited and consolidated before report development.
o The processing of the transaction should be done as soon as the transaction has been fed in the source system. That is the processing should take place on a real time basis and not specifically at the end of the month. Turbodata enables this feature in the following manner:
§ Each transaction fed into the end client source system is assumed to be an order from the end client.
§ The system offers the facility for real time extract and upload(current system is manual but the data can be loaded on a daily basis by the end client go the server)
o Once the data has been loaded onto the server, it is transferred to a normalized database(insert, update and deletes). At the data warehouse level the data cleansing, data transformation, data consolidation activities are done
o Once the data has been cleansed at the datawarehouse level then the reports for GST are developed. In one single lot, GSTR1, GSTR2 and GSTR3 reports can be developed.
o Turbodata is integrated with at least one GSP partner. The end client could look at other GSP partner solutions if it desires the same.
o The deployment of the solution is very easy and convenient. For any end client the deployment should take not more than 20(twenty) minutes. Minimum installation pre requisites are required.
o The data for the end client is stored in a datawarehouse. The end client does not need to worry about changes in the statutory requirements. Other high end services like inventory optimization and predictive analytics are possible on the cloud.
To check why should the end client consider Turbodata GST, please check the following linkage:
http://mndatasolutionsindia.blogspot.in/2018/02/why-turbodata-gst.html
To check why should the end client consider Turbodata GST, please check the following linkage:
http://mndatasolutionsindia.blogspot.in/2018/02/why-turbodata-gst.html
Sample video link: https://www.youtube.com/watch?v=sYbeBfc3ozo&feature=youtu.be
The product uses optimum RAM so that the source system does not hang during extraction as given in the following video:
https://youtu.be/7CULkzc5h2g
Based on the above philosophy, the following are the reasons one should look at Turbodata GST as a GST filing solution.
· Law of Focus: The end client should have one word embedded into customer’s mind. In such a scenario most of the end clients have Tally embedded into their minds for GST filing. Turbodata GST matches the numbers with Tally GST reports to give customers peace of mind and satisfaction.
· Law of opposites:
o Turbodata offers cloud based GST filing system while Tally offers desktop/server based GST filing system
o Turbodata enables faster, easier and more convenient data upload facilities than Tally ERP 9.0.
o Turbodata offers historical data correction automatically for GSTR reports while in case of Tally the end client shall have to re file the offering.
o Turbodata GST can work with multiple ERP systems and is extremely scalable.
· Law of Ladder: Tally has top of mind recall for the customers for accounting accuracy. Turbodata GST team recognizes the same. It matches the GSTR reports with those of Tally GSTR while making it easier and more convenient than Tally to file GST taxes.
· Law of Mind: Tally stands for accuracy while Turbodata stands for speed matching with Tally numbers. Turbodata GST offers GST filing services matching with Tally ERP 9.0 for any ERP.
Understanding the behavior of average Indian customer.
Capturing data entry errors:
A number of times the end client types in wrong data into the source ERP system thereby resulting in wrong outputs and results. Junk inputs imply junk outputs. The ETL team would recommend an auditable output from Turbodata to be used as part of the reporting purposes. Wrong data inputs can impact the end client in one or more of the following ways:
- Wrong tax filing specifically in online scenario.
- Wrong business picture
- Wrong predictive analytics.
As per the Toyota ProductionSystem, bad inputs should not be processed further as it adds to the final costs.
The ETL team(my firm) has found the following errors with regards to the data entry inputs specifically with Tally ERP 9.0.
· Stock input has been in one godown but stock outward movement has been from other godowns:
· Missing purchase or sales order entries resulting in negative stocks at given points in time. One cannot have negative stock balances at any point in time.
Other data input errors that we have commonly seen are as follows:
- Duplicate payment entries
- Duplicate sales entries
- Receipt note entries but no purchase invoice entries
- Payments not having the required bill reference numbers.
How to resolve the errors:
· In an object oriented program it is difficult to catch the errors on a real time basis. The ETL team recommends using the relational databases for catching the errors. The real time extraction module for Turbodata should be used for the same.
· Transferring the data onto the third normal database is recommended. This helps catch data duplicity based on the composite keys.
For example if an end client has made the same amount payment for a given voucher on a given fiscal date, then the same should come as part of the discrepancy report. It is possible that the end client could be correct. There is also a possibility that the payment entries have been made by 2 different resources. Further handling of the given situation is as follows:
· If the end client desires to catch the following error then the username by which the data entries have been done shall not be added to the composite key. In such a scenario there is a discrepancy between the Turbodata ledger balance output and the Tally report. The end client to approve the discrepant entry before the data is input into the system for auditing purposes.
Using perpetual valuations for ledger and inventory instead of periodic valuations. For example if an end client relies on periodic valuations for ledger balances then a duplicate payment entry then the periodic balances at the end of the fiscal month are difficult to catch. For example if an end client has a duplicate entry of Rs. 100k(One hundred thousand only) over a balance of say Rs. 15000k(One fifty million only).
However using the perpetual system it is easy to catch the data entry errors.
Matching the consolidated trial balances and closing stock balances at the database level with the on fly calculations at the software level.
A small story for the end user: as Yuval Harari is Sapians says that mankind is primarily driven by myths. Hence many a managers are driven by myths regarding software or the consulting companies having the right audit numbers(with the managers inputting junk numbers).
A small story from one of my favourite books(Raag Darbari by Srilal Shukla) could best illustrate the point.
The protagonist Ranganath had gone from the city to visit his relative, an aunt’s husband , in the village. During the course of the village fair, it was suggested that the group goes and sees the village temple for the local goddess. At the temple Ranganath found that the statue instead of been of a goddess was of a soldier( for a goddess he was looking for two lumps in front and two lumps in the back). The priest asked for donations for the goddess. To this request Ranganath refused saying that the statue was not of a goddess but of a man. There was an ensuing scuffle between the villagers and Ranganath. Ranganath was eventually rescued by his cousin. On going out and meeting other people, the cousin mentioned the following:
"My cousin has come from the city and is very well read. That is why he talks like a fool."
The author has always associated himself with Ranganath.
The author has always associated himself with Ranganath.
Reconciliation for GST filing:
Understanding the behavior of average Indian customer.
This blog attempts to understand human behaviour is absence of standards or changing standards in our day to day lives.
Based on the above philosophy, the following are the reasons one should look at Turbodata GST as a GST filing solution.
· Law of Focus: The end client should have one word embedded into customer’s mind. In such a scenario most of the end clients have Tally embedded into their minds for GST filing. Turbodata GST matches the numbers with Tally GST reports to give customers peace of mind and satisfaction.
· Law of opposites:
o Turbodata offers cloud based GST filing system while Tally offers desktop/server based GST filing system
o Turbodata enables faster, easier and more convenient data upload facilities than Tally ERP 9.0.
o Turbodata offers historical data correction automatically for GSTR reports while in case of Tally the end client shall have to re file the offering.
o Turbodata GST can work with multiple ERP systems and is extremely scalable.
· Law of Ladder: Tally has top of mind recall for the customers for accounting accuracy. Turbodata GST team recognizes the same. It matches the GSTR reports with those of Tally GSTR while making it easier and more convenient than Tally to file GST taxes.
· Law of Mind: Tally stands for accuracy while Turbodata stands for speed matching with Tally numbers. Turbodata GST offers GST filing services matching with Tally ERP 9.0 for any ERP.
· The Indian consumer would like automated filing of taxes to reduce the prospect of manual errors.
Data reconciliation for GSTR reports:
- Consolidated platform for data consolidation, data cleansing and data transformation.
Attached are the videos regarding the working of the
connector:
Attached video explains the benefits of Turbodata-GST for the end client:
Reports that have been developed and validated with large
Tally data by the ETL team:
·
Nil rated invoice analysis; this includes the
logic for zero rated, exempt, export(LUT Bond), nil rated invoices. The logic
shall include the specs for those cases where a ledger could be declared in
multiple ways by the end client.
·
Document summary: this gives the consolidated
document statement for outward supply, debit notes and credit notes. These
details shall include the non-GST ledgers, items that are non-GST, registered
sales, unregistered sales.
·
HSN summary: this report includes only the GST
ledger and GST applicable items. The logic for the same shall include the logic
for reporting for nil rated items, nil rated ledgers(no tax on the given
ledgers), handling of debit notes/credit notes for HSN reporting.
what is not included?
HSN reporting for those items
whose HSN number is not given in the HSN master as given by Tally ERP 9.0.
·
GSTR1CDNR: this gives the details regarding the
complete debit and credit notes for the end client.
·
GSTR1B2B: this report gives the consolidated
details for the B2B transactions for the end client.
Why should the end client choose the solution based on
Turbodata:
·
Better data auditing enabled by incremental data
load. Say a company has 10 branches. For each branch there are average 5 relevant
reports(GSTR1B2B etc). It is difficult for the manager to ensure that all the
required reports will be loaded onto the system on a daily basis(using Excel). The
following are the advantages of Turbodata:
o
Usage of datawarehouse to capture history where
not possible in the source system like Tally(Nil rated, GSTR1B2CS etc)
o
Lower upload time and single upload facility: by
uploading the raw data. It should take less than 2 minute to upload the entire
data on a daily basis.
o
Better data auditing and validation for cases
entailing credit notes/debit notes/nil rated invoices etc than with ERPs such
as Tally/SAP etc.
o
No prior knowledge of complex taxation laws
required(hence no need to know the date and time when particular taxes are to
be filed): the etl team begs to differ with the same. The end client shall need
to know about the basic details for correct filing.
S Sample spec for GSTR1B2BH1: Attached is a sample
specification for GSTR1B2BH1 for the end client. The ETL team shall follow the
set process flow for capturing the GSTR1B2B details for the end client for any
ERP.
Process flow chart for GSTR1B2B for GSP partner/End Client
B Based on the sample
data for various companies given by the GSP partner, attached is the
specification for the GSTR1B2B invoices.
• Step 1: Check for any ledger where
GST is not applicable.
• Step2 : check for any non –GSTN
items in the extracted data. All the invoices associated with the non-GST items
are to be excluded.
• Step3 : check for GSTN
number(registered and unregistered). This shall be done from the daybook only
and not from the masters. The GSTN data
checking shall need to be done as per daybook entry.
Note: the error in the
GSTN number shall be demarcated by the ETL team.
Pick up all the sales
entries: this shall be done based on the union of the following 2 rules:
• The following primary group names
shall be extracted for sales vouchers: The following shall be the order of
checking these details:
o Find if the partyledgername is
sundry debtor
o In second step find if the
ledgername belongs to ‘sales account’
o In third step exclude the credit
notes and debit notes from the transactions above.
• Pick up all the entries from the
sales table(with voucher type as ‘sales’) where the GSTNTRANSACTIONTYPE belongs to ‘sales’
Note: these details
shall also pick up the sales exempt entries. Also the credit notes/debit notes
and the nil rated invoices that have the GSTNTRANSACTIONTYPE as ‘sales’ shall
be picked up. The latter two types shall thereafter be reported under GSTR1B2B.
• Extraction of ‘ nil rated’
o The Nil rated transactions shall be
extracted using the column GSTNATUREOFTRANSACTION based on the following logic
from the data extract above:
Exempt: sales exempt from the
GSTNATUREOFTRANSACTION.
Export: the voucher type should be
export or GSTtransactionname should have export. However the export parties do
not have GSTN number hence this step shall not be done as part of GSTR1B2B and
related extractions.
Nil rated: the GSTOVRDNNATURE
should have ‘sales%nil%’ entry.
NONGST: ISNONGST flag has to be not
null in the stg_ledger.
REVERSECHARGEFLAG: THE
REVERSECHARGEFLAG is not null
No other
GSTNATUREOFTRANSACTION entries shall be considered as of now. The remaining
vouchers from above should go into GSTR1B2B.
Inclusion of credit
notes/debit notes : the credit notes with the following logic shall be added to
GSTR1B2B.
o The partyledgername has a GSTIN
number
o The vouchertypename is a credit
note/debit note
o The reason code is blank. That is
the end user has not filled any of the Tally related options.
I In the above scenario
the behaviour of the credit note/debit note shall be governed by the nature of
the invoice the note number is referencing. For example if the credit
note/debit note references nil rated invoice then the required credit
note/debit note shall be marked as ‘nil’ rated and so on.
E Extraction of credit
notes data: CDNR and CDNUR is self explanatory.
The credit notes shall
be marked as registered or unregistered based on the GSTN number in the
daybook.
CDNR notes: The CDNR
entries shall be based on the following logic.
o The partyledgername is ‘sundry
debtors’
o The ledgername is ‘sales account’
o The voucher typename is credit note
or debit note.
o The partyledgername has a
verified GSTN number.
o The credit note has a ‘note no’
attached to the same.
o The reason code for the credit is
one of the 7(seven) reason codes given by Tally ERP.
Union the output of
the following logic.
o The partyledgername is ‘sundry
debtors’
o The ledgername is ‘sales account’
o The voucher typename is credit note
or debit note.
o The GSTNTRANSACTIONTYPE of the
ledger entry is not related with ‘sales’
o The credit note has a ‘note no’
attached to the same.
o The partyledgername has a verified
GSTN number
o The reason code for the credit is
one of the 7(seven) reason codes given by Tally ERP.
After this step has
been completed the nil rated credit notes and debit notes shall be excluded.
The following is the suggested logic:
From the given output
above the ETL team shall select those notes that are nil
rated/exempted/non-GSTtransactiontypes/ REVERSECHARGEENTRIES as per Tally. For
the same the GSTN team should maintain the entire history into its target
tables. The following is the definition of the Nil
rated/exempted/Non-GSTTRANSACTIONTYPE/REVERSECHARGEENTRIES:
• Extraction of ‘ nil rated’
o The Nil rated transactions shall be
extracted using the column GSTNATUREOFTRANSACTION based on the following logic
from the data extract above:
Exempt: sales exempt from the
GSTNATUREOFTRANSACTION.
Export: the voucher type should be
export or GSTtransactionname should have export. However the export parties do
not have GSTN number hence this step shall not be done as part of GSTR1B2B and
related extractions.
Nil rated: the GSTOVRDNNATURE
should have ‘sales%nil%’ entry.
NONGST: ISNONGST flag has to be not
null in the stg_ledger.
REVERSECHARGEFLAG: THE
REVERSECHARGEFLAG is not null
For now the ETL team
shall exclude those credit notes that do not have any tax component associated
with the same. These notes shall be classified as ‘nil rated’.
Note regarding filing:
o In case the debit note/credit note
has a wrong note number then the etl process shall still file the same. The
gstn portal shall mark the entry as wrong
o If the debit note/credit note has a
note number with a note date that is prior to the minimum date of the Tally
instance then Tally ignores the same. However the etl team shall extract the
same and file GST with it
o If a debit note/credit note has a
missing note number then Tally excludes
the same, but the ETL team shall include the same in nil rated . It shall be
caught as an error by the GSTN portal.
