What You Will Learn
You will learn how to convert revenue, cost, EBITDA, and market data into one reporting currency using dated FX rates.
Required Dataset
Use the workbook from Lesson 51.
Use:
FX_Rates
FX_Conversion
Create three new tabs:
Currency_Normalization
Normalized_Financials
Normalization_Check
Before You Start
In Currency_Normalization, create fields for:
Company
Reporting Date
Source Currency
Target Currency
Revenue
Cost
EBITDA
Market Cap
Currency Pair
FX Rate
Returned FX Date
Normalized Revenue
Normalized Cost
Normalized EBITDA
Normalized Market Cap
Review Needed
Use USD as the target currency for this exercise.
Step-by-Step Instructions
Step 1. Build the currency pair
If Source Currency is in column C and Target Currency is in column D, use:
=IF(C2=D2,”Same Currency”,”CURRENCY:”&C2&D2)
This creates pairs such as CURRENCY:EURUSD.
Step 2. Pull the dated FX rate
If Reporting Date is in column B and Currency Pair is in column I, use:
=IF(C2=D2,1,IFERROR(INDEX(GOOGLEFINANCE(I2,”price”,B2),2,2),””))
For returned FX date:
=IF(C2=D2,B2,IFERROR(INDEX(GOOGLEFINANCE(I2,”price”,B2),2,1),””))
Step 3. Normalize financial values
Use:
=IFERROR(Revenue*FX Rate,””)
Use the same pattern for cost, EBITDA, and market cap.
The key rule is:
Normalized Amount = Source Amount × FX Rate
Step 4. Ask Gemini to review the logic
Use this prompt:
Act as a finance formula reviewer. Review the Currency_Normalization tab. Check currency pair direction, dated FX logic, returned date, normalized revenue, normalized cost, normalized EBITDA, normalized market cap, and review-needed flags.
Expected Result
You should have a financial table where all values are converted into one reporting currency.
Check Your Work
Confirm that same-currency rows use an FX rate of 1, currency pairs point in the correct direction, returned FX dates are checked, and source-currency values are not summed across currencies.
Optional Extension
Ask Gemini:
Create a constant-currency growth view using prior-period revenue, current-period revenue, prior FX rate, current FX rate, reported growth, constant-currency growth, and FX impact.
