Multi-currency sales and profit dashboard
For an online retailer with three stores, we built a sales and profit dashboard that works across currencies. The stores sell in US dollars, euros and Canadian dollars. The dashboard converts every order to US dollars, so the three stores can be added up and compared.
- Client
- Online retailer with three stores
- Service
- Dashboard
- Data
- 2 years of orders in USD, EUR and CAD
- Tools
- Python, pandas, PostgreSQL, Django, Bash
The questions
- How do the three stores compare once every sale is in one currency?
- How much of each store's growth is demand, and how much is the exchange rate?
- Which store earns the best margin?
Example outputs
Client data stays confidential, so these charts use sample numbers in the same format as the delivered reports.
- +1 ptof the year's reported growth comes from exchange rates
- -4 ptsexchange-rate drag on the Canada store, which grew 6% at constant rates
- 42%gross margin at the EU store, the highest of the three
How the data moves
Orders from three stores and a table of daily rates feed one dashboard.
- US store orders in USD
- EU store orders in EUR
- Canada store orders in CAD
- Daily exchange rates
Monthly sales by store
Index, January = 100. Sales are converted to US dollars at each order's date.
- US store
- EU store
- Canada store
Growth with and without the exchange rate
Year on year. Constant rates means this year's sales converted at last year's rates. The US store sells in USD, so it has no exchange-rate effect.
| Store | Share of sales | Growth as reported | Growth at constant rates | Exchange-rate effect | Gross margin |
|---|---|---|---|---|---|
| US store | 48% | +14% | +14% | 0 pts | 38% |
| EU store | 33% | +9% | +5% | +4 pts | 42% |
| Canada store | 19% | +2% | +6% | -4 pts | 31% |
| All stores | 100% | +10% | +9% | +1 pt | 38% |
Approach
- 1
Collect orders from each store
Load orders, refunds and product costs from the three stores every day, each in the currency it was sold in.
- 2
Convert at the order-date rate
Keep a table of daily exchange rates and convert each order at the rate on the day it was placed. A refund uses the rate of its original order, so it cancels the sale exactly.
- 3
Keep both amounts
Store the original amount, the converted amount and the rate used on every order line. Any total on the dashboard can be traced back to the store's own report.
- 4
Separate currency from demand
Convert this year's sales again at last year's rates. The gap between the two growth figures is the exchange-rate effect.
- 5
Build the dashboard
Sales, profit and margin by store, product and period, with the same definitions on every view.