Are you selling on Flipkart? If yes, then in this edition of e-commerce business you will insights about “How to file GSTR-3B – Flipkart”
There are three simple steps through which we are going to generate the required information that will help you in filing your monthly GST return i.e. GSTR-3B.
These Master Excel files can be imported in Tally ERP9, if you want to import such files you can do so. Kindly check the TDLs loaded on your registered Tally license. In case, you need the same you will have to contact your Tally partner.
By using the power of Excel i.e. through the PIVOT table, we are going to generate the following required sales figure for the respective tax period on the basis of which we are going to file GSTR-3B:
- Column No. 3.1 (outward taxable supplies) that will include:
Intra-state sales figures (Supplies made in your home state) and Inter-state sales figures (Supplies made outside your home state).
- Column No. 3.2 (Inter-state supplies)
Download the Sales report from the Flipkart seller dashboard according to your tax period.
Are you new to the Flipkart seller dashboard? Here is the full information on how to get the desired sales report for your GST return filing purpose.
Formatting the raw sales report (downloaded in Excel format).
Since most of the e-commerce operator does not provide us the sales report as per our accounting software format. So, being an online seller, it is the foremost thing for us to understand the downloaded data i.e. what kind of information is available to us that can enable us to file correctly and accurately our monthly GST return (GSTR-3B).
We will format the downloaded excel file as per our Master file. The master file is the final file on the basis of which we are going to file our monthly GSTR-3B return.
After downloading the sales report from the Flipkart seller dashboard, first, click on the option “enable editing”.
A: RAW FILE
Now we will be discussing first what changes is to be made in Raw File i.e. the downloaded excel file so that it can be pasted in our Master file:
Note: Only the relevant column(s) that are required for our Master Excel file are discussed here.
|Column||Particulars||Action required (if any)|
|Column B||Order ID (we will be needed this in our Master file)||No formatting required for this column.|
|Column D||Product Name||Remove the unwanted [“””]|
|Column F||This will contain the information related to the SKU.||The online seller is required to remove the unwanted [“””SKU:], in order to arrive at your correct SKU. In many cases, it is possible that your accounting software will contain a different SKU against your listed SKU. In that case, the online seller has to mention the SKU that is present in their accounting software so that in case of import of data, this file can be easily downloaded in Tally ERP9.|
|Column H/I||These columns need your special attention that contain data related to Sales, Return, cancellation and Return cancellation.||Refer to the below table for the same.|
Let us first understand how to understand and take into consideration these different concepts.
|Event Type||Event Sub Type||Result|
Here sales figure is in a negative, which clearly states that the particular sale order has been requested for return.
Sale here refer to the actual supplies made during the relevant tax period.
|Return||Cancellation||To be consider as “Return”, to cross verify the same, you can|
– Check order ID in Excel (whether that particular order id has been shown as sale and return, etc.) -Check order ID on Flipkart (you must recheck the status of these particular order id from your seller dashboard)
|Return||Return Cancellation||To be considered as “Sale”.|
It states that particular order id once has been recorded as sales return, is now been taken as return cancelled. That means the return has been cancelled and that particular order id has been taken into account while calculating the supplies made during the relevant tax period.
|Column||Particulars||Action required (if any)|
|Column L/M/AS||Order date/ Approval date/ Invoice date.|
If you are looking for a specific order status, then these dates will help you to analyze the exact status of the order.
|We have to remove the [00:00:00.0] from the date format.|
For master file we would require the Invoice date as stated in Column AS.
|Column N||Product Item Quantity||Remove the unwanted [.0]|
|Column V||Final Invoice amount i.e. price after discount and shipping||Remove the unwanted [.0]|
|Column X||Taxable value i.e. Final invoice amount (-) Taxes||Positive figure indicate the Sales figure, whereas the negative figure indicates the sales return.|
|Column(s)||Related to GST/Taxes will get auto filed in Master File according to your GST Rates.|
|Column AR||Buyer Invoice ID||No formatting required|
|Column AT||Buyer Invoice amount||It should be the base for all the calculation(s)|
|Column AV||Customer Billing State||No formatting required.|
|Column AU||Customer billing Pincode||No formatting required.|
B: Master File
Now let us understand our Master file, column-wise along with that I will share the respective column from the Raw file against each column that needs to be pasted.
|Column No. that is required to copy from Raw file (Downloaded from Flipkart)* after being formatted||Master File (Column where copied data is required to be pasted)|
|We need to enter the ledger name as created in our accounting software (For understanding purpose I have taken “Flipkart”)||E|
|As per formula||I|
|As per formula||X,Z,AB,AD|
|As per your GST rate slab||Y,AA,AC,AD|
|Main Location (If a different godown has been created, then the name of that godown)||AS|
|Narration (as explained in video)||AU|
|NEW_INVOICE & Tax Invoice (For sales)||AV and AW|
|CREDIT_NOTE & Credit Note (For sales return)||-do-|
|Can be left blank or enter AS||BA|
|Use concatenate (as explained in the video)||BH|
Dummy entries are available for your along with the Pivot table for your reference.
Download your Master File – Sales
Download your Master File – Sales Return
We will use the PIVOT table for Sales data as well as for sales return data. Online sellers will have to apply the PIVOT table in both the data files to reach out the net figures for each state(s).
First open the formatted Flipkart Sales Excel file
- Click on the Insert tab from the top menu
- Select Insert Pivot Table
- Create Pivot Table tab will get pop out. Click on the select a table or range and choose where you want the pivot table report to be placed
By default, it will open on the New worksheet, if you want you can select the Existing worksheet also and Click on “Ok” to proceed.
- Now you will see, the new worksheet will open that will show you some Pivot table fields on the right-hand side of the worksheet.
Just click on the fields one by one in the same order
- Shipping Address State
You will get your total sales amount state-wise including your home state where CGST/SGST is charged for the purpose of GSTR-3B.
For entering the data in Table 3.2, just ignore the figures of your home state i.e. intra-state supplies.
Table 3.1 will contain all the sales figures including home state sales.
Likewise, you will have to apply the Pivot Table on the Sales return excel and you will get the sales return figures as per the state wise.
By netting off the figures state-wise you will get the amount that needs to be entered in your GSTR-3B.
In case of any doubt you can refer the below video
Still, if there is some doubt about the topic, feel free to comment us your query.
CA Devesh Thakur