Excise duties report for sales orders

About the report

The excise duties report is universal and can be used for several purposes. It can be used as a basis for calculating excise duties, for documenting the amount of packaging consumed in connection with sales or for reporting deposits on bottles and cans.

In the application, the report is referred to as Quantity declaration report because it is based on the quantity of goods that are taxable. The report is also referred to as excise duties report.

Breweries can define the amount of alcohol in a beer, and manufacturers of ice cream can define the amount of ice mix, chocolate, etc. in ice cream.

Manufacturers selling goods to Germany may report the quantity of packaging in accordance with [das Verpackungsgesetz (Packaging Act)] (https://www.verpackungsregister.org/en/).

Companies that sell beverages in Denmark, Sweden, Norway and Finland can report the deposits on bottles and cans (pant).

 

Step 1: Enable the extension

The report is an extension, which must be enabled in order for it to appear in your account. If you are already using the report as a basis for reporting purchase, you do not need to enable it once again. Then go to Step 2: Define the type of excise duty.

  1. Click your company name in the upper right corner and select Company Settings from the drop-down menu.
  2. Click Extensions in the menu on the left side.
  3. Switch on the Quantity Declarations function.

 

Step 2: Define the type of excise duty

  1. Click your company name in the upper right corner and select Company Settings from the drop-down menu.
  2. Click Quantity Declarations in the center menu.
  3. Click Add new in the upper right corner.
  4. Give the excise duty a Name e.g. Ethanol content, 1.2 - 6 pct. vol
  5. It is optional to enter a Reference Code e.g. as a reference to a specific regulation or related document number.
  6. Select a Calculation base, ie. whether you have to pay excise duty on goods that have been sold (invoiced) or bought (purchased).
  7. Select a Unit from the list. For some quantities it is given which unit it is relevant to measure in e.g. pcs for packaging or liters for ethanol content.
  8. Adding a Tag is optional. You can add a tag by clicking on the + symbol on the right side of the field.
  9. In order for the excise duty to be added to orders and appear in the report, it must be active. If an excise duty is no longer valid, it can be deactivated. Then you still have the option to generate the report retrospectively in tracezilla, but the excise duty is not added to new orders.
  10. It is relevant to specify a country in the field Partner country is if only goods sold to customers in a particular country are subject to taxation.
  11. Fill in the date fields Delivered from and Delivered to if it is only a limited period that excise duty is to be paid on the goods. It is also relevant if the rate of excise duty changes. Then the dates will correspond to the validity period of this rate.
  12. Select a Cost category e.g. Taxes. The category will appear on purchase orders and in reports.
  13. Select a Component, e.g. Palm Oil. Components are relevant for reporting, and is used in the built-in Compass Group Denmark integration.
  14. Enter the rate of the duty in the field Unit price.
  15. Tap Save to create the excise duty.

Once a duty has been created, it can be edited or deleted. It cannot be deleted once it has been used and indicated on Stock Keeping Units. That way, you do not lose your data.

 

Step 3: Indicate the taxable quantity on the Stock Keeping Unit.

  1. Click Warehouse in the top menu and select Stock Keeping Units in the drop-down menu.
  2. You can either choose to edit one SKU at a time or you can edit several by downloading an import template.

 

Edit one SKU at a time

  1. You can search for a SKU in the overview using the filters on the left. For example, search for a SKU Code or SKU name in the filter Keyword.
  2. Click on the blue button with three dots on the right side of the SKU. Select Configure.
  3. Go to the Quantity Declarations tab.
  4. Indicate the quantity that is taxable according to the unit of measurement of the SKU.

Example: There is 4% alcohol in a fruit wine. The SKU is sold in a package of 8 pcs. The excise duty is calculated on the basis of the amount of pure alcohol in a bottle of fruit wine. I use the field in tracezilla to calculate. 4% corresponds to 0.04 and there is 0.75 liters in a bottle. 0.04 x 0.75 = 0.03 liters of pure ethanol per bottle.

Click Save when the quantity has been entered.

 

Edit multiple SKUs at once

You can download a spreadsheet to, for example, Excel, in which you can edit several SKUs at once. Once the values have been added to the relevant SKUs, the spreadsheet can be uploaded into the tracezilla again.

  1. Use the filters on the left side to limit the number of results in the overview. You can search for SKU name or SKU code in the filter Keywords. If you have added tags to the SKUs, you can search for it as well.
  2. When the relevant SKUs appears in the overview, click Import in the upper right corner. Click Get import template.
  3. Open the spreadsheet on your computer.
  4. Scroll to the last columns in the spreadsheet. The number of columns in the import template varies and depends on the settings of the company account. Find the column that in row 1 has the same name as the one you named the tax with in Step 2: Define the type of tax.
  5. In the column, enter the quantity according to the unit of measurement of the SKU, which can be seen in column L: Unit of measure.

Example: There is 4% alcohol in a fruit wine. The SKU is sold in a package of 8 pcs. The excise duty is calculated on the basis of the amount of pure alcohol in a bottle of fruit wine. I use the field in tracezilla to calculate. 4% corresponds to 0.04 and there is 0.75 liters in a bottle. 0.04 x 0.75 = 0.03 liters of pure ethanol per bottle. OB! Please note that the import template can not be uploaded into tracezilla again, if the field contains the calculation. Enter the result of the calculation eg. 0.03

  1. When all the quantities have been entered in the spreadsheet, the file is saved on the computer.
  2. Open tracezilla again. Click Upload in the same dialog window from which you downloaded the template. Select the file from the computer.
  3. Once the changes are registered and saved in the tracezilla, it will say: The file was successfully imported!

 

View the report

Once you have set up steps 1, 2 and 3, you can now start using the report.

1. Go to the Sale tab in the top menu ans select Quantity Declaration in the drop-down menu.

2. In the filters on the left side of the screen you can turn on the quantity declarations that you have defined under your company settings.

3. If you cannot see the filters it might be because you are working on a small screen. In this case, you can find the filters by clicking on the magnifying glass next to the Printer friendly button.

4. In the filters, in the field Invoice Date, you can choose to delimit a time period for which you want to see the desired quantities. You can also choose to view several SKUs at the same time by typing them in the field Stock Keeping Unit. You can also filter on a specific Costumer. This is relevant if you need to declare quantities of packaging sold to German costumers.

5. By using the buttons in the upper right corner of the screen, you can view the report in both a Printer Friendly format as well as in an Excel file (XLSX).

Below you can read an explanation of the fields in the report.

By SKU

Column Explanation
SKU Code This is the SKU Code which refers to the SKU that is the subject to tax.
Global Name This is the name of the SKU which is subject to tax.
Unit of Measure This is the Unit of Measure (UoM) of the SKU. You can read more about SKUs and UoM here.
Quantity, UoM This indicates how many Units of Measure of the SKU have been invoiced.
Net weight (kg) This is the total weight of the invoiced lots. The number is based on the stated net weight of the SKU.
The excise duty The last column shows the taxable quantity.

 

By invoice / SKU

This tab is relevant for reporting sales of beverages with deposits. It is this column which contains the information that must be provided in the reporting to Dansk Retursystem.

Please note that the report does not fit one to one with the reporting template from Dansk Retursystem, as the report also has other purposes than reporting to Dansk Retursystem. You will also have to fill in other information in the reporting template at Dansk Retursystem.

Column Explanation
Invoice number This is the number of the invoice where the beverages with deposits have been sold.
Invoice Date This is the date the invoice was created.
Customer No. This is the number that the customer (partner) has in tracezilla. This is a consecutive number that is assigned when you create a partner in tracezilla.
Customer This is the name of the customer to whom the beverages with deposits were sold.
Address This is the addresss of the customer (street).
Address 2 This is additional address information on the costumer.
Zip Code This is the address of the customer (zip code).
City This is the address of the customer (city).
Country This is the country of the customer.
Phone This is the phone number of the customer.
VAT-ID This is the VAT number of the customer.
SKU Code This is the item number of the SKU sold
EAN, UoM This is the EAN number of the Unit of Measure of the SKU. For bottles or cans with deposit this unit will be pcs.

In the reporting template from Dansk Retursystem this number is referred to as the GTIN number.
The excise duty The last column shows the taxable quantity.

 

The other tabs in the report

Column Explanation
By SKU tag, By Country, By Partner, By Partner/Tag, By Owner, By Order, By invoice, By Month These tabs allow you to view your defined quantities either By SKU tag, By Country, By Partner, By Partner/Tag, By Owner, By Order, By invoice or By Month.
The excise duty The last column shows the taxable quantity.

Outline