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

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-2017 Aptify - Confidential and Proprietary