During the reconciliation process of a bank account in NetSuite, a discrepancy was discovered when comparing the balance amounts between the migrated data and the client’s legacy system. This issue arose due to an open transaction migration that incorrectly recorded a payment of 2,500 GBP with a base amount of 14,000 AED. However, the balance amounts in the bank account showed 3,000 GBP and 13,000 AED for the period, respectively.
To address this discrepancy, it was necessary to adjust the opening balance through journal entries. Specifically, the amounts in GBP needed to be debited, while the amounts in AED needed to be credited, ensuring the correct balance was reflected in both currencies.
The correction was achieved by creating two separate opening journals instead of a single journal with an unusual exchange rate. In the first journal, the amount was debited, and in the second journal, the amount was credited to the bank account. The exchange rate and transaction amounts were calculated using the following equations:
- (Transaction Amount Debited×Exchange Rate 1)−(Transaction Amount Credited×Exchange Rate 2)=1,000 AED
- Transaction Amount Credited−Transaction Amount Debited=500 GBP
The process involved finding two transaction amounts that satisfied the second equation. Then, an exchange rate value higher than the usual rate was determined for Exchange Rate 1. Finally, the correct Exchange Rate 2 value was calculated to satisfy the first equation.
Example Calculation:
Consider the equation:
(200×6)−(700×x)=1,000 AED
In this example, the exchange rate for the opening balance journal, where the amount is credited from the bank account, was found to be approximately 0.28571428.
By applying these adjustments, the balance discrepancies were corrected, and accurate financial data were maintained in both currencies in NetSuite.