Tasks to be completed when documenting an operation (from creation to publication)

1. Enter the Title of the operation / page

2. Add the following Labels:

    • Scope of applicability: ww, country_accounting

    • Country or group of countries (if applicable): belux, china, france, italy, lam, nam,uk_ie, bulgaria, dach, netherlands, iberia, poland, latvia, australia, india, japan, south_korea, thailand, singapore, new_zealand, emea_transversal, apac_transversal

    • Unit and Domain according to the List of labels to be used in the Finance Service Line space

      • E.g. 1: WW Operation in Financial Accounting under domain "Central Finance Processes & Compliance":
        • Labels to be used: ww, financial_accounting, central_fin_proc_compliance

      • E.g. 2: France Operation in Financial Accounting:
        • Labels to be used: country_accounting, france, financial_accounting
          (for country operations, the Domain is always country_accounting)

3. Fill in all fields as described above

4. Name the title of each section using OPD methodology naming convention - Infinitive verb without the “to”, mainly action verb...something) -" I do something..."

5. Once the description of the operation is completed, ensure it is approved and published by launching the SBS-Finance approval workflow

Domain:

Responsibility area:

Table of contents 


Scope


ERP


Frequency

 

References



Forms



Attachments




<< PL - 6268 - Monthly import / export report >>



1. Objective and Scope

1.1. Objective of this Operation

Solvay Poland's import / export report is for Poland's national statistics.


1.2. Scope

WP1 entity - 6268 (62683) Solvay Poland Sp.z.o.o.


2. Definitions

See Finance Glossary:

  • ...


3. Tasks description

Monthly import / export report should be prepared right after the closing or when customers and vendors are closed. When the report is completed it should be sent to Kinga Kasprzycka <kinga.kasprzycka@solvay.com> and Tomasz BOROWSKI <tomasz.borowski@solvay.com>.

The deadline for this task is WD10. 

6268 import/export report's folder is here. 

The main file is here. 

3.1. I check data in SAP KE30

3.1.1. I go to Tcode KE30

Choose report ZZZ-SOLV00 - IFRS Periods/Year

Press

Fill fields as screenshot, choose only period needed for the report (one month), press .

Find 'Plant' in the Navigation, press it and go in the top on 'Navigation' and choose '← plant'

    ->  ->

Make sure there is no new plants
There should be existing ones:
8172    6268 Wloclawek
8247    6268 / Michelin Olsz
8276    6268 / Goodyear Fuld
8323    6268 / Goodyear Debi
8638    6268 / MBU Warszawa8172

If there will be new plant, you should update SAP variants in the Tcodes which will follow. 

3.2. I retrieve material data from SAP MB51

3.2.1. I go to Tcode MB51

Go to Tcode MB51 and choose variant "/6268Imp/Exp", check if everything looks good like in the screenshot, change the posting date fields - you need only current (closure) month. 

Press

Save the report as each month in Excel format     
    List->Export->Local file

Then choose Text with tabs. After that:

1. Select folder where you would like to save 
2. Give file name
3. Save as EXCEL Files (*.xls) format
4. Save file

3.2.2. I open the extracted Excel file

Open the extracted file and do the formatting - delete blank lines - grey colored column A, line first and fourth. 

Then copy columns information to the line below - Material, Material Description, Plant and Name1 to lines below. 

Be careful as the materials changes! 

After all information copied, delete blank lines.

Duplicate the sheet as it will be needed to double-check quantities.
Name one sheet as raw data.

In the sheet which is not raw data - filter column I (EUn) by TO (tonnes) and color them yellow as all materials should be in the same unit of measure, in our case everything should be KG.
Insert additional column - convert - where put formula - tones * 1000 will be kg, drag formula till last line with TO measurement 
Be careful with your computer's set up, most probably you will need additional formatting - column H: Find "." replace by ","

After conversion is done, remove the filter! Copy the new amounts as values in the yellow fields, be careful! 
When new amounts are copied over as values, delete the column J (conversion).

Save excel file-> Save as - check type, it should be excel!

At the end you have two copies of the report - one will be excel and other one will be text document. 
You have to work only with the excel format report.


3.3. I work with extracted file

3.3.1. I upload both files into GDrive folder

Create new folder with name of the month in the 6268 Gdrive folder HERE.


And start working with the spreadsheet where you have two sheets - one with raw data and the other one with converted quantities. 

3.3.2. I start to work the spreadsheet 

Open the spreadsheet and first check if raw sheet quantities matches updated sheet's quantities, to do that, create new sheet and create a pivot table to find the amounts. 

As an example use previous month's spreadsheet, you need to check if quantity by movement type matches, movement types: 601 and 101.


If all quantities matches, follow next steps.

Create a pivot table in the separate sheet, take details from sheet where the quantity is already updated from tonnes to kilograms, see below details to build the pivot.
The main purpose is to eliminate lines (material documents) which offsets each other by movement type.
Be creative and find doc types which offset each other. :) 
The main indicators by movement types:

Mat. Doc.
Qty in unit of entry

Pivot table details:
Rows - Mat. doc.
Values - Qty in unit of entry
Filters- MvT

Filter by movement type, first one for example 311, see the offsetting and continue to build pivots for each movement type.

Below is a print screen with doc types that are offsetting. Please check also other months as example.


When you find material documents that offsets, color them red in the movement type sheet and in the summary sheet (e.g. 6268.04.2022) color entire line.
Insert material doc. one by one in the filter in summary sheet (e.g. 6268.04.2022).
Note: Format the numbers by replacing . by , otherwise there will be errors in the pivot when offsetting the mov. types

Create Sales Pivot when all offsetting material documents have been found.     

  • In the main working sheet filter out all documents with movement type 601 -> make sure that there are no red lines (no lines from offsetting documents
  • Color all 601 lines with green color and copy all lines in new sheet and call it SALES 601
  • Create pivot (with details from the screenshot below) from the data from the new sheet
  • Take F column -> copy the amounts and paste as values in column G
  • In column H -> make a formula
  • Use as example previous month's file    


Create Purchase Pivot:    

  • In the main working sheet filter out all documents without any color - filter by color white ( as the red ones are those who offset each other and green ones are for Sales (601))
  • Copy all lines without any color to new sheet and call it PURCH
  • Create pivot (with details from the screenshot below) from the data from the new sheet
  • Take G column -> copy the amounts and paste as values in column H
  • In column I -> make a formula
  • Use as example previous month's file  


In Purch Pivot sheet create a check box (check previous month's file)


Copy Sales and Purchasing data from pivots from spreadsheet XX.-XX.XX.XXXX with notes and paste to file 6268_MB51_Imp-ex HERE, sheet Im-exp REPORT.  
 

Create many new lines, leave the first line = to previous month to know which order to copy from

  • Copy Mat doc alone, quantity is column I in the pivot sheet, mov. type, etc, vendor code only, as others will have formulas
  • Green cells (sales = 601 and purchases = 101 ) are copied from the spreadseet XX.-XX.XX.XXXX with notes
  • White cells are manually added from the SAP


Check TOTAL quantities in spreadsheet and in file 6268_MB51_Imp-ex
Use the Vlookup to get customer and vendor names and country.


3.4. I work with the main report file

3.4.1. I get all details for Purchasing section

  • Go to Tcode ME23N
  • Take PO number from the file '6268_MB51_Imp-ex YTD 2022' and put it in the SAP tcode
  • Select correct Item line - good received date, material document#
  • Check Total Quantity
  • Check invoices one by one putting Invoice# under column R. Use batch# and quantity as your reference. 
  • Calculate Unit price by your self from the invoice. Amounts without VAT!
  • Put PLN amount value from print screen (yellow) under column E.


3.4.2. I get all details for Sales section

  • Go to Tcode FBL5N
  • Check customer one by one    
  • Only doc with RV type    
  • In t-code MB51 by material doc # you can find the invoice date    
  • Check invoice one by one:    
        Double click on line
        On top Environment - > Document Environment -> Relationship Browser -> required material document number (needs to match with the main  report's mat.doc.) 
  • Check rate for invoice    
  • Fill in prices/amounts/document number into the Im-exp REPORT 




Note. Some customer codes might differ, for example:

 


3.4.3. I finish the report

COGS part

Create Vlookup formula in the J column (COGS) to find the COGS amount by Batch from the Purchase data and multiply with q-ty, you can check previous month's formulas.
If the batch is not in current month, change formula with table area from previous month's Purchase data, repeat it with previous month's if still don't have amounts. 

COGS alert: Don't forget to insert $$ in the vlookup formula

Add current month's totals to the table below :    
    Total Purch
    Total Sales
    Total COGS


Once you are finished, send report to:
kinga.kasprzycka@solvay.com 
+CC tomasz.borowski@solvay.com 

End of document.


 

  • No labels