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.

6.0 Product Knowledge

If you have a comment on this topic, contact Aptify Documentation. If you want to return to the Aptify Community Site, please click here.

About the Weighted Spot Rates