IBM Cognos Proven Practices: Guidelines to Using Historical Rates in IBM Cognos Controller

Nature of Document: Guideline; Product(s): IBM Cognos Controller; Area of Interest: Financial Management

Guidelines to setting up and using historical rates in IBM Cognos Controller.

Share:

Introduction

Purpose

This document provides guidelines to setting up and using historical rates in IBM Cognos Controller.

Applicability

IBM Cognos Controller

Exclusions and Exceptions

There are no known exceptions and exclusions at the time this document was created.


Historical Rates

Currency Rates – Historical Rates Register

The currency register you entered and stored the currency rates for a given period and actuality. You enter historical rates in a separate menu, Group/Data Entry/Historical Rates.

Figure 1 shows the Historical Currency Register and the filters available
Figure 1 shows the Historical Currency Register and the filters available

Entering Historical Rates

In this function you can entered the historical rates as fixed values in two selected currencies, often in the local currency and group currency, to calculate a fixed historical rate. The register is used to provide historical values in converted currency for movement accounts, for example, accounts for the issue of new shares or for some other account that requires a historical rate. The historical values are entered for each new period and actuality that will be used. Alternatively, you can use Group/Copy/Historical Rates Between Periods to copy the historical values from one period and actuality to another.

Note: Currency conversion is performed from local currency to all currencies included in the chosen consolidation structure. To keep the relation of historical rates all the way up in the consolidation structure, you have to enter historical rates for all group and sub-group currencies in the structure. You may have to add several records for the same item. Currencies not entered in the register of historical rates will be converted from local currency using closing or average rates. If the company is connected to more than one consolidation structure, you must enter historical rates for these currencies as well. Be aware of the fact that a very detailed set up of historical rates (for example, historical rates on extended dimensions or on journal numbers) will make the analysis of converted amounts more complicated. On the other hand, when you enter historical rates for G coded accounts it is important to enter historical rates at the appropriate detailed level.

Note: It is possible to enter historical rates directly when entering company journals.

Historical Rate Codes

You must define accounts, which you want to convert into historical values according to the register of historical rates, with currency conversion codes E, F or G using the Define Accounts function. These accounts appear automatically when you state for which company you want to register historical rates. If you select a specific form, only accounts with currency conversion codes E, F or G that are included in this form will be displayed.

Currency Conversion Code E

Historical rates can be entered in the register of historical rates for the specified account. Any amount exceeding what is entered in the register is also converted at the historical rate. If there is no amount in the register, the account is converted at the closing rate. This code can be followed by a reference to a specific account.

Currency Conversion Code F

Historical rates can be entered in the register of historical rates for the specified account. Any amount exceeding what is entered in the register is also converted at the historical rate. If there is no amount in the register, the account is converted at the average rate.

Currency Conversion Code G

Historical rates can be entered in the register of historical rates for the specified account. Any amount exceeding what is entered in the register is converted at the average period rate. If there is no amount in the register, the account is converted according to the rules for currency conversion code D. This code can be followed by a reference to a specific account.

Period Actuality

All historical rates are stored by period and actuality, which means that you can go back to an old period and locate the exact values that were used during the currency conversion.

Store by Specific Periods and Actualities

It is possible to let all historical rates be stored in one period and actuality. In these instances you must define the period and actuality in the general configuration on the Conversion tab.

Extended Dimensions

If an account divided into extended dimensions is going to use the same historical rate for all extended dimensions, no information is registered at extended dimension level in the register of historical rates. If, however, the extended dimensions are going to use different historical rates, the ratio of rates must be registered at extended dimension level.

Counter Company

If you register historical rates for specific counter companies, the rate will only be used for currency conversion of values registered to that counter company. For other companies, the closing or average rates will be used.

Journal Type

If you register historical rates for specific journal types, the historical value will only be used for the currency conversion of values registered to that journal type. Other journal types will be converted at the closing or average rates. If you want the historical value to apply to all journal types, the All Journal Types check box must be selected. The check box is selected by default.

Note: If a historical value in a company journal is entered via the Data Entry/Company Journals menu, this historical value can still be changed.

Journal Number

If you register historical rates for specific journal numbers, the rate will only be used for currency conversion of values registered to that journal number. Other journal numbers will be converted at the closing or average rates.


Copying Historical Currency Rates

Copying currency rates between period

In this function you can copy historical rates between periods and actualities.

Steps:

  1. On the Group menu, click Copy/Historical Rates Between Periods. The Copy Historical Rates Between Periods window opens.
  2. In the Copy From area, enter the actuality and period to copy historical rates from. It is not possible to select several periods and actualities. In the Copy To area, enter the actuality and period to copy historical rates to. It is not possible to select several periods and actualities. Select the Clear Existing Historical Rates for Current Selections Before Copying check box to delete any values already registered for the selected companies, currency codes and target actuality/period. Click Run.
  3. If you want to copy historical rates for all currencies and companies, select the All Companies and Currency Codes check box. All companies means all companies you have access to.

Note

  • When you copy Historical Rates between periods, the From and To currency codes are matched. This means that the historical rates only will be copied when values exist for both the From and To currency. You can select more than one currency code. The From and To Currency Code text boxes are enabled only if the option All Companies and Currency Codes is cleared.
  • There are no in-built year change rules for OB/CB in this copy function, but all accounts will be identical in From and To Period/Actuality.

Copying Historical Rates between Companies

You can use this function to copy historical rates between periods and actualities. You enter such information as:

  • Whether you want to copy historical rates for all currency codes and companies.
  • Whether you want to copy historical rates for specific companies or specific currency codes.
  • The periods and actualities you want to copy rates between.

Note:

You can only copy from one period and actuality to another period and actuality at a time.

All information registered on the Data Entry - Historical Rates menu will be copied. Copying will only take place if there are values stored in both the From Currency Code and To Currency Code columns. Select the Copy Options check box, if the period and actuality you are copying to already includes previously entered rates. Clearing takes place when the criteria are met for the selected period/actuality, the selected company, the selected currency codes and both the From Currency Code and To Currency Code you are copying to.

Note: There are no rules for copying across a year-end in terms of OB/CB built into this copying function. All accounts will be identical in From and To Period/Actuality. The main reason for copying historical rates between companies is probably when a structural change occurs and the same company becomes included in two locations in the group, one to handle the sale of the company and one to handle the acquisition of the company. Before you can copy historical rates, the company that you are going to copy to must be defined. You can select several periods to copy for, but only one actuality at the time. It is not possible to select several companies, neither to copy from nor to copy to. All information on the Data Entry - Historical Rates menu will be copied, but copying will only take place if there are values stored in both the From Currency code and To Currency code columns. Select the Copy Options check box, if the company you are copying to already includes previously entered rates. Clearing takes place when the criteria are met for the selected period and actuality, the selected currency codes, both From Currency Code and To Currency code, and the selected.

Company to Company Copying

Required Steps:

  1. On the Group menu, click Copy/Historical Rates Between Companies. The Copy – Historical Rates Between Companies window opens.
  2. In the Copy Selections area, enter the actuality and period to copy historical rates for. You can select several periods but only one actuality at the time.
  3. Clear the All Currency Codes check box if you do not want to copy historical rates for all currency codes. Instead, enter the From Currency and To Currency codes you want to copy. It is possible to select several currency codes by clicking on the relevant codes in combination with the Control or Shift key.
  4. In the Copy From area, enter the company to copy historical rates from. It is not possible to select several companies.
  5. In the Copy To area, enter the company to copy historical rates to. It is not possible to select several companies.
  6. Select the Clear Existing Historical Rates for Current Selections Before Copying check box to delete any values already registered for the selected actuality/period, currency codes and target company.
  7. Click Run.

Note

When you copy Historical Rates between companies the From and To currency codes are matched. This means that the historical rates only will be copied when values exist for both currencies. You can select more than one currency code. The From Currency Code and To Currency Code text boxes are enabled only if the option All Currency Codes is cleared.


Order for Currency Calculation in IBM Cognos Controller

Order of Currency Calculation

Steps completed as part of the currency translation:

  1. Currency conversion codes B, M, K, L and N are calculated for all accounts using these.
  2. Accounts with currency conversion code D are calculated.
  3. Opening balances, currency conversion codes C and I, are calculated.
  4. Historical rates, currency conversion codes E, F, and G are calculated.
  5. The currency conversion differences, currency conversion codes O and P are calculated.
  6. All accounts are summed to summation accounts.
  7. Currency conversion code A is calculated.
  8. All accounts are summed to summation accounts.
  9. Currency conversion code A2 is calculated.
  10. All accounts are summed to summation accounts.
  11. Values are copied from accounts with reconciliation rules to all accounts with the currency conversion codes U, V, X and Z.
  12. All accounts are summed to summation accounts.
  13. Currency conversion code A3 is calculated.
  14. All accounts are summed to summation accounts.
  15. The final currency conversion difference, the balance item, is calculated and booked using the rules in the general configuration.

Order for Currency Calculation in IBM Cognos Controller

Conversion of the Income Statement and Historical Rates

The account for total net profit in the income statement is 8999. Some accounts summing into 8999 are not converted at average rate, due to the use of fixed historical rates. The account for net profit in the balance sheet (equity specification) is 2099525. Account 2099621 is used for conversion differences on net profit in the equity specification, to allow the closing balance for net profit (in equity spec) 2099699 to be converted at closing rate. To achieve that,2099621 is using conversion code A (pointing to 2099699) and 2099699 is using conversion code B. However, the result will not be as expected.

Root Cause:

You can not use conversion codes A or O based on amounts where net profit BS is involved. This has to do with the calculation Calculation Order. In this case, the account 2099525 first is converted using M-rate to -63132 SEK.

The accounts wit A-code and O-code is calculated based on this value on 2099525. Later, the profit is moved from 8999 to 2099525 and replace the SEK amount with -61251. By then, the A-coded and O-coded accounts are already calculated.

Solution:

Avoid using historical rates in the income statement (or make sure the total net profit is converted at average rate).

Or

Use conversion code V or X (instead of M) for net profit in the balance sheet (here 2099525) and use conversion code A3 (instead of A) for the account holding the conversion difference for net profit (here 2099621).

Loading Historical Rates into Cognos Controller

The only supported method of updating "Historic Rates" is the method available under the "Group" menu and using a SQL script is not supported as it can lead to a corrupt database. Manually is the only correct way to enter the historical rates into Controller. You can copy them forward to other periods. For normal exchange rates you can import with a specification as external structures, in Transfer/External structures/Import external structures.

Unable to delete Historical Rates

You cannot delete the last occurrence of the account 'XXXXXX' in the historical rate data entry screen. Change the values displayed to zero. The line cannot be deleted as the data entry screen automatically displays all accounts for the selected form that are defined to hold historical rates.

Other considerations

If you want to store all historical amounts on a fixed period, you can define this period and actuality in the general configuration, Conversion tab. You should be aware of the fact that a too detailed set up of historical rates (for example on extended dimensions or journal numbers) will make the analysis of converted accounts more complicated. On the other hand, when entering historical rates for G coded accounts it is important to enter historical rates at appropriate detailed level. If you use journals the historical rates have to be entered at journal number level. If you use a more aggregated level to enter historical rates the matching against journals will go wrong and converted values will be miscalculated. This is also valid if you use accounts with extended dimensions or counter company. In the Account column, all accounts that use historical rates (currency conversion codes E, F or G), according to the account structure are displayed, with the exception of the accounts that have references to other accounts. If registered values are not specific enough and the system does not know what rate to apply for a certain item, B, M or D rate will be used. The rate chosen depends on how the account is set up in the account structure. Closing rate B will be used for accounts with currency conversion code E, Average rate for the year (M), will be used for accounts with currency conversion code F and accounts with currency conversion code G will be converted according to the rules for currency conversion code D.


Historical Currency – Examples

Example 1 – Historical Rates with only two roll ups

The example will show Company #1204 – Sweden wand the entering of historical Rates. Company #1204 is connected two structures.

Group 1200 (Legal Currency is EUR$)
Group 1000 (Legal Currency is GBP$)

Since, Company #1204 rows up into two different groups this will required that the historical rates to be enter in each group the company rolls up.

1204 => 1200 => 1000
SEK => EUR => GBP

Figure 2 shows the Trial balance for Company 1204
Figure 2 shows the Trial balance for Company 1204

The focus of this example will be on account B50020. This particular account has Currency Code “G”, which is Fixed Historical/Period Average rate.

The based currency level in this example is GBP (Great Britain Pounds).

Figure 3 shows the currency code and the based currency
Figure 3 shows the currency code and the based currency

The period used will be 0701 (January 2007).

Figure 4 shows the currency register for 0701 (January 2007)
Figure 4 shows the currency register for 0701 (January 2007)

The focus will be SEK/EUR rates. Since, Company #1204 rolls up in GR1200 (EUR) and then GR1200 rolls up into Gr1000 (GBP).

The initial recording of the 50020 (internal share premium account was the following.

Table 1 shows the Currency Translation Calculation and the required amounts to be set up in the historical rate register
Period'0701
AccountLCEurRateGBPRate
50020($80,004.00)($10,784.00)$7.42($6,740.00)$11.87
Currency RatesHistorical RatesB Rate for 0701
SEK$11.87$11.36
EUR$1.60$1.60
Euro => GBP$7.42$7.10
GBP($80,004.00)$11.87($6,740.00)
Figure 5 shows the EUR $10,784 being set up in the historical rate register
Figure 5 shows the EUR $10,784 being set up in the historical rate register
Figure 6 shows the GBP $6,740 being set up in the historical rate register
Figure 6 shows the GBP $6,740 being set up in the historical rate register
Figure 7 shows the trial balance with drill down after the currency translation was ran. Account #B20020 shows -10,784, which is SEK converted to LE (EUR) based on the historical rate in the historical rate register.
Figure 7 shows the trial balance with drill down after the currency translation was ran. Account #B20020 shows -10,784, which is SEK converted to LE (EUR) based on the historical rate in the historical rate register.
Figure 8 shows the the SEK converted to GBP. GBP is the top level reporting currency. The report used the Currency Conversion Report
Figure 8 shows the the SEK converted to GBP. GBP is the top level reporting currency. The report used the Currency Conversion Report

Note

B50020 is calculated at the historical rate based in the historical register (-6,740).

B50010 is calculated with the “B” code or the closing rate. The closing rate in this example was $11.36 (see Figure 4).

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=547920
ArticleTitle=IBM Cognos Proven Practices: Guidelines to Using Historical Rates in IBM Cognos Controller
publish-date=09272010