Operational Overview and Setup Guide

By Jason Frink adapted from technical white paper by Robert Nell

August, 2020

Contents

System Default Currency

Currency Conversion Setup

Turning on the currency conversion system

Specifying a projects “base” currency

Setting up your system default currency exchange rate table

Currency Conversion Workflow

Project import template

Currency assignment hierarchy

Automatic conversions

Converting projects

System exchange rates vs. project specific exchange rates

System Default Currency

Whether or not you plan to convert currencies the system stores a ‘System default currency’. This is set directly in SQL by the Eos Consultant in the ProjectHistory.Configuration table (configuration key “Core.DefaultCurrencyId”).   The set value must be a valid ISO 4217 currency code.  

The system default currency does not generally come into play when the currency conversion system is turned on because currency designations are retrieved from elsewhere.   When the currency conversion system is turned off, the system still supports importing a project that contains costs in multiple currencies.  The project’s base currency used to normalize aggregated cost totals and will inherit the designated system currency. For example, while your system currency may be US dollars, you could have an item where the equipment was paid for in US dollars but the labor was paid in Great British Pounds.  When the system calculates the total for the item, it will convert the Great British Pounds to US dollars before summing up the costs.  This same logic is used when aggregating mixed currencies up to the element and project levels.

Note that for the above functionality to work when the currency conversion system is turned off, the system must be able to find the appropriate exchange rates.  However, when the currency conversion system is turned off, both the System exchange rate table and the Exchange rate sheet in the import file are hidden.  The solution is to manually unhide the Exchange rate sheet in the import file and enter the appropriate rates for the project.  (Both the System exchange rate table and the Exchange rate sheet in the import file are covered later in this document.

If the system default currency is changed after data has been imported into CPH unexpected results may occur including monetary values changing from one currency to another.  If a client thinks they need to change their default currency setting after projects have been imported, Eos will need to get involved.  All data should be backed up, all projects should be reimported after the change, and careful testing should be performed.  

Turning on the currency conversion system

You turn on the currency conversion system in Administration by marking the ‘Use currency conversions’ checkbox on the Factors > Settings page. This setting is stored in the ProjectHistory.Configuration table in SQL (configuration key “CurrencyConversionEnabled”).  

<Alert!> If a client doesn’t plan to convert currencies initially but will likely want to later, they should turn the currency conversion system on at the beginning of their implementation and before production project data is imported. 

Specifying a projects “base” currency

When the currency conversion system is turned on, a field is added to the Field – Project fields – Fields list in Admin called “Project currency”.  It is a system field so it can’t be deleted and it is included in all project import templates as a required field. Project currency is a special list field where the list values are automatically synced with the selected currencies on the Currency conversions setup page.

The Project currency field serves the following functions:

1. It tells the system which currency to normalize to when aggregating mixed currency amounts. It supersedes the System Currency when Currency conversions are turned on.
2. When a project contains currencies that are different from its ‘Project currency’, the system creates a cached copy of the project converted to the ‘Project currency’. For example, if your ‘Project currency’ is USD but half of the project cost is in GBP, you will likely want to study it in all USD.  When you convert it the first time, it will be fast because the system only has to retrieve a cached copy.

Note that when the currency conversion system is turned off, the Project currency field is not visible in the system.  However it is populated behind the scenes with the System currency when a project is published.

Setting up your system default currency exchange rate table

When the currency conversion system is turned on, a new page appears in Admin – Factors called ‘Currency conversions’.  

There are 3 tabs on this page:

• Currencies tab – The tab provides a pick list of all the currencies used in the world. Here you select all the currencies you work with or think you might work with in the future.

• Table properties tab – Here you define the frequency of rates (Monthly, Quarterly, or Yearly) and the time span of your currency exchange rate table.  The time span is driven by the oldest project you expect to ever have in the system (based on Project date). The table will start on the first day of the period your oldest project date falls in.  The choice of frequency is a trade-off between accuracy and how often Admin is willing to update the table with new rates as time carries on.  E.g. If you select monthly, each month Admin should edit the table and add the new rates.  (Someday we may look at enhancing the system to update automatically from a service.)

• Exchange rates tab – This tab becomes available after you complete the setup on the previous two tabs. Here you use the export/import Excel feature to populate your rates.  The system creates a row for each time period and each combination of currencies. You just enter the rates.

Currency Conversion Workflow

Project import template

The following changes occur in the project import templates when the currency conversion system is turned on.

• On the Project sheet:
  1. The ‘Project currency’ field described above is included as a required field.
  2. The “Unit” column heading changes to “Unit/Curr” and for each row that is a monetary field, the column will supply a dropdown list of the selected currencies.

• On the Item sheet, the system displays a companion currency code column to the right of every monetary column.

Currency assignment hierarchy

All monetary values in the system must have a currency.  Currencies are assigned based on the following hierarchy when a project is published:

1. Currency code is specified for the monetary field in the Project import template.  
2. If a currency code is not selected for a field, the Project currency is assigned.  
3. If the Project currency is blank, the System currency is assigned. *
4. If the System default is not specified, “USD” is assigned. **

  * If the currency conversion system is turned on, the Project currency field is required so it should not be blank.

** The System default currency would only be blank due to a configuration error.

Automatic conversions

By default, the system displays “native” project data.  This means that if various currencies were entered in the import template for project and item monetary amounts, these currencies will display in the application.  The system will also create a version of the project converted to its specified Project currency. An example of this is discussed in the Project Currency section.

Note aggregated element data is always normalized to the Project currency.

Converting projects

When the currency conversion system is turned on, a ‘Currency’ drop down field is displayed when you open the Apply Factors dialogue.  When you select a currency to convert a project, the system displays the exchange rate that will be applied.  You can click on the exchange rate to see where the rate is being retrieved from.  (System or project specific rate, more on project specific rates later in this document.)

After selecting a currency and clicking the Apply button, the project will be converted to the target currency.  When you convert a project to a currency the first time, we save a cached copy so that subsequent conversion of that project to the same currency will be very fast.

You can convert projects in Single project, Compare projects, or in Collections using portfolios. The system displays a banner at the top as a reminder that converted data is being displayed.

System exchange rates vs. project specific exchange rates

Cortex is designed with the idea that Admin sets up the System exchange rate table that gets used most of the time to store and retrieve exchange rates.  To override the System exchange rate with a Project specific exchange rate (e.g. like when a special rate was negotiated during the project), there is a sheet in the Project import template for these overrides.   

The system will use a project specific rate if one exists, otherwise it uses the System exchange rate table.  The System exchange rate table stores rates by time period and the ‘Project date’ determines the exchange rate. For example, if the System exchange rate table has been configured to store monthly rates, and the project’s Project date is 05/16/2010, the exchange rate that was in effect back in May 2010 is used for the conversion.