Domain: Country Accounting



Responsibility area: N/A


Table of contents 

By default the table of contents displays Heading 1 & Heading 2 (other levels can be added)



Scope

3. Remove the icon when not applicable


ERP

4. Remove the icon(s) when not applicable


Frequency

5. Remove the icon(s) when not applicable


References

6. Add the link to SAP transaction(s) (when it exists)


Forms

7. Insert the links accordingly and change the link text with the OP name


Attachments
All analysis media files can be found on GDrive:


Extraction: This file support can be used for all companies to compile the data that will be extracted before including in the data file of the file1° Parte da

Res13 - 1ª Parte : It consists of all the data required to calculate the imported percentage for each material. It has a file for each company, always use the file of the previous month because they have cumulative data.

Sale: It finds the cumulative sales moves to calculate the last average sale price for each product. It has a file for each company, always use the file of the previous month because they have cumulative data.

Façon: It is used only for the 7523 plant, to compile the data of the materials that are tolling.

Previous OP << >> Next OP



1. Objective and Scope

1.1. Objective of this Operation

The purpose of this document is to describe the way to calculate the percentage of imported raw materials in the production of each product. 

The monthly calculation is performed to have the percentage of the raw material imported for each product. These data must be updated until the last day of current month in FCI (Ficha de Conteúdo da Importação) for each material inside the "Secretaria da Fazenda" website and also in the material master data in SAP. 

The information to be retrieved from the system should be always from the last two months, for example, the process is being executed on July, considering that the data from SAP must be extracted for June and will be updated in each material master date in August.


1.2. Scope

This procedure applies to company :

  • 7523 Rhodia Poliamida 

Plants: 7525, 7515, 8227, 8287



2. Definitions

See Finance Glossary:


3. Tasks description


Reminder: log in SAP in Portuguese

3.1. I run SAP Transaction ZWPP_MCKOST

Go to transaction ZWPP_MCKOST .

Use the variant RES 13 NEW and run for all plants, the report is done by company.

Obs.: Plants can be added or excluded of the scope according to Solvay’s business dynamics. 

Execute .

Always for the previous month.

After you run extract the report to Excel file, as model:

Insert the filter.

In the column H, enter a filter selecting only the items that are equal to 0.

  • Delete all rows that have a value of 0 (zero), then remove the filter.

For each of the columns A, B, C and D must do the following:

  • Select the column;
  • Scroll to Date and select the option text to Columns;
  • Click in Finish.

Paste in tab of support file Extração from the cell A2, note that in the column C contain formulas. (fromula ex.: =IF(VALUE(E3)=VALUE(B3);"1";"3").

Repeat the same procedure for the other plants using the lower lines variants, remembering that should put the data one below the other.

After you extract the report for all plants, go to the tab Data base in support file Extração, and refresh the Pivot Table with the information of MCKOST tab:


The information in the tab Data base should be included in the support file XXXX Res13 - 1ª Parte (XXXX is the company code)  .

Paste the information from the C2 cell in tab ZWPP40A, note that the Pivot Table  do not update the information in the correct order, the column Quantidade real must be before the UM. 

Complete the column A field with the corresponding period.

Check the column B if are pulling formula.

3.2. I run SAP Transaction SQ00 

Go to transaction SQ00.

To get the material code, go to transaction SQ00 and change the Query areas to Standard.

 

Click in  and change to ZW_BR_IM.

Select query ZWBR_100 and press "Process" .

Use variant Res 13 XXXX (XXXX is the company code).

The report is done by company.

Execute . (No filter/selection by material)

Select the option display in worksheet , in part higher of transaction.

Go to tab RawData.


Delete column H, I e J.

For each of the columns A, B and D must do the following:

  • Select the column;
  • Scroll to Date and select the option text to Columns;
  • Clique em Finish.

Copying from the line 2 to the last cell with information.

Paste in tab  of support file XXXX Res13 - 1ª Parte from the cell B2.

The formulas from the column I will be automatically completed after the insertion of the data, refresh if necessary.

3.3. I run SAP transaction ZWOC30

Go to transaction ZWOC30.

Variant  – SALES XXXX (XXXX is the company code).

The report is done by company.

Execute .

Select the option display in worksheet , in part higher of transaction.


Go to tab RawData.

For each of the columns F, G e H must do the following:

  • Select the column;
  • Scroll to Date and select the option text to Columns;
  • Click em Finish.


Copying from the line 2 to the last cell with information.

Paste in file "Vendas" in the Computer Network after the last line with values. This file should be cumulative of the year.

The columns V to AC in gray will be filled automatically, check if are pulling formula.

Verify if there is difference in column AB "(Total Item NF-IPI-ICMS) - (Liquid + pis + cofins)" can not have values different from zero.

If there are differences it should be analyzed line by line and correct the calculation basis. In some cases the report considers tax values that are exempt, so the value should be disregarded.

Common errors that generate difference in column AB of the taxes which are exempt:

CFOP 5917/6917 has no PIS / CONFINS.

CFOP 6109 has no ICMS / PIS / COFINS.

CFOP 5111/6111 has no ICMS / IPI.

CFOP 5112/6112 has no ICMS / IPI.

CFOP 1918 has no IPI. 

When you don’t have errors, save file.

Return to the support file XXXX Res13 - 1ª Parte and go to the  tab , update the Pivot Table with the data of the file Vendas Resolução 13.


Obs.: The two files should be open to perform the refresh and observe the filters.

 

3.4. I collect data in tab COMEX

This information is provided by Adriano Pereira (He copies the General in order to creat a Ticket on Freshdesk) , with all imported products of the month, the data should be collected in tab in the support file XXXX Res13 - 1ª Parte.

The file contains information from all companies, each code corresponding to a company.

COD_EMPRESA:

001/015 company code 7523

030 company code ZBR1 (Out of our responsability. No need to be done)

008 company code 6343 (Out of our responsability. No need to be done)

Go to the last cell with information and paste the data provided by Adriano, noting that the data should be cumulative of the year.

Check the columns BU:CC if are pulling the formula.

Go in tab, after select Options and select the option Refresh.

3.5. I analyse the data

After all data are in file XXXX Res13 - 1ª Parte, order the column D, as picture. 

Tab ZWPB40A

 


1) Check the column K has #N/A. 


Then you should copy the codes of components that are in column F and follow again the process above in point 3.2., but use the variant RES13COMP XXXX. 




Go back, and check if the column K is with values. 

Remove the filter.


2) Go to the column T and filter the items:

    •  2 Foreign - acquired nationally.

At this moment you should analyze the K column, if any line has information # N / A, you should change there, leaving equal to column J; this mode correcting errors.

Remove the filter. 

3) Filter the column I only the unit of measure LB and the column L only with V.

In cases where the cost is Variable (V) have to multiply column K by 0,45359. 

4) Check the information in footer of worksheet.

You should check the indicated fields by the arrow , look that at column Component of the two fields indicated have the same material.

In this case must  sum the two fields and leave the value in the product.

Obs.: The product will always be the index 1

5) Filter on column P only for percentages higher than 70%.

Once the filter is done, analyze column W and check if there is any amount higher than 1. 

If there is, take the component number on column E and go to Historic Import tab and search for the component on column D. On column I, divide the number by 1.000 in case the Unidade de Medida is on TON .

3.6. I include Tolling material data 

3.6 I include Tolling material data

Specific for the company code 7523.

When you don’t have errors in the tab ZWPP40A, you must include the materials that are Tolling.

Go to transaction MB51 and use the TOLLERS variant:

Extract the data to Excel:

Paste the report in tab Dados acumulados in the file FAÇON, this data should be cumulative for the year of analysis.

Update the formula from column U to X for all rows:

After refresh the Pivot Table on the tab Carga with , 


Consider the worksheet on the right side because it updates the signals, disregard the data that is zero in the column "Soma de Montante em MI ""

Paste the report data into the file XXXX Res13 - 1ª Parte on the ab ZWPP40A below the last line containing information. Change the color o the Fasson pasted data.


Correct if a new circular reference appears, according to Item 3.5. number 4.

Note: It is important that all analysis documents be saved in Gdrive!

When you don’t have errors send the file XXXX Res13 - 1ª Parte to Accounting Platform, every 20th of the month.


Ensure that there is no "N/A" formula error in any column on tab ZWPP40A.

End of document.