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 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 RemovedImage Added

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

   In the budget workbook, click the Add button at the bottom of the worksheet, enter a name for the tab (Reporting Currency), select the Reference tab type, then click Add

   • Use a descriptive name, Reporting Currency.

   • Select the same database and stream you used in the budget setup

   .Select

   • 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

   .Click Add level and select

   • and add a level for the same dimension you selected in the budget setup in step 1 above (Country).

3. Click Next.

   Image Removed
Add
• Image Added

• Set up the additional measures as follows:

  1. Add a

working

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

  2. Add an

input

• 1. 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. Convert the primary input row to a calculation row and enter the following formula: (Local Value)*(FX Rates)

.Click Create Reference Only Tab and
Image Removed
• 1. Image 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
• Image Added

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

   Image RemovedImage Added

5. Enter formulas in In the Reference Only tab:

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

Bring

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

1. Enter part of the name of the

   driver input

   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 ModifiedBring

7. In the Reference tab, 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

      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 RemovedImage 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 RemovedImage Added

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

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

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

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. Image RemovedImage Added

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