Phocas has a new user documentation site. This site will be retired soon.

Create a multi-currency budget

Owned by Denise McGettigan
Last updated: Jul 29, 2024
7 min read

There is a Phocas-wide project underway that will address multi-currency across the Analytics, Financial Statements, and Budgets & Forecasts modules. However, two interim solutions for multi-currency budgeting are available, as outlined on this page.

If your organization operates internationally, you will likely need to budget using multiple currencies. Multi-currency budgeting usually involves budgeting each region in the local currency and then viewing those budgets in a single reporting currency.

There are two methods for creating multi-currency budgets. Compare each method and select the one that best suits your business needs.

Method 1: Use the Budgets & Forecasts module

The Budgets & Forecasts method has three components:

  • The Main tab where you do the budgeting as usual in the local currency.

  • A Lookup tab to store the exchange rates for each of your regions.

  • A Reference tab with multiple measures (driver-based budgeting) to convert your budgeted local currency values to your reporting currency values. The reporting values are calculated by getting the local values from the Main tab and multiplying them by their respective exchange rates from the Lookup tab.

This method has two advantages:

  • The currency conversion happens in real time as local currency values are entered.

  • When you publish the Reference tab, both the local and reporting currency values appear in the budget stream in Financial Statements.

However, currently, there are some limitations to this method. Firstly, this method does not support 3-statement budgeting, and secondly, any General Ledger accounts or entities added to the Main tab also need to be added to the Reference tab.

Multi Ccy (Reference Tab) 26 Jul 24.mp4

For example, suppose you have a budget that includes three regions: Australia, the UK, and the USA. You want to budget for those regions in their local currencies (AUD, GBP, and USD) and in one common currency, AUD, as that is where your head office is located.

  1. Create the budget based on the applicable Financial database, ensuring you:

    1. Select the applicable dimension (Country) as Level 1 (so you can see the respective entities).

    2. Select the applicable local value (currency) measure.

  2. Add a Lookup tab to record the exchange rates:

    1. Use a descriptive name, such as FX rates.

    2. Clear the Category and Account checkboxes because you're only interested in the Country level.

    3. Enter a name for the row, such as FX rate, select the required number of decimal places, and select a suitable aggregation method, such as Median.

      image-20240729-035653.png

  3. In the Lookup tab, enter the FX rates for each country across the period.

  4. Add a Reference tab to convert the local currency values into the reporting currency (AUD):

    • Use a descriptive name, Reporting AUD.

    • Select the same database and stream you used in the budget setup, but select the applicable reporting value measure (this is different from the one you selected in the budget setup). Select the required number of decimal places. Select the same Profit and Loss template you used in the budget setup and add a level for the same dimension you selected in the budget setup (Country).

    • Set up the additional measures as follows:

      1. Add an Input row, enter a name (Local Value), and select the Local Value measure. This will publish the local value to the Profit and Loss stream.

      2. Add a Lookup row, enter a name (FX), and select the lookup tab and row. You don't need to publish this row.

      3. Convert the primary input row to a calculation row and enter the following formula: (Local Value)*(FX)

    • After you save and finish the setup, review the contents of the new Reference tab. It looks very similar to the Main tab, as you used the same template. When you expand any of the lowest-level rows, you’ll see two additional measure rows: one for the local value and the other for the exchange rate.

  5. Link the Reference tab to the Main tab, so instead of using the prior year’s actuals (as per the tab setup), you use the values in the Main tab:

    1. Enter a formula into the first period cell of the first additional measure row (Local Value) that refers to the corresponding cell in the Main tab and press Enter. See Use formulas.

    2. Copy the formula forward across the period, then select all the period cells and copy them down the expanded rows. See Copy and paste values.

  6. Complete the budget data entry on the Main tab. The changes to these local values flow through to the working (Local Value) rows in the Reference tab, where the reporting values are automatically updated.

  7. Publish the Reference tab. The working (Local Value) rows are published to the local measure and the reporting values (account row values) are published to the reporting measure.

Method 2: Use the Designer module

This procedure requires the use of Designer, which is an advanced feature. If you are not confident using Designer or have not had the required training, contact your Phocas account representative.

In Designer, you can add a transform column to the Budgets & Forecasts stream to convert the budgeted local currency value to a reporting currency value.

This method allows you to view the resulting currency conversion in Financial Statements immediately after publishing the local currency values from Budgets & Forecasts. Whereas you can use a Reference tab in the budget to display the resulting transformation from local to reporting value, the reporting value does not update in real-time within the budget. You need to both publish the budget and refresh the page to view the updated reporting currency.

For example, suppose you have a budget in which there are three regions, Australia, the UK and the USA and you want to budget those regions in both their local currencies (AUD, GBP and USD) and in one common currency, AUD, as that is where your head office is located.

  1. In Budgets & Forecasts, create the budget for the three regions in the local value measure, then publish the budget to a stream in the Financial Statements module.

  2. In Designer module and open the budget stream to view the local values.

  3. Click the green Add Transform column button, then select the If-Then option.

  4. Create the logic for the new column:

    1. Enter a title (Reporting Currency).

    2. Enter the parameters, as illustrated in the image below. The parameters comprise of each country and its exchange rate. As Australia uses the local currency, it does not require an exchange rate.

    4. Click Save.

  5. Review the data in the new column (Reporting Currency), then drag the column up into Reporting Value measure in the Measures panel.

  6. Click Save, then rebuild the database.

  7. Back in Budgets & Forecasts, view the reporting currency in the budget using one of these methods:

    • Add a comparison row for the budget stream with the reporting value measure.

    • Add a Reference tab (no need for additional measures as in the above method) for the budget stream and reporting value measure, with an offset as 0, and add the same template and levels.

  8. Complete the budget data entry for the local currency values on the Main tab. The reporting currency values do NOT update accordingly because the conversion calculations happen in Designer. To update those calculations, you need to publish the budget values to Designer.

  9. Publish the Main tab to the existing stream.

  10. Refresh the budget workbook, then expand the budget rows to view the updated reporting currency values.