GST2A Reconciliation

 Documentation for GSTR2A reconciliation for India’s leading Retail Chain

Reason for the development of the module:

 

The module has been developed for chartered accountants and end clients to help resolve the GSTR2A reconciliation issues.

Why is this module required?

·         The data been handled for GSTR2A reconciliation can be very large. With excel handling of the data could be very cumbersome

·         There are a large number of conditions by which GSTR2 might not make a match between the client purchase invoices and the client buyer sales invoices. The reasons for the variations could be with regards to document number, document date, Recepient GSTIN, buyer GSTIN and even the transaction amounts. In order to handle the mismatches between these types of invoices there are a large number of join conditions involved.

·         The developed solution can be a standalone module for Chartered accountants not involving the complete GST extraction and services from various data providers

·         Benefits with regards to ERPs:

o   The solution is based on Azure cloud. It uses the high processing power of Azure cloud for faster data processing

o   The solution offers web and mobile access for GSTR2A reconciliation

o   The client can also share with the vendors using whatsapp and email any discrepancy details

o   The development team is bringing with itself automated extractors from Tally and other ERPs.

o   The development team is bringing with itself specialized Chartered  accountants with experience is GST 2A reconciliation(https://www.linkedin.com/in/sharad-maheshwari-688b733/)

o   The data should be secure(on Microsoft Azure cloud) and there should be facility for each company to see its own data(row level security)

o   The business logic should be auditable.

 

The following are the steps followed for the GST2A Audit process:

 For the GSTR2 A reconciliation the sample client is using the following composite keys

·         Document number

·         Document date

·         Supplier GSTIN

·         Company GSTIN

·         Taxable Amount

·         Total GST amount

 

There can be some variations in the Taxable amount and the total GST amount. That logic has to be incorporated into the final audit report.

Supplier GSTIN and Company GSTIN have to match. However there can be discrepancies in Document Number and document date because of different ERP systems.

The client’s logic entailed that match shall include all 6 criteria. Alternatively, if 4(supplier GSTIN, Company GSTIN, taxable amount and Total GST amount) criteria match but there is discrepancy in the remaining 2 criteria (document number an document date) then it should still be considered matched.

Methodology of the development of the solution:

·         The solution was developed in Powerbi for the following reasons:

o   Consolidate the data from different data sources

o   Incorporate the client logic

o   Easy and secure viewing through the Azure portal

o   Standardization of the formulae.

o   Reduce the handling times for GSTR2A reconciliation with large and varied data.

o   No row limitations as far as the processing of the data is concerned

Attached is the methodology for the same:

1.       Find the exact match for the entries:

1.       Find those entries where the client has filed the returns but the returns are not in GST audit-sample code

3.       Find those entries where the client’s customer has filed the data but client has not filed the data

3.       Find those entries where the client’s customer has filed the data but client has not filed the data

 3.) Find those entries where the document number is not matching:

4.) Document date mismatch data

Once  the above analysis has been done then specific matching criteria can also be done by GST number, document number and document date

Other reports: Based on the analysis after steps 1 ,2 and 3 it is possible to divide the data to find the exact mismatch

Custom variations based on Total tax amount and total amount are also possible. A sample case study is attached herewith:

4.       Match  the ERP and the GST data . The ERP data shall come from any ERP(including Tally). The GST data shall come from the GST portal as part of the GSTR2 reconciliation).

1.       Exclude the entries not in the exact match

5.       Extract those entries that have matching Taxable amount, Total amount, recipient GSTIN, Supplier GSTIN but not matching document number and document date

5.       Extract those entries that have matching Taxable amount, Total amount, recipient GSTIN, Supplier GSTIN but not matching document number and document date

Please contact the following for automating your GST 2A audit process

 

Apoorv Chaturvedi(Phone: +91-8802466356, email: support@turbodatatool.com, support@mndatasolutions.com)

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

Step by step guide for Powerbi Mobile app deployment

 

App Link: https://app.powerbi.com/Redirect?action=OpenApp&appId=5aaa9abf-91b5-461b-9cb6-4252dbee6c6b&ctid=c036e821-d169-45e7-89a2-40b42dccb9cf

Username: frenchtrendz@mndatasolutions.onmicrosoft.com

Password: Bhaiya80$

 

Download Powerbi app from Google store

Choose your company: in this case ‘MNNBI’

Step 3: choose your report

Contact for  your own mobile app(integrated with Tally):

Apoorv chaturvedi

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

Phone: 8802466356

Pre requisite for Mobile app:

·         Tally serial number

·         Odbc should be enabled

Increase your prospects by using our inactive customers report

 There is a need in the current times to increase the number of prospects for the sales resources. Getting new prospects is a very tedious and time consuming job.

However a company is likelier to get business from its past customers than prospecting for new customers.

It is in this regards we have added ‘Inactive customers’ reports to our mobile app.

Why use our solution:

·         The data is stored on the Azure cloud. So it is extremely secure

·         The Tally data has been converted to DAX for extremely fast analysis. One can get mobile apps and dashboards with high complexity with very low refresh and reload times

·         The solution gives the ability to edit the dashboards on the cloud itself

What is the purpose of this report?

·         Identify those customers whose last transaction dates have been more than 90 days from the current fiscal date.

·         Identify the important and the less important customers based on the closing ledger balances. These customers have been segregated into the following categories:

o   ‘A’ category customers are those customers that form the top 25% of the ledger balances. We assumed the ledger balances as a criteria since we wanted to include all the historical transactions.

o   ‘B’ category customers are those that are between 10% and 75% of the total cumulative ledger balances

o   ‘C’ category customers are those that are in the bottom 10%.

The report also gives the rank of the given ledger by the historical balances.

A quick word regarding ABC classification:

·         The ABC classification has been done based on the historical voucher inventory analysis. Any partyledgername associated with the parent vouchertypename ‘sales’ in the historical analysis has been included in the list. This we could include both sundry debtors and sundry creditors in the list.

·         Once the ABC classification was done we identified the entire inventory movement by sales for the given set of items f the given partyledgername as under:

This report run by a slicer would straight away give the important items sold to the given partyledgername on a historical basis.

The same action has been replicated for B and C category partyledgernames.

 

Top A_category partyledgername: this customized report gives the top 10 partyledgernames  for the ‘A’ category(partyledgernames) and the values of the associated stock items as given under:

How to use the given report?

a.)    Identify all the A and B category party ledgernames by using the Turbodata-Powerbi module

b.)    Within each partyledgername identify the top items sold to the given party. Identify the items to be prospected for with each given party.

c.)     A customized mailing solution from Turbodata is also available for the end clients.

 

How to access this report:

a.)    One can access the given report using the following login features:

https://app.powerbi.com/Redirect?action=OpenApp&appId=5aaa9abf-91b5-461b-9cb6-4252dbee6c6b&ctid=c036e821-d169-45e7-89a2-40b42dccb9cf

 

Username: frenchtrendz@mndatasolutions.onmicrosoft.com

 

Password: Bhaiya80$

 

Deployment process: https://mndatasolutionsindia.blogspot.com/2021/04/step-by-step-guide-for-powerbi-mobile.html

 

 

How to go live for your company:

·         Contact Turbodata team for a customized tcp. Turbodata team needs the Tally serial number for the given firm

·         After the tcp has been loaded then the syncing between the client Tally machine and the Azure cloud shall take place through the secure Microsoft gateway for each individual account(details to be given by the M&N BI team)

 

Other features of the product:

a.)    The solution uses $$Loopcollobj functionality from Tally. That implies that all the loaded companies shall be extracted from Tally ERP 9.0 for the purposes of analysis

b.)    The analysis has also been done across all companies

c.)     One can edit the apps and the dashboards using the Azure edit functionalities

Cost of the solution per year : The entire mobile app for the end client costs Rs. 15000/-(fifteen thousand)+GST for the end clients

Price validity: 15^th June 2021.

 

Contact details:

Apoorv Chaturvedi

Email: support@turbodatatool.com;support@mndatasolutions.com

Phone: +91-8802466356

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

Turbofast Powerbi Reports from Tally

 Turbofast Reports: Powerbi

Consolidate and minimize your spreadsheets

M&N Business Intelligence India LLP(www.mnnbi.com)  is a leading analytics provider providing faster, easier and more convenient reporting from various ERPs. We have worked with clients like Medanta, PVR Cinemas, Kurlon etc for helping them optimize their operations through management reports. We help clients reduce their working capital requirements by helping design processes.

In most of the firms, accountants and managers are developing a large number of Excel spreadsheets manually for the top management. This is time consuming, manual and expensive. The large number of spreadsheets are due to the following:

1.           Complexity of information required
2.           Heavy load on Tally during processing large amounts of data

At the end of the process the management has to work with large number of complex spreadsheets.

For getting immediate benefit, we have made a product for   end clients

.Please fill out the form to enable us to get in touch with yourself for deployment:

https://forms.gle/dZGZDnqADxZquJrL9

Product download details: https://drive.google.com/drive/folders/13TOzjHxvTU8mEYuELYWmYj8MyLk-5V5f?usp=sharing

The solution is integrated with Tally and the solution is secure, easy, fast and very convenient.  Please contact  us for deploying the same on your machine.

Benefits:

We can help minimize the number of spreadsheets for the top management by the following:

·         Developing complex reports from Tally machine using Microsoft technology(DAX).  This is done with minimal deployment requirements. We have made the base product with a free version of Powerbi. 

·         The complex reports run atleast 50% faster than in Tally using Powerbi.We also provide data audit features with our product.

Our product gives reports with lower refresh and reload times than Tally on your machine or on secure Azure server. This shall help the client with the following:

·         Give managers required information in lower number of pages in an automated manner.

·         Extensive graphical representation can be done with ease and convenience.

        Advanced predictive analysis modules are also possible.

S      Sample for automation of the spreadsheets by our developer: Automation of spreadsheets sample 

            Next steps:

·        

·         The cost of the module is Rs. 7000/-(seven thousand only)+taxes as applicable. We would need the Tally serial number for the permanent license.

We shall be thankful to understand your requirements in depth  for reporting and Excel automation.

 Sample reports:

    1.) ABC Ledger analysis: This gives an immediate view of the important ledgers whose outstandings are due.

Please capture the important ledgers for receivable purposes from this report.

Application: This report is for collections teams

2.) Cost center wise sales report: here we look at the complete visibility starting from sales order, to sales invoice(including delivery notes) to billing outstandings. Use this report for the following:

a.) Understanding the sales order outstandings

b.) Look at the pending receipts

This report has been developed godown wise.

Application: use this report for collections team

3.)  A critical problem for the end users is that they do not have access to batch wise and godown wise inventory. The valuations are required for calculating the gross profit and for calculating the value of work in progress inventory. This report has been developed as a consolidated statement for stock summary, closing stock report.

Application: use this report for production planning team

5.) Payable and receivable snapshot has been developed for giving a one shot look into payments are receivables basis.

6.) Monthly, yearly and quarterly gross profit views for for those managers who want to automate the profit and loss statements.

6.) Stock item batch analysis: this view gives complete visibility to the inventory planning managers for batch wise and godown wise analysis

7.) Receivable and payable turnover: it helps analyze the credit quality of various parties.

8.) Daybook view: it gives views by voucher, voucher inventory and voucher ledger. One can make most reports from these 3 views.

Other views:  

·  

 

Price: Rs. 5000/-(Five thousand only)+GST

(This includes one simple spreadsheet automation)

Payment details: 

Beneficiary Name: M&N Business Intelligence India LLP

Bank Name: Axis Bank

Branch: DLF, Gurgaon[HR], Gurgaon 122002

IFSC Code: UTIB0000131

A/c Type: CHECKING

Account Number: 910020041330740

Inputs required: Tally serial number

 

 How to install Powerbi:

Download Powerbi from the following link:

https://www.microsoft.com/en-us/download/details.aspx?id=58494

 

Contact details:

Apoorv Chaturvedi

Phone: 8802466356

email: apoorv@mnnbi.com;support@mndatasolutions.com

Accounting analysts for improving collections and margins with ERPs

 Get accounting analysts to develop custom data entry forms over Turbodata using python

Need : Sundry debtors mapping at the time of entry into Tally and at the time of sales order processing.

Problem statement: many a times in various ERP systems various parties, that do not have a good payment history, are entered.

Sales to these parties can result in cash losses. 

The owners desire protection in terms of the quality of the parties that are entered into the system.

Why is an accounting analyst required?

a.) Custom ledger and item hierarchies for the end clients

b.) Complex Tally implementations

c.) Complex legacy collection issues

d.) Design of a custom data analysis and data capture system because there is no workflow system for most other ERPs

e.) Management to work closely with the accounting analyst for handling difficult accounts.

The process flow is explained below:

Solution(proposed to CEOs/CFOs)

a.) A thorough analysis of the client Tally ERP data across all the companies using the Turbodata tool(Tally to Powerbi). This tool does not require the installation of any database. The extract shall involve ledger hierarchy flattening, item hierarchy flattening for trial balance, receivable and payable and ledger movement analysis. A sample of the ledger hierarchy flattening is attached herewith:

https://drive.google.com/file/d/15Y1KjYLDJ3CFEeXS6B0pQSFG2HBg9qWj/view?usp=sharing

A similar exercise shall be done for item hierarchy flattening.

Reason for doing the analysis is the following:

• Tally allows dynamic hierarchies. Understanding each level of hierarchy is critical for understanding the organizational dynamics.
• The ledger and item hierarchies in each company can be different
• We need to understand the balance sheets and bill payable and receivable history  of the companies very well. We need to identify if the pain points of various customers.

b.) Once the proof of concept and analysis has been done then the data is to be input into the Turbodata SQL Server datawarehouse. The SQL Server database shall help with the following:

1. Incremental data load

                2. Maintaining historical data

                3. Customizing the inventory valuations

                4. Speeding up the data access using forms

                5. setting up secure access across various levels of hierarchy

               6. Generating customized python forms for data capture and workflow approval.

Data capture process

a.) A workflow for parties authentication(various hierarchies). In addition to the software systems we propose an online verification system using Turbodata SQL Server along with the python data entry forms. The verification will take place through the Turbodata entry forms at various levels of hierarchy. A sample data entry form is given herewith.

b.) A thorough credit check for new parties: attached bank statements, financial statements, details of credit bureaus

We shall look at quick ratio, Total turnover of the party for the prior fiscal year, profitability, cash profitability ratios.

c.) Possibility for the firm hierarchy to block the party in case the  credit history is not up to the mark. In such a case no entry into the Tally ledger master to take place.

Additionally the management can specify the following:

i.) Decide the hierarchy level from the group flattening at which the given party shall be handled.

For example Tally allows flexible hierarchies for a given party. The management can decide that a party with a large order values 

will be handled  by ledger level4, a lower order value will be handled by ledger level_5. In such a case the key stakeholders for the collection process 

shall be defined.

c.) Solution shall be deployed on client server. The custom hierarchy of the client to be taken into analysis process.

d.) Every party to have a set of stakeholders for ease of collections: this shall be ensured by having the mapping of each party at a particular ledger level 

during the data entry process. Additionally the following steps shall be done to ensure that collections take place in time:

Deploy checklists for the sundry  debtors based on balance sheet analysis from google

Decide the payment terms for the party.

Link the payment terms with the sales order entry process.

Block the sales invoice entry without the sales order entry. The credit check for the party to take place at the sales order entry level.

The above is the ledger entry form

a.) It matches with the ledger entry form with Tally.

b.) Additional fields and control systems shall be added based on the client requirements

Data validation at the sales order entry process entails 2(two) levels of credit checks:

• Ledger: at the ledger level 
• Item: this shall entail checking the inventory valuations at the batch, godown level for the required client. This has been done because many end clients require only specific movement types to be incorporated in the inventory valuation, remove the errors due to company splitting and get a more accurate closing stock valuation at the batch godown level

For example in the above form, for a party for which sales order has come the following 6 KPIs have been checked base don the Turbodata datawarehouse.

a.) Closing ledger balance of the party

b.) Total receivable as of date for the party. This entry comes from Billdetails and should match with the closing balance of the party.

c.) Receivable more than 90 days: This indicates the total receivable with due date more than 90 days

d.)  Cashinflow: this metric indicates the total cash inflow from the party

e.) Sales order pending value : this is the total sales order pending value for the party as of this date. This shall include the pre closed orders also,

Based on the same above inputs the hierarchy of the company can approve or disapprove of the order.

A similar form shall be created for the items(batch, godown wise analysis) to ensure that the required profitability level is maintained.

Name: Apoorv Chaturvedi

Phone:+91-8802466356

email: support@turbodatatool.com;support@mndatasolutions.com

Hire an accounting analyst for gross profit calculations

  Hire accounting analysts for calculating gross profit as per your requirements

Profile: accountants and computer engineers available all over India with Turbodata training to help CFOs with the following problem

Problem statements:

For calculating the cost fo goods sold we use the formula: opening stock value+purchase value-closing stock value.

Thereafter we calculate the gross profit.

Use the accounting analysts to help and calculate the cogs at perpetual  level across unlimited companies, 

unlimited branches and across the complete historical time frame.

Issues with the ERP valuations:

a.) The purchase value is normally calculated with ledger parent 'purchase accounts', excluding order entries. However in the schedule 3 balance 

sheet entries the CFOs and CEOs like to exclude the following entries also:

i.) Delivery notes and receipt notes

ii.) Stock journal entries(this calculation shall also include for dales entries)

iii.) Branch transfer details(a firm could have multiple companies or multiple branches. Stock transfer between them needs to be excluded)

iv.) Purchase of capital goods.

v.) Rate differences(this is included in ledger masters)

vi.) If the company is using cost centers with branches then the solution needs to be configured accordingly.

Similar calculations are to be done for closing stock value with the sales accounts.

Why use our accounting analysts:

a.) The accounting analysts shall come prepared with the software module(with and without database) on site

b.) The reports shall be automated and tamper proof(a critical requirement for many CFOs)

c.) The product will have features row level and filter level contexts and ledger and item heirarchy flattening

d.) The CFO and CEO can dictate how the solution is to be configured.

what else would the analyst do?

a.) Based on the custom calculations the analyst shall help adjust the MRP entries in Tally/other ERP.

b.) Help automate the distribution of MRP details to various internal and external stakeholders in the ERP

Why can't a normal accountant do this job?

a.) Normal accountants are trained only in Excel

b.) Large data limitations

c.) Routine tasks for the accountant

d.) Reliance of the accountants only on the numebrs given in the ERP

e.) Profitability is not a priority for the accountant

Number of companies that have implemented the solution: more than 15

Automation of profit and loss statements: https://www.youtube.com/watch?v=O8_3PMVBUv8

Usage of vlookup and sumproduct in Excel: https://www.youtube.com/watch?v=hpNs1uUYbTg

Sumif(Excel to DAX): https://www.youtube.com/watch?v=_pndahQvpvk

Row level and filter level contexts: https://www.youtube.com/watch?v=_3XlMxB_R0U

Lookup(DAX): https://www.youtube.com/watch?v=eIT4YAITIIg

Time intelligence: https://www.youtube.com/watch?v=6bowkQ4dcxk

Blogspot link: 

Turbodata: https://mndatasolutionsindia.blogspot.com/2021/10/turbofast-powerbi-reports-from-tally.html

Running  sum problem for inventory valuations: https://mndatasolutionsindia.blogspot.com/2021/01/resolving-running-sum-problem-using-dax.html

Weighted average valuation: https://mndatasolutionsindia.blogspot.com/2020/08/weighted-average-valuation-from-tally.html

Batch wise analysis: https://mndatasolutionsindia.blogspot.com/2020/01/turbo-analytics-batch-wise-bill-wise.html

Name: Apoorv chaturvedi

Phone:+91-8802466356

email: support@turbodatatool.com;support@mndatasolutions.com