Hi,
How can I manage the following tax? I tried to manage it with “on previous row total” but I got this error message, “For row 2 in Purchase Taxes and Charges. To include on previous row amount in item rate, rows 1 must also be included.”
The tax is as follows:
We buy 10 gallons of gasoline. We have to pay 3,4,or 5 quetzales per gallon depending on the type of gasoline. The VAT tax is 12% of the net total.
How can I best manage this in ERPNext?
thanks,
Sofia
hi sofia,
I use the following trick, to apply an amount charged on a volume (in my case a water invoice)
First Line just calc VAT
Second line Actual " Description Volume of Gazoline" Under rate enter the Gallons
Third line On previous row “Correction for Volume of Gazoline” rate -100
So the 2nd and 3d line at up to zero, but you can now make calculations based on volume.
Say you have to pay 4 quatzales than per galon than you just make a line with
on previous row , refer to line 2, rate 400
I have a template with various charges based on volume (in yr case 3, 4 ,5 quatchas) and delete the ones that do not apply,.
It is not utterly elegant but will do the job.
@rmehta may be a useful improvement to allow calculation is the tax template based on Quantity instead of $$$ (only)
I think I might have found a solution involving 8 lines, I have been working late so I will post this tomorrow.
Hi Alain!
I have a workaround! 
it involves discounting the gas tax from the unit
price and then adding the tax back in the tax table.
As per the recommendation put forth by @becht_robert, I expanded on it by doing the following:
I set up three accounts, which I named numerically (for ease in searching):
Then the tax template is set up as follows:
ROW #1
Purpose: This row is where you input the gallons of fuel, to enable a calculation to be made with the number entered.
Consider Tax or Charge for: Valuation
Add or Deduct: Add
Type: Actual
Reference Row#:
Account Head: 0901
Cost Center: -
Description: U.S. Gallons
Rate: (U.S. Gallons entered above the Purchase Receipt or Purchase Invoice as a Quantity)
ROW #2
This row corrects the entry above to net the entry to 0 (zero). This row will not be used further.
Consider Tax or Charge for: Valuation
Add or Deduct: Add
Type: On Previous Row Amount
Reference Row#: 1
Account Head: 0901
Cost Center: -
Description: Correcting Row #1
Rate: -100
ROW #3
This row estimates the Tax to be calculated, using row #1 as reference. This row also considers deducting the tax from the total, so that this new total with the tax amount calculated here already deducted, can serve to estimate the VAT, as per this particular example. For premium Gasoline, a tax of Q4.70 per gallon has to be levied
Consider Tax or Charge for: Total
Add or Deduct: Deduct
Type: On Previous Row Amount
Reference Row#: 1
Account Head: 0625
Cost Center: -
Description: Petroleum Distribution Tax Value
Rate: 470
ROW #4
This row simply calculates the net value without VAT of the total remainder from row#3, after removing the petroleum tax. Since VAT = 12% and is included, the rate here is 1/1.12 or 89.2857. This value is now available to refer to in the following rows.
Consider Tax or Charge for: Valuation
Add or Deduct: Add
Type: On Previous Row Total
Reference Row#: 3
Account Head: 0901
Cost Center: -
Description: Value without tax for Premium Gasoline
Rate: 89.2857
ROW #5
Correction for the calculation made on row #4 above
Consider Tax or Charge for: Valuation
Add or Deduct: Add
Type: On Previous Row Amount
Reference Row#: 4
Account Head: 0901
Cost Center: -
Description: Correcting Row #4
Rate: -100
ROW #6
This row deducts the value of the VAT from the Total
Consider Tax or Charge for: Total
Add or Deduct: Deduct
Type: On Previous Row Amount
Reference Row#: 4
Account Head: 01050
Cost Center: -
Description: VAT 12%
Rate: 12
ROW #7
This row adds back the VAT tax to the tally for the purchase invoice.
Consider Tax or Charge for: Total
Add or Deduct: Add
Type: On Previous Row Amount
Reference Row#: 6
Account Head: 0901
Cost Center: -
Description: Adding back VAT, not affecting tax accounts.
Rate: 100
ROW #8
This row adds back the Petroleum Tax to the tally for the purchase invoice
Consider Tax or Charge for: Total
Add or Deduct: Add
Type: On Previous Row Amount
Reference Row#: 3
Account Head: 0901
Cost Center: -
Description: Adding Back Petroleum Tax
Rate: 100
The result from all of this is that only ONE additional data entry must be done with a Fuel Invoice. The Gallons in Tax Row 1, and then it calculates everything on its own. It isn’t by any means “elegant”, but the benefit is that one can leave this template preassembled, and those who enter the transactions just have to select the correct tax template for a new transaction, or simply duplicate an existing transaction.
@sofia, check it out and let me know if this works and if something is missing to net everything to zero.
@tropicalrambler, there is another way to manage the gas tax in fewer
steps.
[image: Inline image 1]
In the taxes and charges table add a line for the VAT.
Total, add, On Net Total, included.
[image: Inline image 2]
Add a second line to the taxes and charges table for the gas tax.
Total, add, actual, not included.
[image: Inline image 4]
The Grand Total is USD 150 as it would be on the invoice for this
purchase.
[image: Inline image 5]
The same method can be used for any tourism or electricity taxes. The key
is to reduce the item price by the item tax so that the VAT is calculated
on the lesser amount, then you add the item tax back in.
Best regards,
Sofia
Thanks, this certainly is a bit easier!
The final solution to this problem is our excise tax calculation module within our electronic invoicing app. You can use it without the electronic invoicing turned on. It will automatically calculate any excise tax previously configured within an item, to be determined by the amount of the item being invoiced.
Electronic invoicing (and excise tax calculation)
Will be updating the comments in the software to english for clearer understanding.
Great that u made progress…Nevertheless, believe taxes on quantities should be part of the main tax and charges system rgds robert
I would appreciate it that before any comment, you please check the code of our app, and you will notice how the end calculation feeds directly to taxes and charges system of ERPNext, as it should be. I am leveraging existing fields and DocTypes to automate this calculation, and enabling a pair of additional fields in the Sales Invoice to help us in our regional requirements.
If you don’t like it, don’t use it. If you have anything to add to our application, feel free to check the code, fork it, modify it and submit a PR. This is why it is a separate application, and it solves that mess of a workaround I posted previously, which was sufficient for the time being for our case.