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

Expense Reports Dates (TBD)

 

• Expense Creation Date = Document Date in the GL
• Transaction Date (when the expenses occurred) = Project Date in the sub-ledger
• GL Posting Date will be equal to Transaction Date, if the period is not closed. If the period is closed, then GL Posting Date will be the 1st day of the next open period, if the "Correct accounting date during posting" parameter is ON.

Fee transaction on the Fixed Price Project in D365 Project Operations (non-stock)

Fee transactions are NOT allowed on the Fixed Price projects in standard D365FO. The "Accrue revenue" button is non-editable for fixed-price projects:

"Accrue revenue" button is non-editable for fixed-price projects

However, when it comes to the Non-Stock version of D365PO (where D365PO is connected to D365FO), the system does not stop you from posting fee transactions (journals) on a fixed-price project. The fee journal is "Confirmed" successfully in ProjOps and the transaction starts appearing under the posted project transaction in D365FO. The only important thing to note is that it generates no vouchers. That means it does not post into the GL. When the "Project statement" is run to check whether it shows any revenue, it doesn't. Please see the screenshots below:

Fee journal posted in D365PO and Actuals generated on the FP Project

Fee transaction appearing in the "Posted project transaction" in D365FO

No voucher was created, as nothing is posted in the GL

No revenue was reported in the sub-ledger as well

When the same fee journal is posted on a T&M project, then the revenue is posted in the GL, on the project sub-ledger and is seen on the project statement. Please see the screenshot below:

Fee journal posted in D365PO and Actuals generated on the T&M Project

Fee transaction appearing in the "Posted project transaction" in D365FO

Voucher was created, as fee journal is posted in the GL

Revenue was reported in the sub-ledger as well

D365PO will still allow you to bill the fee transaction on the fixed-price project.

Fee transaction included on an invoice in D365PO

The invoice once confirmed in D365PO, appears in D365FO.

Invoice for a fee transaction on a fixed-price project in D365FO

Voucher for a fee invoice on a fixed-price project in D365FO

This is the behaviour of a fee transaction on a fixed-price project in D365PO (non-stock).

Intercompany Setup in D365PO (non-stock)

Intercompany Configuration

•       Parameter setup in both the legal entities (Borrowing legal entity – Procurement category, Lending Legal entity – Default Hours and Expense category)

•       Intercompany customer vendor setup in both the legal entities

•       Intercompany setup in the General Ledger setup

•       Project category for expenses to be created in both the legal entities

•       Procurement categories in both the legal entities for IC Supplier Invoice Test

•       For IC Supplier Invoice test, vendor to be created in FinOps and then created in ProjOps

•       For IC Expenses test, employee vendor to be created in Lending entity and linked to the employee

•       Bookable resource created in Lending entity in ProjOps with correct resource role

•       Intercompany Cost and Intercompany Revenue Account in “Ledger posting setup” needs to be setup

•       Employee created in Lending entity in FinOps and associated with the User in System Admin module

•       Price List

•       Two costs price list

•       One for Lending entity – On Organisation Unit

•       One for Borrowing legal entity (Transfer price) – On organisation Unit

•       One sales price list

•       One for Borrowing legal entity – On Project Contract (Pay attention to the “Unit schedule” and “Unit” otherwise the sale price is 0)

•       Unit

•       For Expense (Transaction Category) the Unit should be – Primary Unit

•       For Purchase (Transaction Category) the Unit should be – ea

•       Hence the “Expense” type of Project Categories used for Out of pocket Expense and Pending supplier invoice (Purchase) should be different

•       Customer, Project Contract, Project Contract Line, Project and TASKS created in Borrowing Entity. Tasks are mandatory.

•       “Default offset account for expenses” needs to be setup in the Borrowing entity otherwise integration journal does not bring the offset account for the sales posting. System does not use this “Default offset account for expenses”  but it requires it for posting. This is weird but this is true. System still uses the project posting profile.

•       Check fiscal period is open in Lending and Borrowing Legal entity

•       Exchange rate should be same in ProjOps and FinOps

•       Make sure the Unit of Measure are set to – Fixed unit assignment = Yes (otherwise the Free Text Invoice fails)

     Intercompany Price List Configuration

•        Price Lists

•        UK Cost Price (Type = Cost) – Associated with the loaning entity organization unit

•        Role - Robotics Engineer

•        Cost price

•        US Cost Price (Type = Cost) – Associated with the borrowing entity organization unit

•        Role - Robotics Engineer

•        Resourcing Company – GBPM

•        Resourcing Unit – GBPM

•        Cost price

•        Demo Sale Price (Type = Sales) – Associated with the borrowing entity project contract

•        Role – Robotics Engineer

•        Resourcing Company – USPM

•        Resourcing Unit – USPM

•        Sale price

•        Sales price set “At Cost” for Expense and Purchase transaction categories

 

On-account on a T&M project and dimensions

Dimensions when the customer advance is posted comes from the Project.

Dr AR – Dimensions from Project - 1000

  Cr DeffRev – Dimension from Project - 1000

When the transaction is posted, dimensions come from the respective records like employee or manually selected.

Dr Cost – Dimensions different from project - 200

  Cr Payroll allocation – Dimensions different from project - 200

When the netting off happens at the time of generation of zero value invoice, then the entry is like this:

Dr Deffrev – Dimensions from the project – 200

  Cr Revenue – Dimensions different from the project – 200.

Example with screenshots:

Dimensions on AR invoice for On-account (Deferred revenue)

Dimensions on hours transactions

Dimensions on zero value invoice

Kind regards

Sarang

Customer advance and NTE

 Customer advance created for GBP 12,000 but the NTE value on the project contract header is GBP 10,000.

The customer advance invoice posted without any issues as NTE is not checked at advance level.

The NTE values on the project contract are also unaltered.

First project transaction of expense was posted for GBP 8,000, with a NTE check as "Success & Committed".

This transaction was successfully invoiced and it updated the NTE values on the project contract header. The sale value of expense is marked up by 1%.

Now the next expense was posted for GBP 3000. The transaction has NTE status of "Fail and Failed".

    

This has posted the cost and sale transaction on the FinOps side, which is actually not correct. Only cost should have been posted. The sale accrual should not have posted as this transaction has crossed the NTE limit.

The cost and sale voucher.

The transaction which has surpassed the NTE limit, system will not allow that transaction to be invoiced.

The only option in this case is to increase the NTE limit on the project contract header and then re-evaluate the transaction for NTE limit.

The transaction re-evaluation for the NTE limit is done using this button. (Re-evaluate Not-toExceed)

Once the transaction is re-evaluated, then the transaction status changes to "Committed".

This transaction can now be invoiced. After the invoice, the remaining NTE value is as below:

Also the remaining Advance value is as below:

If the project ends now, then question is how do we return the remaining GBP 890 back to the customer.

To do this, we need to navigate back to originally posted advance invoice for GBP 12000 and click "Correct this invoice". The system is smart enough to note that it has to create a negative invoice (credit note) for ONLY the remaining value. 

Once "Correct this invoice" button is clicked, the total amount is the remaining amount as seen in the screenshot.

And then the normal invoice procedure to continue.

Above step of "Correct this invoice" can also be done after the invoice is "Mark invoice as paid" is clicked.

After the confirm button is pressed, the invoice / credit note will be processed and will be posted in FinOps.

Thanks 

Sarang

Creation of Credit Note in ProjOps

 Once the invoice is "Confirmed" in ProjOps and project invoice proposal "Posted" in FinOps , user can create a credit note by clicking the "Correct invoice" button, which creates a negative invoice in ProjOps.

Once the negative invoice is "Confirmed", then system does NOT automatically create a negative project invoice proposal in FinOps. System gives this error:

The user has to then import the data from staging table (if not automatic, like the way this step is followed for any other project transaction) and then post the project operations integration journal. After this step, the negative transaction starts appearing in the "Posted project transaction" screen and the negative project invoice proposal starts appearing in "Project invoice proposal" screen in FinOps, which can then be posted.