24Appendix D: Multi-Currency across OFSAA Applications

Topics:

·        OFSAA Rate Management

·        Functional Currency

·        Currency Codes

·        002 code

·        Multiple Currencies in Allocation Rules

·        Allocation Engine Processing Flow

·        Selecting Rows from Management Ledger Table

·        Treatment of Constants and Coefficient Values

·        Selecting Rows from Detail Tables

·        Inserting Into Management Ledger Table

·        Updating Detail Tables

·        Notes on Usage of Statistics and Other Balances

·        Monetary Balances and Balance Weighted Objects

·        Statistics and Standard Rates

·        Examples

OFSAA Rate Management

OFSAA Rate Management is a rate management utility that enables users to manage interest rates, exchange rates, and currency data with a high degree of security and control. As part of OFSAA Infrastructure, OFSAA Rate Management handles all currency definitions for OFS Analytical Applications. Currencies are referred to by code and written description. A comprehensive list of ISO-defined currencies is included, and users can define and add additional currencies. For details on using OFSAA Rate Management, see OFSAA Rate Management.

Upon installation, one currency is active and ready for processing: the one identified by your organization as the functional currency (for more information, see Functional Currency). You may activate other currencies and define and activate newly created currencies as needed.

You may define, input, and maintain exchange rates between any two active currencies. In addition to the standard floating exchange relationships, special fixed relationships are available (For example, used before 2000 in the European Monetary Union). OFSAA Rate Management complies with EMU structure and legislation and supports changes in currency rates as well as additions and deletions of the currencies of member countries.

Exchange rates between currencies used in Profitability Management allocations are reciprocal. The rate of exchange between two currencies must be arbitrage-free. Profitability Management does not calculate any currency exchange offsets for non-reciprocal exchange rates.

Functional Currency

At the time of installation, OFSAA Rate Management requires that you designate a functional, or primary currency for your organization. A Swiss multinational bank, for example, would designate the Swiss franc as its functional currency. The functional currency is always active. After you have assigned your functional currency, it cannot be changed. To assign your functional currency, manually update FSI_DB_INFO.FUNCTIONAL_CURRENCY_CD with your selected currency.

NOTE:   

This one-row configuration table also contains your fiscal year definition; for more information, see Fiscal Year Information.

Most customers need only one functional currency, but if you require multiple functional currencies and/or multiple fiscal year definitions, you may establish ledger-specific selections of functional currency and fiscal year by manually updating LEDGER_CCY_AND_FISCAL_YR_INFO (one row per defined Management Ledger table).

Processes operating against a Management Ledger table generally determine Functional Currency and Fiscal Year by consulting LEDGER_CCY_AND_FISCAL_YR_INFO. However, any process operating against a Management Ledger table for which no row has been set up in LEDGER_CCY_AND_FISCAL_YR_INFO determines its Functional Currency and Fiscal Year. This is done by consulting FSI_DB_INFO (that is, FSI_DB_INFO serves as a fallback if you have not set up ledger-specific currencies and calendars in LEDGER_CCY_AND_FISCAL_YR_INFO).

NOTE:   

This one-row configuration table also contains your fiscal year definition; for more information, see Fiscal Year Information.

The designation of functional currency is stored as an ISO currency code in FSI_DB_INFO.FUNCTIONAL_CURRENCY_CD / LEDGER_CCY_AND_FISCAL_YR_INFO.FUNCTIONAL_CURRENCY, as applicable.

Profitability Management allocations can read and write in multiple currencies. However, Profitability Management allocations perform internal calculations in the functional currency.

When multi-currency is disabled, all ISO Currency Code values are written in the Functional Currency.

Currency Codes

ISO Currency Codes are defined for the world's major currencies and are seeded in the OFSAA database at installation. ISO Currency Codes are Simple Dimension members (see Dimension Types in the Overview of OFSAA Infrastructure chapter) that define the currency of any row in the database. ISO Currency Code values used in any of OFS Analytical Applications need to be designated as active in OFSAA Rate Manager. ISO Currency Codes may also be user-defined. User-defined ISO Currency Codes are set up in OFSAA Rate Management (again, for details, see OFSAA Rate Management).

Users must designate ISO Currency Codes as reporting currencies to activate the calculation of conversion rates. Simply designating the currency as active is not sufficient.

002 code

The ISO Currency Code value of 002 represents statistical data in the Management Ledger. An ISO Currency Code value of 002 means “no currency basis”. For example, a row in the Management Ledger representing a square footage statistic does not need a currency association and would, therefore, have a 002 ISO Currency Code. Assigning an ISO Currency Code to a statistic is useful for matching rows that have the same leaf values but differ only by ISO Currency Code (Leaf type Allocation rules).

Multiple Currencies in Allocation Rules

OFSAA Profitability Management allocation engine supports the processing of balances in multiple currencies. When multiple currency functionality is enabled (FSI_DB_INFO.MULTI_CURRENCY_ENABLED_FLG = 1), a Currency dimension is enabled in each of the Allocation Specification dialogs (for example Sources, Drivers, and Outputs) that lists dimension member values. ISO Currency Code functions similarly to other dimension members in the Profitability Management user interfaces except that the ISO Currency Code dimension is a Simple Dimension and therefore does not support hierarchies. When specifying ISO Currency Codes in an Allocation rule, you may only specify a leaf value (an active ISO Currency Code) or a macro value.

·        For all tabs, the allocation rule dialogs support the Functional Currency macro ('001'), the Statistical Currency macro (non-currency Basis of '002'), and all active ISO Currency Codes.

·        The Source and Driver tabs also support an All Currencies macro. All Currencies are the default value for Source and Driver tabs.

·        The Debit and Credit pages within the Output tab also support a <Same as Source> macro. For Dynamic Driver rules, the Debit and Credit pages also support <Same as Driver >and <Match Source & Driver>.

Output Tab Showing Multi-currency Options for a Dynamic Driver Rule

Using the Currency Dimension as a Filtering Condition

As with other Key Processing Dimensions, the use of any value other than <All> will constrain the data that serves as Sources or Drivers to allocation rules. For example, if you were to specify GL Account 12345 – Salary Expense within the Source of an allocation rule, only rows having GL Account 12345 would be included in the Source of your rule. Similarly, if you were to specify US Dollars within the Source of an allocation rule, only rows denominated in US Dollars would be included.

Allocation Engine Processing Flow

In Sources and Drivers, the Profitability Management allocation engine can read multiple input currencies. When posting to the Management Ledger table, the engine can output multiple result currencies. When posting to a detailed table, outputs are converted, if necessary, to match the ISO_CURRENCY_CD on each detail record to be updated.

Allocation Engine Processing Flow for Multi-currency Allocations

The preceding diagram illustrates the basic allocation engine processing flow for multi-currency allocations. Input data for monetary balances are converted to the functional currency. By having all inputs in the same currency, calculations, such as percent distribution, are created on an equal basis. After converting all input data to functional currency, the allocation calculations are performed. The output results of the allocation can be in any currency. The results of calculations are converted from functional currency to the appropriate debit or credit currency for that allocation. As noted earlier you may direct the output currency to your choice for Management Ledger outputs; for detail tables, outputs always match the currency of the records to be updated.

Input rows to allocation rules are checked to determine whether currency translation is necessary. The checks for whether an item needs translation vary between the Management Ledger table and detail tables (Instrument and Transaction Summary tables).

Selecting Rows from Management Ledger Table

When reading rows from the Management Ledger table, the allocation engine decides whether or not it needs to translate the Entered_Balance column value based on the “Column Property” characteristic of each row's Financial Element ID. This decision process applies to Sources and Drivers and is made on a row-by-row basis (one decision point for each distinct Financial Element found). OFSAA Infrastructure supports four Column Properties for Financial Elements: Balance, Balance Weighted Object, Statistic, and Standard Rate.

·        If the Column Property of an input row's Financial Element is Balance or Balance Weighted Object then the value is translated to the Functional Currency.

·        If the Column Property of an input row's Financial Element is Statistic or Standard Rate then the value is NOT translated (that is, the value has no currency-specific basis).

You may execute the following query to see the Column Property for each Financial Element.

-------------------------------------------------------------------------------------------------------------

SELECT A.FINANCIAL_ELEM_ID,

C.FINANCIAL_ELEM_NAME,

B.COLUMN_PROPERTY

FROM DIM_FINANCIAL_ELEMENTS_ATTR A, REV_COLUMN_PROPERTY_DSC B,

DIM_FINANCIAL_ELEMENTS_TL C

WHERE A.ATTRIBUTE_ID = 5004

AND C.LANGUAGE = <Specify your language here or simply use 'US'>

AND A.DIM_ATTRIBUTE_NUMERIC_MEMBER = B.COLUMN_PROPERTY_CD

AND A.FINANCIAL_ELEM_ID = C.FINANCIAL_ELEM_ID

ORDER BY 1;

-------------------------------------------------------------------------------------------------------------

NOTE:   

When you generate a user-defined Financial Element, you must assign a Column Property attribute value.

Treatment of Constants and Coefficient Values

The following constant values are assumed to be either statistical or to be entered in Functional currencies and are therefore never translated:

·        Input values on the Source tab for Constant type allocation rules.

·        Constants found in the Operator for any rule.

·        Values in Static Table Driver rules.

·        Values found by Lookup Table Driver rules in user-defined Lookup Tables.

Selecting Rows from Detail Tables

When reading a column from a detailed table (an Instrument table or a Transaction Summary table), the allocation engine decides whether or not it needs to translate a value based on the column's Column Property characteristic.

·        If a column's Column Property is Balance, then the value is translated to the Functional Currency.

·        If a column's Column Property is anything OTHER than Balance, then the value is NOT translated (that is, the value has no currency-specific basis).

For detail level columns, OFSAA Infrastructure supports the following Column Properties:

You may execute the following query to see the Column Property for any column in an Instrument or Transaction Summary table:

--------------------------------------------------------------------------------------------------------------

SELECT TABLE_NAME,

COLUMN_NAME,

A.REV_DATA_TYPE_CD,

B.REV_DATA_TYPE,

B.DESCRIPTION

FROM REV_TAB_COLUMNS A, FSI_DATA_TYPE_DESC B

WHERE TABLE_NAME = <Insert table name here>

AND A.REV_DATA_TYPE_CD = B.REV_DATA_TYPE_CD

ORDER BY 1, 3, 2;

--------------------------------------------------------------------------------------------------------------

Inserting Into Management Ledger Table

Allocations inserting into the Management Ledger table treat Currency as a Key Processing Dimension. For example, in a Dynamic Driver type of allocation that outputs to Management Ledger table, if the allocation's Debit value in the Currency dimension is <Same as Source> and if five distinct currencies are found in the Source data, then the engine will output to five currencies in its Debit currency. If the allocation's Debit value in the currency dimension is <Same as Driver> and if six distinct currencies are found in the Driver data and if the allocation's Driver is constructed to use Force to 100%, then the allocation engine will perform a percent-distribution calculation to the six currencies.

The engine decides how to Debit and Credit the Currency dimension based on the instructions you specify in an allocation rule. The decision as to whether or not to translate output values to the currency basis of the target output currencies is a completely separate one.

For any row in the output of an allocation rule, the engine decides whether or not to convert to a target currency based on the Column Property of the Financial Element for that row.

As was the case when selecting data from the Management Ledger table:

·        If the Column Property of a target row's Financial Element is Balance or Balance Weighted Object then the value is translated from the Functional Currency to the target currency.

·        If the Column Property of a target row's Financial Element is Statistic or Standard Rate then the value is NOT translated. That is, the value has no currency-specific basis).

Updating Detail Tables

When updating a Balance type column within any of the detail tables, outputs are translated to match the currency of the records to be updated.

Notes on Usage of Statistics and Other Balances

It is important to understand the details of the behavior of the allocation engine with respect to currencies before building your rules. Some elements that you may regard as being statistical in nature are registered within the system as Balances or as Balance Weighted Objects and are therefore always translated (both when serving as inputs to rules and when output from rules). You may wish to store other statistical elements as a function of a currency, that is, elements having different values for each currency, but which you do not want the engine to translate. Finally, you may wish to store elements that you regard as being statistical in nature but which vary as a function of currency and which you do want the engine to translate.

Monetary Balances and Balance Weighted Objects

Monetary Balances (or simply Balances at the detail table level) have a strong currency basis that is relatively obvious. Ending balances and average balances for Balance Sheet elements and expense and revenue balances are the most common examples.

In a multi-currency implementation, instrument-level monetary balances are inherently stored as “transacted” balances, that is, as balances stored in local currency. The monetary balances that you source from your general ledger system and which you store in the Management Ledger table will generally be 100% functional. However, you may wish to aggregate instrument-level balances to your Management Ledger table and to store them there in their local currency amounts and/or generate non-functional balances in your Management Ledger table.

Balance Weighted Objects include elements such as weighted average Transfer Rate (Financial Element 140) at first glance appear to have a statistical nature but have a direct relationship to underlying monetary balances and therefore have the same strong currency basis as Monetary Balances.

More commonly, you will write your allocation rules that debit or credit the Management Ledger table using the 001 – Functional Currency macro or using the ISO Currency Code that represents your Functional Currency (these are equivalent definitions).

Statistics and Standard Rates

The Statistics and Standard Rates Column Property Financial Elements are not translated – neither on input (Sources and Drivers) nor on output (Debits and Credits).

Similarly, at the instrument level, columns whose Column Property is other than Balance are never translated.

The meaning of the term “statistic” is contextual. Statistics often lack a currency basis (that is, you do not want the allocation engine to translate them, neither on input nor on output). Most often, transactional volumes, activity volumes, unit costs, and other statistics do not vary by currency in their meaning. For example, you may wish to utilize headcount statistics as a driver in one or more allocation rules. Your headcount statistic would be invariant as to currency.

On the other hand, you may wish to store some other kinds of “statistics” as a function of currency. Weighted average interest rates on loans and deposits or weighted average transfer rates, for example, have a strong currency basis.

Examples

Example #1

In this example, a “debit only” Constant allocation rule generates a single row in Euros in an environment in which the Functional currency is US dollars.

In this example, if the USD to EUR exchange rate is $1.2987 per Euro then the allocation rule's Debit produces an output value of €77.00 (100/1.2987).

Example #2

Similar to example #1, in this example a “debit only” Constant allocation rule generates a single row in Currency Code “002 – Non Currency Basis” in an environment the Functional currency is US dollars.

In this example, note that even if the allocation had an output to a Balance type Financial Element (Ending Balance, for example), no translation would have occurred since '002' means “No currency basis”. The debit value in this example is 100.

Example #3

Example #3 is an allocation rule that distributes an equal amount of ledger-level expense to an instrument-level balance column.

In this example, we are allocating a pool of ledger-level Statement Processing expenses down to the instrument level for Checking and Savings products. The methodology we have selected is to allocate an equal share of the ledger-level expense to every row in the CASA (Checking and Savings Accounts) table on the assumption that every row generates an equal amount of expense for the bank.

Once completed, we would expect that the total USD equivalent of the amount of the Statement Processing Expense allocated to the instrument level would be exactly equal to the amount originally sourced from the ledger level.

Example #4

Example #4 is an allocation rule that distributes ledger-level expense to an instrument-level balance column on a percent-to-total basis of another instrument-level balance column.

In this example, we started with Source data (ledger-level) Loan Loss Reserve of $40,000,000 that was to be allocated to detail level data in the Mortgages table for a portfolio of 10,000 USD-denominated loans the total average balance of which was $2 billion; and 10,000 JPY-denominated loans the total average balance of which was ¥ 200 billion. In this case, the current exchange between JPY and USD is 100:1 so the USD equivalent average balance of the JPY mortgage portfolio is equal to the USD mortgage portfolio – each portfolio is valued at 2 billion USD.

Since the JPY and USD portfolios are of equal size, we would expect that each portfolio should receive an equal distribution of total Loan Loss Reserve ($20 million to each portfolio). We expect that each dollar of USD average balances would receive an amount equal to each ¥ 100 of JPY average balances. After the allocation rule had been run, we would expect to find $20 million in allocated Loan Loss Reserve associated with USD mortgage rows and ¥ 2 billion in allocated Loan Loss Reserve associated with JPY mortgage rows.