GST calculations vs US Sales Tax calculations
It appears that the GST calculation in OpenVPMS is based on the selling price, with the assumption that the tax is INCLUDED in the price - is this correct? For instance - if I have an item that sells for $4.19 with a 10% GST, it will display 0.42 for tax - however the total sale will still be $4.19.
This is not how US Sales Tax works. US Sales Tax is added to the total sale as an additional cost. So an item selling for $4.19 with a 10% Sales Tax (note: in the US this would be a crazy high tax, but I'm using this for illustration) would have a total sale of $4.19 +0.42 (Tax) = $4.61.
How would one go about changing the OpenVPMS logic to correctly calculate the tax and invoice totals? I would think we'd need to have a switch for any tax item saying that this tax is INCLUDED in the selling price, or to be ADDED TO the selling price. Is it already there or will an enhancement be needed to support US style Sales Tax?
Thanks. Pete
GST calculations vs US Sales
Hi Pete,
The current tax setup in OpenVPMS is a bit more complex than that but on its simplest form your summary is correct. Still it may be worthwhile going through it anyway.
1. Tax setup
One or more taxes can be applied against a practice, practice location, product type, product and/or customer.
2. Product Pricing.
The current product sell price is stored inclusive of tax.
The current product cost price (buy price) is stored exclusive of tax.
The current calculation to calculate the sell price from the buy price is
price = (cost * (1 + markup/100) ) * (1 + tax/100)
The tax rate to utilise in this calculation is actually based on any taxes in the product or its associated product type or if none set in either of these then it will utilise taxes set in the practice location/practice.
3. Customer Transactions
When a product is charged the application checks the customer record for any tax exclusions and removes these taxes from any taxes it finds by looking at the product/product Type or if none the practice location/practice.
The new tax , unit price and fixed price is calculated using the resultant remaining taxes and these are stored in the invoice item. The unit price and fixed price are inclusive of tax values. The tax amount is the total of the tax for both the fixed and the unit prices.
The customer transaction stores the addition of the invoice item totals and taxes so the total is also tax inclusive.
Now in Australia we essentially have a 10% GST on all products so we typically set this tax against the practice and nothing on the products and product types.
In regards your issue I would say this is a display issue rather than a calculation issue. Once you understand that the prices displayed on the product need to be tax inclusive then the actual tax calculation is correct either way.
i.e in your example the product price would be entered or calulated as $ 4.61. In the invoice the line total would be $4.61 and the tax would be $ 0.42. If this was the only item on the invoice the invoice total (amount to pay) would be $4.61 and tax total would be $0.42.
Also you would have the option to show/print our invoices as tax inclusive or tax exclusive by designing the template showing the individual total prices as they are or by calculating the exclusive price (total - tax) on each item and the tax and by extension the tax inclusive total (total).
Anyway not sure if I have correctly undertstood the issue but if not then lets keep discussiing and get a resolution. Definitely want to make it work for you guys :-)
Cheers
Tony
Great Explanation
Tony,
Great explanation. Thank you.
Here in the US, we have Sales Taxes that vary by state. The range of tax is 0% up to 9% depending on state and locale. Also, we have some items that are -'taxable' and others that are not, just as you do. However our pricing on invoices for any items (taxable or not) never includes the tax. The typical US invoice would be as follows (I am excluding non relevant columns such as quantity for simplicity):
ITEM 1 <Description> <Unit Price> <Total Price> <Taxable Flag (Usually a "T" or "Tax">
...
ITEM N <Description> <Unit Price> <Total Price> <Taxable Flag>
SUBTOTAL <Total of all items, without any tax>
TAX <Total Tax - based on tax rate and sum of all taxable item prices above>
TOTAL <Subtotal + Tax>
Any way we can produce this behavior in the current product?
I hope so. This will be a big sticky point here as tax reporting (among other things) relys on having a total selling price of taxable items exclusive of tax. Also, the population expects to see the TAX line in an invoice and it would be a time wasting explanation point to have to show that tax was included in certain items
-- Pete
GST calculations vs US Sales
Hi Pete,
I believe we can meet these requirements in the current product by working with you to develop a new invoice template that will show ex tax rather than inc tax prices. Templates are developed outside of the main application and can be loaded directly into the application through the Administration -> templates workspace. As these are based on jasper reports you can utilise iReport or similar report designers to design them.
Happy to help you with this and then you can test some tax scenarios to make sure it works as expected.
Cheers
Tony
GST calculations vs US Sales
As I look more deeply into this, it looks like it may be a bit of a thornier issue to solve.
It appears from the code I have reviewed that GST is calculated at the item level. For each individual item sold, if it is taxable the tax is computed and then all taxes are added to return a total tax amount. In the US, things are done differently. If you sell 7 items, 5 of which are taxable, the total of the 5 taxable items is added together THEN the tax is applied - so in cases where a tax rate results in a rounding error there can be a discrepency in the total tax amount if you were to compare it to taxing items individually. Also, the actual cost basis of the tax is different due to the differing methods.
Example: 10% GST on 5 items vs. 10% US Sales Tax on 5 items.
Item 1: Price: $3.54 (0.35 GST) - US Sale Price is $3.19 (US Sales Tax on this item sold alone would be $0.32)
Item 2: Price: $0.73 (0.07 GST) - US Sale Price is $0.66 (US Sales Tax on this item sold alone would be $0.07)
Item 3: Price: $1.24 (0.12 GST) - US Sale Price is $1.12 (US Sales Tax on this item sold alone would be $0.11)
Item 4: Price: $2.22 (0.22 GST) - US Sale Price is $2.00 (US Sales Tax on this item sold alone would be $0.20)
Item 5: Price: $3.94 (0.39 GST) - US Sale Price is $3.55 (US Sales Tax on this item sold alone would be $0.36)
Aussie Total Sale is: $11.67 with GST of $1.15
US Sale is: $10.52 with Sales Tax of $1.05 for a total of $11.57 ($0.10 difference) (NOTE: Adding the tax together for individually sold items would net a tax of $1.06, 0.01 too high).
This is significant, so IMHO this Tax issue is one that will need to be addressed prior to any real US introduction. I am not sure how to get this started, from what I have seen it would require a new module and the ability to activate either GST or US Style Sales TAX modules based on locale. NOTE: Canadian Sales taxes operate similarly and they also have a VAT that I am not certain whether it would apply to Veterinary products and services - although I do believe that any VAT calculations would be similar (at least in concept) to GST.
--- Pete
Re: New or updated comment for General User's Discussion Forum t
Hi Pete,
those calculations are incorrect as the amount of GST you have calculated on the Australian sales is wrong. The GST is 10% of the GST exclusive price. To calculate the GST from a GST inclusive price the tax is 1/11, not 10%. So it should be:
Item 1: Price: $3.54 (0.32 GST) - US Sale Price is $3.22
Item 2: Price: $0.73 (0.07 GST) - US Sale Price is $0.66
Item 3: Price: $1.24 (0.11 GST) - US Sale Price is $1.13
Item 4: Price: $2.22 (0.20 GST) - US Sale Price is $2.02
Item 5: Price: $3.94 (0.36 GST) - US Sale Price is $3.58
Aussie Total Sale is: $11.67 with GST of $1.06
US total = 10.61, sales tax= $1.06
Cheers,
Matt Y.
Re: New or updated comment for General User's Discussion Forum t
Thanks Matt, I see that now...
but it still does not help the US case. We're going to have to show a product price that does not include tax, then show a tax line that is added to the total of the invoice to be correct in the US, as individually taxed items will not always generate the correct amount of tax. Also the US public is used to seeing pricing without tax, then the tax number being added.
I guess what I'm asking is whether this is something that will be added to the development list, or whether we'll (US users, providers) be required to write this in order to make the application usable in the US. If it's added to the development list, what kind of lead time is there? These answers will be key in our determination on whether to press forward.
--- Pete
GST calculations vs US Sales
Hi Pete,
I think given that the internal tax calculations are working I believe we just need to confirm that we can present an invoice to the customer that uses these values but presents it in a format that is suitable for your locale. I am pretty confident we will be able to do this without any development required as presentation of the internal invoice information to the outside world is handled by jasper report templates not internal code.
Lets work together on this soon. :-)
Cheers
Tony
P.S. Think this conversation should be moved to the implementers forum
GST calculations vs US Sales
Tony
Thanks, I am all for that... We're anxious to have something here we can show to potential customers.
--- Pete
Re: GST calculations vs US Sales
Somebody resolve that issue?
Re: GST calculations vs US Sales
No - its still outstanding. An umbrella project has been set up to discuss issues such as these.
See http://www.openvpms.org/project/openvpms-internationalization
-Tim
Re: GST calculations vs US Sales Tax calculations
Tony, Tim,
I see the Internationalization project is still open and I'm thinking that the Tax issue for the US market is still outstanding based on this. Can you confirm or deny that? I have installed this software over the weekend in a VM for testing and would like very much to deploy it at my practices here in the US however I would need this issue solved.
Thanks, Kevin
Re: GST calculations vs US Sales Tax calculations
Hi Kevin,
I don't believe US style sales tax is supported. However, I've deployed in the US without it. I'd be happy to talk to you on the phone if it helps.
Thanks, Paul
Re: GST calculations vs US Sales Tax calculations
Paul,
Thank you for the offer and I will very likely give you a ring in the next few days, please let me know if there are specific days or times that are better as I want to be mindful of your time. I do have a number to reach you.
Kevin
Re: GST calculations vs US Sales Tax calculations
US style sales tax isn't supported at present. To proceed, the internationalization project needs to get some more support from users.
Regards,
Tim
Re: GST calculations vs US Sales Tax calculations
Hi Tim, What support is needed and can you clarify if the issue is simply with the reports or if the architecture needs to be modified?
Kevin
Re: GST calculations vs US Sales Tax calculations
http://www.openvpms.org/project/openvpms-internationalization
This is the current project under discussion Kevin, as you can see it hasnt been costed yet, I imagine getting interest in from Australian users, to fund a project that has no direct benefit to them is difficult
To those I would say:
Localizing OPENVPMS would increase the user base, increase the potential funds to create improvements.
Cheers
Ben
Re: GST calculations vs US Sales Tax calculations
Changes that have been identified so far are documented at:
The US sales tax support documented in OVPMS-916 requires database schema changes.
Regards,
Tim
Re: GST calculations vs US Sales Tax calculations
Tim,
Thanks for the reply and related jira's, I agree with Ben, the more users the more value add.
The zip code issue would not be an issue for me given my geographic location, however the tax issue would seem to pose issues. Has there been any scoping done to estimate the time and cost to address the Tax issue? If so please let me know.
Kevin
Re: GST calculations vs US Sales Tax calculations
I haven't done any detailed scoping of the work as yet.
The changes to include ex-tax prices and totals requires changes to:
Based on prior experience I would think there is at least 4 weeks work involved to implement.
It might take longer, but I'd need to do a detailed analysis of whats required, and ensure there is broad consensus with the approach.
-Tim
Re: GST calculations vs US Sales Tax calculations
Thanks Tim, I'll try to talk to Paul who posted above and see how he is handling it as the US state he is in has very similar tax laws to my own state. Hopefully there is a way to work with this in the short term that isn't too painful. I'd much rather fund a % of this enhancement and help us all.
Kevin