[NEW FEATURE] VAT report

[hpema]

Hi Team,

We need to build a vat report that users are able to run to do their vat. I have placed files and report on folder for further discussion.

https://drive.google.com/open?id=1-uMD0l5jHJg2QiCCxnCFtfwQ7BvNj2eJ

Kind regards Hemant

[agritheory]

11/11/19: Waiting on some test cases and discussion with fiance team from @hpema

[hpema]

Hi team,

here are the rules from my discussion with finance

Customer documents 1) All local customer invoices/credit notes gets put into box 1, showing the excluding, including and tax amount 2) Any assets sold should be placed into box 1A (At Aptronics this is rarely used) 3) Any customer invoice/credit note that is foreign and no vat charged with item type services goes into box 2 4) Any customer invoice/credit note that is foreign and no vat charged with item type product goes into box 2A

Supplier Documents 5) And purchase on captial goods goes into box 14 showing the vat amount 6) All supplier transaction including journals etc, that have vat charges goes into box 15 7) Any customs charges goes into box 15A, this is usually for foreign imports where the actual invoice wont have any VAT but we process a seperate customs invoice where the full value for that particular invoice or lines gets recorded as customs vat. (Examples will be provided)

this is the standard approach and report should show the details on the documents grouped by type and date order.

[hpema]

AS per our discussion add accounting dimensions to the tax transactions to facility the VAT report output.

[SaiFi0102]

@agritheory @hpema I have been thinking of the design and here are some thoughts:

Account Dimensions may not be implementable for Tax Boxes because GL Entries for taxes are made using Taxes and Charges table's Tax Amount in an invoice rather than Line Items. So I'm thinking of alternate solutions out loud.

Here's a way to solve the VAT Report and Automatic Tax Calculation problem with the least changes to ERPNext. The report will be slightly complex. See if this makes sense and let me know if there are any issues with this.

How Tax Category and Item Tax Templates are auto fetched

• You can assign Tax Category to a Customer or Supplier or even an Address and it'll automatically be fetched into the transaction
• You can even manually set Tax Category in a transaction
• Item Tax Templates are auto fetched based on Item / Item Group and Tax Category

How the masters can be defined (a rough idea)

• Tax Category (assign to Customer / Supplier)

  1. Local
  2. Foreign

• Item Tax Template (set rate as applicable) (assign to Item Group)

  1. Service | Local (assigned to Tax Category Local)
  2. Service | Foreign (assigned to Tax Category Foreign)
  3. Goods | Local (assigned to Tax Category Local)
  4. Service | Foreign (assigned to Tax Category Foreign)
  5. Fixed Asset | Local (assigned to Tax Category Local)
  6. Fixed Asset | Foreign (assigned to Tax Category Foreign)

• Sales/Purchase Taxes and Charges

  1. You just need one that will be set as default. You can set the rate to anything, the actual rates will be used from Item Row / Item Tax Template

• Tax Rule

  1. No need to set Tax Rules

• You can even name Item Tax Templates as Tax Box 1, Tax Box 1A ...

• You can make Tax Category field in DocType Customer, Supplier, Sales Invoice, Purchase Invoice as mandatory

For Sales/Purchase Invoices:

The VAT report can pick up data from Invoices and distinguish between tax boxes using the following fields:

• Item Tax Template at an item row level: there can be a setting to map Item Tax Template to Tax Box

It can get the following data from:

• Net Amount (Company Currency) (item table) for Tax Exclusive Amount
• Item Wise Tax Detail (hidden json field in Taxes and Charges table) for Tax Amount and for calculating Tax Inclusive Amount

For Journal Entries

• Still thinking about this. More on this soon
• Maybe all Debits to VAT Account using Journal Entry can go to Tax Box 15

[agritheory]

Maybe all Debits to VAT Account using Journal Entry can go to Tax Box 15

To the account defined in the Tax Rule named "Tax Box 15"? Trying to follow this thought all the way to the T account posting...

[SaiFi0102]

I didn't mention Tax Rule and I didn't mention account either.

This whole idea is to use only one account and no tax rules, but rather item tax templates to distinguish between tax boxes

[hpema]

Hi Saif,

I was thinking of this last night - and im in agreement. Lets use the item tax category as you specify and implement it and I can test. Journals, im confirm with the finance ladies that bank journals and other types also need to have VAT on them, so not sure how we will configure those. Unless we add item tax catogery field there and if its filled then we use it.

Somehow also build a report to show any vat transactions with no category so we can update them if need be before running final VAT report and submissions.

Your thoughts?

Can we start to get this together so we can get users input?

kind regards

[SaiFi0102]

It would have been best and easiest if you separated taxes by accounts. However, this is also possible. I'll work on this on Monday. Let me know which instance to test on.

[hpema]

you can test on aptronics.agritheory.com

[SaiFi0102]

Part of VAT report work on https://github.com/Aptronics/erpnext/pull/9