GSTR-2B Reconciliation Demo — Step-by-Step Video Walkthrough

0:00Why VLOOKUP breaks on GSTR-2B reconciliation

Hi, my name is Pavan. Welcome to gstreconcile.in. In this video, I'm going to explain how to do the GST reconciliation of your purchase register against the GSTR-2B using the gstreconcile.in software. Every month you download the GSTR-2B from the portal and open the purchase register next to it and start matching each invoices by eye or you do a VLOOKUP. It will break the exact moment when the supplier has typed a small mistake in the invoice number.

0:31Runs in your browser + the 7 match statuses

Now before we proceed further there are three things that are worth noting. Point number one this entire GST reconciliation will run in your browser. So we don't upload your purchase register to our servers. So there is nothing to trust, nothing to breach. Point two, it doesn't just say matched or not matched. Your every invoice falls in these buckets. Either it is a matched, a partial match, a mismatch Only in Books, Only in 2B, Cross-PAN or a duplicate.

And for the partial match, it tells you exactly why it is closed but not the exact match. The invoice number is same but the taxes differ by four rupees. Or the tax is right but the date is apart by 2 days. Or it is the same invoice written in one file like this and in another file it might be written like this. So you are not rechecking every row you go to the one that needs a decision.

1:18Pricing: Free vs Unlimited

Now when it comes to the pricing I kept it very simple. One is the free version and another is unlimited version. When it comes to the free, you get three GST reconciliations per month and up to 5 MB limit support. In the unlimited sections which costs you ₹999 per month. In this you get unlimited GST reconciliations which means you need not to worry about how many clients you are having.

So you can upload infinite number of clients purchase register and do the reconciliations. And it gives you up to 25 MB limit of file support. And you can download the reconciliation file, reconciliation report and also email support and also the phone call support. If you opt for monthly, it will cost you ₹999 per month. If you go for yearly subscription which you will get 25% off and it will cost you only ₹8,999 per year and per month it cost you ₹750.

2:14Dashboard: where to upload Books and GSTR-2B

Now let's go to the dashboard and see exactly how to do the GST reconciliation. Now if to go to dashboard you have to click on top here dashboard. It will takes you to the dashboard. Now when you look into the dashboard there are two places where you can upload the file. On the right side you have to upload the GSTR-2B on the left side it was a purchase register.

Now for the GSTR-2B we expect you to upload the GSTR-2B file which is downloaded from the GST portal and we expect you to upload the unmodified GSTR-2B. So just download it from the portal and upload it here.

2:55Why Books must use the purchase register template

Now when it comes to the purchase register you have to download a Excel template from here. Here you can see the download Excel templates. The moment you click this button a Excel template will be downloaded for you. Now the question comes why we expect you to upload your purchase register in this template because there are many people who will be using 40 different softwares like Zoho, Tally, Busy and all.

Now there are 40 different formats will be there. Now instead of guessing at the exact column where the tax column is which is very wrong, it might be sometimes get wrong. So it's rather easy, very easy to put your data into this template. Now I will show you exactly how you can do this one. The moment you click this download Excel button, it will get you a template and the template will look like this. Now it is similar to the GSTR-2B.

3:53Books template: required vs optional columns

Now in this XLSX document we have three sheets. In the bottom you can see B2B, B2B-CDNR and the instruction file. Now when it comes to the instruction here I have clearly mentioned exactly which column is required, which column is optional, which column is recommended. Now for the GSTIN column it is required and we expect you to upload the valid 15-character GSTIN and for the trade or legal name we it is recommended column.

It is not a required column. Now when it comes to the invoice number, invoice date, those both two columns are must you have to upload the data otherwise gstreconcile.in software will throw error. Now when it comes to the invoice value, it is optional but it is very recommend to upload the invoice value. Now when it comes to the supply attract reverse charge, it is optional and taxable value it is very recommended to upload.

Now when it comes to the IGST, CGST, SGST, these three columns it is a must the value should be there and the sum of three values IGST, CGST and SGST should not be equal to zero. And there is a CESS also it is very recommended in case if you don't have IGST, CGST, SGST and we expect you to have a CESS as well. So this is a guide or a instruction sheet.

When you open this instruction sheet, it will explain it to you and it's quite self-explanatory. You can come and look into this which fields or which columns are mandatory and which are optional or which are recommended.

5:25B2B and B2B-CDNR sheets

Now when it comes to the B2B the columns will look like this. GSTIN supplier, trade or legal name, invoice number, invoice date, invoice value, supply attract reverse charge, taxable value, integrated tax, central tax, state tax and ITC availability and same goes for the B2B-CDNR. Now for the B2B you have to enter all the invoice numbers here and for the CDNR you can paste all the data here.

Now once it is done it will look like somehow like this. This is a dummy data that which I have got from the internet. This is how B2B B2B-CDNR will looks like.

6:28Upload errors: file type and size limit

Now let's go and see and try to upload this data in the gstreconcile.in software and let's see are there any errors that you might face when trying to do the GST reconciliation and there are quite a few errors errors that you might face when doing the reconciliation now I'll go one by one there are primarily two errors that which you might get one is the unsupported file if you're uploading it in a CSV or some other format so we expect you to upload it in a xlsx or xls format and the other one is the file limit.

For a free tier, there is a 5 MB file limit and for the unlimited tier, there is a 25 MB limit. So, you have to make sure that your purchase register is under these two limits. Now, let's go and upload the purchase register and let me upload the dummy GSTR-2B file as well.

7:11Error: Books file missing B2B / B2B-CDNR sheets

Now, let's look at the some of the errors that you might face while doing the reconciliation. Let me upload the errored purchase register here on the left side. Let me go and upload this particular file which is this. Now let's do the reconciliation and let's see what error you might get. Upon doing the reconciliation first it will check the quota whether you have quota there or not.

After that you can see error. The error says that the uploaded books file is missing both B2B B2B-CDNR sheet. Please ensure that you are using the correct file format. You can download the books template from the dashboard or you can click this button. It will download the file for you. So what this error is saying? This error is saying that in this XLSX template or a purchase register there is a B2B or a B2B-CDNR sheets are missing.

So when you come and look at here this is the file that I have uploaded previously. You can see in the bottom here I have sheet one sheet two but as I said the template that which we expect you to have a B2B B2B-CDNR sheets which is like this B2B B2B-CDNR sheets.

8:26Error: missing header rows

Now let's look at the another error that which you might get. Now let me upload the second error file which is this books 2 missing header rows of columns. Let me do the reconciliation. It will check for the quota and let's wait for some time and let's look at the error what it throws. Yeah, we got the error. Now let's look at this what it says. The B2B sheet inside your uploaded books file is missing its standard header rows.

Please ensure that you are using the correct books format and you can download the books template from the dashboard here. Now let's look at the file exactly what this error is saying. This is the exact file that which I uploaded. Imagine just just look at here you have the B2B B2B-CDNR sheets here that we are making sure now what it's saying is it is missing the standard header rows.

What does it mean here? Look at here. If you look at the original template that I'm having the on the fifth and the sixth row are the entire my header rows are there. But when you look at this file here at the fifth and sixth rows the header rows are missing. That's what it is throwing around. So we expect you to upload the unmodified books template file.

You just have to come here into the books template and enter the data. So this is the second error that you you might get sometimes.

9:53Error: missing required columns

Now let's look at the second let's look at the third error that which you might get. Let me upload another file as well and let's do the reconciliation and let's see what it throws. Yeah, here error came in the purchase register B2B sheet is missing the following required columns invoice number IGST. Please upload the file in the correct books format. You can download the books template from the dashboard.

Now what does it says here? It says that if the uploaded file is missing some of the columns here you can see this is the dummy data again here some of the columns are missing. If you try to modify the columns it will throw this error. So you have to upload an unmodified books template file.

10:40Error: empty Books file

Now let's look at the fourth error that which you might get. Let me upload the fourth error as well. Yep. Let's do the reconciliation and let's see the error. Let it check the quota. Yeah, now we got the error. What does it says? It says that no data found in the books file. The B2B and B2B-CDNR sheets are empty. You can download the books template from the dashboard.

Now what is it saying? Let's go and let's look at the file that which I have uploaded here. As you can see that this column this row is having all the header rows but entire data is missing here. You can see at the B2B-CDNR as well all the data is missing. So when you try to upload a empty template it will throw error saying hey put some data in it and try to reupload it. So you can check into this error.

11:35Error report: invalid GSTIN, missing invoice number or date

Now for the final error this is a very important error that you might get. You most of the times get actually let me upload this file. Now this is one of the very important error. It says that if you have some of the mistakes done in the purchase register let's say you have modified a GST number or you have entered a empty invoice date or you have entered a empty invoice number.

In this case it will throw a error and it will also give you a report file. You can download the report from here. The moment you click the download, it will download your error report. You can see exactly where which line is wrong. And after that modifying in that exact row, you can you can re-upload the same file. No need to again do the work from the beginning.

So download the error report and let me show you how the error report will look like this. So here it is very important. So as I said in the instructions there are some of the required and recommended fields are there. So we expect you to have a data in all these columns. Let me go to the B2B sheet and let's see what the error is saying. Now in the first row it is saying that missing GSTIN number or yeah you can see here in the seventh row it is having a missing a GST number.

So we expect to have a GST number mandatory. After that here one more row is missing which is a invalid GST number. So it says that invalid GST number which is this. When you look at this number it might looks correct to you but I'll make sure that this is having some of the mistake. So you have to apply you have to upload a valid GST number that you have to make sure.

So here there is a small mistake that which you might do. So that it is being caught here. Now let's look at the another error which is missing invoice number. Here if you look at this 9th row here you have a missing invoice number and again missing invoice date and then the total tax cannot be zero. Yes, here you are missing an invoice date that is there and the next error is total invoice cannot be zero.

Now if you look at this particular row here the sum of all the taxes all the taxes which is IGST CGST SGST all are zero. So this is the important fifth error that you which you might get. So you have to make the modifications here which you can do after that reupload the same file again.

14:18Reconciliation summary: matched, partial, ITC at risk

Now let's upload a valid valid purchase register file. Let me remove this and let me come here and upload the correct purchase register file and let's hit the GST reconciliation and let's wait for some time. It does the reconciliation and yeah we got the report. Now let's go and analyze this one. Now let me scroll it down and let's analyze what this report is. Now if you come to look here we have two sections.

One is the invoice B2B and the debit credit note CDNR. As I said in the purchase register we have two sheets which is B2B and then the B2B-CDNR. So for two sheets we have two different sections. So if you want to look into the B2B-CDNR records and you can click on this and you can look into here and if you look if you want to look into the B2B invoices you can click here and we can see here there are total 3,500 entries where 956 are matched and where 1,900 partial matches are there.

This is a very interesting number. Now this partial match is exactly where your VLOOKUP in the standard Excel will fail exactly. Now we have only in books 318 only in 2B 318. The total ITC at risk is 29.67 lakhs.

15:50Invoices, GSTIN Summary and Totals & Checks views

Now if I scroll it down here we have three more section. What does it say? Here we have invoices section GSTIN Summary and then the last one is Totals & Checks. In the invoices we will show each invoice row entry by entry. Now if you click on the GSTIN Summary here it will give you the GSTIN-wise summary where the records are sorted by the who is the highest risk amount.

So here if you see at the first record this GSTIN number which is the Nexus logistic 27 and its ITC at risk at 84,121. Now what exactly is missing? Now we will come and look at this one later.

16:32Match basis and discrepancies columns

Now let's go into the invoices and let's look what exactly is there. Now here you have all records matched which is 956 partial match 1908 records only in books only in 2B. Now here you can see in the header we have a status header and then the match basis discrepances GST and party and all. Now we will show all the differences side by side. As you can see here, as you can see in the IGST, CGST, SGST, CESS and all I'm showing you the difference side by side.

What's the value in the books? What is the value in the 2B and how much difference is there and we do the same for the date, invoice number, taxable value, invoice number and all. Now let's look at the match basis column. Let me quickly hover my cursor in the match basis. Now let's look into it. What does it say? It says that this match basis column explains exactly how we pair these records together.

It tells you which variable successfully match it such as full invoice match. The invoice numbers are identical. Invoice matched with the typos. We ignore small minor differences such as formatting differences, slashes, dashes and all. And then the tax amount matched, date matched, GST number matched. So these are all the matching basis that which you can see what things have matched perfectly. Now when it comes to the discrepancies column, let me hover over it and we can see a tool tip over there.

What does it say? It says that highlight exactly what difference between your books and 2B tax difference date mismatch. This tells you what you need to investigate or ask your supplier to amend. Now what does the discrepancies column says? It says that exactly what is wrong in your invoice. Is there a wrong in invoice number or is there a tax amount difference? What exactly is wrong? So it tells you this column.

18:33Partial match: tax difference

Now let me click on the partial matches section. Here you can see a button partial match. The moment we click on this we can see exactly what difference is there. Now in the match basis I said that GSTIN number, invoice and date are exactly matched. The only difference is in the tax. Now if I look in the tax it says that tax in the discrepancies column I can see here row it is saying partial match and in the discrepancies it is saying tax and it needs a verification.

Now let me scroll to the right and let's see what difference is there. It says that oh here in the IGST you have what 25 rupees difference in the 2B there is a 25 rupee difference is there. Now you can look into it. Now this tax difference and then the dates are depends on the two things. I'll let you know all the settings a bit later. Now let's look at few of the partial invoices what could be.

Now let me scroll it down. Yeah these are all the tax differences. So you can see tax differences of 12 rupees 25 rupees something and all.

19:44Partial match: invoice number typo

Now let me scroll it down and let me go to some other records. And if I click here it says this. Yep. Let's look at some of the differences. What could it be? Now if we look into here it says that invoice number there is a minor difference in the invoice number in the top. You can see here in the invoice number if you see at this row there is a minor difference in the invoice number.

Let's look into what is the minor differences here. The invoice number in the books it is INV/78-68/0003 in the GSTR-2B it is the same exactly but at the end there's a small /25 is there which is added by the supplier. Now this is a minor difference is there and here one more minor difference we can see same here you can see here it is INV but in the supplier GSTR-2B it is IMV so it is a minor typo by the supplier now this exact thing will fail in the VLOOKUP if you are going to do it via Excel so this is a benefit doing it with the gstreconcile.in.

20:58Partial match: different invoice number, same tax and date

Now let's look at the another match here it says invoice number there is no match here yeah we can see that there is no match between this two columns. But why it is being a partial match? It says that GSTIN is matching, tax is matching, date is matching. If we come and look at here, there is no difference between the IGST, CGST and SGST. So they are exactly matching.

So this is the thing. So here you have invoices number. There are minor differences, no match and all. Here we can look into it exactly what differences is there. So this is how you can analyze the partial matches.

21:34Only in Books and Only in 2B

Now when it comes to the only in books it says that there are some records that which are in your purchase register but it is missing in the GSTR-2B. Now if you look into it here it's you can see this is the GST number. This is a party name and you have this particular invoice number which is present in the books. That's why it is not there in the 2B and you can call to the supplier saying hey this particular invoice is missing from your supplier and we can do the same for the only in 2B which means there is a record in GSTR-2B but it is missing in your purchase register. So for that you can look into this what exactly things that you are missing in the purchase register.

22:21Tax and date tolerance settings

Now let me go to the partial match column once again. There is a one more important thing. Now for the match basis there are two things. One is a tax value the other end is the date. These two things are configurable. Now why I'm saying here is a tax. There is a it needs verification. Here you can see it's a small amount 25 rupees. Why it needs a verification?

You might think like that. So this tax amount number and then the date are both configurable. You can see in the side panel there is a settings button here. If I click on this one, you can see this popup is there. Here you can tolerate, you can enter the tolerable tax amount. Now here I entered the 5 rupees which means if the tax amount is below 5 rupees, I'm saying it is a match.

Now if the tax amount is more than five, I'm saying hey there is a mismatch. We have to look into this one. Now the same goes for the days tolerance as well. If there is a date and date in the invoice number, date in the 2B if they differ by 3 days. Now I'm say let's say it is a match. Now if you want to put it strictly like 1 if you want to say that yeah there shouldn't be difference more than 1 day in the invoice date and the 2B you can come and configure these settings however you want.

Anyhow I suggest you to keep the five rupees as a tax tolerance and days tolerance as three. Let me save the setting and you can modify it whenever you want.

23:50GSTIN Summary: ITC at risk per supplier

Now let's look into the another section that which I mentioned earlier which is GSTIN Summary. You can look into the GSTIN-wise summary where all the entries are sorted by the ITC have the higher risk. Now this particular supplier is the higher risk. Why it is the higher risk? Because they are having the partial matches of 48 and they have only in books six records and only 2B nine records.

24:24Cross-PAN and duplicates

So you can look into the exactly what they are missing and we have one more section called cross-PAN as well and then the duplicate cross-PAN means if the supplier accidentally filed into the another sheet you can get that record here as well. It will point it out.

24:37Totals & Checks

Now let's look into this totals and check section. What does this section say here? All your books sum amount compared against the GSTR-2B. You can look into this taxable value of this sum of your purchases are compared against the GSTR-2B. Same goes for the IGST, CGST, SGST, CESS and then the total tax amount, total invoice values, how much you are at risk. You can check all the things here.

Now this is these are all the three column three sections invoice GSTIN Summary totals and checks. Now for the credit note debit note section also the same thing you can come and look into all the invoices or the GST invoice summary or the Totals & Checks.

25:22Download the Excel report

Now after this thing if you want to download it as Excel format you can click this download Excel button here. It will download the Excel format. Now let's look into it. How does the Excel report will look like? Now this is the Excel report. Here again you have totals of the six sheets are available which are reconciliation B2B summary of B2B totals and checks. Again reconciliation CDNR summary CDNR totals and checks.

Now let's look into this here what we are having. Now, same goes for the status column. We have match basis. And here I have a note as well. If you want to look into it what the each column or the each header note says for the match basis for the discrepancies under and you can come and filter it by the partial match as well. The moment to filter it can look into exactly what the partial matches are.

And when I come to the discrepancies, I can look into total number of discrepancies are there. Let's say let me click on the invoice minor differences. If I click okay, I can see total invoices that are by day differing and let me look into the invoice number what this invoice numbers are saying. You can see the difference of the invoice number which are minor minor differences that are there.

So you can call your supplier number or you can reconcile here itself. Now the same goes for the summary as well. You can look into the GSTIN Summary totals and checks reconciliation for the B2B-CDNR and download. Here you can compare everything. Yeah. And one more thing that I forgot to mention which is at the end of the sheet you can see the description what is matching or what is not matching.

Here it says tax and date match invoice has a minor difference or typo difference. Here you have a clear description as well. Now here the differences are comparable side by side. This is how the exported data of the file reconciliation will look like.

27:37Search by GSTIN or invoice number

Now let me go back to the GST reconciliation. Here I have one more search bar here. Here if you want to search by any GST number or any invoice, you can quick do a quick search over here. Hit the invoice number and hit enter. It will show you exactly the field here. So this is the search bar that which you can use.

27:58Wrap-up and contact

So this is all about the gstreconcile.in here in this video I have explained how to do the GST reconciliation with your purchase register against the GSTR-2B. So that's the whole thing. That's how you can do the GST reconciliation in the software. Let me go back to the landing page and let's look at the pricing ones. So if you feel like you can do the unlimited if you have a need of doing the unlimited reconciliations you have many clients now you can opt for this plan yearly plan which is a 25% off otherwise if you want to go monthly plan you can opt for the ₹999 per month and you can cancel it anytime.

So that's it and thanks for watching this video. Please do like and subscribe this channel and if you want any features if you have any improvements to be made please drop it in the comment and if you want if you want to contact me so you can contact me at these two places here they have support here you can mail at the support@gstreconcile.in and you can call at this number and if you have any queries or any doubts you can call at this number and I will respond to this number. Yeah, that's it for this video and thanks