Understanding Tax: How to setup Australian Wine Equalisation Tax using Tax Included with Item Price
This post is about setting up Tax Details and Tax Schedules to handle the Wine Equalisation Tax for Australian GST when Tax is Included with the Item Price. While this is a specific request, the post discusses some generic concepts and limitations with how the tax engine in Microsoft Dynamics GP works.
When the Goods and Services Tax (GST) was introduced in July 2000, part of the process of adding a GST to the retail price of almost all goods and services was the removal of the old wholesale sales tax system. The GST was applied at every level of the sales cycle at a rate of 10% (and is still 10%). If you are not the end consumer, you can claim the GST you paid back as a credit and apply the GST when you sell the item on. So, the net result is that GST is applied at the final consumer sale.
The old wholesale sales tax was applied on the last transaction prior to the final consumer sale... the final wholesale transaction. The rates varied depending on the type of goods, but could range from 20% to 40% or more. Services did not have a tax previously, but would now incur the GST. Definition: A Service is anything that is not a Good. Goods are physical and tangible, Services are "Not Goods".
Ok, so why the quick lesson in Australian taxation history?
This was so I could explain what the Wine Equalisation Tax is. Before the GST the government received a certain amount of tax for each bottle of wine sold. When the GST was introduced, that amount decreased significantly. So to maintain the revenue stream, an additional tax was added to wine so that the tax recieved after the GST was equal to the tax received before the GST. Hence, the 29% Wine Equalisation Tax or WET.
Now, to simplify the creation of the Tax Details and Tax Schedules need for Australian GST, there is a Tax Information Setup wizard in the Busines Activity Statement (BAS) Reporting dictionary that is installed when you select Australia as your country during the Microsoft Dynamics GP installation. This wizard allows the user to select the types of tax needed and the naming conventions desired and will then generate the Tax Details and Tax Schedules and apply them to setup and master records.
The wizard can set up Wine Equalisation Tax, but it only does this for the situation when Tax is a Percentage of Sale/Purchase and is added to the Item Price.
The wizard creates the Tax Details which work in the following way:
Sale of Wine: $100.00
GST on Wine: $10.00 (10% of $100.00)
WET on Wine: $29.00 (29% of $100.00)
GST on WET: $2.90 (10% of $29.00)
Total: $141.90 ($100.00 Wine + $29.00 WET and $12.90 GST)
This uses 3 Tax Details, one for GST, one for WET and one for GST on WET, also known as WEG.
So, now I have a customer asking to have all the taxes Included with the Item Price. They want to enter $141.90 as the Sale amount and have the system work backwards to calculate the tax amounts. This is a little harder than you might think at first.
The problem is that the Dynamics GP Tax Engine works in a serial fashion. Let's try and implement the same configuration as discussed above with a 10% GST, a 29% WET and a 10% WEG on the WET.
If GST is calculated and removed from the Item Price first and then WET is removed, the calculation is incorrect.
Sale of Wine (inc tax): $141.90
GST on Sale: $12.90 (10% out of $141.90)*
WET on Balance of Sale: $29.00 (29% out of $129.00)
WEG on WET: $2.64 (10% out of $29.00)
Total: $141.90 ($97.36 Wine + $29.00 WET + $15.54 GST)
* This is calculated by mulitplying the amount (inc tax) by (Tax rate) / (100% + Tax rate).
For example: for a 10% tax = 10% / (100% + 10%) = 10 / 110 = 1 / 11, or divide by 11.
If WET is calculated and removed from the Item Price first and then GST is removed, the calculation is also incorrect.
Sale of Wine (inc tax): $141.90
WET on Sale: $31.90 (29% out of $141.90)
WEG on WET: $2.90 (10% out of $31.90)
GST on Balance of Sale: $9.73 (10% out of $107.10)
Total: $141.90 ($97.37 Wine + 31.90 WET + $12.63 GST)
The issue is that the order the Tax Details are calculated is the order specified by the alphabetic sorting of the Tax Detail IDs and as each one is calculated, the Item Price is reduced by the amount of the tax calculated.
The solution is fairly simple. Two changes in the settings will make this work. If we get rid of the WEG (don't create the Tax Details for it), and select the option to make the WET a Taxable Tax, it will simplify the calculations and make sure that the GST is calculated first:
Sale of Wine (inc tax): $141.90
GST on Sale (inc WEG): $12.90 (10% out of $141.90)
WET on Balance of Sale: $29.00 (29% out of $129.00)
Total: $141.90 ($100.00 Wine + $29.00 WET and $12.90 GST)
Finally we have the correct calculation for a Tax Included in Price configuration.
Below are the screenshots for the Tax Details needed (based on default naming conventions for the Tax Information Setup wizard):
Tax Detail for Sales with WET Included in Item Price
Tax Detail for Purchases with WET Included in Item Price
Tax Schedule for WET Included in Item Price
Below is an example Receivables Batch Edit List showing an example of a WET transaction with the Tax added to the price and the Tax included in the price.
Receivables Batch Edit List
System: 15/05/2012 17:22:53 Fabrikam, Ltd. Page: 1
User Date: 12/04/2017 TRANSACTION EDIT LIST User ID: sa
Batch ID: TEST Audit Trail Code: Batch Frequency: Single Use
Posting Date: 12/04/2017 Batch Comment: Test Batch
Trn Total Actual: 2 Trn Total Control: 0
Batch Total Actual: $283.80 Batch Total Control: $0.00
Approved: NO Approved By: Approval Date: 0/00/0000
Type Document Number Doc Date Post Date Debtor ID Name Salesperson
------------------------------------------------------------------------------------------------------------------------------------
Sales Amount Trade Discount Freight Amount Misc Amount Tax Amount Document Total Discount Avail
------------------------------------------------------------------------------------------------------------------------------------
SLS SALES00000001005 12/04/2017 12/04/2017 AARONFIT0001 Aaron Fitz Electrical
$100.00 $0.00 $0.00 $0.00 $41.90 $141.90 $0.00
General Ledger Distributions
Account Number Account Description Account Type Debit Amount Credit Amount
000-4110-01 US Sales - Retail/Parts SALES 0.00 100.00
000-2300-00 IL State Sales Tax Payable TAXES 0.00 41.90
000-1200-00 Accounts Receivable RECV 141.90 0.00
----------------- -----------------
141.90 141.90
Tax Detail Distributions
Tax Detail ID Tax Detail Description Tax Amount
AUSGST-GST GST Taxable Sale 10.00
AUSGST-WEG GST on WET Sale 2.90
AUSGST-WET GST Wine Equal. Tax Sale 29.00
-----------------
41.90
SLS SALES00000001006 12/04/2017 12/04/2017 AARONFIT0001 Aaron Fitz Electrical
$141.90 $0.00 $0.00 $0.00 $0.00 $141.90 $0.00
General Ledger Distributions
Account Number Account Description Account Type Debit Amount Credit Amount
000-4110-01 US Sales - Retail/Parts SALES 0.00 100.00
000-2300-00 IL State Sales Tax Payable TAXES 0.00 41.90
000-1200-00 Accounts Receivable RECV 141.90 0.00
----------------- -----------------
141.90 141.90
Tax Detail Distributions
Tax Detail ID Tax Detail Description Tax Amount
AUSGST-GST GST Taxable Sale 0.00
AUSGSTI-GST GST Inc Taxable Sale 12.90
AUSGSTI-WET GST Inc Wine Equal. Tax Sale 29.00
-----------------
41.90
--------------------- ---------------- ----------------- ----------------- ------------------ -------------------- -----------------
$241.90 $0.00 $0.00 $0.00 $41.90 $283.80 $0.00
===================== ================ ================= ================= ================== ==================== =================
Hope you found this discussion interesting.
David