1. Objective and Scope

1.1. Objective of this Operation

1.1.1        Process context

This procedure is the reconciliation of the interests on IBA. This check allows us to confirm that the calculation made by the system  is correct. We need to use SAP and Excel files to perform the control and this procedure will explain what its need to be done to accomplish it.

1.1.2        Objective

Accurately calculate the interests of IBA on excel file.

This process needs to be performed on a monthly basis on D-1 after the Interests calculation on IBA is complete in companies 2232 and 4044.

1.1.3        Critical areas and impact on other processes

Accuracy and completeness are key given to the impact that the process has on affiliate level.

1.1.4        Process description

The SU Treasury Accounting team is responsible to perform the reconciliation. The following have to be assured for the control to be efficient:

1. Upload IBA 591* YTD balance for respective month from GR55
2. Upload the P&L details for current month accounts 6500030000 and 7510030000 
3. Create the Pivot table for P&L accounts by Trading Partner / Currency / Amount in Local Currency (EUR)
4. Sort the amount in Local Currency (EUR) < and >30k EUR (other amounts just group and hide)
5. Check the Debit and Credit rates for Positive and Negative IBA balances and put in the file
6. Create the Analysis tab "IBA":
   1. Analyze IBA´s with > -30k and < 30k EUR
   2. Investigate differences > 5% and < -5% between interests calculation (theory and booked in SAP)
7. When Calculation is done - the Totals by IBA should be copied in CUMULATIVE file to see Cumulative analysis for the year

1.1.5        Roles and responsibilities

Syensqo SA Treasury Accounting Team is in charge of interest calculation process. There is no segregation of duties between the team members.

1.1.6        Parties involved

In case any problem is encountered during the execution of the check please check with treasury accounting team members.

1.2. Scope

This procedure is applied to entities 2232 and 4044.

This procedure is under the responsibility of the SU Treasury Accounting team to ensure the replication in the affiliates.

2. Definitions

See Finance Glossary:

• IBA - Internal Bank Account
• PI2 - SAP Treasury  Production System

As explained before this process is the reconciliation of the interests on IBA calculation. For more information on how the interests are calculated please see the following operating procedure:

OP7 - Interest calculation on IBA_062018_updated 20210922

3. Tasks description

The following procedure will have focus on how to build the control for company 4044.

The first step is to open a previous control created (M-1) and make a save as in the following folder:

\LUCAP-USERS\TREASURY ACCOUNTING\INTERNAL CONTROLS YYYY\RtR.TRE.01.09_Interest on IBA

The file to use as a reference is:

On the next chapters we will show you how to build the different tabs of the control:

For company 2232 the procedure is different since we make the cumulative analysis.

The file to use as a reference is:

On the next chapters we will show you how to build the different tabs of the control:

3.1. For company 4044 

3.1.1. SAP “MONTH”

In this chapter we need to retrieve the balances of the IBA´s in D-1.

Go to PI2, transaction GR55 and Report Group Z4F1 (BFC balance sheet - year overview):

Then fulfill the year, month and the company code:

Select “Lead Column”

And then “Settings” – “Column Attributes” 

And put 99:

Next go to “View” and select “Expand report fully”:

A similar screen will appear:

You can see that for each schedule you are able to see the GL accounts.

The next step is to retrieve all GL accounts 591* (from Assets and Liabilities) to excel.

Go to “Settings” – “Options”

And choose “excel” has an output:

Afterwards copy paste the information to an excel file:

Paste it in tab 1) Balance sheet.

Next you need to:

• Take out all colours;
• You just need the “Cumul 9”. You may eliminate the rest of the columns;
• Create a filter and choose all accounts starting with “591”;
• You can eliminate all rows with no amount (blanks) and zero amounts;
• The Negative sign in liabilities are after the number. You need to change this because the sign has to be first. Select these types of amounts, go to “Data” – “Text to Columns” and press Enter until the end. You can custom the cells has a number afterwards;
• Cross check if the total you have is the same with the sum of the 591* in SAP. This is to guarantee that we didn´t lost any information along the way.

Now we are in good condition to copy paste the information to our control file.

Copy the “Lead Column” to the appropriate column in tab 2)SAP SEP, and the amounts to “August”:

Please do not delete the columns “IBA”, “CUR” & “TP”. These columns have formulas:

Please sort the information from biggest to smallest.

Final result:

3.1.2. YEARS (JOURS DEVISES)

Add the file from Hatem to which have the information the number of days in a year (360/365)

This information is retrieved from treso team. Contact the Treasury Accounting members for more detail.

This information is very useful because it will allow us to equalize the yearly rates to day rates according to the type of currency.

3.1.3. INTR

Go to FS10N – And put P&L accounts 6500030000 & 7510030000

Double click on the balance of the current month

• filter by D3 postings = Interest on IBA calculated and booked in SAP
• or filter by Text "01MMYYYY-31MMYYYY"
• FYI - each posting contains the Interest scale in attachment with details that the system calculates the Interest, when and how much booked, which % is used

Export the information to Excel:

 “List” – “Export” – “Spreadsheet”:

Save as an excel

And copy paste it to our tab in the control file.

3.1.4. PIVOT INTR

3.1.4.1. Create pivot table

We will first create a Pivot table using the information in Tab “INTR”.

We need to sort the information by Trading Partner / Currency / Amount in Document Currency / Amount in Local Currency (EUR)

Please note that the sum in Local Currency has to be the same has our extraction in tab “INTR” and the SAP selection.

Next, copy paste (values) the table above to new columns and:

• Sort the amount in Local Currency (EUR) from biggest to smallest;
• We need to hide all amounts that are smaller than 30K and higher than -30K

And Hide:

These affiliates and currency are the ones that we need to analyze in tab “IBA”.

3.1.4.2. Interest Rates

Go to transaction SE16 and select table T056P

Press Enter

A Similar screen will appear:

To get the interests of a currency we have to follow the rule:

• Interest indicator for CREDIT = M.”CURRENCY”G05C
• Interest indicator for DEBIT = M.”CURRENCY”EG02D

Let´s put the example for 4044:

3.1.5. IBA

First you need to copy paste all information from tab “SAP JUL” to the first 4 columns:

Next go to transaction Z3F_BALANCE_VAL_DATE and fill the items accordingly:

Execute

A similar screen will appear:

We then need to extract all the information to our control file:

Afterwards, the fields “value date balance” & “open items balance” in the control tab “IBA” will be automatically fill, since the cells have a VLOOKUP formula.

Next copy paste from tab “PIVOT INTR” the table made with the interest rates to tab “IBA”:

The purpose now is to filter my table with only these IBA´s. We just need to check if the selection is correct because these cells also have formulas.

If the IBA is not in the yellow cells, it will appear #N/A.

Now we need to fill the columns “YEAR”, “DAYS”, “RATE” & “INTEREST IN SAP” accordingly:

And finally:

The column with the difference in percentage. Here we need to investigate the calculation for a difference higher than 5%. For July we need to investigate 2 calculations.

Final result for tab “IBA”:

3.2. For company 2232

3.2.1. Interests SAP

Go to FS10N – And put P&L accounts 6500030000 & 7510030000

Double click on the balance of the current month

• filter by D3 postings = Interest on IBA calculated and booked in SAP
• or filter by Text "01MMYYYY-31MMYYYY"
• FYI - each posting contains the Interest scale in attachment with details that the system calculates the Interest, when and how much booked, which % is used

Export the information to Excel:

 “List” – “Export” – “Spreadsheet”

3.2.2. Pivot (first refresh)

After we have the information from SAP, we need to refresh the pivot table in “Pivot” tab to retrieve the information of the IBA accounts with interests in the month.

Further ahead, we will return to this Pivot tab to complete the analysis.

3.2.3. Interest rates

First step is copy the information from Pivot tab highlighted above and past it in column A on the Interest Rates tab.

Update the period to analyze.

Column Code will be automatically fill.

To retrieve the interests rates for each currency:

Go to transaction SE16 and select table T056P

Press Enter

A Similar screen will appear:

To get the interests of a currency we have to follow the rule:

• Interest indicator for CREDIT = M.”CURRENCY”G05C
• Interest indicator for DEBIT = M.”CURRENCY”EG02D

With this information we will have the table complete.

3.2.4. Value date balances

Copy the IBA accounts in column I of Interest rates tab;

Past it on column B in Value date balances tab;

Update period.

Next go to transaction Z3F_BALANCE_VAL_DATE and fill the items accordingly:

Execute

A similar screen will appear:

We then need to extract all the information to our control file:

3.2.5. EOM rates

Go to Syensqo Treasury Online

https://build-prod-syensqo-2r1hkgl4.workzone.cfapps.eu10.hana.ondemand.com/site/portal#workzone-home&/workpages/LfdKUASV09jOyVc3l0E0Ee

Choose Financial & Credit tools

End of month rates

Then choose the month and the reference currency EUR.

It will appear:

Extract to excel:

We will use the End of month rates.

We need to update the rates that have the differential different from 1.

For the USD, for example, the end of month rate will be 118.39 / 100 = 1.1839

After we have all the rates for each currency, we update columns B, C and D.

3.2.6. Pivot

Now we have all the information in this tab, we can proceed with the analysis between the theoretical interest and the actual interest calculated in each IBA.

We should analyze differences higher than 5%. We also need to check if the amount is significant to be analyzed or not.

If we see the example below, we have a difference of 178,64% in 591USD6044, however, the difference in amount is negligible (-0,15€).

We analyze differences higher than 5% and higher than 30k in EUR.

3.2.7. Justification

Use Justification tab to comment on the analysis performed regarding the IBA that was higher than 5%. This information will automatically appear in tab Pivot (column L).

3.2.8. Dashboard

In Dashboard tab, we have the cumulative view on the interest calculation:

We can see in the example above that the cumulative difference is less than 5%.

3.3. Investigation Diff. Higher 5%

In this procedure we will see the example only for 591EUR5835. For the rest you have to apply the same logic.

First go again to SAP – FS10N transaction (chapter 3.3 of the procedure) and select the D3 document that we wish to analyze. In this case we have to look for TP 5835:

And if you look closely, this amount is the same has our column “Int in P&L”:

Then you need to take a screenshot of the interest scale in the D3 document and paste it in the tab “MM.YYYY”.

In our case double click on the document itself and download the file:

And paste the month that we are analyzing in the tab “MM.YYYY”.

A similar screen will be shown:

The first part of our analysis is to confirm that the column “amount” of our print screen is align with our SAP:

Go to FS10N – Choose Account 591EUR5835 – Select all items from the cumulative balance

Then filter the value date from 01.MM.YYYY until 31.MM.YYYY

Make a new filter for the posting date: exclude range from last day of the month until 31.12.9999

And sum by value date:

Now compare the amounts in local currency with the amount from our interest scale. They should match. If they match it means that we need to take the average from what the system calculated.

 If you look closely, the system calculates the interests applying the rate, to the balance by value date by days. If the balance has a big change, or if it keeps changing a lot, this means that we cannot use just the final balance has we did in our tab “IBA” to see if the interests were well calculated. We will use an average that the balance had throughout the month.

For that we need to go to transaction Z3CTB – ZINTERET – Interest Calculation on general ledger account.

Apply the following variants according to the company being analyzed:

• 2232 INT 2023
• 4044 INT CALC

And fill accordingly:

Execute

A similar screen will show:

Note: We can also use the file prepared during closure for Interest calculation detail stored in \LUCAP-USERS\TREASURY ACCOUNTING\MONTH END CLOSING\YYYY\MM.YYYY

You need to calculate the average, to do that, you can use the amounts highlighted in the red square.

And now you need to replace the amount in tab "Pivot" in 2232 and “IBA” in 4044, as follows:

Keep the old amount just for reference.

Has you can see the difference now is under 5% and its justified.

4. Manual Corrections

No manual corrections

End of document.