GST Reconciliation Excel: The Full Guide to GSTR-2B Matching, ITC Claims and Legal Compliance [Read Order]
GST Reconciliation Excel: The Full Guide to GSTR-2B Matching, ITC Claims and Legal
Introduction
In the complicated ecosystem of the Goods and Services Tax (GST) regime, saying ‘compliance is key’ has never been more meaningful. The benefit of Input Tax Credit (ITC) is the lifeline that saves the cascading effect of taxes for enterprises, big and small. But that claim is not automatic. It is contingent upon the lengthy procedure of reconciliation. Reconciling data from a taxpayer’s books and the government site isn’t just a compliance activity but a strategic imperative to protect revenue and prevent legal hazards.
What is GST Reconciliation?
GST reconciliation is the method of matching the data maintained in the accounting records of a taxpayer (Purchase Register) with the data provided by suppliers and shown in the government’s returns (GSTR-2B and GSTR-1). The purpose is simple, but vital: to spot inconsistencies like missing invoices, wrong tax amounts or duplicate entries.
The disagreement leads to a gap in the ITC claims. The recipient’s ITC is at risk if the supplier does not upload an invoice or file their return late. As per legislative framework, the availability of ITC is provided under Section 16 of the CGST Act, 2017 only when the goods/services are received, the tax is paid by the supplier to the government, and the recipient obtains a valid tax invoice. This “second condition” of tax payment by the supplier is only checked by reconciliation with GSTR-2B.
Also Read:GST on Cross-Border Management Fees and Technical Services
Beyond the Basics: Types of Reconciliation
The reconciliation into numerous critical categories, mainly Sales and Purchase data.
1. Sales Reconciliation - Reconciliation of taxpayer’s sales register with Form GSTR-1 to check if all outward supplies are being reported.
2. Purchase Reconciliation: Matching of Purchase Register with GSTR-2B Form to verify ITC claims.
3. E-Invoice Reconciliation: Verification of data reported in the e-invoice gateway with GSTR-1 and books of accounts.
Of all these, the reconciliation of GSTR-2B with the Purchase Register is the most high risk operation for any finance team. GSTR-2B is a static auto-drafted statement indicating the ITC available to a taxpayer for a certain tax period. GSTR-2B is a static return for the month and not subject to change unlike GSTR-2A. It is the last source for claiming credit.
💼 Make Tax Audit Filing Faster, Smarter & More Accurate | Click Here
The Legal Imperative of ITC and Reconciliation – Case Laws
Several judicial decisions are stated the need for strict reconciliation. The Courts have generally found that the right to ITC of the beneficiary is conditional.
In the landmark case of M/s. Sadhanlal Chandra vs. Union of India (Calcutta High Court), the Court remarked that “where the supplier has failed to deposit the tax, then ITC cannot be denied to a bona fide purchaser if the purchaser can prove the genuineness of the transaction”. But this remedy is generally conditional upon the purchaser showing due diligence.
In the case of M/s. Transtonnelstroy Afcons Joint Venture vs. Union of India, the Madras High Court had ruled that the duty to pay tax is on the supplier and the customer cannot be penalised for the default of the provider. However, the practical reality under Section 16(2)(aa) is that the invoice has to be reflected in GSTR-2B.
In a recent case, the issue of availability of ITC where the suppliers have not submitted returns was dealt with by the Allahabad High Court in M/s. Siddhartha Tubes Ltd. vs. State of U.P.. To safeguard the credit of the receiver, the court has directed the department to recover dues from the defaulting vendors. These examples demonstrate an important trend that courts are protective of valid ITC but the burden is very much on the taxpayer to maintain pristine records and establish that reconciliation was tried and discrepancies found and remedied.
In another case, M/s. D.Y. Patil College of Engineering vs. State of Maharashtra, the Bombay High Court said that department cannot refuse ITC on technical grounds if the transaction is legitimate. But the court also observed that taxpayers need to check consistency of their claims with the data available with the department. That makes the reconciliation procedure a shield against litigation.
Excel-Based Reconciliation – A Step-by-Step Guide
There is sophisticated software but Excel remains the most flexible tool for customised reconciliation, as seen in the 6-step method in the graphic instruction.
Step 1: Import Data: The first step is to obtain the GSTR-2B JSON file from the GST portal and upload it into Excel format. At the same time the Purchase Register (PR) of the period in question should be prepared.
Step 2: Normalisation: This is the most important phase. Data should be cleansed to be consistent. GSTIN formats should be standardised (e.g., 15 characters), invoice numbers should be cleaned of special characters (hyphens, slashes) and dates should be represented identically. "no match" result for one character mismatch in an invoice number.
Step 3: Matching Data using Excel Functions: The essence of the reconciliation is matching datasets. The book proposes using contemporary Excel functions such as XLOOKUP or older functions such as VLOOKUP.
XLOOKUP: Ideal for matching several criteria, ie GSTIN, Invoice Number and Invoice Date at the same time.
IF and AND Functions: Useful to check if the tax amount in the PR is same as the amount in GSTR-2B.
Step 4: Identifying Differences:
- Not available in GSTR-2B: Invoices in Purchase Register not available in GSTR-2B. This means the supplier has not posted the invoice or not filed GSTR-3B. This may result in a potential loss of ITC.
- Missing in Purchase Register: Invoices appearing in GSTR-2B but not in PR. These could be purchases recorded in another period or fake invoices posted without the taxpayer's knowledge.
- Amount Mismatch: Differences in taxable value or tax amounts, typically owing to typographical errors by the supplier.
Step 5. Correct: If there are differences, the taxpayer should contact the vendors to correct the errors or make modifications. Internal books need to be updated for “Missing in PR.”
Step 6: Reporting and Dashboard Development: Excel’s Pivot Tables and Conditional Formatting can be used to create dynamic dashboards . Unmatched rows can be coloured in red automatically by conditional formatting, allowing easy visual analysis of the data.
Advanced Excel Techniques for Efficient Reconciliation
- Conditional Formatting: This feature helps to visualise the data quality. One can highlight duplicate invoice numbers or GSTINs to promptly catch fraud or errors.
- Pivot Tables: These are great for summarising vast amounts of data. A pivot table may easily show the total ITC available vs total ITC claimed, segregated by supplier or document type.
- Dashboards: A reconciliation dashboard gives a bird’s eye view of compliance status i.e. data such as “Total Invoices Processed”, “Matched Percentage”, “ITC at Risk”, etc.
Conclusion
In today’s regulatory environment, with authorities tightening the leash on bogus ITC claims and compliance violations, reconciliation is the first line of protection. It corresponds to the input tax credit claimed with the credit admissible as per government records.
It is a tough process but with the systematic way from importing the data to creating a dashboard in excel it becomes manageable and efficient. If businesses are meticulous in completing the 6-step verification process and use features such as XLOOKUP, they can be assured that they are claiming every penny of valid credit and simultaneously warding off legal complications. As the GST law matures, Courts would be inclined to favour taxpayers who can demonstrate a robust reconciliation process so establishing that compliance is not only a statutory obligation but a sound economic strategy.
With regulators cracking down on bogus ITC claims and compliance gaps in today’s regulatory environment, reconciliation is the first line of defence. It is matched to the credit allowed as per government data against the Input Tax Credit claimed.
Support our journalism by subscribing to Taxscan premium. Follow us on Telegram for quick updates
