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: Treasury Reporting

Responsibility area: Supervise the closing activities D+1

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



Frequency


 

 

 

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 Form name



Attachments

8. Add the link to attachments or external links




Previous operation << >> Next operation



1. Objective and Scope

1.1. Objective of this Operation

WHY - Describe the objective of the operation

The objective of this document is to prepare reporting files based on statements provided by newedge (the clearing house) that will allow to reconcile the cash movements of the period.

Also to identify potential discrepancies that must be investigated with Newedge. In any case, the statement is the basis (considered as correct) and in case of discrepancies, we use the amount on the statement in accounting rather than the reporting (that could contain issues).

Newedge statements (Pulse 3): monthly statements downloaded from the website of Newedge to:

  1. Prepare reporting files by activity and nature of cash movement to feed the reconciliation and the accounting entries performed in the “cadrage” file.

  2. Reconcile the cash movements generated by the activities of Solvay Energy Services (from SES01 to SES09). The latter being done in the “cadrage file”

  3. Prepare the cash reconciliation for Gas and Electricity sales & purchases (invoice from ECC European Commodity Clearing ) is performed through the file ECC 201X

  4. Prepare the pivot CO2 in order to feed the CO2 reconciliation and accounting entries (SES01 CO2 activity).


Nature of the activities SESXX with Newedge

SES 01 : Buy/Sell CO2 emission rights

SES 02 : Buy/Sell Brent (limited number of transactions)

SES 03 : Buy/Sell Electricity

SES 04 : Buy/Sell Gas

SES 05 : Buy/Sell spot Gas/Electricity 

SES 06 : Buy/Sell Coal


1.2. Scope

Detail the scope (legal entities, etc.)

This procedure applies to SES company code 3865

2. Definitions

Definitions should be added in the Finance Glossary - Add definition and link it to respective Letter in Finance Glossary 

See Finance Glossary:


3. Tasks description

WHAT and HOW - Main content of the operation

The content should be mainly organized in 2 levels of Headings to ensure a proper link with the Table of Contents.

Select the level of heading in the "Paragraph" option.

3.1. I access Pulse website

See chapter 3.2.2 of I am prepared to start the closing activities


3.2. I download newedge bank statements

Click on “Reporting”

Click on “Statements”

Click on « GMI Statement Monthly » to get the pdf files needed to prepare the reportings.


Enter the date (last working day of the closing month)

Example in June 2019: we do not use June 30th which was a Sunday. If you select 30th you’ll get an empty report.

Click on the icon in the column “action” to download the reports.

You’ll get the following screen:


Check again that the period is correct (closing period)

Click on the download icon « in French Télécharger »

Then record the pdf file in the shared disk Treasury-GAR on the folder Newedge of the closing month:


Same process for all SES activities (from SES01 to SES09).

Note that if no transaction occurred for a specific activity, no pdf file will be available.


3.3. I create new monthly reports

Select  « reports » in the “reporting” menu 

Enter the date (from the first working day until the last working day of the closing month)

Then select « Listed Derivatives »

The statements have to be extracted in both pdf and xlsx formats. xlsx files will be used to generate xls reports to be used for cadrage and pivot CO2 procedures.

To respect the file names used for pdf and xls.

Watchpoints: 

  • When downloading the report: do it one by one and wait until the file is created otherwise the download will be canceled.

  • In case we need to export in csv format, check the format (point, comma) otherwise the pivot tables might display wrong figures.


Here is the list of statements to be downloaded:

1)

Download Pdf and excel to be used to prepare the file


2)

Download Pdf and excel to be used to prepare the file 


3)

Only download pdf

To be used on xlsx format to prepare the file


4)

Download Pdf and excel to be used to prepare the file 


5)

To be used on xlsx format to prepare the file


6)

To be used on CSV format to prepare the file


How to build the reporting files?

  1. Copy the file of the previous month, save it in the current month´s folder \\NOHVFS01\Treasury-GAR\SES\YYY\MM\Newedge and rename it

  2. Copy the last two worksheets (TCD and data) and rename them with the new periods

  3. Clean the data worksheet (keep the titles) and copy/paste special/values the new data.

  4. Refresh the pivot table (Tableau Croisé Dynamique “TCD” in French).


Example for Purchase & Sale (same for all files)

Yellow tabs are databases and TCD tabs are worksheets with pivot tables. 

Again, as explained in the watch points section, in the csv reports, you need to check the format of the numbers (comma and point => I usually replace all points to commas) otherwise the pivot tables might display wrong figures.


“Commission rates” statement  used to build the file

Select the report « commission rates » from the Reports - Listed Derivatives web page.

Download the pdf and csv files.

Csv data: attention to the format number in columns W, Z, AC, AF, AI, AL

Update data in newly created “commission rates” tab

 

Update the pivot table in newly created TCD sheet:

This report is used to book the charges  (Commissions, fees, and other charges) to P&L in the “cadrage” file . Note that when cadrage file is ready, differences could occur between the fees really debited from the bank account (bank statement) and the report here aforementioned. In that case, a clarification request should be sent to Newedge and we consider the bank statement situation as the truth for the closure meaning we adapt “cadrage” to post the appropriate P&L amount that matches the bank statement. Then we adapt the reporting file with an updated version. When the answer is received from Newedge, to assess the correctness of the entry performed. 

Reconciliation after posting can be done by selecting the P&L accounts, filtering by activity (assignment) and by posting date:

Example: 14k€ on SES04 in accounts 98300330

Reconciled with cadrage file on activity SES04:

and fees transferred from 98300811 to 98300330 to isolate the fees.


“Prior day balance” used to build 

Select the report « Prior day balance » from the Reports - Listed Derivatives web page and extract the PDF and CSV files.  Csv data: attention to the format numbers

Update data in newly created “PRIOR_DAY_BAL”

 

Update the pivot table in newly created TCD sheet:

Update FX EUR/USD  rate in the cell A23 by checking it up in SAP PI1 transaction ZRATE.

After making cadrage postings you may conclude by taking print screens of account 55101000 and confirm if your end result are the same.


“Purchase and sale”

Select the report « Purchase and Sale » from the Reports - Listed Derivatives web page and extract the PDF and CSV files.  Csv data: attention to the format number in column M « PsAmount ».

Insert the new column and copy formulae from the previous month.

For Pdf:

For CSV:

Example of pivot after update:

Note that on activity SES05 "COMMODITY SPOT", the amount cannot be used to complete the cadrage file.

The report is used as an input for the “cadrage file” to update the worksheet.

Example for activity SES04: we can see again on line P&L the amount from the pivot 10,2€m and this amount is part of the 4.880k€ that we could see in the above SAP print screen.


“Positions by account and product” statement  used to build the file

Select the report « Positions by account and product » from the Reports - Listed Derivatives web page and extract the PDF and CSV files. Csv data: attention to the format number in column  P “Marketvalue”.

.


Example of pivot after update:

The report is used as an input for the “cadrage file” to update the worksheet.

Example for activity SES01: we can see on line OTE Option the amount from the pivot 815€k and this amount is part of the -19.926k€ that we can see in the SAP print screen.

With cadrage file we will book the amount between balance sheet accounts: cash in transit (52000100) vs restricted cash (55101000). The posting will only be concluded with CO2 pivot file where we will hit the margin call accounts (57000100 and 55100100), financial instruments (46112000 and 46912000) and P&L (98300814 and 98300811).


“Balances & equity by Currency” used to build the file

Select the report « Balances & equity by Currency » from the Reports - Listed Derivatives web page and extract the CSV file. Csv data: attention to the format number in columns O, S, T, X et Z.


Example of pivot after update:

Watchpoint is the date filter (last working day only)

The report is used as an input for the “cadrage file” to update the worksheet.

Example for activity SES01: 

The accounting entries permit to recognize the cash movement on margin requirements and margin excess/deficit. 


“Activity” used to build the file

Select the report « Activity » from the Reports - Listed Derivatives web page and extract the CSV file. Csv data: attention to the format number in columns Q « amount ».

Example of pivot after update:

The report is used as an input for the “cadrage file” to update the worksheets SESXX by computing the cash transfers.

Example for activity SES01: 


3.4. I create reports for Energy controller

There are two reports that energy controller has asked us to perform.


3.4.1 I create report "Analyse activité NEWEDGE"

1- Extract a report from Newedge in csv format

Now you need to change the format in columns "P" , "S", "AI"  & "AD" (replace point by coma). To do this our suggestion is:

a) Close all excel files

b) Open control panel ==> Region and Language ==> Additional Settings

Replace decimal symbol: comma => point

Replace Digit grouping symbol: point => space


Repeat same logic tab "Currency".

c) Click "Apply" and "Ok"

d) Open the report downloaded and for columns "P" , "S", "AI"  & "AD" click on the number format:

e) Save the report has an xls format now and close the excel file

f) Undo changes made in caption b)


Create 3 new columns

AW: 

AX: 

AY: 


Create and update pivot table

Create new tab with pivot  and change the data source to the new report you created. Refresh pivot table you will get a result similar to the following table:


We need to reconcile the amount with the cash transfers occurred during the month:

"Activity" & "Commissions" retrieved from statement SES05:

"Transfer funds" retrieved from report 

"P&L SES 05" retrieved from report 

If you still have differences you need to check the statement of SES05  and look for unusual finance data. Example from closing 06.2020:


3.4.2 I create report "Analyse SES 05"

1- Extract a report from Newedge in csv format

Now you need to change the format in columns "I" & "O" (replace point by coma). To do this our suggestion is:

a) Close all excel files

b) Open control panel ==> Region and Language ==> Additional Settings

Replace decimal symbol: comma => point

Replace Digit grouping symbol: point => space


Repeat same logic tab "Currency".

c) Click "Apply" and "Ok"

d) Open the report downloaded and for columns "P" , "S", "AI"  & "AD" click on the number format:

e) Save the report has an xls format now and close the excel file

f) Undo changes made in caption b)


Create 2 new columns

Column AA: IF(H2="b";I2;I2*-1) (Qty with sign)

Column AB: AA2*O2 (Qty x Trade price)


Create and update pivot table

Create new tab with pivot  and change the data source to the new report you created. Refresh pivot table you will get a result similar to the following table:

We need to reconcile the amount with the cash transfers occurred during the month:

"Activity" & "Commissions" retrieved from statement SES05:

"Transfer funds" retrieved from report 

"P&L SES 05" retrieved from report 

If you still have differences you need to check the statement of SES05  and look for unusual finance data. Example from closing 06.2020:

3.5. I send an email to Risk team

Once all reports are retrieved you may send an email informing that the reports are ready for consultation:



Send to: Risk team

CC: CAM, controllers (CO2 and Energy), TSU Reporting

Emails can be found in 3.1. I know the teams involved

End of document.

 




 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: Treasury Reporting

Responsibility area: Supervise the closing activities D+1

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



Frequency


 

 

 

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 Form name



Attachments

8. Add the link to attachments or external links




Previous operation << >> Next operation



1. Objective and Scope

1.1. Objective of this Operation

WHY - Describe the objective of the operation

The objective of this document is to prepare reporting files based on statements provided by newedge (the clearing house) that will allow to reconcile the cash movements of the period.

Also to identify potential discrepancies that must be investigated with Newedge. In any case, the statement is the basis (considered as correct) and in case of discrepancies, we use the amount on the statement in accounting rather than the reporting (that could contain issues).

Newedge statements (Pulse 3): monthly statements downloaded from the website of Newedge to:

  1. Prepare reporting files by activity and nature of cash movement to feed the reconciliation and the accounting entries performed in the “cadrage” file.

  2. Reconcile the cash movements generated by the activities of Solvay Energy Services (from SES01 to SES09). The latter being done in the “cadrage file”

  3. Prepare the cash reconciliation for Gas and Electricity sales & purchases (invoice from ECC European Commodity Clearing ) is performed through the file ECC 201X

  4. Prepare the pivot CO2 in order to feed the CO2 reconciliation and accounting entries (SES01 CO2 activity).

Note that in order to easily use the date, some files are downloaded in CSV and then converted into xls format.


Nature of the activities SESXX with Newedge

SES 01 : Buy/Sell CO2 emission rights

SES 02 : Buy/Sell Brent (limited number of transactions)

SES 03 : Buy/Sell Electricity

SES 04 : Buy/Sell Gas

SES 05 : Buy/Sell spot Gas/Electricity 

SES 06 : Buy/Sell Coal


1.2. Scope

Detail the scope (legal entities, etc.)

This procedure applies to 3865

2. Definitions

Definitions should be added in the Finance Glossary - Add definition and link it to respective Letter in Finance Glossary 

See Finance Glossary:


3. Tasks description

WHAT and HOW - Main content of the operation

The content should be mainly organized in 2 levels of Headings to ensure a proper link with the Table of Contents.

Select the level of heading in the "Paragraph" option.

3.1. I access Pulse website

See chapter 3.2.2 of I am prepared to start the closing activities


3.2. I download newedge bank statements

Click on “Reporting”

Click on “Statements”

Click on « GMI Statement Monthly » to get the pdf files needed to prepare the reportings.


Enter the date (last working day of the closing month)

Example in June 2019: we do not use June 30th which was a Sunday. If you select 30th you’ll get an empty report.

Click on the icon in the column “action” to download the reports.

You’ll get the following screen:


Check again that the period is correct (closing period)

Click on the download icon « in French Télécharger »

Then record the pdf file in the shared disk Treasury-GAR on the folder Newedge of the closing month:


Same process for all SES activities (from SES01 to SES09).

Note that if no transaction occurred for a specific activity, no pdf file will be available.


3.3. I create new monthly reports

Select  « reports » in the “reporting” menu 

Enter the date (from the first working day until the last working day of the closing month)

Then select « Listed Derivatives »

The statements have to be extracted in both pdf and xlsx formats. xlsx files will be used to generate xls reports to be used for cadrage and pivot CO2 procedures.

To respect the file names used for pdf and xls.

Watchpoints: 

  • When downloading the report: do it one by one and wait until the file is created otherwise the download will be canceled.

  • In case we need to export in csv format, check the format (point, comma) otherwise the pivot tables might display wrong figures.


Here is the list of statements to be downloaded:

1)

Download Pdf and excel to be used to prepare the file


2)

Download Pdf and excel to be used to prepare the file 


3)

Only download pdf

To be used on xlsx format to prepare the file


4)

Download Pdf and excel to be used to prepare the file 


5)

To be used on xlsx format to prepare the file


6)

To be used on CSV format to prepare the file


How to build the reporting files?

  1. Copy the file of the previous month, save it in the current month´s folder \\NOHVFS01\Treasury-GAR\SES\YYY\MM\Newedge and rename it

  2. Copy the last two worksheets (TCD and data) and rename them with the new periods

  3. Clean the data worksheet (keep the titles) and copy/paste special/values the new data.

  4. Refresh the pivot table (Tableau Croisé Dynamique “TCD” in French).


Example for Purchase & Sale (same for all files)

Yellow tabs are databases and TCD tabs are worksheets with pivot tables. 

Again, as explained in the watch points section, in the csv reports, you need to check the format of the numbers (comma and point => I usually replace all points to commas) otherwise the pivot tables might display wrong figures.


“Commission rates” statement  used to build the file

Select the report « commission rates » from the Reports - Listed Derivatives web page.

Download the pdf and csv files.

Csv data: attention to the format number in columns W, Z, AC, AF, AI, AL

Update data in newly created “commission rates” tab

 

Update the pivot table in newly created TCD sheet:

This report is used to book the charges  (Commissions, fees, and other charges) to P&L in the “cadrage” file . Note that when cadrage file is ready, differences could occur between the fees really debited from the bank account (bank statement) and the report here aforementioned. In that case, a clarification request should be sent to Newedge and we consider the bank statement situation as the truth for the closure meaning we adapt “cadrage” to post the appropriate P&L amount that matches the bank statement. Then we adapt the reporting file with an updated version. When the answer is received from Newedge, to assess the correctness of the entry performed. 

Reconciliation after posting can be done by selecting the P&L accounts, filtering by activity (assignment) and by posting date:

Example: 14k€ on SES04 in accounts 98300330

Reconciled with cadrage file on activity SES04:

and fees transferred from 98300811 to 98300330 to isolate the fees.


“Prior day balance” used to build 

Select the report « Prior day balance » from the Reports - Listed Derivatives web page and extract the PDF and CSV files.  Csv data: attention to the format numbers

Update data in newly created “PRIOR_DAY_BAL”

 

Update the pivot table in newly created TCD sheet:

Update FX EUR/USD  rate in the cell A23 by checking it up in SAP PI1 transaction ZRATE.

After making cadrage postings you may conclude by taking print screens of account 55101000 and confirm if your end result are the same.


“Purchase and sale”

Select the report « Purchase and Sale » from the Reports - Listed Derivatives web page and extract the PDF and CSV files.  Csv data: attention to the format number in column M « PsAmount ».

Insert the new column and copy formulae from the previous month.

For Pdf:

For CSV:

Example of pivot after update:

Note that on activity SES05 "COMMODITY SPOT", the amount cannot be used to complete the cadrage file.

The report is used as an input for the “cadrage file” to update the worksheet.

Example for activity SES04: we can see again on line P&L the amount from the pivot 10,2€m and this amount is part of the 4.880k€ that we could see in the above SAP print screen.


“Positions by account and product” statement  used to build the file

Select the report « Positions by account and product » from the Reports - Listed Derivatives web page and extract the PDF and CSV files. Csv data: attention to the format number in column  P “Marketvalue”.

.


Example of pivot after update:

The report is used as an input for the “cadrage file” to update the worksheet.

Example for activity SES01: we can see on line OTE Option the amount from the pivot 815€k and this amount is part of the -19.926k€ that we can see in the SAP print screen.

With cadrage file we will book the amount between balance sheet accounts: cash in transit (52000100) vs restricted cash (55101000). The posting will only be concluded with CO2 pivot file where we will hit the margin call accounts (57000100 and 55100100), financial instruments (46112000 and 46912000) and P&L (98300814 and 98300811).


“Balances & equity by Currency” used to build the file

Select the report « Balances & equity by Currency » from the Reports - Listed Derivatives web page and extract the CSV file. Csv data: attention to the format number in columns O, S, T, X et Z.


Example of pivot after update:

Watchpoint is the date filter (last working day only)

The report is used as an input for the “cadrage file” to update the worksheet.

Example for activity SES01: 

The accounting entries permit to recognize the cash movement on margin requirements and margin excess/deficit. 


“Activity” used to build the file

Select the report « Activity » from the Reports - Listed Derivatives web page and extract the CSV file. Csv data: attention to the format number in columns Q « amount ».

Example of pivot after update:

The report is used as an input for the “cadrage file” to update the worksheets SESXX by computing the cash transfers.

Example for activity SES01: 


3.4. I create reports for Energy controller

There are two reports that energy controller has asked us to perform.


3.4.1 I create report "Analyse activité NEWEDGE"

1- Extract a report from Newedge in csv format

Now you need to change the format in columns "P" , "S", "AI"  & "AD" (replace point by coma). To do this our suggestion is:

a) Close all excel files

b) Open control panel ==> Region and Language ==> Additional Settings

Replace decimal symbol: comma => point

Replace Digit grouping symbol: point => space


Repeat same logic tab "Currency".

c) Click "Apply" and "Ok"

d) Open the report downloaded and for columns "P" , "S", "AI"  & "AD" click on the number format:

e) Save the report has an xls format now and close the excel file

f) Undo changes made in caption b)


Create 3 new columns

AW: 

AX: 

AY: 


Create and update pivot table

Create new tab with pivot  and change the data source to the new report you created. Refresh pivot table you will get a result similar to the following table:


We need to reconcile the amount with the cash transfers occurred during the month:

"Activity" & "Commissions" retrieved from statement SES05:

"Transfer funds" retrieved from report 

"P&L SES 05" retrieved from report 

If you still have differences you need to check the statement of SES05  and look for unusual finance data. Example from closing 06.2020:


3.4.2 I create report "Analyse SES 05"

1- Extract a report from Newedge in csv format

Now you need to change the format in columns "I" & "O" (replace point by coma). To do this our suggestion is:

a) Close all excel files

b) Open control panel ==> Region and Language ==> Additional Settings

Replace decimal symbol: comma => point

Replace Digit grouping symbol: point => space


Repeat same logic tab "Currency".

c) Click "Apply" and "Ok"

d) Open the report downloaded and for columns "P" , "S", "AI"  & "AD" click on the number format:

e) Save the report has an xls format now and close the excel file

f) Undo changes made in caption b)


Create 2 new columns

Column AA: IF(H2="b";I2;I2*-1) (Qty with sign)

Column AB: AA2*O2 (Qty x Trade price)


Create and update pivot table

Create new tab with pivot  and change the data source to the new report you created. Refresh pivot table you will get a result similar to the following table:

We need to reconcile the amount with the cash transfers occurred during the month:

"Activity" & "Commissions" retrieved from statement SES05:

"Transfer funds" retrieved from report 

"P&L SES 05" retrieved from report 

If you still have differences you need to check the statement of SES05  and look for unusual finance data. Example from closing 06.2020:

3.5. I send an email to Risk team

Once all reports are retrieved you may send an email informing that the reports are ready for consultation:



Send to: Risk team

CC: CAM, controllers (CO2 and Energy), TSU Reporting

Emails can be found in 3.1. I know the teams involved

End of document.