Many businesses want to see measures across multiple currencies, for example: sales for all countries or customer balances for all account currency types. Typically when dealing with multiple currencies a ‘base’ currency to report on is chosen to make life easier (you can’t simply add Euro sale values to Sterling sale values and get the correct total sales answer). This means data needs to be converted to base currency so we can accurately see how we are doing.

Currency exchange rates are by no means static, so movement in exchange rates can influence your measures.

For example, if you wanted to see total balances across months you might see something like this:

Looking at this we can see that May 2009 was a great month!…. Or was it?

Well lets look at the raw data that makes this chart.

You can see in May 2009 we actually lost €100,000. The above report paints a very different picture to what actually happened! The reason why the chart shows such a large jump is that Sterling became a lot stronger against the Euro giving the illusion of a good month, when really it wasn’t.

This means we have to track Unrealised FX Gain and Loss to understand the numbers better.

Depending on whether you are using ‘End of Day’ or ‘Start of Day’ exchange rates this is done differently.

End of Day Rate

To calculate unrealised FX using end of day rates use this formula:

(Today’s Measure @ Today’s Exchange Rate) - (Today’s Measure @ Previous Exchange Rate)

Start of Day Rate

To calculate unrealised FX using start of day rates use this formula (rates should be in direct quotation e.g. local ccy value * rate = base ccy value)

(Today’s Measure - (measure delta)) * (exchange rate delta)

Once unrealised FX is calculated we can show a slightly different report:

We will use the Balance £ – Unrealised FX Accumulation to display in our report, which gives us:

This shows something quite different from the report above, but it shows a more accurate visualisation of the numbers. It shows that May 2009 was actually a bad month.

Now that you understand the importance of tracking Unrealised FX you need to decide at what granularity to track it. I would recommend tracking it at the lowest level possible so that you can see it at any aggregation level. This makes your design a little more tricky but gets you the most flexibility.