Feature Request: Sales Tax report

[freelock]

I'd like is a sales tax report to simplify my tax reporting. Most of my revenue is getting reclassified as retail, which means I now need to start collecting sales tax from many of my customers -- before now I only had a couple dozen taxable transactions in a quarter, now that goes up to hundreds.

What I do now is do a GL report on the sales tax account with amounts > 0.01 in the reporting quarter, and then I sort on the "Source" column. Next I drop those into a spreadsheet (can't export CSV because there's no newlines, ods and xlsx exports are also broken, so I'm copying/pasting the html) and add:

• Column to subtotal the tax for each location code, putting in a SUM on the last row before the source changes
• Column for the tax basis of each row
• Go look up the tax basis, following the links from the GL report, and enter them one by one into the spreadsheet
• Add another column to sum the tax basis for each location code
• Add some calculations below the fields, where I have 4 lines: a) Retail sales total amount (from PNL) b) In-state sales (sum of the tax basis column) c) FX/Interstate sales ( b - a ) d) Intrastate sales (b - the sum of the tax basis for the Seattle location -- needed for my city tax return)

... I then plug these into two returns:

• State -- Retail sales total amount, deduction for FX/Interstate, and then in the sales tax detail area, the total tax basis for each location code.
• City -- Retail sales total amount, deduction for FX/Interstate, deduction for Intrastate

Now comes the complication. I have two tax-exempt clients in Washington -- one is a tribe, the other is a housing authority. So I asked Claude AI how I should be tracking these sales, and ended up with a recommendation to create two new tax accounts (because these are treated differently). Apparently my tribal client is entirely tax exempt -- I don't need to collect or remit sales tax for them, and I can deduct their payments from the top-line revenue before I pay "B&O tax" on the revenue. However, my housing authority client is exempt from sales tax, but NOT B&O -- so for them, I need to include the revenue in my retail sales -- but deduct the amount after the B&O calculation (but before the sales tax). So I've set up 2 new sales tax accounts, set to use the "Simple" tax reporting, with a 0% rate. And I've applied those to the appropriate customers. So now a couple questions:

1. Should I also set the new taxes on any taxable products these clients purchase? I'm thinking this would at least get entries on that tax account...; or

2. is tagging them enough to get appropriate reports? Here's the summary - "I need to track Washington state sales for B&O tax reporting, where some customers are sales-tax-exempt but B&O-taxable (housing authorities), and others are fully exempt (tribal sales). I need to generate reports showing gross sales broken down by location and exemption type."

[ehuelsmann]

Go look up the tax basis, following the links from the GL report, and enter them one by one into the spreadsheet

This might not work for you, but: I have an obligation to report taxes (VAT) in the categories (a) within The Netherlands, (b) within the EU, (c) outside the EU and (d) VAT on products bought in the EU where no VAT was charged.

I'm using Tax Forms to collect the tax-base numbers; I'm assuming my accountant uses these numbers to multiply with the appropriate tax rates.

[ehuelsmann]

So I've set up 2 new sales tax accounts, set to use the "Simple" tax reporting, with a 0% rate.

For my understanding: those being the "B&O tax" and the "tax exempt" accounts? I do understand that you'd set the "tax exempt" account to 0%, but why did you set "B&O tax" to 0%? Don't you collect this tax from your customers? I mean: you have to remit the B&O tax, but it's not on invoices to collect it (as in: prices are inclusive)?

[ehuelsmann]

We used to have a "taxable sales" report, but we removed it, because it lacked design and it wasn't clear how to handle cash-basis reporting versus accrual-basis reporting. Also, the report would show different numbers if transactions with posting dates in reported-on periods would be created. Do you need the report to be like a reconciliation report (allocating taxes to a specific report and thereby never including them in any other report again)? Or is the original type of report what you need (a simple query over a specific date range, subject to change at any time)?

[freelock]

B&O tax is not collected from customers -- it's a regressive tax on gross revenue per revenue type (services have one rate, retail another, wholesale another). That part is easy.

On retail sales, we do collect tax from customers on all taxable products/services (which got a lot more broad). The tax rates vary based on the address at the point of sale -- which for our services is the customer's billing address since we don't have a physical store. There is a different rate for different addresses in Washington state, and if we establish "nexus" in another state (e.g. by hiring an employee who lives/works from there) we may need to collect tax for that state as well.

For services B&O tax, the customer status didn't matter -- it was a tax on us. For retail, however, we learned it gets even more complex. We have a tribal government as a client that is entirely exempt from both retail sales tax, and any B&O tax on that revenue. We have another client that is a housing authority, which has a special tax exempt status -- we don't collect or remit sales tax, but we are obligated to pay B&O tax on that income. It's all very confusing.

But what I do need is a report that shows a summary of the tax collected on an accrual basis for each location code, within a particular time period, and the cost basis of that tax -- that's what I actually need to report.

Specifically, here's what the tax filing looks like:

1. Total amount of retail sales
2. Deduction: Interstate/Foreign sales
3. Page with a table where you can add a row for each location code -- pick the location, and add the cost basis (taxable sales) for customers using that location code for the quarter. These are prepopulated with the location codes used on previous returns.
4. Validation that the total cost basis for all the locations matches the total amount of retail sales minus the FX transactions.

There's a bit more info on the screens breaking out the taxable amounts between state, local, and transit authority agencies, but I only need to provide the total sales amounts for each location.

To get this info out of Ledger, I run a GL report on the tax account, filtering for the date period and amounts >= 0.01 (if I don't filter this I get a ton of empty transactions). I click on the "Source" column to sort by location code.

I then save this as a CSV because the ODS export doesn't work.

I then go through the CSV to manually delete the all the extra commas because the CSV export is broken.

Then I load it in LibreOffice, and add some columns to the right of the data -- a column where I add a sum for all transactions within each location, a column for the tax basis, and another column to sum the tax basis for each location. Then I go through and check individual invoices to find the amount that was taxed and add it to the column -- fortunately these have generally been one of 3 different services, and it's pretty easy to tell which was which for each transaction, and there have only been around 40 of these transactions per quarter, up to now.

Finally I sum all the columns to make sure I got the individual sums correct, put in the gross retail sales amount, subtract the FX amount, and compare the balance with the sum of all the locations.

For one location - Seattle -- I have an additional return to file (although it sounds like I will be exempt from this next year! so maybe only one more quarter I need to do this) - for the Seattle report, I add another row for the gross Seattle sales, and subtract that from the retail sales as well to get the "Intra-state" sales total, which I can deduct on my Seattle return.

This has been a tiny fraction of my sales I needed to do this manual work for -- but starting with my next return, it applies to pretty much all my sales, except for a handful of customers with longer term contracts, where I can use the old Services classification until next April.

So what I really need is an easy way to report on the taxable basis, linked to the tax collected and location code, for all the transactions within a particular quarter.