How to...manage Conversion Rates
The world is getting smaller with businesses and organisations working across country borders more than ever before. With this globalisation comes data challenges that can be solved through a number of data preparation strategies. One of the most typical strategies is to determine currency conversion rates to ensure the financial numbers being analysed are consistently set when currency is being reported by a multinational organisation.
Conversion Rate challenges
Conversion Rates such as currency seems like such a simple thing to do. Multiple the currency rate on to the original value and you have the currency you required. Easy. Well not quite so. Depending on the level of accuracy you want, just taking a single, constant exchange rate might not be sufficient. Currency exchange rates fluctuate throughout the day, each day.
Trying to determine what rate to use may make a significant difference even on smaller transactions, added up over time, the small differences can lead to overstating results, or understating profits. Deciding on the frequency of the conversion rates (hourly, daily, weekly etc) is another important decision to make. Greater frequency assists with accuracy but makes the conversion rate tables more difficult to apply, especially when applying to different time periods.
Applying conversion rates consistently is also key, especially when working across a number of different business units. Let's look at how to do this within Prep.
How to apply Conversion Rates in Prep
Using the Week 6 Preppin' Data challenge in 2020, let's work through a currency conversion challenge.
Step 1 - Create a consistent granularity of data for the conversion
To do this we firstly need to understand the input data:
Step 2 - Join the datasets together
Using a Join Step in Prep, you can now join together the two different datasets (the source data and the conversion rates). If everything is in one table already, then you can move past this step.
Step 3 - Apply the conversion rate
Now everything is in one table, you can apply the conversion rate to the value you are converting.
This results in the conversion rate being applied to the value, making analysis for the user a lot easier than having to build separate calculations themselves in each Desktop workbook.
Step 1 - Create a consistent granularity of data for the conversion
To do this we firstly need to understand the input data:
Conversion Rate table
Sales values to convert
The datasets above are not initially ready for conversion as we don't have a field to join the datasets on. For these datasets the individual dates at the day level need to be 'rolled up' to the 'Week' column needed in the Conversion Rate table. This is a typical assessment to make as ultimately, you need to decide what frequency do you want to look to do the conversion and then find the rate within that period and ensure the correct conversion rate is held for that level.
The challenge set requires the best and worst case scenario of conversion to be found. This means the data preparation task is to find the minimum value of dollars that would be exchanged for one British Pound per week, as well as finding the maximum value per week. Other tactics used in such scenarios include:
- Finding the average value for a time period
The context for the question being posed is what will ultimately determine what technique you will need to deploy.
- Finding the 'Opening', or 'Closing' value as an indicator of the likely value that an exchange would have happened at in that time period.
Step 2 - Join the datasets together
Using a Join Step in Prep, you can now join together the two different datasets (the source data and the conversion rates). If everything is in one table already, then you can move past this step.
Step 3 - Apply the conversion rate
Now everything is in one table, you can apply the conversion rate to the value you are converting.
This results in the conversion rate being applied to the value, making analysis for the user a lot easier than having to build separate calculations themselves in each Desktop workbook.
Long term strategies for Conversion Rates
Frequency
One of the most challenging aspects of handling conversion rates, like currency exchange rates, is knowing what frequency the business will want to apply those rates. If a single value per hour, day, week, month, quarter and year, is held as a reference table this is very useful but that table is going to get pretty big, pretty quickly.
By only storing the data you are likely to need, or ensure the rules are clearly understood as to how to determine which is the correct rate to use, you will spend a lot less fixing mismatched values further down the reporting pipeline of work.
Holding History Tables
One of the most challenging aspects of handling conversion rates, like currency exchange rates, is knowing what frequency the business will want to apply those rates. If a single value per hour, day, week, month, quarter and year, is held as a reference table this is very useful but that table is going to get pretty big, pretty quickly.
By only storing the data you are likely to need, or ensure the rules are clearly understood as to how to determine which is the correct rate to use, you will spend a lot less fixing mismatched values further down the reporting pipeline of work.
Holding History Tables
The reference tables mentioned above can get big very quickly. Ensuring the history tables are kept somewhere centrally where every 'data prepper' can access is very important. Depending on how far back questions get asked will depend on how long history tables will need to get maintained for. Building robust history tables with the latest values incrementally updating overtime is a well worthwhile investment in time.
____________________________________________
Practice how to apply Conversion Rates in the following challenge:
____________________________________________
Practice how to apply Conversion Rates in the following challenge:
