We are allowed to trade in USD and ZWL. For the purposes of tax filings such as VAT returns, amounts are assessed in the base currency (ZWL), but are payable in USD and ZWL according to the proportion of income generated in the respective currencies.
Is there a built-in report I can use, or a custom report I can generate, that will show me all income for a specified period separated into the two currencies?
The “Sales Invoice Totals by Customer” report comes very close to what I need, except that it shows amounts for the USD customers in USD. I would like to see the base currency (ZWL) equivalent amounts. Since the exchange rate has changed multiple times during the period in question, I can’t just do a conversion using the information in the report.
I do not need to see customer- or transaction-level detail – all I really need is two figures:
The income for the period from ZWL transactions
The income for the period from USD transactions, expressed in ZWL using the exchange rates from the dates of the transactions
All my customers denominated in USD have “ (USD)” appended to their names, which could be useful for filtering in custom reports. The ZWL customers just have their names.
Can I suggest a very “clumsy” work around, take a backup of the business with a name including “USD Customers” and import that business. Now delete the ZWL only customer transactions, which will result in only USD transactions expressed in ZWL. Deduct these values from the main business values to get the ZWL only transactions. Or vice versa if the ZWL customer transactions will result in a smaller number of deletions.
Custom reports are way to go. In the latest version (20.8.61) I exposed a few more fields such as currency and account amount.
So here is how it would work.
Let’s start with list of customer invoice totals in base currency:
But you don’t want base currency, you want to see totals in customer currency. Just change Amount to AccountAmount so it’s like this:
Now you will see it’s still not useful because customers across multiple currencies are mixed together. In other to group them by currency. Check the Group By... button and group them by AccountCurrency like this:
This will group your sales invoice totals by customer currency.
This can be achieved by simply checking Groups to collapse to hide transaction-level details and just show totals for your groups.
But I still recommend not to group to collapse so you see what specific transactions made up your totals.
Thanks @Brucanna for your suggestion. I ended up using the Tax Transactions report, copying this to a spreadsheet, and then doing some manual work to separate the two currencies. Contrary to my statement in my opening post, it is actually useful to be able to see all the transactions, particularly to cross check from the spreadsheet application against Manager’s other tax reports.
Thanks @lubos. This looks like it will be very useful, and I appreciate your worked examples. This was my first VAT return in two currencies like this, but I’ll also need to do income taxes, payroll, etc, so I’m sure the ability to separate the currencies in the custom reports like this will be useful across all those report types. I’ll need to spend a bit more time figuring out exactly what I need and how to structure the reports, but even if I have to do a little work in a spreadsheet application it looks like I should now be able to access all the information I need fairly easily, and hopefully get it all structured appropriately too.
It suddenly occurred to me when entering details of a VAT payment to the tax authority that I could use a new tax code for the USD transactions. I have a VAT payable (USD) liability account anyway for VAT paid on certain imported goods in USD, and so I can have another tax code that posts to this account for VAT on USD sales. This also simplifies my reconciliation, as it’s much easier to see what VAT I owe in each currency, and which transactions make up those amounts.
Now all I need is a way to automatically select the VAT 14.5% (USD) tax code for all sales to USD customers… (Though it’s fairly easy to confirm after the fact that I haven’t used the ZWL code for USD customers or visa versa, so it’s not a big deal.)