Currency & FX Conversion

Last updated on 2025-03-03

Overview

Variables, if set to Currency format, have a Currency drop-down where you can explicitly choose the Currency (e.g. USD, JPY, GBP), which will set the currency prefix symbol (e.g. $ ¥ £) or wording (e.g. AUD).

The currency setting on variables is useful for two main reasons:

  • Visual indication: Displaying clearly which currency each variable is in (in the variable icon, and in each cell).
  • FX conversion: Converting data in different currencies to the variable-set currency (i.e. for multi-currency / multi-entity consolidation).
Exchange Rate (FX) Conversion

When data that has a currency set is connected to a variable in a different currency, xP&A will automatically convert all data-connected values to the variable-set-currency:

  • e.g. if your variable is in AUD, but you connect data that is in USD, xP&A will convert the USD data values to AUD
  • e.g. if your data is in USD but you want to see how that converts to EUR, set the variable to EUR and xP&A will convert USD to EUR
Data Source Currency Settings
Shows the detail view of a data source. The blue arrow points to the currency settings Currency settings for a data source
  • For common data sources (like QBO and Xero) currency settings are automatically detected and set by xP&A.
  • For other data sources where xP&A can’t auto-detect the currency (e.g. Google Sheets), currency can be manually set (but currency settings are completely off by default).
  • When you create new variables from a data source with currency set, xP&A will set the variable's format and currency appropriately.

Only the user that created the data source can update the currency settings.

What Logic Does xP&A Use to Convert?

There are two different types of rates xP&A uses, Average and Final. These are shown (and set if user-editable) in the data source preview.

  • Average: the average FX rate during the period (month/week/day/etc, depending on the model granularity). This is the default for Profit & Loss data sources.
  • Final: the final FX rate of the period. This is the default for Balance Sheet data sources.

For example, in a monthly model, the average rate would be the average of the daily rates for the month, and the final rate would be the rate on the last day of the month.

You can see the FX rates for each period used by drilling-down into the cells of the converted variable. The rates are sourced from Open Exchange Rates, and are not adjustable.

Payroll/HRIS data sources do not have currency set because often different employees are paid in different currencies. Our Headcount wizard show how you to translate all employees into one common currency using a Currency dimension, and a user-populated FX rate variable. We do not recommend setting a data-source level currency for this data source, unless every employee is paid in the same currency.

For any periods where xP&A cannot access an historic FX rate, xP&A will carry the latest historic rate forward, and convert "future" data values at that rate. For example, in a monthly model, if there are historic FX rates until November 2023, but you have data available and connected for December 2023, xP&A will convert the December 2023 value using the latest rate from November 2023 (depending on whether data source is set to Average or Final FX rates).

If you use a currency that is not on our list (e.g. a certain cryptocurrency), you can select Custom (at the very bottom of the list) and set your own prefix/suffix to reflect the currency.

Shows a list of custom currencies set up by the user Working with custom currencies

If you have a Custom currency set, xP&A will not be able to do any auto-translation.

xP&A will take the average or final rate of the elapsed time period to-date. e.g. if there is data present for January 2024, but we are only at 15th Jan 2024, xP&A  will use the average or final rate for 1st-15th January 2024.

For any data items that are constant (i.e. non-time-varying), xP&A will convert these at the latest completed period's rate. For example, in a monthly model, if there are historic FX rates until November 2023, but the current date is 15th December 2023, xP&A will convert the constant value using the latest rate from November 2023 (depending on whether data source is set to Average or Final FX rates).

Lucanet xP&A uses the same logic for data connected to models without a time dimension (i.e. granularity of None).

Different Ways to Set Up Your Consolidation Models

There are many ways you can set up your consolidation in xP&A.

You can either have no entity breakdown (in which case you would drill-down to see detail at the entity level), or you can use dimensions to breakdown by entity (see below) which means you can easily visualise historics by entity, and forecast by entity, too.

Shows the category 'Marketing' that is broken down by Entity (UK, US), and data is connected at the entity level 'Marketing' is broken down by Entity (UK, US), and data is connected at the entity level

You can also have separate models for each entity, and then have a linked consolidation model that combines them together.

Note, however, that we do not recommend this, as (1) you will not be able to take advantage of our automatic FX conversion (unless you also connect the consolidated model to data from each entity), (2) adding extra entities would be manual (building an extra model, vs. simply adding an extra dimension item in the Entity dimension as per above).