Foreign Currency Transaction with Revenue Allocation
The functions discussed in this topic require the Revenue Commitments feature to be enabled.
This example illustrates the line level deferred revenue reclassification process as of Version 2013 Release 2. See Adopting Line Level Deferred Revenue Reclassification.
The key difference between this example and Base Currency Transaction with Revenue Allocation is that foreign currency is used on this sales contract.
On January 1, you create a sales order with the following line items. Each item has its own revenue and deferred revenue accounts (Rev1, Rev2, Rev3, Rev4, DefRev1, DefRev2, DefRev3, and DefRev4). The sales order is written in GBP, and the spot foreign exchange rate is 2 USD/GBP.
Item |
Sales Amount |
VSOE Ratio |
Revenue Allocation |
Carve In Amount |
Carve In Ratio |
Carve Out Amount |
Carve Out Ratio |
---|---|---|---|---|---|---|---|
1 |
£100 |
0.25 |
60 |
0 |
0 |
40 |
0.4 |
2 |
£50 |
0.17 |
40 |
0 |
0 |
10 |
0.2 |
3 |
£50 |
0.38 |
90 |
40 |
0.8 |
0 |
0 |
4 |
£40 |
0.21 |
50 |
10 |
0.2 |
0 |
0 |
Total |
£240 |
1 |
240 |
50 |
|
50 |
|
The same logic is used to calculate carve in/out ratios as in the Base Currency Transaction with Revenue Allocation example.
On January 20, you create an invoice to partially bill the order. The exchange rate is 1.5 USD/GBP. This table shows how the billing amount allocation is calculated:
J |
1.5 USD/ |
||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Item |
A |
A |
A |
Gross C |
Gross C |
Carve In (FX) |
Carve In (Base) |
C |
C |
Effective C |
Effective C |
A |
Carving A |
1 |
£50 |
$75 |
Def |
£50 |
$75 |
|
|
20 |
$30 |
£30 |
$45 |
1.5 |
–30 |
2 |
£20 |
$30 |
Def |
£20 |
$30 |
|
|
4 |
$6 |
£16 |
$24 |
1.5 |
–6 |
3 |
£30 |
$45 |
Def |
£30 |
$45 |
£19.2 |
$28.8 |
|
|
£49.2 |
$73.8 |
1.5 |
28.8 |
4 |
£20 |
$30 |
Def |
£20 |
$30 |
£4.8 |
$7.2 |
|
|
£24.8 |
$37.2 |
1.5 |
7.2 |
Total |
£120 |
$180 |
A/R |
£120 |
$180 |
£24 |
$36 |
24 |
$36 |
£120 |
$180 |
|
0 |
During the billing amount allocation, the gross billing amount is redistributed to individual line items. Because the sales order is denominated by a foreign currency, the billing amount allocation process happens twice:
-
First, to convert the transaction currency amounts in the Gross Cumulative Billing (FX) column to the amounts in the Effective Cumulative Billing (FX) column:
-
The item 1 GBP 50 gross cumulative billing becomes GBP 30 effective cumulative billing.
-
The item 2 GBP 20 gross cumulative billing becomes GBP 16 effective cumulative billing.
-
The item 3 GBP 30 gross cumulative billing becomes GBP 49.2 effective cumulative billing.
-
The item 4 GBP 20 gross cumulative billing becomes GBP 24.8 effective cumulative billing.
-
-
A second time for the base currency amount, to convert the “Gross cumulative billing (base)” to “Effective cumulative billing (base)”
-
The item 1 USD 75 gross cumulative billing becomes USD 45 effective cumulative billing.
-
The item 2 USD 30 gross cumulative billing becomes USD 24 effective cumulative billing.
-
The item 3 USD 45 gross cumulative billing becomes USD 73.8 effective cumulative billing.
-
The item 4 USD 30 gross cumulative billing becomes USD 37.2 effective cumulative billing.
-
-
Then divide each item’s effective cumulative billing (base) by its effective cumulative billing (FX), to get the average foreign currency exchange rate on this item.
-
The last column shows the base currency deferred revenue adjustment posting, which converts the gross cumulative billing (base) to effective cumulative billing (base). When billing has occurred over multiple accounting periods, this column shows only the net adjustment within the current period.
At the end of January, the following revenue recognition amounts are posted with a foreign currency exchange rate of 2 USD/GBP:
R |
|
|
|
2 USD/GBP |
|||
---|---|---|---|---|---|---|---|
Item |
Amount |
Cumulative Rev Rec (FX) |
Account |
Debit |
Credit |
Debit (Base) |
Credit (Base) |
1 |
£10 |
10 |
DefRev1 |
10 |
|
20 |
|
|
|
|
Rev1 |
|
10 |
|
20 |
2 |
£30 |
30 |
DefRev2 |
30 |
|
60 |
|
|
|
|
Rev2 |
|
30 |
|
60 |
3 |
£70 |
70 |
DefRef3 |
70 |
|
140 |
|
|
|
|
Rev3 |
|
70 |
|
140 |
4 |
£20 |
20 |
DefRev4 |
20 |
|
40 |
|
|
|
|
Rev4 |
|
20 |
|
40 |
Total |
£130 |
130 |
|
130 |
130 |
260 |
260 |
The next step is to calculate the foreign currency gain/loss on individual items. The logic is as follows:
-
Find the overlapping foreign currency amount. This is the lesser of the cumulative effective billing and the cumulative revenue recognition, both in the foreign currency. In this example:
-
Item 1 has an overlapping amount of GBP 10, the cumulative revenue recognition amount.
-
Item 2 has an overlapping amount of GBP 16, the cumulative effective billing amount.
-
Item 3 has an overlapping amount of GBP 49.2, the cumulative effective billing amount.
-
Item 4 has an overlapping amount of GBP 20, the cumulative revenue recognition amount.
-
-
Compare the average billing foreign currency exchange rate calculated on individual items to their average revenue recognition foreign currency exchange rates, and calculate the foreign currency gain/loss as the difference.
Foreign currency gain/loss = Overlapping foreign currency amount * (average billing foreign currency exchange rate – average revenue recognition foreign currency exchange rate)
-
Sum up the line level foreign currency gain/loss to get the order level foreign currency gain/loss.
This table shows the detailed foreign currency gain/loss calculation for January and its GL posting in base currency:
Line Level Foreign C |
|||||
---|---|---|---|---|---|
Item |
Overlap |
Gain/Loss |
Account |
Debit |
Credit |
1 |
10 |
–5 |
Rev1 |
5 |
|
|
|
|
DefRev1 |
|
5 |
2 |
16 |
–8 |
Rev2 |
8 |
|
|
|
|
DefRev2 |
|
8 |
3 |
49.2 |
–24.6 |
Rev3 |
24.6 |
|
|
|
|
DefRev3 |
|
24.6 |
4 |
20 |
–10 |
Rev4 |
10 |
|
|
|
|
DefRev4 |
|
10 |
Total |
95.2 |
$–47.6 |
|
|
|
At the end of January, the total revenue recognition base currency amount on this sales order is $260 – $47.60 = $212.40. The total billing base currency amount is $180. This leaves an under-billed amount of $32.40. This is posted as an adjustment JE to the unbilled receivable account. If the foreign currency exchange rate in the end of January is 2 USD/GBP, the adjustments are as follows:
Month-end adjustment |
January |
|||
---|---|---|---|---|
Account |
Debit (Base) |
Credit (Base) |
Debit (FX) |
Credit (FX) |
Unbilled A/R |
32.4 |
|
16.2 |
|
Deferred Revenue System |
|
32.4 |
|
16.2 |
Because the sales order is denominated by foreign currency, the unbilled receivable adjustment is also posted in the foreign currency, British pounds, with the foreign currency exchange rate 2 USD/GBP (the January ending rate). The foreign currency balance on unbilled receivable will go through the period-end foreign currency revaluation and generate unrealized gain/loss if the exchange rate fluctuates from the original unbilled receivable posting rate. This exchange rate, used by the first unbilled receivable adjustment JE of a specific sales order, is recorded as a fixed exchange rate and used by all subsequent unbilled receivable adjustment JE postings for the sales order.
Here are the balances at the end of January:
Ending Balance |
January 31 |
---|---|
A/R |
180.00 |
Rev1 |
15.00 |
Rev2 |
52.00 |
Rev3 |
115.40 |
Rev4 |
30.00 |
Total Revenue |
212.40 |
DefRev1 |
30.00 |
DefRev2 |
–28.00 |
DefRev3 |
–41.60 |
DefRev4 |
7.20 |
Deferred Revenue System |
32.40 |
Total Deferred Revenue |
0.00 |
Unbilled A/R |
32.40 |
The total deferred revenue balance of this order is now zero, and there is a positive $32.40 balance in the unbilled receivable account.
In February, you create another bill on this order with a different foreign currency exchange rate, 3 USD/GBP. This triggers the same billing amount allocation logic:
F |
3 USD/ |
||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Item |
A |
A |
A |
Gross C |
Gross C |
Carve In (FX) |
Carve In ( |
C |
Carve Out ( |
E |
Effective C |
Average FX Rate |
Carving A |
1 |
£50 |
$150 |
Def |
£100 |
$225 |
|
|
40 |
$90 |
£60 |
$135 |
2.25 |
–60 |
2 |
£20 |
$60 |
Def |
£40 |
$90 |
|
|
8 |
$18 |
£32 |
$72 |
2.25 |
–12 |
3 |
£15 |
$45 |
Def |
£45 |
$90 |
£38.4 |
$86.4 |
|
|
£83.4 |
$176.4 |
2.115 |
57.6 |
4 |
£20 |
$60 |
Def |
£40 |
$90 |
£9.6 |
$21.6 |
|
|
£49.6 |
$111.6 |
2.25 |
14.4 |
Total |
£105 |
$315 |
A/R |
£225 |
$495 |
£48 |
$108 |
48 |
$108 |
£225 |
$495 |
|
0 |
The average foreign currency exchange rate on each item is re-calculated based on the latest effective cumulative billing amounts (foreign currency and base).
At the end of February, you post the following revenue recognition amounts with a foreign currency exchange rate of 2 USD/GBP:
R |
2 USD/GBP |
||||||
---|---|---|---|---|---|---|---|
Item |
Amount |
Cumulative Rev Rec (FX) |
Account |
Debit |
Credit |
Debit (Base) |
Credit (Base) |
1 |
£30 |
40 |
DefRev1 |
30 |
|
60 |
|
|
|
|
Rev1 |
|
30 |
|
60 |
2 |
£5 |
35 |
DefRev2 |
5 |
|
10 |
|
|
|
|
Rev2 |
|
5 |
|
10 |
3 |
£20 |
90 |
DefRef3 |
20 |
|
40 |
|
|
|
|
Rev3 |
|
20 |
|
40 |
4 |
£10 |
30 |
DefRev4 |
10 |
|
20 |
|
|
|
|
Rev4 |
|
10 |
|
20 |
Total |
£65 |
195 |
|
65 |
65 |
130 |
130 |
With the new billing and revenue recognition, the foreign currency gain/loss on individual items is calculated. The last two columns show the net posting in this period to derive the latest foreign currency gain/loss from the prior month’s foreign currency gain/loss.
Line Level Foreign C |
|||||
---|---|---|---|---|---|
Item |
Overlap |
Gain/Loss |
Account |
Debit |
Credit |
1 |
40 |
10 |
Rev1 |
|
15 |
|
|
|
DefRev1 |
15 |
|
2 |
32 |
8 |
Rev2 |
|
16 |
|
|
|
DefRev2 |
16 |
|
3 |
83.4 |
9.6 |
Rev3 |
|
34.2 |
|
|
|
DefRev3 |
34.2 |
|
4 |
30 |
7.5 |
Rev4 |
|
17.5 |
|
|
|
DefRev4 |
17.5 |
|
With the new revenue recognition amounts and foreign currency gain/loss amounts, the effective revenue recognition amount in base currency is now $425.10. The total billing amount of $495 surpasses the revenue recognition amount. Consequently, you reverse the prior unbilled receivable adjustment using the same foreign currency exchange rate as the original unbilled receivable reclassification JE:
Month-end adjustment |
February |
|||
---|---|---|---|---|
Account |
Debit (Base) |
Credit (Base) |
Debit (FX) |
Credit (FX) |
Unbilled A/R |
|
32.4 |
|
16.2 |
Deferred Revenue System |
32.4 |
|
16.2 |
|
The ending account balances for February are as follows:
Ending Balance |
February 28 |
---|---|
A/R |
495.00 |
Rev1 |
90.00 |
Rev2 |
78.00 |
Rev3 |
189.60 |
Rev4 |
67.50 |
Total Revenue |
425.10 |
DefRev1 |
45.00 |
DefRev2 |
–6.00 |
DefRev3 |
–13.20 |
DefRev4 |
44.10 |
Deferred Revenue System |
0.00 |
Total Deferred Revenue |
69.90 |
Unbilled A/R |
0.00 |
In March, you continue this process and create an invoice for the remaining value of this order with a foreign currency exchange rate of 1.5 USD/GBP as shown in this table:
M ( |
1.5 USD/ |
||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Item |
A |
A |
A |
Gross C |
Gross C |
C |
Carve In (Base) |
C |
Carve Out ( |
E |
Effective C |
Average FX Rate |
Carving A |
1 |
£0 |
$0 |
Def |
£100 |
$225 |
|
|
£40 |
$90 |
£60 |
$135 |
2.25 |
0 |
2 |
£10 |
$15 |
Def |
£50 |
$105 |
|
|
£10 |
$21 |
£40 |
$84 |
2.1 |
–3 |
3 |
£5 |
$7.5 |
Def |
£50 |
$97.5 |
£40 |
$88.8 |
|
|
£90 |
$186.3 |
2.07 |
2.4 |
4 |
£0 |
$0 |
Def |
£40 |
$90 |
£10 |
$22.2 |
|
|
£50 |
$112.2 |
2.244 |
0.6 |
Total |
£15 |
$22.5 |
A/R |
£240 |
$517.5 |
£50 |
$111 |
£50 |
$111 |
£240 |
$517.5 |
|
0 |
When a sales order is fully billed, the effective cumulative billing amount (FX) on an item should equal its revenue allocation amount (FX). This is not true for the effective cumulative billing amount in base currency because multiple bills can be created with different foreign currency exchange rates. The process is to derive the final average foreign currency exchange rates on individual items as shown in the previous table. Then recognize the residual revenue amounts.
R |
2 USD/GBP |
||||||
---|---|---|---|---|---|---|---|
Item |
Amount |
Cumulative Rev Rec (FX) |
Account |
Debit |
Credit |
Debit (Base) |
Credit (Base) |
1 |
£20 |
60 |
DefRev1 |
20 |
|
40 |
|
|
|
|
Rev1 |
|
20 |
|
40 |
2 |
£5 |
40 |
DefRev2 |
5 |
|
10 |
|
|
|
|
Rev2 |
|
5 |
|
10 |
3 |
£0 |
90 |
DefRef3 |
0 |
|
0 |
|
|
|
|
Rev3 |
|
0 |
|
0 |
4 |
£20 |
50 |
DefRev4 |
20 |
|
40 |
|
|
|
|
Rev4 |
|
20 |
|
40 |
Total |
£45 |
240 |
|
45 |
45 |
90 |
90 |
The foreign currency gain/loss is recalculated as follows:
Line Level Foreign C |
|||||
---|---|---|---|---|---|
Item |
Overlap |
Gain/Loss |
Account |
Debit |
Credit |
1 |
60 |
15 |
Rev1 |
|
5 |
|
|
|
DefRev1 |
5 |
|
2 |
40 |
4 |
Rev2 |
4 |
|
|
|
|
DefRev2 |
|
4 |
3 |
90 |
6.3 |
Rev3 |
3.3 |
|
|
|
|
DefRev3 |
|
3.3 |
4 |
50 |
12.2 |
Rev4 |
|
4.7 |
|
|
|
DefRev4 |
4.7 |
|
With the new foreign currency gain/loss, the total revenue recognition amount in base currency is $517.50, exactly the same as the total billing amount in base currency because the order is fully billed and recognized. No additional unbilled receivable adjustment is required in this period.
The ending balances in March are:
Ending Balance |
March 31 |
---|---|
A/R |
517.50 |
Rev1 |
135.00 |
Rev2 |
84.00 |
Rev3 |
186.30 |
Rev4 |
112.2 |
Total Revenue |
517.50 |
DefRev1 |
0.00 |
DefRev2 |
0.00 |
DefRev3 |
0.00 |
DefRev4 |
0.00 |
Deferred Revenue System |
0.00 |
Total Deferred Revenue |
0.00 |
Unbilled A/R |
0.00 |
As shown, when a sales order is fully billed and recognized, even though different billing and revenue recognition exchange rates are used, the result is a zero balance on all deferred revenue and unbilled receivable accounts.