1. Objective and Scope

The purpose of this document is to describe the monthly process to update SIMULATION exchange rates in BFC.

SIMULATION exchange rates are used in RSB reporting category (restatement of Y-1 with Y scope and Y exchange rates) in the months for which there are no ACTUAL rates yet.

Every month, around WD1/WD2 (WD = working day), after ACTUAL rates have been published, the Corporate Controlling team reviews the projection of exchange rates for the next quarter months until the end of the year.

BFC Admin team creates the 4 tables 03.YYYY, 06.YYYY, 09.YYYY, 12.YYYY at the beginning of each year (save as from PREV rates) and updates the tables every month (TC and TMN only).

Both, creation and update, ONLY upon requests from Corporate Controlling team. 

Below are the 2 types of conversion rates updated in this monthly process:

  • TC - Closing rate 
  • TMN - Average cumulative  rate
  • the other Exc. rates types (TO, TMNY-1...) updated in ACTUAL (REEL) Exc. Rates tables are excluded there


2. Upload files preparation  

The Corporate Controlling team sends an email to the BFC Admin with an Excel file attached with the quarter rates to be updated. 






The information BFC Admin needs is in BFC format tab.




There's an Excel macro workbook - BFC Simul ER update.xlsm - that should be used in order to generate the BFC import files.

Access the Simul ER update.xlsm through the link below and download it to your PC 





The Simul ER update.xlsm file has 6 tabs: 

  • data input - to which we will copy the data from BFC format tab of Corp Controlling's file. 
  • 03.2022 / 06.2022 / 09.2022 / 12.2022 - these tabs will be automatically updated from data input tab  (VLOOKUP formulas).

Important note: If there's any change in data input tab (e.g. new currency added), the other tabs must be adjusted manually (new currency lines must be inserted and formulas copied).

Every year we need to rename these tabs: 03.YYYY / 06.YYYY / 09.YYYY / 12.YYYY 

  • consistency check - BFC retrieval to check no major variances in tax rates compared to last quarter








In the file received from Corporate Controlling select the entire "BFC format" tab and press Copy (CTRL+A).



In Simul ER update.xlsm file , in data input tab , select Paste → Values 


The other tabs will be automatically updated.





Click on  to generate the import files.



BFC import files (one for each quarter) will be saved in the same directory where the Simul ER update.xlsm is saved.

Notes:

  • As of April we don't update 03.YYYY anymore
  • As of July we don't update 03.YYYY & 06.YYYY anymore
  • As of October we don't update  03.YYYY & 06.YYYY & 09.YYYY anymore




3. BFC upload

The files  MM.YYYY.txt have the correct format ready to be uploaded in BFC.


3.1 Update SIMULATION tables in BFC_Top

In this example, we'll update 06.2022.

In the Exchange rates module,

  • Open the corresponding SIMULATION 2022.06 table
  • Select File → Import






  • Processing existing data → Replace








Click on Import


Confirm there are no errors.


Click on Finish and Save before exiting the table.



3.2 Run check consistency 

 Before piloting the exchange rates table to BFCProd it is recommended to run a consistency check on the rates just uploaded into BFCTop.

Connect to BFCTop through BFC Excel Link WEB and open Simul ER update.xlsm file / tab consistency check. 


The objective is to compare the rates just updated with the previous quarter and check if there are big variances that could indicate a typo error. 


How to proceed with these checks:

  • Update period in cell C1 (03; 06; 09; 12).
  • Cells E1, F1, H1 and I1 will be updated through formulas
  • Connect to BFCTop and refresh the file
  • In columns E and F - retrieve of what we've just uploaded into BFC 
  • In columns H and I - retrieve of previous quarter rates
  • In columns K and L  - variances in %

There's a conditional formatting in columns K and L: in case of big variances these cells are highlighted in red.

In such situations, BFC Admin should ask the requestor to check and provide the correct rates.




3.3 Piloting to BFC_Production database

 Task EXPL-ADMIN has to be used to pilot SIMULATION Exchange Rates tables.
















4. Run Consolidation RSB version V4


In case it has been requested, BFC Admin should also run RSB Variant 4 consolidation after rates have been piloted.



5. Confirmation to Corp. Controlling 

Once exchange rates have been updated and RSB V4 conso run, BFC admin should inform the requestor.



END OF THE PROCEDURE