Export Sales Exchange Rate Effect and Margin by Currency Report
Short answer: A custom ALV report that splits the change in local currency revenue from foreign currency billing items into volume, price and exchange rate effects, and shows the gross margin by customer, country and currency. Exporters see how much of the revenue growth really comes from sales and how much from currency movements.
- Estimated effort
- M · 6–8 working days
- System
- ECC and S/4HANA
- Tables
- 7
- Scope
- Global
- Built by
- NeKuDos Teknoloji
- Last updated
Why is the standard not enough?
VF05 lists billing documents; KE30 profitability reports can show revenue in two currencies depending on configuration. There is no standard key figure structure that splits the revenue difference between two periods into volume, price and exchange rate effects, so this is usually done by hand in Excel. The custom report makes the split at billing item level with the posted rates.
Who uses it? Sales and export managers, finance and controlling teams, company management.
Closest standard alternatives
Tables used
The tables the report reads and their join fields. ECC and S/4HANA differences are marked separately.
| Table | Role | Join | System |
|---|---|---|---|
| VBRKBilling Document Header | Billing header: document currency, accounting rate, billing date (driving table) | — | ECC + S/4 |
| VBRPBilling Document: Item Data | Billing item: quantity, net value, cost | VBRP-VBELN = VBRK-VBELN | ECC + S/4 |
| KNA1Customer Master (General Section) | Customer name and country | KNA1-KUNNR = VBRK-KUNAG | ECC + S/4 |
| T001Company Codes | Company code currency | T001-BUKRS = VBRK-BUKRS | ECC + S/4 |
| TCURRExchange Rates | Average rate for the selected exchange rate type | TCURR-KURST = P_KURST AND TCURR-FCURR = VBRK-WAERK AND TCURR-TCURR = T001-WAERS | ECC + S/4 |
| MAKTMaterial Descriptions | Material description | MAKT-MATNR = VBRP-MATNR AND MAKT-SPRAS = SY-LANGU | ECC + S/4 |
| I_BillingDocumentItemBilling Document Item | In S/4HANA it can replace VBRP with a clean core approach | BillingDocument = VBRK-VBELN | S/4HANA only |
Selection screen parameters
| Parameter | Description | Type | Reference field | Required | Example |
|---|---|---|---|---|---|
| S_BUKRS | Company code | Range / multiple | VBRK-BUKRS | Yes | 1000 |
| S_VKORG | Sales organization | Range / multiple | VBRK-VKORG | No | 1000 |
| S_FKDAT | Current period billing date | Range / multiple | VBRK-FKDAT | Yes | 01.07.2026 – 30.09.2026 |
| S_FKDA0 | Comparison period billing date | Range / multiple | VBRK-FKDAT | Yes | 01.07.2025 – 30.09.2025 |
| S_WAERK | Document currency | Range / multiple | VBRK-WAERK | No | EUR, USD, GBP |
| S_LAND1 | Customer country | Range / multiple | KNA1-LAND1 | No | DE, NL, GB |
| P_KURST | Average rate type (if empty, the billing rate is used) | Single value | TCURR-KURST | No | M |
| P_LEVEL | Detail level: C = customer, M = material, D = currency | Single value | — | No | M |
Output columns
| # | Column | Source |
|---|---|---|
| 1 | Customer | KNA1-NAME1 |
| 2 | Country | KNA1-LAND1 |
| 3 | Currency | VBRK-WAERK |
| 4 | Quantity (current) | Calculated Σ VBRP-FKIMG, credit memos negative |
| 5 | Revenue (document currency) | Calculated Σ VBRP-NETWR |
| 6 | Average rate (current) | Calculated Local revenue / revenue in document currency |
| 7 | Revenue (local, current) | Calculated Σ VBRP-NETWR × VBRK-KURRF |
| 8 | Revenue (local, comparison) | Calculated Σ VBRP-NETWR × VBRK-KURRF in the comparison period |
| 9 | Volume effect | Calculated (Q1 − Q0) × P0 × R0 |
| 10 | Price effect | Calculated (P1 − P0) × Q1 × R0 |
| 11 | Exchange rate effect | Calculated Q1 × P1 × (R1 − R0) |
| 12 | Cost | Calculated Σ VBRP-WAVWR |
| 13 | Gross margin % | Calculated (Local revenue − cost) / local revenue × 100 |
Sample output
| Customer | Country | Currency | Quantity (current) | Revenue (document currency) | Average rate (current) | Revenue (local, current) | Revenue (local, comparison) | Volume effect | Price effect | Exchange rate effect | Cost | Gross margin % |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Nordic Apparel GmbH | DE | EUR | 42,000 | 252,000 | 47.80 | 12,045,600 | 9,126,000 | 1,162,800 | 516,600 | 1,240,200 | 9,154,800 | 24 |
| Demo Retail Ltd. | GB | GBP | 18,500 | 129,500 | 55.60 | 7,200,200 | 6,105,000 | 349,200 | 72,600 | 673,400 | 5,616,400 | 22 |
| Holland Textiles B.V. | NL | EUR | 9,600 | 61,440 | 47.90 | 2,943,000 | 3,168,000 | -712,800 | 115,200 | 372,600 | 2,384,000 | 19 |
| Atlantic Garments Inc. | US | USD | 26,000 | 135,200 | 41.20 | 5,570,200 | 4,416,000 | 368,000 | 197,600 | 588,600 | 4,511,000 | 19 |
How does the report work?
- Billing documents and credit memos that are not cancelled and fall into the current or comparison period are read from VBRK for the selected company code and sales organization; only those whose document currency differs from the company code currency (T001-WAERS) remain.
- Quantity (FKIMG), net value in document currency (NETWR) and cost (WAVWR) come from VBRP; credit memos are counted with a negative sign.
- Revenue in local currency = NETWR × VBRK-KURRF (the rate passed to accounting). Depending on the selection, the average rate in TCURR can be used instead.
- For each customer, material and currency, the average unit price (P) and average rate (R) are calculated for the current (1) and comparison (0) periods.
- Volume effect = (Q1 − Q0) × P0 × R0; price effect = (P1 − P0) × Q1 × R0; exchange rate effect = Q1 × P1 × (R1 − R0). The three effects add up to the local revenue difference.
- Gross margin = local revenue − cost; subtotals are given by customer, country and currency.
Performance notes
Limit VBRK by company code, billing date and document currency and read VBRP for the selected documents. Buffer TCURR rates once per period. With large volumes, run the report in the background.
Features
- ALV list (sort, filter, subtotals)
- User-specific layout variants
- Double-click drill-down to the document Double-clicking a line opens the related billing documents in VF05; double-clicking a document opens VF03.
- Export to Excel
- Runs as a background job
Authorization objects
- V_VBRK_VKOBilling document display authorization by sales organization
- V_VBRK_FKADisplay authorization by billing type
Frequently asked questions
Which exchange rate is used?
By default the rate with which the invoice was passed to accounting (VBRK-KURRF) is used, so the figures match revenue in the general ledger. Optionally the period average of a selected exchange rate type can be used for the comparison.
Are exchange differences on collection included?
No. The report shows the exchange rate effect on the sales side. Exchange gains and losses realized on collection are tracked separately in accounting; a separate report can be used for the valuation of open items.
Does it work on S/4HANA?
Yes. VBRK and VBRP are the same in S/4HANA; if clean core is preferred, billing items are read via I_BillingDocumentItem.
Related reports
Foreign Currency Open Items Exchange Difference Preview Report
A custom ALV report that reads customer and vendor open items by document currency, revalues them at the key-date rate and shows the unrealized …
Customers with Declining Order Intake vs Prior Year Report
A custom ALV report that compares each customer's order value in the selected period with the same period of the previous year and shows the drop in …
Sales Order Gross Margin Report by Item
A custom ALV report that compares the net value of every sales order item with the cost determined in pricing and shows the gross profit and margin …
Let us build this report for your system
Briefly describe your system and expectations. We will clarify the scope and come back with an effort and price quote.