Currencies

Overview

Currency conversions in spreadsheets have traditionally been complex, but they are fully automated with Francis.

You might need to convert currencies if:

1. Your accounting system’s functional currency differs from the presentation currency you want to use for modeling and reporting.

2. Your organization includes multiple legal entities with different functional currencies, requiring a unified presentation currency for consolidation.

Francis allows you to convert currencies from connected data sources, simplifying consolidation, modeling, and reporting.

Francis' methodology complies with IAS 21, “The Effects of Changes in Foreign Exchange Rates. The currency features are currently in beta and are subject to change throughout Q4 2024.

Basics

Choose your exchange rates

You can choose between market rates or custom rates. All exchange rates are set monthly. To enable currency conversion for actuals from connected accounting systems, add a currency data source via Settings > Integrations.

Market rates

Market rates are automatically imported from the European Central Bank (ECB) and are the preferred choice for most users.

Monthly closing rates are imported directly from the European Central Bank.

Monthly average rates are calculated as the arithmetic mean of daily closing rates, i.e., the sum of the month’s daily closing rates divided by the number of days in the month.

If you need further details about the market data source, please contact us at [e-mail].

Custom rates

Custom rates are manually entered and can be helpful if your organization is part of a larger group that uses standardized exchange rates.

For custom rates, you must specify a currency cross (e.g., EUR/USD) and input the monthly exchange rates.

The first exchange rate you input will be extrapolated backward. For instance, if your first entry is January 2020 at 7.5, all earlier periods (e.g., 2019, 2018, 2017, etc.) will use this rate for conversion.

Exchange rates are not extrapolated forward, so if you use custom rates, you must provide rates for each month up to your last closed month to ensure accurate conversions. If a rate is missing, actuals will default to an exchange rate of zero for that period.

Users provide both average and closing rates.

Apply currency conversion to ERP connections

To convert actuals, go to the settings section for each accounting system.

Francis automatically detects the functional currency when importing data from an accounting system. You only need to specify the exchange rate data source and the target/presentation currency.

You must manually specify the functional currency for data from a Google Sheets source.

How conversion works

Francis follows the guidelines of IAS 21 to convert currencies.

• All accumulated amounts for Assets and liabilities are translated using the closing rates for the months in which they are presented.

• Income and (P&L) are translated using the average rates for the months in which they are presented. For practical reasons, a monthly average rate approximates the exchange rates at the transaction dates.

• Equity accounts are translated using the average rates of the months in which transactions occur and then accumulated. For practical reasons, a monthly average rate approximates the exchange rates at the transaction dates.

Current vs. historical rates

Francis converts assets and liabilities using "current rates", while equity accounts are converted using "historical rates".

Current rates offer the most up-to-date view of an account’s value if it were to be realized in the target currency. In contrast, historical rates provide a more consistent picture that closely aligns with the historical investment value.

Recognizing gains and losses related to currency conversion

When translating actuals from a functional currency to a presentation currency, adjustments must be made to account for gains and losses related to currency conversion.

Exchange differences result from two things:

• Average vs. closing rates: Translating P&L and equity accounts with averages while translating assets and liabilities with current closing rates results in a gain/loss.

• Closing rates that change over time: Translating assets and liabilities at a closing rate that differs from the previous closing rate results in a gain/loss.

#1 Average vs. closing rates

When P&L and equity values are translated using historical average rates, while assets and liabilities are translated using current closing rates, the financial statements will show discrepancies.

#2 Closing rates that change over time

When converting asset and liability values with different closing rates every month, values will fluctuate even though there have been no new transactions.

The new equity account: Other comprehensive income: Francis currency translation

All exchange differences should be recognized under other comprehensive income, part of equity. Once currency conversion is applied, Francis automatically creates a new general ledger equity account named "Other comprehensive income: Francis currency translation." Once created, it can be mapped to your model and included in the cash flow statement to offset the difference described below.

The new account capturing gain/loss related to currency conversion is currently in private beta and will be available soon.

By default, the exchange difference will create a discrepancy in the cash flow statement. For example, the cash amount in the bank account may have changed due to exchange rate differences despite no new transactions. You must include movements in the new equity account "Other comprehensive income: Francis currency translation" in your cash flow statement to ensure your cash flow check matches.