How to Reconcile GSTR 2B with Books ?

Reconciling GSTR 2B with books is an important process that helps businesses ensure the accuracy of their GST (Goods and Services Tax) returns. GSTR 2B is a statement that is generated by the GST portal and contains details of all the purchases made by a business from its suppliers during a particular period. Reconciling this statement with the business's own books of accounts helps to ensure that all the purchases have been correctly recorded and that the correct GST has been paid.

Here are the steps to reconcile GSTR 2B with books:

1.      Download GSTR 2B from the GST portal: To begin the reconciliation process, the business must first download the GSTR 2B statement for the relevant period from the GST portal.

2.      Compare GSTR 2B with the Purchase Register: The business should compare the details of purchases recorded in GSTR 2B with the details recorded in its own purchase register. This will help to identify any discrepancies in the purchase values, GST rates, and GST amounts.

3.      Check for missing invoices: If an invoice is missing from GSTR 2B, the business should contact the supplier to ensure that the invoice has been correctly recorded and filed with the GST department.

4.      Check for errors in GST rates: If there are any errors in the GST rates recorded in GSTR 2B, the business should contact the supplier to correct the mistake.

5.      Check for errors in GST amounts: If there are any errors in the GST amounts recorded in GSTR 2B, the business should contact the supplier to correct the mistake.

6.      Record the corrected details: Once all the discrepancies have been identified and corrected, the business should update its purchase register with the corrected details.

7.      File GSTR 2A: After reconciling GSTR 2B with books, the business should file GSTR 2A which contains the details of all the purchases made during a particular period.

 

Reconciling GSTR 2B with books can be done using excel formulas to make the process more efficient and accurate. Here are a few excel formulas that can be used for the different steps of the reconciliation process:

 

1.      VLOOKUP: This formula can be used to match the invoices in GSTR 2B with the invoices recorded in the business's purchase register. It allows you to search for a specific invoice number in the purchase register and returns the corresponding GST rate and GST amount.

2.      SUMIF: This formula can be used to sum up the GST amounts for each GST rate. It allows you to specify a criteria, such as GST rate, and then adds up all the GST amounts that meet that criteria.

3.      COUNTIF: This formula can be used to count the number of invoices that are missing from GSTR 2B. It allows you to specify a criteria, such as invoice number, and then counts the number of instances that meet that criteria in the purchase register but not in GSTR 2B.

4.      IFERROR: This formula can be used to check for errors in GST rates and GST amounts. It allows you to specify a formula, such as comparing the GST rate in GSTR 2B to the GST rate in the purchase register, and then returns an error message if there is a discrepancy.

5.      INDEX/MATCH: This formula can be used to update the purchase register with the corrected details. It allows you to search for a specific invoice number in the purchase register and then update the GST rate and GST amount based on the corrected details in GSTR 2B.

 

Reconciling GSTR 2B with books can also be done using Power Query and Pivot tables in Excel. Power Query is a powerful data connection and transformation tool that can be used to import, manipulate and clean data from various sources. Pivot tables, on the other hand, is a powerful data summarization tool that can be used to create dynamic and interactive reports. Together, Power Query and Pivot tables can make the process of reconciling GSTR 2B with books more efficient and accurate.

 

Here are the steps to use Power Query and Pivot tables for reconciliation:

1.      Import GSTR 2B and Purchase Register: Use Power Query to import the GSTR 2B statement and the business's purchase register into Excel. The data can be imported from different sources such as CSV, Excel, or even a web page.

2.      Clean and Transform Data: Use Power Query to clean and transform the data by removing duplicates, filling in missing values, and splitting columns as needed. This will ensure that the data is in the right format for reconciliation.

3.      Create Pivot Table: Use the Pivot Table wizard to create a pivot table that summarizes the data from GSTR 2B and the purchase register. This will allow you to compare the purchase values, GST rates, and GST amounts from both sources.

4.      Add Calculated Fields: Use calculated fields to add new columns to the pivot table that show the differences between the data in GSTR 2B and the purchase register. For example, you can add a column that shows the difference between the GST amount in GSTR 2B and the GST amount in the purchase register.

5.      Use Filters and Slicers: Use filters and slicers to drill down into the data and identify any discrepancies. For example, you can filter the pivot table to show only the purchases that have a difference in GST amount.

6.      Use Conditional Formatting: Use conditional formatting to highlight any discrepancies in the data. For example, you can format the cells that have a difference in GST amount to turn red.

By using Power Query and Pivot tables, businesses can quickly and easily reconcile GSTR 2B with their own books of accounts. This can help to ensure that all purchases have been correctly recorded and that the correct GST has been paid. The use of Pivot and Power Query also makes the process more efficient and interactive, allowing businesses to easily identify discrepancies and correct them as soon as they appear.

 

Conclusion : By reconciling GSTR 2B with its own books of accounts, a business can ensure that all its purchases have been correctly recorded and that the correct GST has been paid. This can help to avoid any discrepancies that could lead to penalties and fines from the GST department.