Scenario
How to compute for the Average Consolidated Exchange Rate
Solution
Consolidated Exchange Rates are calculated based on the rate type set on Accounts. Average Consolidated Exchange Rate is the weighted average of the exchange rates of transactions applied during the period to Accounts with the Average General rate type.
Here are the steps to follow to verify the Average Consolidated Exchange Rate:
1. Create a Transaction Saved Search that will extract posting transactions associated to Accounts that uses the Average General Rate Type
a. Set the following Criteria on the Search:
- Account: General Rate Type = is Average
- Subsidiary = Child Subsidiary
- Period = Preferred Month
- Posting = True
b. Set the following fields as under Results tab:
- Consolidated Exchange Rate = None
- Show Totals = True
- Columns subtab:
-
- Document Number
- Date (important)
- Amount | Function = Absolute Value (important)
c. Export the result.
** Amounts will be shown in the child subsidiary's base currency
2. Create the Currency Exchange rate Saved Search that will track down the effective exchange rates for the preferred month.
a. Navigate to Lists > Accounting > Currency Exchange Rates > New > Search
b. Set the following Criteria on the Search.
- Base Currency: of Parent Subsidiary
- Transaction Currency: of Child Subsidiary
- Effective date: Date Range of Preferred Month
** if there are transactions in the transactions saved search above that are outside the selected period's date range, make sure to pull up those effective exchange rates too.
** if there are transactions in the transactions saved search above that are outside the selected period's date range, make sure to pull up those effective exchange rates too.
3. Export and place the currency exchange search result on another sheet.
4. Use V Look up function in Excel to pull up the effective exchange rates applicable to each transaction in the list.
Rationale: We need to convert the transaction amounts from the child subsidiary's base currency to the parent subsidiary's base currency
5. Add another column in Excel to get the transaction amounts in the parent subsidiary's base currency
Formula = Absolute Amount column from the transaction saved search * Effective Exchange Rate from V Look Up
6. Get the sum of transaction amounts in the parent's subsidiary base currency
7. Use the formula below to compute for the Average Consolidated Exchange Rate
Divide the sum (from step #6) by the total of the transactions in the child subsidiary's currency (from step #1)
Significant change in Average Consolidated Rate occurs when transaction’s effective date has no equivalent exchange rate. Also, the basis of the system in computing the average consolidated rate is the posting date of the transaction and not its effective date.