Since dealing with multiple currencies has become commonplace in a global economy, it’s no surprise that currency conversion is needed everywhere in global organizations that implement and run SAP software solutions. While currency conversion was mainly the purview of the ERP system in the past, ERP systems no longer carry that burden alone. Today’s managers want reports on aggregated data in various currencies. Uncover the hidden complexities of currency conversion in management reporting, and discover easy-to-understand examples that help you communicate these complexities to project stakeholders to ensure that your solution will meet all multi-currency reporting requirements.
Key Concept
The currency conversion equation is: source amount × exchange rate = target amount. On the surface, it looks very simple, but the complexities of currency conversion lie primarily in the exchange rate, which changes over time, and in the level of aggregation of your data. Neither one are usually specified explicitly in high-level business requirements documents. The source and target currencies are easy to identify, but the ever-changing exchange rate causes problems. You have to consider the different exchange-rate types, including spot rates (the exchange rate on the period-ending date), monthly average exchange rates (the average rate across a month), constant exchange currencies (frozen or fixed currency exchange rates), and combinations of the various types of currencies in your aggregated data. They all increase your conversion’s level of complexity.
Today, software architects face the challenge of designing solutions across system boundaries, as well as across geographical and monetary boundaries. Their IT organizations urge them to use standard tools, rather than custom development, to design flexible solutions with a lower total cost of ownership (TCO). In this context, I focus on designing the currency conversions across different applications for management reporting purposes. While the mathematics behind currency conversion appear simple, many software solutions struggle to find a way to implement a cohesive currency-conversion software framework within their applications.
To this end, I provide some concrete examples of the complexity in currency conversion and how it can negatively influence project timelines, resources, and budgets. I explore the communication gap that can occur between business and IT in currency conversion using examples and challenges in sample tables that business users will find easy to understand. Along the way, I relate these examples and challenges back to various functionalities in SAP ERP and SAP NetWeaver Business Warehouse (SAP NetWeaver BW). One goal is to raise the awareness of potential gaps in the requirements stage and to give IT staff members the tools they need to address those gaps early in the project.
Note
As of the writing of this article, SAP has moved to a new naming convention for SAP NetWeaver Business Intelligence (SAP NetWeaver BI). This is now SAP NetWeaver Business Warehouse (SAP NetWeaver BW).
The following information unveils the complexities hidden in currency conversion requirements and provides easy-to-understand examples that help you communicate these complexities to business users. This article is not a how-to guide; rather, it is intended to make you aware of the questions around currency conversion that you should investigate early in the project to avoid a costly awakening later.
Prerequisites
The audience for this article includes project managers, business analysts, software architects, and developers. You should be familiar with the basics of financial statements and accounting. Knowledge of the functionalities of SAP ERP and SAP NetWeaver BW is helpful, too.
Business and IT Requirements
Currency conversion involves a variety of topics, such as arbitrage trading (i.e., the practice of exploiting imbalances among exchange rates in different markets by selling in one market and simultaneously buying in another, pocketing the difference between the rates as profit). This article, however, focuses on management reporting in different currencies (i.e., the seemingly simple translation of the numbers in a report from one currency to another).
Business requirements such as currency conversion that seem so simple at the beginning of a project can suddenly explode in complexity, creating the need for custom SAP solutions with a lot of code that might exceed both your budget and project deadlines. If these projects succeed, your custom SAP solutions won’t be maintainable and will have a higher TCO than solutions using standard functionality. You can avoid this situation, however, if you become aware of hidden complexities, such as coping with changing exchange rates in year-to-date (YTD) reporting, early in the project and you educate the project team and its sponsors about those issues using concrete examples. To illustrate the hidden complexities, let’s consider the following example requirements statement, similar to what is frequently found in statements of work (SOWs) or project objectives:
End users are expected to be able to report actual and planned data in the relevant local currency, as well as in the group currency, which is the United States dollar (USD, or $).
While this statement sounds deceivingly precise, it is far from providing developers with the level of detail they need to implement this requirement in a software solution. The procedural business complexities exist in that gray area between business and technology: too detailed for the business user, but too general for the SAP technology solution. The oft-quoted communication gap between business and IT is very real; business requirements documents often contain statements like the one above, describing something that looks simple at first glance, but later increases exponentially in complexity.
Currency conversion is a challenge for two main reasons: business-procedural complexities and software-related complexities.
Note
Symbols for the US dollar, London pound (GBP), and the Euro appear throughout this article. Depending on the font set as the default for your web browser, these symbols may not display correctly on screen or in print.
This article uses the following three types of currency in its examples:
- Transaction currency: The currency in which an individual sales transaction is recorded, such as Euros (€), for a sale to a European customer
- Local currency: The accounting currency of the selling business unit (BU) or company, such as Great Britain pounds (GBP, or £), for a BU based in London
- Corporate reporting currency: The currency in which the corporate headquarters operates, such as USD, for an organization headquartered in the United States
For example, consider that when a London BU sells a bike in Portugal, the transaction is carried out in Euros. However, the London BU’s local currency is the GBP, and the company’s corporate headquarters reports the same transaction in USD.
Note
Typically, profit and loss accounting performs its currency conversion using the average rate for the period, while balance-sheet accounts use the period-end rate for their currency conversion. So, for any currency-conversion software framework, it’s important to determine the currency-conversion rates (SAP R/3 table TCURR for exchange rates) according to the different types of exchange rates (SAP R/3 table TCURV for exchange rate types) to ensure that you get the desired numbers in the target currency.
The Communication Gap Between Business and IT
Let’s look at four concrete examples to examine those cases in which currency conversions can lead to reporting problems. These examples should help close the communication gap by handling any potential concerns with the relevant business users early in the project. You can use the tables illustrating each problem in the following challenge sections as a starting point for discussing and clarifying these challenges with the relevant business constituencies. You can use Microsoft Excel for this purpose. It’s a tool that business and technical users understand well, so it can be instrumental in bridging the gap.
These are the four currency-conversion challenges illustrated in this article:
- Model YTD reporting
- Exchange-rate gains and losses
- Exchange-rate changes in performance management
- Different exchange-rate paths
The Business Scenario
Assume that a mountain-bike manufacturer in the US has multiple BUs abroad, including one each in London and Hamburg. London sells bikes in the UK, as well as to a customer in Portugal. Hamburg services the German market. In this case, you use the following currencies:
- London: GBP
- Germany: Euro
- Portugal: Euro
Because USD is the currency for global management reporting, you also need to report everything in USD so that you can roll the numbers from across the world together into one corporate result and enable a comparison of different businesses in one common currency.
The bike manufacturer doesn’t have a single global ERP system; each BU has its own local ERP system (not necessarily SAP). To enable corporation-wide management reporting, the headquarters plans to implement SAP NetWeaver BW as its global management reporting data warehouse. The company envisions that SAP NetWeaver BW will gather all relevant financial data from the local BU systems and convert the data to USD according to global standard rules.
In the next section, I present the four challenges and the questions that you should initially address and clarify to ensure that the data warehouse project addresses the business requirements of converting the data to USD, according to global standard rules. For each challenge, I provide example numbers in tables — highlighting in gray the questionable results — and the issue surrounding them. As you work with your business users to address the results, you will find that the answers may drive a second set of questions, similar to those listed in the “Questions Arising from Challenge n” subsections that follow each challenge.
Note
As of the writing of this article, SAP has moved to a new naming convention for SAP NetWeaver Business Intelligence (SAP NetWeaver BI). This is now SAP NetWeaver Business Warehouse (SAP NetWeaver BW).
Challenge 1. Model YTD Reporting
Let’s consider London’s revenue to illustrate the first challenge: YTD reporting. For example, assume that London sold £500K worth of bikes in January and £600K worth in February. Also, assume that the January exchange rate is $1.50 to £1. In February, however, the GBP is much stronger at $2.00 to £1. This fact leads to the information in Table 1.
Revenue in GBP
|
£500K
|
£600K
|
£1100K
|
Exchange rate
|
$1.50/£1
|
$2/£1
|
$2/£1
|
Revenue in USD
|
$750K
|
$1200K
|
$1950 or $2200?
|
|
Table 1 |
Report YTD across currencies |
The question is what should you report as YTD in USD at the end of February: $1950 or $2200? If you add the monthly values in USD, you get $750K + $1200K = $1950K. However, if you convert the February YTD value of £1100K at the February exchange rate, you get £1100K × $2/£1 = $2200K.
Questions Arising from Challenge 1
Which value does the business user expect to see in the February YTD reporting: $1950K or $2200K? You need to discuss this question before you begin because its answer influences the solution’s design and potentially its cost. The answer determines what formula to use in your YTD calculations. Clarification of this value helps the IT team flesh out the following technical questions:
- Do you need to store YTD, period values, or both in local currency in the data model? If you store both, how do you ensure that the period values always add up and reconcile to the stored YTD values? To answer these queries, you have to model your InfoCubes to hold these results and you should include this design task in your project plan. It potentially affects the project timelines and solution complexity.
- Do you need to provide a reconciliation mechanism, especially when people back-post into previous periods? Depending on how automated a reconciliation is, it can take a lot of time to develop and test a reconciliation framework in SAP NetWeaver BW. Refer to the SAP white paper “How to Reconcile Data Between SAP Source Systems and SAP NetWeaver BI” on SAP SDN (www.sdn.sap.com – for which logon credentials are required) for more info.
- Do you need to configure on-the-fly currency conversion on the front end? If you do, you need to plan for it. The SAP Help site (https://help.sap.com) provides the details on the setups you need.
- Do you want to restate the values from previous periods according to the current exchange rate? Restatement of financial results is a common practice. It basically means that historic financial data is adjusted to reflect, for example, current exchange rates. If this is a requirement in your organization then you have different options, such master data driven modeling, dropping and reloading data or realignment loads of the InfoCube into itself. This is a common practice for realignments. You specify the InfoCube as its own data source and then load it into itself, restating the data in the transformations. If you need to restate previous values, you have to allocate some time and resources during the design phase to flesh out the details.
- How do you configure the SAP NetWeaver BW exchange-rate type in transaction RSCUR (Currency Translation Type)? Check out the F1 help in transaction RSCUR. It provides detailed instructions for each configuration option.
In essence, the users of the system should answer the question of what numbers they expect to see for YTD reporting. Their answers will also drive a lot of technical decisions.
Challenge 2. Exchange-Rate Gains and Losses
Changing exchange rates leads to gains and losses in the target currency if the conversion is performed in SAP NetWeaver BW for management purposes. How do you capture the gains and losses incurred due to changing exchange rates? For example, if a customer receives an invoice for £100 at the current exchange rate, the invoice amount equates to $200 of accounts receivable. When the customer pays the £100 a month later at the new exchange rate, it is worth $210 (Table 2).
Amount in GBP
|
£100
|
£100
|
Exchange rate
|
$1.50/£1
|
$2/£1
|
Amount in USD
|
$200
|
$210
|
Exchange-rate gain/loss in USD
|
|
$10 gain |
|
Table 2 |
Exchange-rate gains and losses |
If the invoice has cleared, the value should be $0 — in GBP and USD. How do you handle the $10 that resulted from the exchange rate differences?
Although the AR open item of £100 clears (in the GBP transaction currency), you still need to reconcile the exchange-rate gain in USD. Typically, you would address this kind of example while you implement transactional SAP ERP systems to handle currency fluctuations (constantly changing exchange rates) during day-to-day processing, such as clearing AR in all currencies. Figure 1 shows SAP General Ledger accounts for handling exchange-rate differences via SAP R/3 transaction OB_GLACC11 (Chart of Accounts). SAP General Ledger accounts are set up in SAP ERP to keep track of the gains and losses that exchange-rate fluctuations cause.

Figure 1
SAP General Ledger accounts for handling exchange-rate differences
However, when you convert data to a common currency in an enterprise data warehouse, you need to have a solid understanding of how the underlying systems handle exchange-rate gains and losses and how you want to handle the gains and losses that the conversion in SAP NetWeaver BW causes within the SAP NetWeaver BW system. For the mountain-bike company mentioned earlier, which performs all currency conversions for management reporting in SAP NetWeaver BW, management must decide how it wants to report the differences.
Questions Arising from Challenge 2
How does the end user expect to see gains and losses resulting from exchange-rate differences? Should they go into a separate account created in SAP NetWeaver BW? Is it sufficient to translate trial balances without considering the accounts for gains and losses to avoid the problem of gains and losses accounts in SAP NetWeaver BW, or do you need more detailed data to meet the users reporting requirements? The answers to the following questions can affect your data model:
- What accounts do you need to bring into SAP NetWeaver BW?
- What data granularity do you need to ensure that you can convert the appropriate data? Are trial balances sufficient, or do you need individual transactions?
- What calculations do you need to perform to capture and store variances in the database? Do you want to post variances against separate variance accounts created in SAP NetWeaver BW only — similar to those in SAP R/3 (Figure 1)?
- How should you configure your currency-conversion type’s time references in transaction RSCUR? Are fixed time references sufficient or do you need variable time references (see the “Back-Posting with Changing Exchange Rates” sidebar).
- How do you need to configure your exchange-rate types in transaction SPRO (SAP Reference IMG)? For example, can you use constant currency as a rate type? (For more information, see the “Constant Currencies” section later in this article)
In essence, your users need to clarify how they want you to report exchange rate gains and losses that currency conversions create in your SAP NetWeaver BW system. This ensures that your system is configured to your users’ needs.
Back-Posting with Changing Exchange Rates
A problem arises when data is back-posted into prior periods. When a financial period ends, there might be postings that haven’t been done yet. For example, you might receive an invoice or expense for January in February. The amount might need to be back-posted into January. This is particularly common during the “period-end close” or “financial close.”
Assume that in January 2008, there were postings yet to be made to close out 2007. These postings should be converted using the previous year’s exchange rate. SAP NetWeaver BW allows a time-dependent lookup of the exchange rate based on an InfoObject such as the posting date 0PSTNG_DATE (Figure A). The posting date is the date “for” which the transaction is posted (as opposed to creating the date “on” which the transaction is posted). You can define this time-dependent rate lookup via an InfoObject in transaction RSCUR. This way, entries made in January 2008 use the correct rate for conversion. Back-postings into 2007 use the effective 2007 exchange rate while entries for 2008 use the 2008 rates.

Figure 1
Use the posting date to look up the correct currency exchange rate
Challenge 3. Exchange-Rate Changes in Performance Management
If you assume that your organization has clarified the definition of YTD in Challenge 1, you can now compare actual YTD sales against the BUs’ sales-revenue targets in USD. Assume that both London and Hamburg had revenue targets of $10M in 2007. During this time, London had bike sales of £5M and Hamburg had bike sales of €8M. You use the respective year-end exchange rates (e.g., GBP to USD, Euros to USD) to convert these bike-revenue results back into USD. As it happens, £5M and €8M both equal exactly $10M in 2007, so both London and Hamburg BUs make their sales targets.
For 2008, assume that the revenue target is $10M again, and London and Hamburg have exactly the same amount in sales — £5M and €8M, respectively. However, the exchange rates from Euros to USD and from GBP to USD are different in 2008. In USD, London’s sales converted to $11M, while Hamburg’s revenues converted to $9.6M. Regardless of the fact that both BUs had exactly the same performance in 2008 that they showed in 2007, London appears to exceed expectations while Hamburg seems to fall short of its sales target in USD. In a peer-to-peer comparison when corporate headquarters compares those numbers in USD, the Hamburg BU appears to have fallen behind the London BU, when in fact their performance was exactly the same as in 2007 when they were both on target. Table 3 illustrates the situation.
Revenue in local currency
|
£5M
|
€8M
|
£5M
|
€8M
|
Exchange rate
|
$2/£1
|
$1.25/€1
|
$2.20/£1
|
$1.20/€1
|
Revenues in USD
|
$10M
|
$10M
|
$11M
|
$9.6M
|
Target set by corporate
|
$10M
|
$10M
|
$10M
|
$10M
|
Results
|
On target
|
On target
|
Overachieved
|
Fallen short
|
|
Table 3 |
Distortion of performance due to exchange-rate changes |
How do you want to separate performance-related changes in financial results from those that exchange rate variations cause (i.e., how do you ensure that you see London’s apparent over-achievement and Hamburg’s apparent shortfall are not the result of BU performance, but are merely exchange-rate differences)?
While this scenario is quite obvious and straightforward, in real life the problem of exchange rate changes diluting measures of performance can be much more convoluted. The changes can include multiple components, such as material prices, labor cost, and freight fares in different currencies and from many different sources, all of which make the delineation more difficult.
For example, consider the following, more realistic scenario. London and Hamburg both sell to customers in multiple countries in various currencies. First, they convert sales back into their local currency. Conversion rates might vary, for example, in the conversion from sales currency to local currency on the revenue side and in the conversion from local currency to corporate currency at headquarters.
To get a picture of a BU’s gross margin, consider the cost of goods sold (COGS). Let’s say that India produces all the bike products sold in Europe. Then you must also consider the conversion to the local currency for the cost of the product and for the shipping, which might be in yet another currency. Adding multiple plants to the scenario and considering raw materials, costs, and labor rates in different currencies and at different exchange rates leads to a complex situation containing multiple currency conversions via different methods on various local ERP systems.
Deciding how to handle the gains and losses from Challenge 2 can help minimize the impact of these fluctuations. However, exchange rates that vary from one day to the next (or more often) can create problems in performance measurement. The ability to separate performance (i.e., BU controllable actions) from exchange rate-related (i.e., non-controllable) issues is usually a key requirement for financial management reporting in multiple currencies. It’s important to emphasize this point explicitly to ensure that your management reporting solution enables business users to separate performance from simple fluctuations in the exchange rates. Using constant currencies is one option.
Questions Arising from Challenge 3
What is the potential impact of currency fluctuations on your ability to measure business performance? How do you handle these types of changes today? What improvements do you expect the new solution to present? Are restatements of financial results required to ensure year-on-year comparability of financial data? Do you use constant currencies? The answers to these questions can affect your solution design by raising even more inquiries, such as:
- Do you need to build and store restated values for management reporting to allow an apples-to-apples comparison of numbers across multiple years? If so, you should add the detailed design of this restatement mechanism to your project plan and resourcing estimates.
- What data granularity do you need to use to ensure that you can convert the data to allow you to separate performance-related results from exchange-rate impacts? Do trial balances provide the level of detail required to separate performance impact from the impact of Forex changes, or do you need to convert individual transactions?
- What calculations do you need to perform to capture and store variances in the database? For example, do you need to store the difference between results at constant currency conversion and spot rate?
In essence, your management needs to specify how it wants to separate performance-related results from exchange-rate fluctuations. Are constant currencies an option for your organization?
Challenge 4: Different Exchange Rate Paths
Another challenge is the concatenation of multiple currency translations. Let’s assume that London and Hamburg each sell €500K worth of bikes in Portugal. London’s local ERP system will convert the €500K into its local currency, GBP. Subsequently, these pounds are converted to USD when converting total revenue (including all other revenue in GBP) for global management reporting. Hamburg converts its Euros directly to USD. Now, that can lead to a difference between London and Hamburg, as shown in Table 4.
Transaction currency
|
€500K
|
€500K
|
€0
|
Exchange rate
|
£0.7/€1
|
1
|
|
Local currency
|
£350K
|
€500K
|
|
Exchange rate
|
$2/£1
|
$1.25/€1
|
|
Corporate currency
|
$700K
|
$6.25M
|
$0.75M
|
|
Table 4 |
Variation in USD reporting from different exchange-rate paths |
How does your organization want to handle the differences in corporate currency ($0.75M in the above example) arising from different exchange-rate paths when there is no difference in the actual transactions in Euros?
Note
This problem becomes amplified when you use different exchange-rate types (e.g., spot rate vs. monthly average) for the different conversion steps and when the conversions occur at different times. In this situation, you can potentially get big differences between the various exchange-rate paths (e.g., Euros via GBP to USD vs. Euros to USD).
Questions Arising from Challenge 4
What scenarios of concatenated currency conversions exist in your company (e.g., does the example illustrated in Table 4 occur in your organization)? What results do your users expect to see? Are they aware of the differences between different currency conversion paths ($0.75M in the example)? The answers to the following questions can affect the solution:
- Do you need to bring in transaction-level detail and convert from various transaction currencies to USD, rather than just converting trial balances in local currency and to USD? If the complete set of transaction data is available in SAP NetWeaver BW, you could convert all transactions using one method, avoiding the problem of different exchange-rate paths. However, it can be very complex and expensive — even impossible — to bring all the transactions of a global organization into a single data warehouse.
- Should you convert your currency to a reference currency rather than storing different exchange rates (see the “SAP Reference Currency” sidebar)?
In essence, you should investigate whether different exchange rate paths, as illustrated in Table 4, can lead to distorted results in your organization. If so, perhaps SAP reference currencies are a potential path for you to handle this issue.
SAP Reference Currency
One way to mitigate the conversion differences that the concatenation of different rates causes, is to use a reference currency in SAP. Rather than storing conversion rates for all currencies in SAP, you store only the exchange rates to the reference currency — in this case, USD. Then, all foreign currency translations are executed in two stages via the reference currency. For example, only the following rates are stored in your SAP system in table TCURR: GBP to USD has an exchange rate of £1/$2, while EUR to USD has an exchange rate of €1/$1.25. Note that there is no direct rate for GBP to EUR. To convert from GBP to EUR, the SAP system uses the GBP to USD rate and the inverse of the EUR to USD rate to calculate the exchange rate. So, GBP to EUR has an exchange rate of 2/1.25 = 1.6.
You can maintain a reference currency in table TCURV (transaction SPRO and then follow menu path General Settings > Currencies > Check Exchange Rate Types) (Figure A).

Figure 2
Maintain exchange-rate types with reference currency
The reference currency mechanism works in both SAP R/3 and SAP NetWeaver BW. Click the reference currency field, as shown in Figure A, and press F1 to get detailed documentation on how the reference currency works.
Constant Currencies
Most of the challenges with currency conversion exist because exchange rates change over time. Unit conversions, such as feet to meters, kilograms to pounds, or gallons to liters, have constant conversion rates, so they aren’t as big a challenge as currency conversions. What would happen if you forced a stability of exchange rates between currencies for a given period of time (i.e., the fiscal year)? Most of your challenges would simply go away. Some companies actually do freeze the currency-conversion rates for their fiscal year and use this frozen plan rate to manage performance at constant currencies.
While constant currencies address most of the exchange-rate challenges discussed above, they come with a new group of trials:
- Year-on-year comparisons and restatements
- How to handle aggregated data
- Gap between management reporting and reality
Let’s look at each one of these.
Year-on-Year Comparisons and Restatements
Freezing currency exchange rates for the fiscal year to enable management reporting without diluting the results with exchange-rate fluctuations attracts the challenge of year-on-year comparisons. Similarly, determining trends across multiple years is now more difficult unless you restate the previous years at the current year’s frozen rate. Consider the example in Table 5.
Revenue local currency
|
£5M
|
£5M
|
£0
|
Exchange rate (see note below)
|
$1.80/£1
|
$2/£1
|
|
Revenue at constant currency in USD
|
$9M
|
$10M
|
$1M or 11%
|
|
Table 5 |
Year-on-year comparisons when freezing exchange rates |
How does your organization want to handle the year-on-year differences in USD arising from the changing exchange rate? In the example, 11% is a material difference when there is no difference in the results in local currency. Financial restatements (i.e., restating 2006 revenue at the 2007 exchange rate) provide a common way of eliminating this problem and allowing an apples-to-apples comparison of 2006 and 2007 revenue figures.
Note
The historic USD to GBP exchange rates in 2006 and 2007 were:
Spot rate as of January 1
|
$ 1.72380/£1
|
$ 1.95910/£1
|
$ 0.23530/£1 or 13.7%
|
Average rate over the year
|
$ 1.84295/£1
|
$ 2.00181/£1
|
$ 0.15886/£1 or 8.6%
|
|
Table A |
Rule group transformation in SAP NetWeaver BW |
Regardless of which method you use to determine the frozen rate — spot rate or average rate — the requirement for restatement is obvious as an exchange rate impact of 13.7% and 8.6%, respectively, would dilute year-on-year comparisons.
|
To enable year-on-year comparisons, a management reporting solution using constant currencies requires a restatement capability. For example, to compare 2006 figures with 2007 data, you should restate 2006 figures according to the 2007 exchange rates — especially when handling multiple local currencies. So, you need to account for this in your project plan and resourcing when you design the reporting solution.
How to Handle Aggregated Data
When you gather data from various different local systems to enable corporation-wide management reporting, it’s virtually impossible to bring every transaction into the data warehouse — especially in large organizations with many different ERP systems. The complexity of such an endeavor can be enormous and so can the costs, significantly outweighing the benefits. Because you can book in many different currencies to a variety of accounts (such as AR), some conversion occurs in the local ERP systems. Also, when you deal with many small legacy systems, typically you only upload trial balances in the local currency — mostly via flat files. The ERP trial balances might already contain some currency conversions — typically, at the spot rate or the average rate — but not at the constant currency rate you defined in the global data warehouse.
For example, SAP ERP Profitability Analysis (CO-PA) doesn’t keep its value fields in transaction currencies. Usually, it uses only the currency associated with the company code and the operating concern currency (Figure 2).

Figure 2
Currency types as found in CO-PA
Tip!
Although the CO-PA value fields (accounts) are not held in transaction currency, CO-PA actually contains the transaction currency and the exchange rate (
Figure A). It’s possible to recalculate all CO-PA value fields in the transaction currency and then retranslate it at constant currencies for the target period. This process adds complexity — and thus cost — to the project.

Figure 3
Transaction currency and exchange rate in CO-PA Operating Concern
Table 6
Transaction currency in ERPs
|
€100
|
€100
|
€0
|
Rate in local ERP (not frozen)
|
£0.7/€1
|
£0.75/€1
|
|
Local currency (loaded into SAP BW)
|
£70
|
£75
|
£5 “baked” into the source data
|
Constant frozen rate for 2007
|
$2/£1
|
$2/€1
|
|
Corporate currency
|
$140
|
$150
|
$10
|
|
Table 6 |
Compare the different exchange rates in the source data at local currency |
How does your organization want to handle currency exchange-rate differences that are part of the trial balance data coming into the data warehouse (i.e., the £5 resulting in a $10 difference in the example above)? Are these differences material in your organization or can they be ignored?
In the example in Table 6, the trial balances in local currency that are loaded into the data warehouse already contain some currency fluctuations from the changes in the Euro-to-GBP rate. Unless you translate every transaction in every system around the world using the constant currency rate, some currency fluctuations still sneak into management reporting. The financial controllers should gauge whether these fluctuations are material before they implement a constant currency framework for management reporting. More often than not, though, these fluctuations are negligible.
Depending on how granular the incoming data needs to be to avoid diluted results due to currency fluctuations, the data staging needs to be designed for your company. For example, a trial balance flat file load from various local source systems typically involves considerably less work than loading all the transaction details.
Loading the company code currency from CO-PA is less complex than recalculating every value field into transaction currency based on the exchange rate field. Doing the latter translates directly into increased project complexity and, therefore, higher TCO down the line.
Gap Between Management Reporting and Reality
While currency fluctuations aren’t welcome in performance management because they dilute the results, at an overall corporate level, you need to see the aggregated results using effective exchange rates to understand how the organization is doing overall. In the final analysis, it doesn’t matter whether you earned a particular dollar because of better performance or currency fluctuations. Either way, the corporation’s profit is a dollar higher. This is also true when you try to reconcile management reporting (internal control reporting) with legal statutory reporting (external legal reporting), which is especially important in the US. Internal reporting is used to measure performance, while external reporting is a legal requirement. US Generally Accepted Accounting Principles (GAAP) and International Financial Reporting Standards (IFRS) are examples of external reporting standards.
So, your company needs to be able to convert the management reporting numbers from the frozen exchange rate to the effective exchange rate for all involved currencies to get an accurate picture of the overall numbers at the corporate level. For example, to gauge the impact of currency rate changes, consider the development of the exchange rate from USD to GBP over time (Figure 3).

Figure 3
Change of USD to GBP exchange rate over time
Regardless of whether you use the average 2006 exchange rate for a GBP-to-USD conversion or the January 1, 2007, spot rate as the frozen plan rate for 2007, the results for the London BU at frozen rates differ from the results at the effective rate at a later point in time (Table 7). The effective exchange rate is what the company uses to state its results – like the current spot-rate of USD to GBP rate. The frozen plan rate is the rate the company uses for performance management – usually determined at the beginning of the fiscal year.
Spot rate as of January 1, 2007
|
$1.95910/£1
|
$2.10500/£1
|
$0.14590/£1 or 7.4%
|
Average rate 2006
|
$1.84295/£1
|
$2.10500/£1
|
$0.26205/£1 or 14.2%
|
|
Table 7 |
Exchange-rate fluctuation challenges when using constant currencies |
Also, note that this example only considers one local currency: GBP. In reality, fluctuations can add up for the many different local currencies that the various BUs use — in this business scenario, the fluctuations for the GBP-to-USD conversion for the London BU as well as those for the EUR-to-USD conversion for the Hamburg BU. In essence, your currency conversion framework has to allow corporate to look at overall results at the effective rate, as well as at constant currencies to gauge the “Forex-Impact.” Otherwise, you wouldn’t have full visibility into how the company is performing (Table 8).
Local currency
|
£5M
|
€8M
|
|
Constant frozen rate for 2007
|
$2/£1
|
$1.25/€1
|
|
Corporate currency at frozen rate
|
$10M
|
$10M
|
$20M
|
Corporate revenue in USD at spot rate
|
|
|
What is the corporate result at spot rate?
|
|
Table 8 |
Corporate revenue in constant currencies and effective rate |
What is the total roll-up of revenue at the spot rate vs. the frozen rate? Without retranslating all local currency figures at their respective spot rate, you can’t answer this question. Thus, when designing a solution, you might have a requirement to account for reconversions of BU results at alternative currency conversion rates (such as spot rate or monthly average rate).
Technically, the solution for reconversion at the effective rate — as opposed to the frozen plan rate — is usually similar to the restatement solution needed to allow comparable year-on-year reporting described in the “Year-on-Year Comparisons and Restatements” section above. Thus, it makes sense to design these two functions, reconversions and restatements, together in your conversion framework using the same functions.
Complexity Simplified
You need to be able to communicate the complexity of modeling foreign currency conversions in SAP NetWeaver BW to non-technical project managers and business sponsors. This complexity lies mainly in the change of the exchange rate over time and the data aggregation level. Therefore, implementing a common currency conversion framework for management reporting can be a difficult task — especially if you are pulling together data from various systems in different currencies at different aggregation levels (e.g., trial balances, transaction level detail).
The challenges and examples in this article make it easier to explain to business users the decisions they need to make to ensure that the implementation of a consistent currency conversion framework. The various tables herein illustrate each challenge using intelligible examples with simple sample records. You can create these kinds of tables with examples for your organization easily in Excel. This facilitates the discussion with the business users to flesh out the correct answers to the challenges your organization faces. Excel models are a great tool for helping business users to specify their requirements explicitly at a fine-grained level; they make it non-ambiguous and easily understandable for developers and system implementers.
Jens Koerner
Jens Koerner is product manager, Mobile Platform, at SAP Labs. Previously, he was senior director, leading the SAP Enterprise Performance Management (EPM) and Business Intelligence (BI) Customer Solution Adoption team. He also was a senior director-level SAP enterprise architect and project manager focused on the development of SAP solutions such as SAP Business Warehouse (SAP BW) or SAP Business Planning and Consolidation (SAP BPC). He has led global implementations with various customers in the US, the UK, and Germany and was a member of the Inforte BI leadership team.
You may contact the author at jens.koerner@sap.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.