Page Comparison

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.

In Budgets and Forecasts, you can use the combination of a Reference Only tab with multiple measures (driver-based budgeting) and a Manual Entry driver tab to convert the budgeted local currency value to a reporting currency value. The advantage of this method has over option 2 is that 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 driver tab.

This method has two advantages; the currency conversion happens in real time as local currency values are entered , and by publishing the reference tab when you publish the Reference Only tab, both the local and reporting currency values appear in the budget stream in Financial Statements. Note however that However, currently, there are some limitations of this approachmethod. Firstly, this approach method does not support 3-statement budgeting, and secondly, any GL General Ledger accounts or entities added to the main Main tab would also need to be added to the second Reference Only tab. We are investigating changes we could make to address these limitations

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.

      Image Added

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

   1. In the budget workbook, click the blue plus button next to the Main tab, enter a name for the tab (Reporting Currency), select the Reference tab type, then click Add.

   2. Select the same database and stream you used in the budget setup.

   3. Select the Reporting Value measure (this is different to the one you selected in the budget setup).

   4. Select the same Profit and Loss template you used in the budget setup.

   5. Click Add level and select the same dimension you selected in the budget setup in step 1 above (Country).

   6. Click Next.

      Image Added

   7. Add the additional measures:

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

         Image Added

   8. Click Create Reference Only Tab and review the contents of the new 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.

      Image Added

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

   Image Added

4. Enter formulas in the Reference Only tab:

   1. Click the Reference Only (Reporting Currency) tab and expand all rows.

   2. Bring in the local value data from the Main tab:

      1. Enter part of the name of the driver 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.

         Image Added

   3. Bring in the exchange rate data from the Manual Entry driver tab:

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

      2. Enter part of the name of the driver 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.

         Image Added

      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) driver 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.

         Image Added

   4. Clear the filters, delete the text in the search box and expand all rows again, then review the data in the Reference Only (Reporting Currency) tab.

   5. 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 Only tab, where the reporting values are recalculated.

   6. Publish the Reference Only 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.

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. (same advantage as above?)  Whereas you can use a Reference Only 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 refresh to view the updated reporting currency.