Under GST, when an audit of accounts is conducted, the auditor is required to prepare GSTR 9C which requires much details about the year under audit in different ways. Table 14 which requires an expense wise bifurcation of the Inputs availed is labor intensive, mainly owing to the fact that such a requirement was not foreseen. They are made optional for the first two years to make the work easier.
I would like to share few ideas on how one can discover such data from Tally ERP9 software. Both the methods stated have a pre-requisite that GST Input is booked against the expense in a single Entry (things might not work if you pass two separate entries for expense booking and Input booking). The first method is much easier as no further work needs to be done in Excel.
Tally has a feature that provides data for the GSTR 3B data that can be useful if you have correctly defined the voucher types(say for each expenses category, there exist a voucher type). Follow the path : Gateway of Tally –> Display Menu –> Statutory reports –> GST Reports –> GSTR 3B. There you can find the 3B data for a month which can be changed to an entire year’s data by changing the period(F2).
Under the ‘Inward supplies’ section, you can see the ‘purchases taxable’ grouped under ‘Taxable’ head. Incase you are seeing the GSTR 3B that has the same headings as that of a Return such as ‘Eligible ITC’ instead of Inward supplies, change the type of report to View Summary(Alt+V). Drill down(actual technical term) on purchases taxable and you will probably see voucher wise Input details for the entire year. Now all you have to do is change the report type to Ledger Wise(Alt+L). You can see the entire year’s Input broken down into the respective Ledgers i.e. expenses they came from(as illustrated in Image).
In case by some reason, the first method was unfruitful, try this method. Take up the Input GST ledgers one by one and then take a columnar report of them. For that open an Input Ledger, then change the view to Columnar(Alt+F8). A window pops up asking various columns required, and you don’t need to change any, so press Ctrl+A to accept all. What you will be seeing is a columnar view of each Entry made with ledgers affected by them as various columns(like in the image below).
More work needs to be done to use this data. Export the same to Excel. GST ledger is also a column and now use you excel skills to find the results in such a way that total of GST amounts against which an expense is booked is found out. You can use filters to display only entries having one expense and note the total of GST amount against that. This is not so easier way to work around.
Hope you find use of this article and do let know thorough comments, in case of any mistakes or of an easier solution.