Spreadsheet Add-in User's Guide for Excel

Working with Currency Conversions

Organizations with offices in different countries generally do business in the currency of the host country (known as the local currency). Such organizations must convert data entered in local currencies to a common currency for consolidation and analysis.

The Essbase Currency Conversion product can be purchased separately for Essbase. If your organization has purchased this product and has implemented a currency conversion application, you can take advantage of the powerful capabilities of Hyperion Essbase Currency Conversion. The Database Administrator's Guide describes how to design and implement a currency conversion application.

The following sections provide a brief tutorial for working with currency conversions:

• "Retrieving Currency Conversion Data"
• "Connecting to the Sample Currency Databases"
• "Performing Ad Hoc Currency Reporting"

Retrieving Currency Conversion Data

This section focuses on basic currency conversion concepts, including the easy-to-use Currency Report command.

A currency conversion application consists of two databases:

• A main database that contains data in local and converted values
• A currency rates database that contains exchange rates

Apply exchange rates from the currency rates database to local values from the main database to derive converted values. The Essbase server product installation includes a sample currency conversion application (installation options) that consists of two sample databases: a main database called Interntl and a currency rates database called Xchgrate.

The Sample Interntl database consists of five dimensions: Year, Measures, Product, Market, and Scenario. All but the Market and Scenario dimensions are identical to the Sample Basic database. The Market dimension includes Toronto, Vancouver, Montreal, France, Germany, Spain, and the UK. The Scenario dimension handles different currency types (such as Actual and Budget) in both local and converted currencies. In this database, all local currencies are converted to the common currency of U.S. dollars.

The Sample Xchgrate database, which is a subset of the main database, contains four dimensions:

• The CurTime dimension accommodates different exchange rates by month.
• The CurName dimension contains names of currencies from their respective markets.
• The CurCategory dimension contains the names of the various currency categories that may be applied to the categories of Measures. For example, one rate is applied to Profit and Loss items and another rate is applied to Balance Sheet items.
• The CurType dimension enables a currency database to contain rates for different scenarios, such as Actual and Budget.

Connecting to the Sample Currency Databases

To complete the following exercises, the Sample Interntl and Sample Xchgrate databases must be installed on the server. Contact the Essbase system administrator if these application and database pairs are unavailable.

To retrieve data from the Sample Interntl database:

1. Select Essbase > Connect.
2. Select the Sample Interntl database and click OK to complete the connection.

   The Essbase installation also includes sample Excel files that illustrate currency conversion concepts.

3. From the \Essbase\client\sample directory, open Local.xls.

   The worksheet contains actual (Act) and budget (Bud) data entered in local currencies for New York and Germany.

   Figure 166. Retrieving Local Data
   
   
   

4. From the \Essbase\client\sample directory, open Convert.xls.
5. Select Essbase > Retrieve.

   The worksheet contains values for Actual and Budget as they display after conversion.

   Figure 167. Retrieving Converted Data Values
   
   
   

   Notice that the worksheet contains data that is converted to U.S. dollars. Values for New York remain the same, but Germany values are converted. Essbase converts the values by using the exchange rates from the Sample Xchgrate database.

6. From the \Essbase\client\sample directory, open Rates.xls. Connect to the Sample Xchgrate database.
7. Select Essbase > Retrieve.

   Figure 168. Retrieving Exchange Rates from a Currency Database
   
   
   

The worksheet contains all possible combinations of exchange rate scenarios, categories, and types by month. Because this example converts to U.S. dollars (US$), the sample file contains a base rate of 1 for US$. Therefore, the local and converted figures remain the same for New York. Essbase converts the figures for Germany, however, by using data values in the currency database, as follows:

• Essbase divides data values from Actual by values in the Act xchg currency type.
• Essbase divides data values from Actual @ Bud xchg by values in the Bud xchg currency type.
• Essbase divides data values from Budget by values in the Bud xchg currency type.
• Essbase bases all figures in Convert.xls on the CurCategory of P&L and the CurTime of Jan.

Note:

A conversion can be defined as a multiplication or division operation on exchange rates. The definition is determined by the application designer.

Performing Ad Hoc Currency Reporting

A main database, such as Sample Interntl, usually contains values that are converted and stored in the database. You may want to perform currency conversions dynamically, as well. Essbase provides this capability with the Currency Report command. This command enables you to interactively change the currency rates and types applied to the retrieval.

To perform an ad hoc conversion on data in the Convert.xls file:

1. From the \Essbase\client\sample directory, open Convert.xls.

   The worksheet contains data that is already converted to U.S. dollars.

2. Select Essbase > Connect and connect to the Sample Interntl database.
3. Select Essbase > Retrieve.
4. Select Essbase > Currency Report.

   Essbase displays the Essbase Currency Report dialog box.

   Figure 169. Essbase Currency Report Dialog Box
   
   
   

   The Essbase Currency Report dialog box enables you to interactively modify the exchange rates applied to the retrieval. The box contains options for currency settings, names, categories, and years. For more information on these options, see the Essbase Spreadsheet Add-in online help.

   Note:

   The dimension names CurName, CurType, and CurCategory are default names for a currency database. The application designer can use different names for any of these dimensions.

5. Select the currency settings that you want to apply.

   For example, select CN$ from the CurName list box and Bud xchg from the CurType list box.

6. Click Apply to apply the settings.
7. Select Essbase > Retrieve to refresh the data in the worksheet with the results of the ad hoc conversion.

   Figure 170. Performing an Ad Hoc Currency Conversion
   
   
   

   Essbase converts the New York and Germany figures to Canadian dollars (CN$).

8. Click the Clear button in the Essbase Currency Report dialog box to disable currency reporting and return to standard retrieval mode.

   Note:

   Performing a currency report retrieval does not change values in the database. It simply performs a temporary conversion as part of the retrieval. Converted data values may not always balance, because the ad hoc conversion is performed on values that were previously calculated or previously consolidated in another currency.

If values must balance and verify, they must be converted to the target currency in the database, calculated, and retrieved. This procedure differs from the ad hoc currency conversion retrievals described in this section; see the Essbase system administrator for more information.