Data Modelling
Part of Financial performance analysis in BI
Documenting currency conversion assumptions
Record currency pair, rate source, date, direction and missing-rate treatment so converted BI figures can be reproduced and reconciled.
A financial BI report that combines currencies should state what was converted, why, at which rate and on which date. Keep the original amount and currency alongside the converted result. Two reports can otherwise disagree because they answer different currency questions.
Name the purpose of the figure
Distinguish a finance-approved ledger amount, translation for a presentation currency, and a management comparison at a chosen reference rate. Label a constant-rate comparison as an analytical view; keep it separate from reported amounts.
The applicable rate and date depend on the accounting treatment and reporting purpose. Finance must determine and document the treatment for the item and entity; a BI rate setting cannot do so.
Record the conversion rule
Document these fields for each converted measure:
| Field | What to record |
|---|---|
| Currency pair | Source and target currencies |
| Input | Original transaction amount, ledger amount or other approved basis |
| Rate source | Provider or maintained finance table and its version |
| Rate date | Transaction date, period end or approved management reference date |
| Quote direction | Target units per source unit, or the inverse |
| Missing-rate rule | Whether the result is held, flagged or uses an approved substitute |
| Precision | Calculation precision and display-rounding point |
| Owner | Who approves changes and resolves exceptions |
Write the formula in words as well as code. For example, a quote in foreign-currency units per Australian dollar must be inverted when converting a foreign-currency amount to Australian dollars. Check the direction with an independently calculated example before release.
Explain date and source limits
Choose and document a rate-date approach that fits the approved reporting purpose. A monthly report does not automatically justify using a monthly-average rate.
The Reserve Bank of Australia publishes exchange-rate series. It describes rates for most currencies other than the US dollar as indications of market value that may differ from dealer quotes, and cautions against relying on its published data for regulatory or commercial purposes.
An organisation considering those series must assess whether they suit its approved reporting purpose; the rate used in its ledger may come from another source. The RBA's daily series also excludes New South Wales public and bank holidays.
State how the report handles weekends, holidays and missing rates instead of silently choosing another date. Keep the requested and applied rate dates visible. If a corrected rate or late transaction changes an earlier result, identify which reports need revision or an annotation.
Key Exchange Rate Data Sources and Limitations
- Rate provider
- Reserve Bank of Australia (RBA)
- Data type
- Market value indications (not dealer quotes)
- Exclusions
- New South Wales public and bank holidays
- Use caution for
- Regulatory or commercial decisions
Reconcile the result
For a closed period, compare original-currency subtotals with their source before conversion. Then compare converted amounts with the appropriate finance reference using the same entity, item population and accounting basis. Separate rate differences from missing records, recognition dates and rounding. A rate difference cannot explain a missing transaction.
Retain the rate-table version, conversion rule and report cutoff with the released figure. This makes the amount reproducible and explains why a management constant-rate view can differ from a ledger or presentation-currency amount.
