Table of contents
1. Objective and Scope
1.1. Objective of this Operation
The purpose of this document is to provide instructions for the Sales by Product (Prodcom) for company code 6068.
1.2. Scope
This document provides information on how to prepare the Sales by Product (Prodcom) to company code 6068.
2. Definitions
See Finance Glossary:
- ...
3. Procedure
3.1. Go to transaction KE30 and Report ZZZ-SOLV00
Deselect all and choose Qty invoice and Market Product Sales
...
Choose only Year
Export the data to Excel
3.2. Save as Excel file
Adjust the file:
- Duplicate the tab to have raw data and adjusted data tabs
- Filter in columns Qty Invoice and Market Product Sales 0.00 and delete those lines (where on both columuns columns are 0.00)
Copy the data in the Spreadsheet file tab KE30, copy only:
...
Column C (Sales Qty KG)
Column D (Sales GBP)
3.3. Go to transaction MB51
Variant: 7771 PROD COMM
6068 PROD COMM
...
Double check measures by base unit of measure
Check total amounts
...
Copy all the material numbers
Save file to Excel
Add Net weight to the MB51 report - VLOOKUP function - In case there is other conversion rate than KG, calculate it manually (for example LB to KG)
Copy paste only TOTAL amounts from sheet "MB51 by material" to Spreadsheet Prodcom tab MB51 (+ conversion (col E))
3.4. TAB KE30
Work with the Spreadsheet tab KE30
Copy below the products that are non production items:
- All products that starts with 19*****
- Double check Production description that all looks like non production items. In case of doubt, check the Product
Make a screenshot from KE30
Check that the totals matches and there is no difference
In case Col E there is #N/A:
- From MB51 - Product type 101, 102 - materials have been purchased
- If there is another type then this product hasn't been purchased this year
- That means that the specific product 'production KG' we used from the previous year's inventory to make the product this year
- Therefore in column E there might be #N/A
Col E write manually 0.00
Color the cells so it is visible that these cells do not contain formula
In Column G comments write: not purchased during 2021
https://drive.google.com/drive/folders/1VND_SCwAh8XVZO_YwgZ8WoXbNLvr8N6r
* For Co. 6068 there is a list of specific products that need to be reported separately - TRADED GOODS:
- Filter in Column H Traded goods all the values (exclude #N/A)
- Copy paste these products under Non-production items
- AND delete these items from the Production list (so in the Col H there are no values remaining)
* For Co. 6068 in column B Product Description filter "Fentamine"
These needs to be copied under Traded goods
AND delete these items from the Production list.
Double check that the formulas below are okay and there is no difference (or the diff is small)
3.5. TARIC CODES
In the tab KE30 check column F Comm Code and filter out all the #N/A
In order to clarify the Taric code, you must write an email to htsclassification@solvay.com
Attach the file with Product + Product description
When you receive data from the HTS, update the tab Taric codes
Add the data below, do not delete the previous lines!
Go to tab KE30 to the Non Production Items and find the Comm Code that has #N/A. Update those manually (write them in the tab Taric codes, the Comm Code use something similar as previously)
3.6. Comms Pivot
Go and update the Pivot table
Check the pivot table -> there are codes that have the same beginning (should be reported under the same Comm Code category), go to tab Taric codes and update them so they are grouped accordingly. This is more for Co. 7771
Example, filter beginning 70194 and update to 70194 + 70195 (here 7019400019 -> update to 70194 + 70195) and so on.
In this link you can search for specific material Commodity Code
https://www.trade-tariff.service.gov.uk/find_commodity
Go to tab ONS Return
Check Section A - compare to the survey's PDF if all the commodity codes are added
Delete the ones under Section A that are not stated in the PDF survey
*Note that comm code 3921909000 is reported under 3921901 !!!
Check in tab Comms Pivot Column F Identified in tab ONS Return
If there is #N/A that means that the Commodity Code hasn't been added to tab ONS Return. Add it manually in the tab ONS Return under applicable category.
To see the category check Column H where section is identified
B.1 section >25k GBP
B.2 section <25k GBP
*Note that the formula does not work for Comm Code that looks like text (example: 70194+70195). It will show in Column H as #VALUE!
Write the section manually and color the cells to indicate that there is no formula
Go to tab ONS Return, scroll down to C - other income:
1) For 6068 for Merchanted Goods (Traded Goods) add formula from KE30For 6068
For 7771 there are no traded goods
Go to tab ONS Return, scroll down to C - other income
2) Under Non-Production income (Service Sales) write the amount from KE30 Service Sales
3) Royalties - For 7771 report royalties under non-production income (service sales)
For 6068 do not report royalties as they are profit sharing not external royalties
When you have added all the Commodity codes, go to tab ONS Return and scroll down
Update the formula for KE30 total
