Create a multi-currency budget

Owned by Denise McGettigan
Last updated: Jan 25, 2024
7 min read

There is a Phocas-wide project underway that will address multi-currency across the Analytics, Financial Statements and Budgets and 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 takes the form of 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 and Forecasts module

In Budgets and Forecasts, you can use the combination of a Reference tab with multiple measures (driver-based budgeting) and a Manual Entry tab to convert the budgeted local currency value to a reporting currency value. The reporting values are calculated by getting the local values from the Main tab and multiplying them by their respective exchange rates from the Manual Entry 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.

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. 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 Local Value (currency) as the measure.

  2. Add a Reference tab to convert the local currencies into the common currency (AUD):

    • Use a descriptive name, Reporting Currency.

    • Select the same database and stream you used in the budget setup but select the Reporting Value measure (this is different to the one you selected in the budget setup). 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 in step 1 above (Country).

    • Set up the additional measures as follows:

      1. Add a Working row, enter a name (FX Rates), and select the Median aggregation option.

      2. 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.

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

    • 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. However, it displays zero values, as you have not yet entered any exchange rates.

  3. Add a Manual Entry tab and enter the exchange rate data in relation to the common currency (AUD).

  4. In the Reference tab, bring in the local value data from the Main tab:

    1. Enter part of the name of the Input row (Local) into the search box above the worksheet to filter the worksheet and display only those rows, then expand all rows again. See Search for an item in a worksheet.

    2. Enter a formula in the first period cell of the first input row that refers to the corresponding cell in the Main tab and press Enter. See Use formulas.

    3. 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.

  5. In the Reference tab, bring in the exchange rate data from the Manual Entry tab:

    1. Delete the text in the search box, then expand all rows again.

    2. Enter part of the name of the Working row (FX) into the search box above the worksheet to filter the worksheet and display only those rows, then expand all rows again.

    3. Filter the worksheet to display data for the first region (Australia) only.

    4. Enter a formula in the first period cell of the first working row that refers to the corresponding cell in the Manual Entry (FX Rates) tab and press Enter.

    5. Copy the formula forward across the period, then select all the period cells and copy them down the expanded rows.

    6. Repeat the above steps iii to v, to enter the exchange rates for the other regions.

    7. Clear the filters, delete the text in the search box and expand all rows again, then review the data.

  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 recalculated.

  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 and 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 and 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 and 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 and 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.