GSTR1 is a detailed return that has to be filed every month for the outward supplies/sales made in the concerned tax period.
This article will provide detailed steps on how to export data from QuickBooks in the government excel utility template to file GSTR1.

Step 1: In the Reports section, go to the GSTR Reports tab and under GSTR1 Reports section, click on the button Export GSTR1.

User-added image


Step 2: In the popup (GSTR1 – Consolidated Excel) choose the Start Date and the End Date for which you want to download the report and click on Generate Excel.


Step 3: Below are the list of do’s, don’ts and manual inputs required by the user in Excel after the download.
Note:
  • Use “Out of scope” tax code for transactions with customer type as Overseas and SEZ only and where an LUT (Letter of Undertaking) has been obtained from the government.
  • Shipping charges and shipping tax need to be added as a line item in the invoice and sales receipt, rather than adding them in the inbuilt shipping fields, for them to be included in the GSTR1 reports.
  • We are using the same acronyms as the government excel utility.

Section 4, 6b and 6c - b2b [1]:
  1. You will need to manually put data for the mandatory columns Reverse charge and non-mandatory column E-Commerce GSTIN and Cess for each row.
  2. If any data is missing for other mandatory field i.e. GSTIN/UIN of RecipientInvoice NumberInvoice dateInvoice valuePlace of Supply, Invoice TypeRate and Taxable value, add them manually.
Note:
  • Invoice value must be same for any row items if they have the same invoice number.
  • Invoice type must be same for any row items if they have the same invoice number.
  • If any of the mandatory line items are missing then delete the entire row of the transaction.
Section 5- b2cl [2]:
  1. Add inputs for the non-mandatory columns E-Commerce GSTIN and Cess for each row.
  2. If any data is missing for other mandatory field i.e. Invoice Number, Invoice date, Invoice value, Place of Supply, Rate and Taxable value, add them manually.
Note:
  • Invoice value must be same for any row items if they have the same invoice number.
  • Invoice value, which the sum of the amounts of line items of an invoice in QuickBooks, should be greater than ₹ 2.5 lakhs for all the row items in excel.
  • If any of the mandatory line items are missing, delete the entire row of the transaction.
Section 6a- exp [3]:
  1. Provide for the mandatory column Port code manually.
  2. For export of products: If any data is missing for other mandatory field i.e. Export Type, Invoice Number, Invoice date, Invoice value, Shipping Bill Number, Shipping Bill Date, Rate and Taxable value, fill them up manually.
  3. For export of services: If any data is missing for other mandatory field i.e. Export Type, Invoice Number, Invoice date, Invoice value, Rate and Taxable value, fill them in manually. The columns Port code, Shipping Bill Number and Shipping Bill Date are not required to be filled.
Note:
  • Invoice value has to be same for any row items if they have the same invoice number.
  • Export type must be same for any row items if they have the same invoice number.
  • Supplies made to SEZ unit/SEZ Developer/Deemed exports are included in the section 4- b2b.
  • The government utility software doesn’t allow upload of multiple line items in case of export of services (Only the first line item for an invoice/sales receipt get added rest are skipped). If you try to upload multiple line items for an invoice/sales receipt then they have to be manually added in the utility software by editing the same.
  • If any of the mandatory line items are missing, delete the entire row of the transaction.
Section 7- b2cs [4]:
  1. Provide manual inputs for the mandatory Column Type and non-mandatory columns E-Commerce GSTIN (mandatory only if Type is chosen as “E”) and Cess Amount for each row.
  2. If any data is missing for other mandatory field i.e. Place of Supply, Rate and Taxable value then those have to be manually filled by the user.
Note:
  • If any of the mandatory line items are missing then delete the entire row of the transaction.
  • Credit note and/or refund receipt raised against any invoice or sales receipt in the current filing period will have to be manually subtracted from the transaction.
Section 8- exemp [5]:
Provide manual input for the non-mandatory column Non-GST supplies.
Note:
  • Credit note and/or refund receipt raised against any invoice or sales receipt in the current filing period will be manually subtracted from the transaction.
  • You will need to manually upload this section in the government website as currently the utility software doesn’t support this section.
Section 9B- cdnr [6]  and cdnur [7]: You will need to manually upload this section. 

Section 11- at [8]  and atadj [9]: You will need to manually upload this section 

Section 12- hsn[10]:
  1. Provide manual inputs for the mandatory column UQC and non-mandatory columns Description, State/UT Tax Amount and Cess Amount for each row.
  2. For missing data in other mandatory columns, i.e., HSN, Total Quantity, Total Value and Taxable Value, and non-mandatory columns Integrated Tax Amount, State/UT Tax Amount and Central Tax Amount, please fill them up manually.
Note:
  • If any of the mandatory line items are missing, delete the entire row of the transaction.
Section 13- docs [11]: Provide manual inputs for the non-mandatory column  Cancelled if any invoices or sales receipt has been marked as void in QuickBooks. 

Step 4: Navigate to  gst.gov.in from your web browser, click on  Downloads > Offline Tools > Return Offline Tools > Download to download zip folder for government utility software. 

Step 5: Install the utility on your computer and run the software. 

Step 6: Click on NEW under Upload new invoice/other data for return. 

Step 7: Fill in all the 6 mandatory fields as stated below:
  1. GST Statement/Returns: GSTR1
  2. GSTIN of Supplier: Company GSTIN.
  3. Financial Year: 2017-18
  4. Tax Period: July
  5. Aggregate Turnover in the preceding Financial Year: Company turnover for FY17.
  6. Aggregate Turnover- April to June, 2017: Company turnover for April 2017 to June 2017 quarter.
Step 8: Click on  ProceedImport Files > Import Excel > A popup ( Choose File to Upload) will open up.

Step 9: In the popup, navigate to the folder which has the Excel, select the file and click  Open.

Step 10: On the warning popup select  Yes.

Step 11: A success message will be displayed for the successful upload, or a warning message will be displayed with the errors in the sheet with a partial or no upload.

Step 12: Once the transactions are successfully uploaded click on  View Summary > Generate File to generate a JSON file to be uploaded on the government website  gst.gov.in.

References:
  1. b2b- The section includes all the invoices and sales receipt raised against the customers (Regular, Composition, SEZ and Deemed exports) who are GST registered.
  2. b2cl- The section includes all the invoices and sales receipt raised against the customers who are GST un-registered, Invoice value is greater than 2.5 lakhs and the place of supply is different than the state in which the QuickBooks user business is registered i.e. interstate supplies.
  3. exp- The section includes all the invoices and sales receipt raised against the customers who are out of India.
  4. b2cs- The section includes all the invoices and sales receipt (excluding the ones included in b2cl) raised against the customers who are GST un-registered.
  5. exemp- The section includes all the invoices and sales receipt with tax rates 0% GST, 0% IGST, Exempt GST, Exempt IGST and Out of Scope only.
  6. cdnr- This data is currently not recorded in QuickBooks. This section will capture all the credit notes raised against the invoices and sales receipt for registered customers in the current filing period.
  7. cdnur- This data is currently not recorded in QuickBooks. This section will capture all the credit notes raised against the invoices and sales receipt for non-registered customers in the current filing period.
  8. at- This data is currently not recorded in QuickBooks. This section will capture all the advances receive in the current filing period for which invoice has not been issued.
  9. atadj- This data is currently not recorded in QuickBooks. This section will capture all the advances receive in the earlier tax period and adjusted against the invoice in the current tax period.
  10. hsn- The section includes the HSN wise summary of products sold in the current filing period.
  11. docs- This section includes the summary of document issued in the current filing period.