Work snippetMulti-currency
Multi-currency invoices, payments and revaluation from Jobber to Xero
How I would approach CAD and GBP clients for a 12-tech installer with a USD ledger, so each invoice and payment carries a stored rate, the difference is posted as a realised gain or loss, and open items are revalued at month end.
Work snippet with example figures.
- Area
- Multi-currency
- Tools
- Jobber · Xero
- Typical scope
- US$2,000–3,600
- Timeline
- About 5 weeks · 4 fixed milestones
Context and systems map
A 12-tech specialist installation business based in one country that also serves clients in two others. The ledger is in USD, the client base is billed in CAD and GBP, and Jobber holds the clients and invoices. Today the foreign invoices are keyed into Xero by hand at whatever rate the bookkeeper remembers.
The systems in play
In the example each client carries a currency, invoices reach Xero in that currency at a defined rate, payments are recorded at the rate on the day, and the difference is posted to a gain or loss account instead of vanishing into a rounding line.
| System | What it does for the business |
|---|---|
| Jobber | Clients, quotes, jobs and invoices. The currency is held as a field on each client in the example. |
| Xero | Ledger in USD with CAD and GBP enabled, foreign currency invoices, payments and a revaluation at month end. |
| Rate source | One published daily rate table used for every conversion, saved with each document. |
| Sync flow | A scheduled flow that creates invoices and payments in Xero in the client’s currency. |
| Bookkeeper | Works the exceptions list and posts the month-end revaluation. |
| Accountant | Approves the gain and loss accounts and the revaluation method. |
How the data moves
- Client currency set in Jobber
- Invoice in client currency
- Invoice in Xero with the day’s rate
- Payment at the payment-day rate
- Realised gain or loss
- Month-end revaluation of open items
Problem
What the owner sees
- Invoices in foreign currency that arrive in the ledger as one lump of US dollars
- Customers who underpay by the bank fee and nobody knows why
- Month-end balances that do not move with the exchange rate
- A reconciliation difference every month that nobody can explain
Foreign currency adds two moving parts: the rate when the invoice is raised and the rate when it is paid. If the job system and the ledger disagree on either, the balance is wrong in at least one currency. The fix is a single rate source and a defined treatment for every document type.
| Everyday situation | What goes wrong |
|---|---|
| Invoice keyed in USD | The CAD amount the customer sees and the ledger amount do not match |
| Payment at a different rate | The difference lands in a rounding account or in sales |
| Credit note in a foreign currency | Converted at today’s rate instead of the original invoice rate |
| Bank fee deducted by the customer’s bank | The invoice stays open for a few dollars forever |
| Open balances at month end | Receivables are never revalued |
| Client with two currencies | Invoices end up in the wrong one |
Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookReview of the clients, invoices and payments in each currency for the last 12 months, with the rates actually used.
- Agree the rulesRate source, rate timing, gain and loss accounts, credit note treatment and revaluation method are written down.
- Build and testThe flow is tested on past invoices in each currency with the results compared to a hand calculation.
- Go live and closeThe flow goes live for new documents. Month-end revaluation is run with the accountant for the first two months.
| Item | Example setting |
|---|---|
| Client currency | One currency per client, set in the client record, with an exception list for clients with two |
| Rate source | One daily rate table; the rate is stored on every document |
| Invoice rate | The rate on the invoice date |
| Payment rate | The rate on the payment date; the difference goes to a realised gain or loss account |
| Credit notes | At the rate of the original invoice, so the credit nets to zero |
| Bank fees | Customer short payments up to 15.00 in the ledger currency are posted to a bank charges account with the invoice reference |
| Revaluation | Open foreign currency items revalued at month end and reversed on the first day of the next month |
| Approval | Rate overrides need the finance lead and a reason |
Flow and data mapping
The first table follows a foreign currency invoice from the job to the bank. The second maps the fields, including the rate, between Jobber and Xero.
| Stage | In Jobber | In Xero | Rate used |
|---|---|---|---|
| Invoice | Amount in client currency | Invoice in the same currency | Rate on the invoice date |
| Part or full payment | Payment in client currency | Payment against the invoice | Rate on the payment date |
| Short payment (bank fee) | Balance remains | Small difference to bank charges | Payment-day rate |
| Credit note | Credit in client currency | Credit at the original rate | Rate of the original invoice |
| Month end | Not applicable | Open items revalued | Month-end rate |
| First day of the next month | Not applicable | Revaluation reversed | Not applicable |
| Jobber | Xero | Rule |
|---|---|---|
| Client currency field | Contact default currency | Must exist in Xero or the document is held |
| Invoice total | Invoice total in the document currency | Equal to the cent |
| Line tax | Tax on each line | Same code and rate as the base rules |
| Invoice date | Invoice date and rate date | Rate looked up in the rate table |
| Payment amount and date | Payment and rate date | Rate stored with the payment |
| Credit amount | Credit note | Rate copied from the invoice |
| Client reference | Reference | Job number and invoice number |
Edge cases handled
Agreed in advance with the finance lead and the operations manager, and tested before go-live.
| Situation | How it is handled |
|---|---|
| Rate missing for a weekend date | The last published business day is used and the date noted |
| Client changes currency | New currency from a stated date; open invoices stay in the old one |
| Payment lands in USD for a CAD invoice | Paid at the bank’s converted amount; difference posted to realised loss or gain |
| Credit note after a rate move | Original invoice rate used so the credit nets to zero |
| Overpayment in a foreign currency | Held as an overpayment in that currency and listed |
| Invoice in a currency not enabled in Xero | Held with a clear message; never posted in USD |
| Rate override | Reason required and approved by the finance lead |
| Month-end revaluation on a part-paid invoice | Revalued on the remaining balance only |
Worked example
The tables below show a CAD invoice, a month-end revaluation and a GBP invoice. They show the shape of the checks, not a client result.
| Step | CAD | Rate | USD |
|---|---|---|---|
| Invoice raised | 5,000.00 | 0.7400 | 3,700.00 |
| Payment received | 5,000.00 | 0.7350 | 3,675.00 |
| Realised loss | 25.00 |
| Item | CAD | USD at invoice rate | USD at month-end rate | Unrealised difference |
|---|---|---|---|---|
| Open invoices, CAD | 2,000.00 | 1,480.00 | 1,458.00 | (22.00) |
| Step | GBP | Rate | USD |
|---|---|---|---|
| Invoice raised | 1,200.00 | 1.2500 | 1,500.00 |
| Payment received | 1,200.00 | 1.2650 | 1,518.00 |
| Realised gain | 18.00 |
| Reason | Owner |
|---|---|
| Rate missing | Bookkeeper |
| Currency not enabled | Bookkeeper |
| Short payment above 15.00 | Finance lead |
| Rate override | Finance lead |
Monitoring and alerting
Rates and revaluation fail quietly: the numbers look right until someone compares them to a bank statement. The alerts focus on missing rates and unexplained differences.
| Signal | Threshold | Alert goes to | First action |
|---|---|---|---|
| Rate missing | Any document without a stored rate | Bookkeeper | Rate added from the source, document released |
| Currency mismatch | Client currency not equal to the invoice currency | Bookkeeper | Document held and corrected |
| Realised difference | Any single item above 100.00 | Finance lead | Reviewed for a wrong rate |
| Revaluation not run | Not posted by the 3rd business day | Finance lead | Revaluation run and approved |
| Open foreign items | Any older than 60 days | Finance lead | Chased or written off with approval |
| Sync failure | Any failed push | Manaz Afzal and bookkeeper | Replayed once the cause is fixed |
Test plan
Every check has a written pass condition and a written fail condition before it is run. A FAIL stops the next step until it is cleared and re-run.
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Invoice conversion | Invoices in CAD and GBP | USD equals the hand calculation | Any difference |
| T2 | Payment gain or loss | Pay at a different rate | Difference in the right account | Difference in sales |
| T3 | Credit note | Credit after a rate move | Credit nets to zero | Residual balance |
| T4 | Short payment | Pay 5.00 short | Posted to bank charges with the reference | Invoice left open |
| T5 | Revaluation | Run on three open invoices | Equals the hand calculation; reversed next month | Any difference |
| T6 | Currency not enabled | Invoice in a third currency | Held with a message | Posted in USD |
| T7 | Rate override | Override without a reason | Blocked | Accepted |
| T8 | Reconciliation | Bank account in each currency | Zero unreconciled difference | Any difference |
Go-live and rollback
The flow is switched on for new documents on the first day of a month. Existing open items are revalued once and then follow the same rules.
| Step | Owner | Gate to continue | Rollback trigger |
|---|---|---|---|
| Enable currencies and accounts | Accountant | Accounts approved | Any account missing |
| Load the rate table | Manaz Afzal | T1 passes on past invoices | Any difference |
| Opening open items revalued | Bookkeeper | T5 passes | Revaluation not accepted by the accountant |
| Flow live for new documents | Bookkeeper | T2 to T4 pass | Any wrong posting in the first week |
| First two month-ends | Finance lead and accountant | Revaluation approved | Any unexplained difference |
| Rollback | Owner | Flow paused, postings from the new rules reversed from the log | Decision recorded with the reason |
Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Systems Audit | Read-only review of 12 months of foreign currency invoices, payments and the rates used, a 45-minute call, written findings and a fixed quote |
| Currency rules | Rate source, timing, gain and loss and credit note rules in writing |
| Rate table | One source stored on every document |
| Sync flow | Invoices, payments and credits in the client currency |
| Month-end revaluation | A procedure and a schedule for the accountant |
| Exceptions list | Missing rates, short payments and overrides |
| Backtest | Past invoices in each currency recalculated and compared |
| Handover | Written SOPs, a recorded handover video and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing and statutory returns. |
| Also outside the scope | Hedging or treasury advice. Opening foreign bank accounts. Changing price lists. Software subscriptions (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Audit and rules | Findings, fixed quote, currency rules in writing | Week 1 | 20% |
| M2 Build and backtest | Flow built, past invoices recalculated | Weeks 2 to 3 | 35% |
| M3 Live and revaluation | Live for new documents, first revaluation | Week 4 | 30% |
| M4 Handover | Second month-end, SOPs, handover video, 14 days of fixes | Week 5 | 15% |
Typical scope for this kind of project: US$2,000–3,600
About 5 weeks across 4 fixed milestones. The Systems Audit (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I will say where I would look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Audit is the right first step.