I didn’t do conversions outside the program, but as suggested I made a new and clean test administration as attached way below.
I made 2 different currencies. GBP and EUR. I set up 2 exchange rates of 1 GBP = 1,125 or 1,25 EUR.
I now made billable time and billable expenses in GBP and EUR .
All time and expenses have been separately invoiced (3 expenses + 3 times = 6 invoices total).
I believe you could recreate the following in a new test administration later on your own system to independently conclude recreate it.
First some screenshots and then the error in the program explained in detail.
Exchange rates used
All of these amounts are easy to follow and are as you would expect and we should conclude that everything has been invoiced out at the correct amounts.
Now lets take a look at the balance:
There seem to be values left on the billable expenses and billable times.
GBP expenses has been invoiced at the correct amount.
The EUR expense 2 has the same amounts as well and have the same date.
EUR expense 1 has a different date.
The amount on 01/11/2016 was € 20 recorded as spent; or divided by the exchange rate of 1,125 total £ 17,78
The amount on 03/11/2016 was € 20;recorded on the invoice or divided by the exchange rate of 1,25 total £ 16,00
The is a difference that is not being adjusted for £ 1,78. that should’ve been recorded as a write-on/off
GBP time has been invoiced at the correct amount.
The EUR time 1 has the same date but different amount on the invoice. £ 80 was written down as billable time (€ 100 / 1.25) but £ 64 is being invoiced. Or € 100 / 1.25 / 1.25 = £ 64> and uses twice the exchange rate of that day
There is a same day difference appearing that is not being adjusted for £ 16,00.
The EUR time 2 has the different date and different amount on the invoice. £ 84,44 was written down as billable time (€ 95 / 1.125) but £ 60,80 is being invoiced with a write of for £ 6,75, so the total would come down to £ 67,55 as recorded to be invoiced by the program, leaving a gap of £ 16,89. The same calculation error applies> £ 84,44 / 1.25 = £ 67,55.
The £ 67,55 is calculated by € 95 / 1.1125 / 1.25 and uses 2 different exchange rates because 2 days are referenced.
The £ 60,80 is calculated by € 95 / 1.25 / 1.25 and has the same problem as EUR time 1
The adjustment error amounts to ( € 95 - € 95 / 1.25 ) / 1.125 = £ 16.89
There is a different day difference appearing that is not being adjusted for £ 16,89.
I guess that we should conclude that recorded invoiced out amounts are recorded wrong.
The write-on or write-off adjustments should be valued as “amount of recorded foreign amount / exchange rate of that day MINUS amount of invoiced foreign amount / exchange rate of that day”
As an example the recorded time of € 95 would be on the balance at an exchange rate of 1.125 thus £ 84,44 and the invoiced amount as € 95 / 1.25 as £ 76 leaving a write-on/off for £ 8.44 as a foreign exchange difference on the P&L.
If you concur with my conclusion and solution how do we flag the developers?
And just for fun: remove the exchange date of 03/11/2016.
Billable expenses in the balance changes to zero
Billable time in the balance changes to £ 19,26 >> € 195 / 1.125 is debited but € 195 / 1.125 / 1.125 is credited
test (2016-11-03).manager (2.4 KB)