Foreign Currency Transaction with Revenue Allocation

Important:

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:

January 20												1.5 USD/GBP
Item	Amount (FX)	Amount (Base)	Account	Gross Cumulative Billing (FX)	Gross Cumulative Billing (Base	Carve In (FX)	Carve In (Base)	Carve Out (FX)	Carve Out (Base)	Effective Cumulative Billing (FX)	Effective Cumulative Billing (Base)	Average FX Rate	Carving Adjustment Posted to Deferred Revenue
1	£50	$75	DefRev1	£50	$75			20	$30	£30	$45	1.5	–30
2	£20	$30	DefRev2	£20	$30			4	$6	£16	$24	1.5	–6
3	£30	$45	DefRev3	£30	$45	£19.2	$28.8			£49.2	$73.8	1.5	28.8
4	£20	$30	DefRev4	£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:

Revenue Recognition (January 31)							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 Currency Calculation
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

Note:

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:

February 20												3 USD/GBP
Item	Amount (FX)	Amount (Base)	Account	Gross Cumulative Billing (FX)	Gross Cumulative Billing (Base	Carve In (FX)	Carve In (Base)	Carve Out (FX)	Carve Out (Base)	Effective Cumulative Billing (FX)	Effective Cumulative Billing (Base)	Average FX Rate	Carving Adjustment Posted to Deferred Revenue
1	£50	$150	DefRev1	£100	$225			40	$90	£60	$135	2.25	–60
2	£20	$60	DefRev2	£40	$90			8	$18	£32	$72	2.25	–12
3	£15	$45	DefRev3	£45	$90	£38.4	$86.4			£83.4	$176.4	2.115	57.6
4	£20	$60	DefRev4	£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:

Revenue Recognition (February 28)							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 Currency Calculation
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:

March 20 (fully billed, fully recognized)												1.5 USD/GBP
Item	Amount (FX)	Amount (Base)	Account	Gross Cumulative Billing (FX)	Gross Cumulative Billing (Base	Carve In (FX)	Carve In (Base)	Carve Out (FX)	Carve Out (Base)	Effective Cumulative Billing (FX)	Effective Cumulative Billing (Base)	Average FX Rate	Carving Adjustment Posted to Deferred Revenue
1	£0	$0	DefRev1	£100	$225			£40	$90	£60	$135	2.25	0
2	£10	$15	DefRev2	£50	$105			£10	$21	£40	$84	2.1	–3
3	£5	$7.5	DefRev3	£50	$97.5	£40	$88.8			£90	$186.3	2.07	2.4
4	£0	$0	DefRev4	£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.

Revenue Recognition (March 31)							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 Currency Calculation
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.

Related Topics

Revenue Commitment Examples

Base Currency Transaction Without Revenue Allocation

Base Currency Transaction with Revenue Allocation

VSOE with Foreign Currency Revenue Commitment Example

Revenue Commitment with One-Time Revenue Item Example

General Notices