Foreign exchange sensitivity analysis for finance

Foreign exchange sensitivity analysis measures how alternative exchange rates change the dollar value of a company's revenue and costs while the underlying operating amounts stay fixed. Calculate the exposure separately for each currency, apply a supplied scenario rate, and reconcile the resulting dollar change. This separates a currency assumption from changes in sales volume, pricing or purchasing.

Define the measure and the rate quotation

Start with the business question. A US finance team may want to understand the currency effect on next quarter's contribution from selected customer and supplier contracts. That is a different scope from measuring every balance-sheet exposure or reproducing booked foreign-exchange gains and losses.

Net currency exposure = Revenue in that currency − Costs in that currency

USD contribution = Net currency exposure × USD per unit of that currency

Currency sensitivity = Net exposure × (Scenario rate − Base rate)

All rates below are USD per one unit of the named currency. A higher number means that unit converts into more dollars. This convention matters: the Federal Reserve's H.10 release generally quotes currency units per USD, with specified exceptions. Check the source heading before copying a rate into a workbook.

The example uses illustrative planning assumptions supplied by finance, rather than current market quotations. It keeps operating quantities and local-currency prices unchanged. For the broader distinction between changing one assumption and changing several together, see sensitivity analysis.

Prepare the currency amounts from your export

Start with customer revenue and attributable operating costs for the same planning period. Retain transaction currency, amount, period, business unit and the source contract or account. Aggregate into currency rows only after excluding duplicates and confirming which costs belong in the selected contribution measure.

The example is a US equipment business with euro customer receipts, Canadian-dollar supplier costs and domestic operations. These are the fixed operating inputs:

Do not total the unconverted currency-unit columns: euros, Canadian dollars and US dollars are different units. The scenario reduces the USD value of each foreign currency unit by 5%. For EUR, 1.1000 × 0.95 = 1.0450; for CAD, 0.7500 × 0.95 = 0.7125. USD remains at 1.0000 because it is the reporting currency.

Keep the gross revenue and cost columns even when the main question concerns net exposure. They let commercial and procurement teams see why a currency's net position is positive or negative, and help finance trace the amounts back to source rows.

Build the spreadsheet calculation

Put the six input columns above in A:F, with headers in row 1 and currencies in rows 2:4. Column D can be calculated rather than manually entered. Add G for base contribution, H for scenario contribution and I for the sensitivity:

D2 = B2-C2
G2 = D2*E2
H2 = D2*F2
I2 = H2-G2

Fill down through row 4. An independent check for column I uses the rate difference directly:

J2 = D2*(F2-E2)
K2 = I2-J2

Every K row must be zero apart from spreadsheet precision. Using unrounded rates, the results are:

EUR loses 500,000 × (1.0450 − 1.1000) = 27,500. CAD gains 11,250 because −300,000 × (0.7125 − 0.7500) is positive. The company therefore loses 27,500 − 11,250 = 16,250 of contribution under the supplied rates.

The combined result is smaller than the EUR loss because the CAD cost exposure moves the other way. That offset comes from the operating amounts shown above; it is not an assumption that all currencies will move together in practice.

Reconcile gross revenue and costs as well

Calculate revenue and costs separately under each rate table. These formulas preserve the gross view:

L2 = B2*E2
M2 = C2*E2
N2 = B2*F2
O2 = C2*F2

Fill down, then sum each column. The resulting totals must reproduce the net contribution table:

For example, base revenue is 1,200,000 × 1.1 + 600,000 × 0.75 + 2,000,000 = 3,770,000. The company check is 925,000 − 16,250 = 908,750. Display dollar results rounded to whole dollars while preserving unrounded values in the workbook.

Change the scenario without changing operations

For a second variation, increase both foreign-currency USD quotations by 5% from base. EUR becomes 1.1550 and CAD becomes 0.7875; USD remains 1.0000. EUR contribution becomes 500,000 × 1.1550 = 577,500, CAD becomes −300,000 × 0.7875 = −236,250, and domestic contribution remains 600,000.

Total contribution is now 941,250, an increase of 16,250 from base. The equal and opposite result follows from a linear calculation and symmetric rate changes. It would not necessarily hold if operating amounts, contract terms or other included exposures changed between scenarios.

Use scenario analysis in a spreadsheet when the business also wants to change sales and cost assumptions. If the question concerns valuation discount rates, DCF sensitivity analysis handles that separate job.

Assign the review and document its limits

Treasury should review the supplied currency assumptions, FP&A should own the operating exposure schedule, and the controller should confirm how the selected measure differs from booked accounting results. Record the scenario date, scope, exclusions and quotation convention beside the rate table.

The SEC's market-risk disclosure rule describes sensitivity using hypothetical changes in market rates and calls for explanations of assumptions. The same discipline helps an internal planning worksheet remain understandable when someone revisits it later. This worked contribution schedule is not, by itself, a complete market-risk disclosure.

Where it goes wrong

• Rate directions are mixed. Multiplying by foreign currency per USD reverses the intended conversion. Normalize quotations before applying scenarios.
• Foreign-currency amounts are already converted. Applying another rate to a USD ledger amount double-converts it. Keep original-currency amounts separate from reporting-currency columns.
• Revenue alone is called exposure. Costs in the same currency can offset revenue. Retain both gross amounts and reconcile the net position.
• Different periods are netted together. A receipt and payment may have different timing. Use period-level schedules when timing matters rather than assuming a quarterly net settles together.
• Contribution sensitivity is presented as total profit sensitivity. Taxes, financing, balance-sheet items and contracted currency arrangements are outside this example. Identify exclusions before using the result in a wider forecast.

Apply the scenarios to your own currency file

Bring a revenue-and-cost export with original currencies and a table of supplied scenario rates. Covirage maps the columns, and its tools compute and check the translated amounts; the external AI model explains the currency effects with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See FP&A reporting for the supporting finance workflow.