About the Weighted Spot Rates
When foreign transactions are batched and exported, the currency spot rates associated with the batched transactions are calculated and exported as well. A calculated currency spot rate is required because the individual transactions reference currency spot rates that are, in all probability, not the same for each transaction in the general ledger batch. Also, since general ledger (GL) batches do not consist of just one transaction, a method is employed to determine the overall currency spot rate for the batch. This is accomplished by calculating a weighted average for the currency spot rates.
Examples are provided in the following sections:
Calculating Weighted Currency Spot Rates
The following example shows how the weighted spot rate is determined during the GL batching process. The transactions reflect sales from Company A, which operates in the United States with a functional currency of US dollars and makes sales in US dollars and Canadian dollars. Two order transactions are included in the GL batch.
On January 3rd, a sale for C$100,000 was made and the currency spot rate for the transaction was $1.55 CAN to $1 USD. The cost of goods sold for the order is C$77,500. The following table lists the GL entries for the transaction.
Date | Account | Debit | Credit |
January 3 | Accounts Receivable | C$100,000 |
|
January 3 | Sales |
| C$100,000 |
January 3 | COGS | C$77,500 |
|
January 3 | Inventory |
| C$77,500 |
On January 4, a sale for C$200,000 was made and the currency spot rate for the transaction was $1.525 CAN to $1 USD. The terms for this order include a 10% discount on the order making the order total C$180,000. The cost of goods sold for the order is C$152,500. The following table lists the GL entries for the transaction.
Date | Account | Debit | Credit |
January 4 | Accounts Receivable | C$180,000 |
|
January 4 | Sales |
| C$200,000 |
January 4 | Sales Discounts | C$20,000 |
|
January 4 | COGS | C$152,500 |
|
January 4 | Inventory |
| C$152,500 |
The general ledger batch that includes the two orders consists of the following entries.
Account | Debit | Credit | Weighted Spot Rate |
Accounts Receivable | C$280,000 |
| 1.533835 |
Sales |
| C$300,000 | 1.533243 |
Sales Discounts | C$20,000 |
| 1.525 |
COGS | C$230,000 |
| 1.533333 |
Inventory |
| C$230,000 | 1.533333 |
The weighted spot rate is calculated during the general ledger batching process by using the following formula:
Sum (FC1 ... FCn) / Sum[(FC1/SR1) ... (FCn/SRn)]
To calculate the value of the foreign currency amount in the functional currency, you divide the foreign currency amount by the weighted average spot rate (as shown in the right-hand column in the tables below).
For this example, the following weighted spot rates were calculated.
Accounts Receivable Calcuation
The Accounts Receivable weighted spot rate calculation is:
(100000+180000) / [(100000/1.55) +(180000/1.525)] = 1.5338354
| Amount | Currency Spot Rate | Amount / Currency Spot Rate |
January 3 Order | C$100,000 | 1.55 | $64,516.12903258 |
January 4 Order | C$180,000 | 1.525 | $118,032.78688524 |
Total | C$280,000 |
| $182,548.92 (USD) |
Sales Calculation
The Sales weighted spot rate calculation is
(100,000+200,000) / [(100,000/1.55)+(200,000/1.525) = 1.533243243.
| Amount | Currency Spot Rate | Amount / Currency Spot Rate |
January 3 Order | C$100,000 | 1.55 | $64,516.12903 |
January 4 Order | C$200,000 | 1.525 | $131,147.541 |
Total | C$300,000 |
| $195,663.67 (USD) |
Sales Discount Calcuation
The Sales Discount weighted spot rate calculation is 20,000 / (20,000/1.525) = 1.525.
| Amount | Currency Spot Rate | Amount / Currency Spot Rate |
January 4 Order | C$20,000 | 1.525 | $13,114.7541 |
Total | C$20,000 |
| $13,114.7541 (USD) |
Cost of Goods Sold Calculation
The Cost of Goods Sold weighted spot rate calculation is (77500+152500)/((77500/1.55)+(152500/1.525)) = 1.5333333.
| Amount | Currency Spot Rate | Amount /Currency Spot Rate |
January 3 Order | C$77,500 | 1.55 | $50,000 |
January 4 Order | C$152,500 | 1.525 | $100,000 |
Total | C$230,000 |
| $150,000 (USD) |
Inventory Calculation
The Inventory weighted spot rate calculation is (77500+152500)/((77500/1.55)+(152500/1.525)) = 1.5333333.
| Amount | Currency Spot Rate | Amount / Currency Spot Rate |
January 3 Order | C$77,500 | 1.55 | $50,000 |
January 4 Order | C$152,500 | 1.525 | $100,000 |
Total | C$230,000 |
| $150,000 (USD) |
Weighted Spot Rate Account Effects
Weighted spot rates are a type of average. When totals from individual calculations are compared to totals calculated using a weighted average, the results are the same. The following example uses at the Accounts Receivable portion of two order transactions from Calculating Weighted Currency Spot Rates and compares the amount that is posted to A/R if the individual transactions are posted as compared to the amount that is posted to A/R from the batched transactions.
Transaction 1
A sale of C$100,000, when the currency spot rate is $1/C$1.55, results in a US dollar transaction value of $64,516.13
Transaction 2
A sale of C$180,000, when the currency spot rate is $1/C$1.525, results in a US dollar transaction value of $118,032.79
Individual Transaction Total
Posting the individual transactions to the A/R increases the value of the A/R by ($64,516.13 + $118,032.79) or $182,548.92.
Weighted Transaction Total
The weighted spot rate for these transactions is 280,000 / ((100,000/1.55) + (180,000/1.525)) or 1.533835458. When the total transaction amount of C$280,000 is converted to US dollars using the weighted spot rate, the total is $182,548.92. When the GL batch is posted, the value of the A/R increases by $182,548.92.
Evaluation
Whether the calculation is done individually on each transaction or with a weighted spot rate on a group of like transactions the values of the GL batches are the same. The weighted spot rate allows the GL batching process to group together like GLs and currency types regardless of the different spot rates referenced by each transaction.
Copyright © 2014-2019 Aptify - Confidential and Proprietary