currency conversions in planning

Guideline

Currency Conversion in IBM Cognos Planning

Product(s): IBM Cognos Planning

Area of Interest: Modeling

Currency Conversion in IBM Cognos Planning 2

IBM Cognos Proprietary Information

Copyright Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company. 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. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to the information contained in this document will be documented in subsequent editions. This document contains proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos. Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBM and the IBM logo are trademarks of International Business Machines Corporation in the United States, or other countries, or both. All other names are trademarks or registered trademarks of their respective companies. Information about Cognos products can be found at www.cognos.com This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to [email protected] .

mailto:[email protected]



Contents 1 INTRODUCTION............................................................................................ 4 1.1 PURPOSE ..............................................................................................................4 1.2 APPLICABILITY .......................................................................................................4 1.3 EXCLUSIONS AND EXCEPTIONS....................................................................................4 2 SELECTED IFRS RULE SUMMARIES............................................................... 4 3 CURRENCY CONVERSION METHODS IN A CENTRALIZED ENVIRONMENT... 6 4 CURRENCY CONVERSION METHODS IN A DE-CENTRALIZED ENVIRONMENT19



1 Introduction

1.1 Purpose This document provides best practice guidelines for designing IBM Cognos Planning models at organizations that have currency conversion requirements.

Increased globalization has resulted in more business organizations with international operations. This has lead to an increase in the number of customers who require the ability to plan, budget, and forecast in multiple currencies. IBM Cognos Planning allows multi-national organizations to perform bottom-up planning throughout the entire organization with the use of IBM Cognos Planning Contributor. IBM also provides these organizations with the ability to perform top-down planning and provide global planning assumptions through the use of IBM Cognos Planning Analyst. Two approaches to currency conversion will be presented in this document: 1) Centralized - which will leverage the top-down planning capabilities of Analyst; and 2) De-centralized which will leverage the bottom-up capabilities of Contributor.

1.2 Applicability IBM Cognos Planning v. 7.3 through v. 8.1

1.3 Exclusions and Exceptions None identified

2 Selected IFRS Rule Summaries

The International Financial Reporting Standards are not directly applicable to budgeting, forecasting, or planning data as it is forward-looking and not subject to the rigors if the IFRS rules for reporting of actual historical financial reports for public, investor, governmental, and regulatory bodies. This information is supplied for context and may be relevant if IBM Cognos is an intermediate source for actual data supplied to the financial reports. International Financial Reporting Standards (IFRS) represent a set of rules that publicly held multi-national organizations, and even certain non-public multi-national companies, must adhere to in order to accurately represent their financial performance and standing. These rules should be taken into account when designing multi-currency IBM Cognos Planning models that will be used as a source for reporting actual financial results. However, the majority of the time the rule summaries below labeled 2 and 3 will apply to forward-looking planning, budgeting, and forecasting data. Since there is no IFRS requirement to provide plan, budget, or forecast data to regulatory agencies and investors, the IBM Cognos Planning data will fall under the “Convenience Reporting” IFRS rule if it is included in a financial statement at all.



If the IBM Cognos Planning model will not serve as an intermediate or direct source for reporting actual financial results and the planning data will not be included in any financial statements issued to regulatory agencies or investors, then there is no applicability of the IFRS rules on currency conversion other than to maintain consistency between plan and actual for better variance analysis.

The following paragraphs are a summary of the IFRS (International Financial Reporting Standards) issued by the IASB (International Accounting Standards Board) related to the reporting of currency conversions on financial statements. These summaries are available at IAS Plus (http://www.iasplus.com/standard/standard.htm) a website provided by Deloitte.

1) Translation from the Functional Currency to the Presentation Currency

The results and financial position of an entity whose functional currency is not the currency of a hyperinflationary economy are translated into a different presentation currency using the following procedures: [IAS 21.39]

• assets and liabilities for each balance sheet presented (including comparatives) are translated at the closing rate at the date of that balance sheet. This would include any goodwill arising on the acquisition of a foreign operation and any fair value adjustments to the carrying amounts of assets and liabilities arising on the acquisition of that foreign operation are treated as part of the assets and liabilities of the foreign operation [IAS 21.47];

• income and expenses for each income statement (including comparatives) are translated at exchange rates at the dates of the transactions; and

• all resulting exchange differences are recognised as a separate component of equity.

Special rules apply for translating the results and financial position of an entity whose functional currency is the currency of a hyperinflationary economy into a different presentation currency. [IAS 21.42-43]

2) Disclosure of key sources of estimation uncertainty.

Also new in the 2003 revision to IAS 1, an entity must disclose, in the notes, information about the key assumptions concerning the future, and other key sources of estimation uncertainty at the balance sheet date, that have a significant risk of causing a material adjustment to the carrying amounts of assets and liabilities within the next financial year. [IAS 1.116] These disclosures do not involve disclosing budgets or forecasts.

3) Convenience Translations

http://www.iasplus.com/standard/standard.htm



Sometimes, an entity displays its financial statements or other financial information in a currency that is different from either its functional currency or its presentation currency simply by translating all amounts at end-of-period exchange rates. This is sometimes called a convenience translation. A result of making a convenience translation is that the resulting financial information does not comply with all IFRS, particularly IAS 21. In this case, the following disclosures are required: [IAS 21.57]

• Clearly identify the information as supplementary information to distinguish it from the information that complies with IFRS.

• Disclose the currency in which the supplementary information is displayed.

• Disclose the entity's functional currency and the method of translation used to determine the supplementary information.

3 Currency Conversion Methods in a Centralized Environment

Centralized currency conversion is used as the currency conversion method at the majority of IBM Cognos Planning customers. The benefits of centralized currency conversion are: 1) Remove currency conversion option as a variable from end users in order to reduce a source of error; 2) Reduce the size and complexity of the Contributor model; and 3) Global variables are more consistently managed at a top level rather than a bottom-up level.

In a centralized approach, currency rate tables are maintained by the Analyst model administrator and the application of those rates to the various cubes and dimensions are also controlled by the Analyst model administrator. The currency conversion rates are pushed down to an assumptions cube to each hierarchical node in the model. A dimension that holds at least 3 items (Local Currency, Exchange Rate, and Reporting Currency) is required to convert the Local Currency to the Reporting Currency. The calculation option should be set to “Force to Zero” (or Weighted Average) for the Exchange Rate so that parent-level nodes do not display or use aggregated exchange rates. Also, the Reporting Currency can contain the calculation that either multiplies or divides the Exchange Rate (depending on numerator/denominator currency used to develop the exchange rates) against the Local Currency to calculate the Reporting Currency. Finally, the calculation priority for the Reporting Currency calculation should be set to “Low”, otherwise the conversion calculation can potentially override other dimensional calculations and produce undesired results

An example of the currency conversion calculation dimension is shown below:


The application of the currency conversion dimension is usually done on the reporting cube or summary cube. Detailed data collection cubes in a Contributor model usually do not need to be converted to a reporting currency.

An example of a reporting/summary cube that has had the currency conversion dimension applied to it is shown below:



In the above example, the currency conversion calculation dimension was applied directly to a summary/reporting cube. However, the currency conversion dimension can also be applied to an intermediate calculation cube and linked to a non-calculating reporting cube.

The currency exchange rates are typically contained in a dedicated assumptions cube, an example of which is shown below:

The exchange rates may be used by several models in an enterprise-wide deployment of IBM Cognos Planning, so this table would be a good candidate for storage in a common library.

Populating exchange rates for forward-looking planning, budgeting, and forecasting models is usually an exercise in estimates. “There is no reliable method available to forecast exchange rates. Paul Krugman and Maurice Obstfeld write in their book "International Economics: Theory and Policy" (New York, HarperCollins:1994): If exchange rates are asset prices that respond immediately to changes in expectations and interest rates, they should have properties similar to those of other asset prices, for example, stock prices. Like stock prices, exchange rates should respond strongly to "news," that is, unexpected economic and political events; and, like stock prices, they therefore should be very hard to forecast.”



The graph above illustrates the challenges in developing an accurate forecast for exchange rates for the currency rates table in the IBM Cognos Planning Model. Since there is such variability and lack of precise predictability, any plan that utilizes exchange rates will inevitably have a variance from actual performance due to the exchange rate variances. This variance can be isolated in order to analyze the actual-to-plan variance due to controllable factors (efficiency, price of inputs, etc.). Options for reporting on variance due to foreign exchange will be discussed in a separate document.

Once the currency conversion rate table has been established, the next step is to link this to the currency conversion rate dimension item in the cubes that have been identified for currency conversion.

Several options exist to set-up this link. If the geographic dimension of the planning model is stable and there is only the need to covert from a local currency to a single reporting currency then a D-Link from the currency table to the e-list (a geographic dimension, in this example) could be used. This link could be Analyst-to-Analyst or Analyst-to-Contributor. A simple example is shown below:



If there is a need for more dynamic switching between reporting currencies then a D-Cube Allocation Link can be used in conjunction with a D-Cube formatted D-List. This will allow the Analyst model administrator to switch reporting currencies on the geographic dimension easily vis-à-vis a drop-down currency list selection. An example of a currency conversion D-Link using a D-Cube Allocation is shown below:



Since this is a centralized approach to currency conversion and these links will be run by the Analyst model administrator, remember that if this model library is the basis for a Contributor application, do not to add these links to the Update Links Table for the target D-Cube when asked.

With this approach, the following results occur when selecting the CAD currency for Australia and running the links:



The flow of a currency conversion model can be from a Contributor application that was designed purely for local currency input to a separate Analyst model that was designed to take those Contributor local currencies and run the conversion. From there the converted data can either be sent back to a summary/reporting cube in the Contributor model or to a separate reporting model. This is illustrated in the diagram below:



The benefits of this approach are: • Less complex and lighter Contributor model • Standard and consistent application of currency rates • Model administrator has sole accountability for currency rates

Another centralized approach to currency conversion is to simply link the exchange rates from a dedicated exchange rate cube in a library that is separate from the Contributor model’s basis library. This approach differs from Option 1 (above) in that the currency conversion calculation occurs in the Contributor model. This approach is illustrated in the diagram below:



This option may be considered more of a hybrid between centralized and de-centralized, since the calculation is occurring on the Contributor grid. In this scenario, the Contributor user does not choose a currency; one is assigned to the user based on the Analyst model administrator’s criteria.



An example of a Contributor model built with this approach is presented in the screenshots below:

1) The Total Company Aggregation of January in Local Currency:



2) The Total Company Aggregation of January in Local and Reporting Currencies:



3) An individual Contributor (Australia) can enter data in Local Currency and see the results in Reporting Currency (note that the Contributor User has no control over the Exchange Rates or the currency assignments):



4) Each hierarchical e-list node is totaled correctly as shown below where the Europe node correctly aggregates the Local and Reporting currencies:



5) A European child node (England) is shown below:

The benefits of this approach to currency conversion are: • Exchange rates for all models can be maintained in a single library

location • Model administrator maintains control over currency assignments and

exchange rates • End users can see the reporting currencies as they view the

summary/reporting cubes in the Contributor model.

4 Currency Conversion Methods in a De-Centralized Environment

In a de-centralized approach to currency conversion, Contributor users are given more control over the details of the currency conversions of their specific planning data. These approaches can vary from: allowing the Contributor user to choose the specific reporting currency; to having the Contributor user input the currency conversion rates to be used for the conversion calculation. In general, the more de-centralized the approach, the more variability the results will be. As most organizations employ bottom-up enterprise-wide planning to capture the variations in performance across the organization, one could suppose that an organization may also want to capture different outlooks on future exchange rates. However, most


Currency Conversion in IBM Cognos Planning 20 organizations prefer to have centrally controlled global variables such as: exchange rates, inflation rates, interest rates, etc. set by the top of the planning organization rather than at the lower levels. If global variables are changed at the individual contributor level, comparisons between and among the various plans become difficult. For various reasons, some organizations may want to allow plan contributors to have some control over the currency conversion for their plans. The diagram below shows the data model flow of a de-centralized currency conversion approach:

This flow is fairly simple as the calculations and currency rate tables are held within the Contributor model. Rate tables are created as assumption cubes and any exchange rate changes would be made by the Analyst model administrator. This is a level of control still maintained by the administrator in this de-centralized/hybrid approach. A fully de-centralized approach would allow end users to input exchange rates as well as decide on individual currencies for the conversion calculation. Below are some screenshots of a Contributor application that takes this approach in it’s design:


Currency Conversion in IBM Cognos Planning 21 1) This model includes an assumptions cube that is populated by the Analyst model administrator. QTR-1 totals can be hidden using an Access Table as they are not used in any calculations.

2) The end user has the ability to choose which currency rate to apply when calculating the Reporting Currency.


Currency Conversion in IBM Cognos Planning 22 3) Planning hierarchy calculations are correctly totaled in the de-centralized/hybrid approach:


Currency Conversion in IBM Cognos Planning 23 4) As more control is given to end users in the de-centralized approach, more errors can occur making the plans difficult to compare across the organization.

The benefits of this approach to currency conversion are:

• End users have more control over the currencies used for conversion to Reporting Currency

• Model administrator maintains control over exchange rates • End users can see the effect of different currencies as they view the

summary/reporting cubes in the Contributor model.


currency conversions in planning

Documents