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.

For example, suppose you have a budget in which there are that includes three regions, : Australia, the UK, and the USA and you . You want to budget for 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 Removed

      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 Added

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

   Image Added

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

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

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

Enter a formula in

• Use a descriptive name, Reporting CurrencyAUD.

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

  Image RemovedImage Added

• Set up the additional measures as follows:

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

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

     Image RemovedImage Added

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

  Image Removed

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

   Image Removed

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

8. 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 input row additional measure row (Local Value) that refers to the corresponding cell in the Main tab and press Enter. See Use formulas.

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

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

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

      Image Removed

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

   6. 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 Removed

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

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

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

      Image Removed

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

9. 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 recalculatedautomatically updated.

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

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.

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.

   3. 

   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.