Thursday, October 3, 2024

Currency and Exchange rates in ProjOps and FinOps

Currency on the Project record 

There are 3 currencies in FinOps:

  • Transaction currency - In which transaction happens
  • Accounting currency - In which the company accounts (configured in the Ledger under Accounting currency)
  • Reporting currency - In which the company reports (configured in the Ledger under Reporting currency)

There are 3 currencies in ProjOps as well, but shown differently in the forms and views:

  • Transaction Currency and Environment Currency is seen in the "Actuals" view
  • Accounting Currency (from FinOps) is seen on the Project record for cost values

There is effectively 1 currency in ProjOps which is Environment currency (which mostly is USD, can be any but for the implementation I am currently working it is USD)


This section of the project record is displayed in the "Accounting Currency" (Accounting currency setup in the Ledger in FinOps) OR for that matter, any total COST currency field in any record in ProjOps is displayed in Accounting Currency.


That means if the Accounting Currency is GBP and the if the cost on the project is posted in INR. Then in ProjOps the currency triangulation would happen as ProjOps does not have currency pairs like FinOps. In ProjOps every currency is pegged to the environment (Base) currency which is USD in this case.

So when the "Accounting currency" is GBP, "Cost Currency" is INR, then system would first convert the INR into USD and then USD into GBP. System can't directly convert INR to GBP.

ProjOps - Assuming the Project Contract is also in GBP (so the Accounting Currency is GBP and Project Contract Currency is GBP as well)

  • COST (Time, Expense and Procurement) - System would first convert the INR into USD and then convert the USD to GBP. 
  • UNBILLED SALE - Amount would be in GBP as that is the currency of the Project Contract.

FinOps - When this transaction reaches FinOps, the COST and UNBILLED, the currency would behave as follows:

  • Transaction Currency - INR for Cost
  • Accounting Currency - GBP
  • Reporting Currency - USD (Assuming reporting currency is USD)

Currency on the Actuals view


  1. Please refer to the first screenshot/image above from ProjOps Actuals view. The fields highlighted in RED get their values from ProjOps and fields highlighted in BLUE get their values from FinOps.
  2. The FinOps related fields "Accounting Amount" and "Accounting Amount (Base)" are not calculated correctly and we suspect this to be a Microsoft bug. However, PM residing in ProjOps can still look at the ProjOps related fields .i.e. "Amount" and "Amount (Base Currency)".
  3. The "Exchange rate" field is displaying the exchange rate from ProjOps and "Account Exchange Rate" is displaying the exchange rate from FinOps.
  4. Accounting Currency values will reside in FinOps. FinOps will display values in three currencies - Transaction Currency, Accounting Currency and Reporting Currency and all three can be different.
  5. However, ProjOps will display the "Amount" in Transaction Currency and "Amount(Base Currency)" in environment/ base currency, which in our case seems to be USD.
  6. To make things work correctly from currency perspective – Exchange rates needs to be maintained correctly in both ProjOps and FinOps as they do NOT automatically sync as of now. Please refer to the second image below where I have explained the concept pictorially about the two currency tables in the ProjOps system.
  7. There are two currency tables in ProjOps:
    1. Currency Table
    2. Currency Exchange Rate Table

Maintaining the Currency Exchange Rate 


There are two currency tables in ProjOps. They are:

  • Currency table
  • Currency Exchange Rate table

Currency Table - The currency table is a stand-alone table in ProjOps, where all the currencies are pegged against the base/environment currency. In the current implementation, the base/environment currency is USD, hence every currency in this table in ProjOps is pegged against USD. The exchange rates initially will be 1 to 1. That means 1 GBP to 1 USD. Someone has to manually update the table to ensure correct exchange rates are reflected in this table. The exchange rate from this table is used to display data on the forms, and list pages in ProjOps. So if the exchange rate is not maintained correctly, the data displayed in ProjOps will NOT be correct.

Currency Exchange Rate Table - The currency exchange rate table is the one where the data is synched from D365 FinOps. The exchange rate from this table is used when the transactions are going from ProjOps to FinOps. For example - Time, Expense etc.

Main problem where Microsoft has left a hole in the solution is: That the "Currency table" is not synched with the "Currency exchange rate table" and hence there has to be some automation built to make sure that the data is consistent OR there has to be a robust process to ensure that the data in "Currency table" is manually updated to the values in "Currency exchange rate" table.

IMPORTANT

  1. Please note that the "Actual Cost" fields shown in the screenshot above from ProjOps will NOT equal to the value in the FinOps as the "Actual Cost" field in ProjOps displays the TOTAL value based on the latest exchange rate in ProjOps. On this COST field in the Project record, system logic changes the entire amount to the latest exchange rate. In FinOps, the total considers the historical value of the cost based on the historical exchange rate. However, this is not the case in the "Actuals" view as seen in the screenshot above of the "Actuals" view.
  2. The issue with Microsoft when it comes to the display in the "Actuals" view, for the "Amount" fields without "BASE" in its name, system displays all in the "Currency" of that Actuals line. This is a limitation of ProjOps. The "Amount" is in IDR but the "Accounting Amount" is actually in USD as USD in the Accounting currency in this example. But because of the system limitation the "Accounting amount" value also is shown with IDR currency symbol. 
  3. For "Amount" fields with "BASE" in their name, system displays the amount in the environment currency which is USD in this case. Hence it is recommended not to look at "Accounting amount" and "Accounting amount (Base)" as it is confusing for the user in ProjOps.






For example: 

Assumption - All the exchange rate are setup correctly in ProjOps and FinOps.

  1. Suppose if the Environment currency of ProjOps is EUR, then "Amount(Base Currency)" will be shown in EUR in ProjOps in the Actuals view. 
  2. Accounting Currency setup in the Ledger section in GL in FinOps is GBP, which means the "Cost" fields on the Project record in ProjOps would be shown in GBP
  3. Reporting Currency in FinOps can be USD, setup in the Ledger section in GL in FinOps
  4. Transaction Currency can be any in ProjOps, but for example, it is INR
  5. Project Contract Currency in ProjOps can be any, but for example it is SGD
  • With this setup, when the transaction is posted in ProjOps, it will be in transaction Currency - INR
  • Cost would be posted in INR, Unbilled sale would be posted in SGD
  • The data seen on the Project record would be in GBP as the accounting currency is GBP. The currency translation would happen from INR to EUR and EUR to GBP, as the environment currency is EUR and currency exchange rate is pegged against EUR. (Triangulation)
  • The data seen on the Actuals view would be: 
    • COST - Amount would be in INR and Amount (Base Currency) would be in EUR
    • UNBILLED SALE - Amount would be in SGD and Amount (Base Currency) would be in EUR
  • Reporting currency would be USD in FinOps





No comments:

Post a Comment