...

Currency Translation in IBM Cognos Controller Guideline

by user

on
Category: Documents
2

views

Report

Comments

Transcript

Currency Translation in IBM Cognos Controller Guideline
Guideline
Currency Translation in IBM
Cognos Controller
Product(s): IBM Cognos Controller
Area of Interest: Financial Management
Currency Translation in IBM Cognos Controller
2
Copyright and Trademarks
Licensed Materials - Property of IBM.
© Copyright IBM Corp. 2009
IBM, the IBM logo, and Cognos are trademarks or registered trademarks of
International Business Machines Corp., registered in many jurisdictions worldwide.
Other product and service names might be trademarks of IBM or other companies. A
current list of IBM trademarks is available on the Web at
http://www.ibm.com/legal/copytrade.shtml
While every attempt has been made to ensure that the information in this document
is accurate and complete, some typographical errors or technical inaccuracies may
exist. IBM does not accept responsibility for any kind of loss resulting from the use of
information contained in this document. The information contained in this document
is subject to change without notice.
This document is maintained by the Best Practices, Product and Technology team.
You can send comments, suggestions, and additions to [email protected]
Currency Translation in IBM Cognos Controller
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ................................................................................................................ 4
APPLICABILITY ......................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS ..................................................................................... 4
2
CURRENCY TRANSLATION ............................................................................ 4
2.1
2.2
2.3
2.4
2.5
2.6
2.7
CURRENCY CODES..................................................................................................... 4
CONVERSION METHOD ............................................................................................... 5
FUNDAMENTAL DIFFERENCES BETWEEN “I” AND “C” CONVERSION METHODS ........................... 6
CUMULATIVE TRANSLATION ADJUSTMENT........................................................................ 7
LOCAL VS. GROUP CURRENCY FOR INVESTMENT TEMPLATE................................................. 13
CURRENCY TRANSLATION STEPS ................................................................................. 18
D-RATE ANALYSIS .................................................................................................. 19
Currency Translation in IBM Cognos Controller
4
1 Introduction
1.1
Purpose
This document provides guidelines on applying and using currency rates and
translation in IBM Cognos Controller.
1.2
Applicability
IBM Cognos Controller
1.3
Exclusions and Exceptions
There are no known exceptions and exclusions at the time this document was
created.
2 Currency Translation
2.1
Currency Codes
IBM Cognos Controller includes different currency codes that must be
activated prior to using the codes in the company structures.
•
•
You define the base currency that other currencies are measured
against. If the code does not exist then you have the opportunity to
create your own code.
You must check the active column in order to activate the currency
code to be used in the company structures.
Currency Translation in IBM Cognos Controller
•
2.2
•
2.3
You can multiple or divide local currency by the base currency rates.
There is also an option to select the unit, for instance 1=1 or 1=1000
Yen.
Conversion Method
IBM Cognos Controller has the ability to use different rates when calculating
the currency translation. The list below is of currency rates that can be used
in IBM Cognos Controller:
•
5
Rates B, M, D can be entered in the currency register. You can
manually enter the rates or load the rates on a monthly basis using
staging tables or flat files or Business Viewpoint.
Rates, E, F, G rates are historical rates that are loaded into IBM
Cognos Controller using the historical rate register. If no historical
rates are entered, the B, M, D rates will be used.
D-Rate and G-Rate Comparison
Currency Translation in IBM Cognos Controller
6
The D rate makes the calculation when the LC value is 0. This is not a
desirable outcome when discovering that something has been booked on the
wrong ext. dimension. You get a currency translated value also at the wrong
ext. dimension, even though the entered LC value is 0.
Practically, all default values are calculated in the beginning of the currency
translation program, which is why you get a translated value according to the
D rate.
2.4
Fundamental Differences between “I” and “C” Conversion Methods
The C code is supposed to be used when there are no local values at all on
the account, most typically for Opening balance of currency translation
difference. The calculation of C code is always "detailed", meaning that the
translated value is retrieved from the same Journal type and journal number
in the Closing balance.
For the “I” code you can select if the calculation should be "detailed" or "not
detailed" (under General configuration). The “not detailed” translation
retrieves the rate ratio to use from the same Journal type in the Closing
balance. It’s recommend that the "not detailed" translation is used. The total
will be correct while the different journal numbers may differ depending on
how the different journal numbers are translated in the Closing balance. If
using the detailed translation, you have to be sure that the same journal
number with the same transactions exist both in the Opening and the Closing
balance, which sometimes is difficult. The best practice is to use the detailed
conversion to be sure to get the correct figures in OB per journal. In
combination with “do not change journal number” it is not more complicated
than not using it. If a journal is missing in OB, you will get a difference
anyway. And with the I-rate, it could be more difficult to find out what journal
is missing.
Moreover, both for the I code and the C code, there is a lot of matching done
between the Opening and the Closing balance, to find the correct rate ratio (I
code) or value (C code) to use for the Opening balance. Matching is done on
journal type, journal number (if detailed translation), extended dimensions (if
used), counter company + translation currency + counter dimension (if
intercompany account). For automatic journals matching is also done on
automatic journal type, counter company and the "ktypkonc" concept,
meaning the merge of the consolidation type and group. The ktypkonc
concept is important if there for example has been structural changes made.
In conclusion, there are a lot of things that happen when translating currency
with Opening balances. Experience is that a lot of problems with the I code
comes from journals that exist in the Closing balance if not copied to the
Opening balance. As the translation program calculates a rate ratio from the
journals that exist in the Closing balance, this might give a different rate than
expected for the Opening balance.
Currency Translation in IBM Cognos Controller
7
Please be aware of that when using the C code, Closing balance amounts in
translated currency will be copied to the Opening balance also when there is
no local amount on the account. This is due to the fact that the C code from
the beginning was meant to be used for opening balances of translation
differences, for which it does not exist any local value. What might happen
when you have structural changes is that the old translated value for the old
owning (talking about elimination) might be copied to the opening balance
even though you don't have values in the local currency. In these cases it is
better to use the I code (with not-detailed translation).
2.5
Cumulative Translation Adjustment.
The cumulative translation adjustment comes into play when the currency
method used is Method#1. This method is referred to as the current method
of translation financial Statements.
•
•
•
•
All assets and Liabilities of the foreign entity are translated at the
current rate on the date of the balance sheet; this preserves the
relationship between all the balance sheet items that formerly existed
in the foreign currency
Share Capital is translated at Historical Rates
All revenues and expenses are translated using the average rate. In
the case of IBM Cognos Controller it is recommended using the “M”
rate and not “D” rate. However, the “D” rate is predominantly used
when translated income statement results using period average rate,
i.e. monthly average rate.
Retained Earnings will become a blended rate
In IBM Cognos Controller there are several ways to handle currency
translation. Some companies may require that they track the changes in rates
within the various specification, i.e. fixed assets, debt, etc.
Below is an example of Fixed/Intangible Assets being set up to translate base
on the code”A”
Currency Translation in IBM Cognos Controller
Currency Method for B303500030
Notice the rate in this example is “A” (difference between the
closing/average rates and converted amount).
Currency Register:
8
Currency Translation in IBM Cognos Controller
Goodwill Specification
Goodwill FX Calculatiion
The ending, balance sheet rate was 1.1564 and balance sheet rate for 0412
was 1.2354. So, there is FX adjustment of $966.80.
9
Currency Translation in IBM Cognos Controller
10
There is at least one issue when storing the values in group currency, it’s that
Controller will not take into account any historical values mention and will
always convert your figures at closing rate.
Cumulative Translation Account
As part of calculating Cumulative Translation account. There are different
ways to setting up and using the translation account. The recommended
practice is to use movement extensions/account (See below example)
3600
3600010
3600020
3600030
3600040
Cumulative Translation Adjustment
Opening, Balance
Additions
Other
Manual Adjustment
In this particular example the account will set up as an integrated movement
account, which means the ending balance sums up to the main account.
The CTA-movements on all other Equity-accounts, therefore you do not build
movements on the CTA-accounts itself. This gives you the possibility to
analyze the CTA per Equity-account. The currency difference from E150 gets
a separate movement in the currency difference part of Retained earnings.
This model is used in Sweden and Switzerland.
Currency Translation in IBM Cognos Controller
11
Once you have define the Cumulative Translation Account then you will be
required to defined the account in the General Configuration >
Reconcile 2 tab – see below print screen
The currency difference in General configuration gets a separate movement
in Retained earnings – this row should always be near 0, since all other
currency differences have been taken care of through the account structure.
When the system calculates the translation and differences will then be
posted to the account in the General configuration.
This particular example all the companies in this company structure all used
Method 1 or the Current Method for translation.
Currency Translation in IBM Cognos Controller
Example 1 – Currency Translation Adjustment
The only difference in this example is the rates used to convert the income
statement profit.
12
Currency Translation in IBM Cognos Controller
13
Actual Foreign Currency Translation
2.6
Local vs. Group Currency for investment template
There is an option to activate an automatic journal that will handle the
currency translation.
Control table#150 creates an automatic journal with a conversion difference
relating to the investments. Automatic journal type 18 is used here.
Currency Translation in IBM Cognos Controller
14
This automatic journal is useful both when investments are stored in the local
currency and when they are stored in the group currency. If you store
investments in the local currency, you may need to adjust the shareholdings
in subsidiaries. If you store investments in the group currency, you may need
to adjust the share capital. You can also follow up on conversion differences
on other equity accounts. In both cases, the original investment by the
parent is compared to the acquired amount in local currency converted to the
current rate (which rate depends on the coding of the accounts).
A pre-requisite for calculating currency conversion difference in investments
is that you enter accurate accounts used in the investment register ("From
Accounts" in the control table) in both the company’s local currency and the
parent’s currency (or the selected currency if that differs from the parent’s
currency).
Define Control Tables - Conversion Differences in Investments
If you store investments in the local currency, LC, this is where you specify
the accounts used for investments. Any conversion differences will be posted
to another account, so that the investment accounts do not contain values
when the group is consolidated. If you store investments in the group
currency, GC, you specify the equity accounts you want to calculate
conversion differences on. The share capital account is normally used, but
other accounts may also be used.
The following example demonstrates how E150 is set up to handle currency
adjustments for the investment account.
You will need to activate and configure E150 in order for it to work properly.
Currency Translation in IBM Cognos Controller
You will then need to define the control table –
15
Currency Translation in IBM Cognos Controller
16
After the control table has been configured with the correct accounts. The
data then will flow based on the account structure/currency codes. In this
particular example there are no movement extensions or historical rates used
on the equity account.
1. Elimination of the investment – the initial investment for this company is
recorded as 742,993 or in the parent’s currency. In this particular case
the rate used is EURO dollars.
2. Elimination of the equity – in this case the elimination of equity is based
on the common share account only. There could be other accounts that
need to be eliminated, so you will need to bare this in mind when you
built the original control tables for E150.
Currency Translation in IBM Cognos Controller
17
The actual calculation based on the account structure is “B” rate or in this
case would be
3. Conversion differences on the investment
Currency Translation in IBM Cognos Controller
18
The FX/Inv adjustment for the period is calculated in this example the
following way:
-Notes• It is important to understand the account structures and currency
rates used for equity, investment, etc. The set up will impact on how
the E150 Control Table is configured and used.
• If movement accounts/extensions must all be considered when setting
up the investment elimination control tables.
• When storing the values in group currency will not take into account
any historical values and will always convert your figures at closing
rate.
2.7
Currency Translation Steps
Currency translation is performed by specific steps in IBM Cognos Controller:
• Currency conversion codes B, M, K, L and N are
calculated for all accounts using these.
• Accounts with currency conversion code D are calculated.
• Opening balances, currency conversion codes C and I, are calculated.
• Historical rates, currency conversion codes E, F, and G are calculated.
• The currency conversion differences, currency conversion codes O and
P are calculated.
• All accounts are summed to summation accounts.
• Currency conversion code A is calculated.
• All accounts are summed to summation accounts.
• Currency conversion code A2 is calculated.
• All accounts are summed to summation accounts.
• 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.
• Currency conversion code A3 is calculated.
• All accounts are summed to summation accounts
• The final currency conversion difference, the balance
item, is calculated and booked using the rules in the general
configuration
Currency Translation in IBM Cognos Controller
2.8
19
D-Rate Analysis
IBM Cognos Controller calculates the individual period movements, currency
converts them and then accumulates the individual values to arrive at the
converted year to date values which are stored in the database tables.
•
M-rate is based on yearly average rate. So, depending how the m-rate
is determined and how it relates to the d-rate calculation over time it
may cause some differences between the translated amount.
Fly UP