Understanding Tax: How to setup Australian Wine Equalisation Tax using Tax Included with Item Price


David Meego - Click for blog homepageThis 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

Comments (2)

  1. mgomezb says:

    Wow! Sounds like I need to bring you some wine from the States. It will be less harmful to our brains.

    MG.-

    Mariano Gomez, MVP

  2. David Musgrave says:

    Posting by Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com.au/…/understanding-tax-how-to-setup.html

Skip to main content