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
Re: GST calculations vs US Sales Tax calculations
Hello Everyone,
I realize that the last post in this thread is now almost two years old, but was wondering about the status of this issue and how the US folks have addressed it.
I'm a new user trying to set up OpenVPMS for use in a Canadian practice with perhaps an even more complex tax setup as in the US. In BC, we have a non-blended Provincial Sales Tax and national GST. GST is normally charged on everything, whereas PST is a goods-only tax, not chargeable on services.
If this is a template issue as some of the discussion above suggests, then that is one thing, but if we can't get it in a form that is acceptable to the bookkeeper or tax auditors, that's another. From what I can find under the on-going projects, is this still an as-yet unfunded $15,000 project?
Thanks,
Sam
Re: GST calculations vs US Sales Tax calculations
The tax projects you are referring to haven't received funding.
In discussions I've had off list with a US practice that is migrating to OpenVPMS mid year, they aren't concerned with the per-line taxation style. They currently use RxWorks, which does taxation the same way.
Re: GST calculations vs US Sales Tax calculations
Thanks Tim,
So if it can be handled as a reporting issue as suggested in your recent discussion, then that would be great. Maybe when we get to that point later this year we can look at generating a package of templates that can be used by everybody in this position. That could be a pretty useful project and might open up OpenVPMS to an even wider audience.
Sam
Re: GST calculations vs US Sales Tax calculations
Hey everyone!
My name is Samantha and i work at a low cost spay/neuter in the United States. We are having a hard time understanding how to change the taxes.
How do we make it so that when we input a price (either fixed or unit price) it doesn't automatically give us a tax. (i'm assuming that's a GST thing)
As Americans we can muddle our way through the system, but this seems to be giving us a lot ot trouble!
If anyone could throw me some tips that would be greatly appreciated.
Thanks,
Samantha
Re: GST calculations vs US Sales Tax calculations
Samantha - if you read the whole of the above thread and also (from the Concepts section of the online help) http://www.openvpms.org/documentation/csh/1.8/concepts/taxes
you should gather that OpenVPMS (currently) follows the Australian/European standard of showing prices as Tax-Included - NOT the US/Canadian standard of showing prices as Tax-Excluded.
In your case (and bear in mind that I do not understand your situation fully), I would:
1) use Administration|Lookups|Tax Type to add a tax rate as follows:
2) use Administration|Organisation|Practice to edit the practice to add the above tax rate
3) [now you need a calculator] - say you consider that the item's price is $10 - this is the ex-tax price - but OpenVPMS wants an inc-tax price - so $10 + 8.8% = $10.88 - so this is the price you tell OpenVPMS
4) depending on how you want your invoices you may want to adjust them.
Hopefully one of our US or Canadian users will comment on the above and correct anything that I have got wrong.
Note that there is a project to enhance OpenVPMS to handle prices the US way - but it is not fully funded. It is something that we understand is needed.
Regards, Tim G
Re: GST calculations vs US Sales Tax calculations
Samantha,
We're quite happily using OpenVPMS in Canada where the tax situation is more complex than it is in the States as we have two taxes, one provincial (applied sometimes) and one national (applied all the time). To further complicate things, as in the States, people here expect to see prices tax-exclusive, the tax, and finally the total.
We have had to utilize the workaround that Tim G noted above... when you enter prices, you need to enter them tax-inclusive. As the taxes are inconsistently applied, we have set up PST and GST tax types, and applied them as appropriate to each item or service.
The key to using OpenVPMS outside of Australia/Europe is in constructing templates for your financial documents... invoices, credits, tax reports, etc. that display prices as your clients expect. It has been a couple of years since I looked at the default OpenVPMS templates (which I think only display price-inc), but I had to generate new templates that created a price-ex variable (price-inc minus tax amount), then for each item, displayed the price-ex field and tax. I then totaled and summarized all at the bottom of the invoice. I employed a similar technique for doing monthly tax reports, etc.
I did produce a package of templates that you might be able to adapt to fit your needs. They are more complicated than what you need as I had to incorporate some logic to determine which tax(es) were being charged, but they do show how to set up the price-ex variable and fields.
Hope this is of some help, or at least encouragement. With a little work you should be able to get OpenVPMS to work quite well for you in your setting. Let me know if you need any assistance.
Sam Longiaru
Kamloops, BC
Re: GST calculations vs US Sales Tax calculations
Samantha:
1) Sam's Canadian package is in the Resource Library at http://openvpms.org/customisation/canadian-pstgst-template-package
2) When I built the Practice Summary and Key Performance indicators reports for the 1.8 release I was careful to ensure that these could report ex-tax positions. Some bugs have been fixed in these reports and the fixed versions are in the Resource Library - see http://openvpms.org/customisation/key-performance-indicators-report and http://openvpms.org/customisation/practice-summary-report-updates
3) all the reports (ie the reports accessed via Reporting|Reports) in the 1.8 package show dates that are in the local format - ie in mm/dd/yy format for you. However, this is not true of the of the other stuff - for example the Debtors report (generated when you press the Report button on the Reporting|Debtors screen) is hard coded to use dd/mm/yy format. I will try to get time to fix this for the 1.9 release.
Regards, Tim G
Re: GST calculations vs US Sales Tax calculations
Hey Everyone.
Thanks for the speedy replies. I've printed out your advice and will get to work on it immediately.
If i have any other questions i will get back to you , but it seems that you have shown me the steps on how to fix my situation. I think that was more of my problem than anything else, basally i knew what i needed to do,just not HOW.
Thanks so much again!
samantha
Re: GST calculations vs US Sales Tax calculations
Hey Everyone,
I'm still having problems with the taxes, part of the problem is that i have to work on this system while i'm at work and i get interrupted a lot of the time, so a lot of times i get lost.
At our out clinic, we cover the taxes on services ( i.e surgeries, declaws etc) and only charge for goods. I went into Administration and changed the taxes there, but it's still showing up on services.
The link for the templates Sam shared are only coming up at codes, and i'm not sure what to do with them. Our computer guy can only come at certain times and it's never regular, so i usually have a list of things i need help with, but i just can't seem to figure this out.
I really wish there was a step by step instruction booklet, haha..
Maybe i'm just not doing something right, maybe i'm making this more difficult than i need to, but some advice would be appriciated.
Thanks again, and i promise i generally am pretty literate when it comes to computers and technology.
Samantha
Re: GST calculations vs US Sales Tax calculations
Samantha,
The .jrxml files in the template package I linked to, need to be opened in Jaspersoft Studio. If you haven't done so yet, visit their site, register if need be and download the free Community edition. Studio is an updated replacement for Jaspersoft iReports which you may see referenced in some older OpenVPMS documents. The purpose of Studio is to allow you to customize the document templates that ship with OpenVPMS. After customizing the templates, you can then upload them to OpenVPMS. From that point on, OpenVPMS will use your customized templates to produced documents - invoices, reports, etc. - suitably designed by you for your practice.
So to make sense of the .jrxml files, install Jaspersoft Studio and open a .jrxml file in that program. You will see that you get a visual layout of the document, and can edit it at that point. Studio has a bit of a learning curve, but is not unlike many other report-generating programs.
The .jrxml files I linked to are ones that I have customized for the Canadian PST/GST multi-tax system. They work well up here. You'll be glad to know however, that your issue is somewhat simpler. The standard OpenVPMS templates are designed to deal with one tax, as I suspect most US practices require. The default templates will really only need to be slightly reworked so that instead of showing a tax-inclusive price on each line of an invoice for example, the tax-exclusive price is shown. This is easily done by adding a variable to the template into which you store the value of the tax-exclusive proce (tax-inclusive price minus the tax). You can then display a field showing the value of the new tax-exclusive variable in your template. I only referenced my Canadian templates as they show where new variables are added and how the math was done. The math for you (with one tax) is rather straightforward.
I can probably help you design what you need, or at least help get you started on the right path. You will need to have JasperStudio up and running anyway, so do that as your first step. Then we can look at how to generate a US-customized invoice. You'll find that once you understand the procedure for generating a customized invoice template, the process for generating all the other required documents, credits, tax reports, etc., will fall into line.
Sam
Kamloops, BC
Re: GST calculations vs US Sales Tax calculations
Samantha,
I have gone ahead and stripped out of my invoiceItems template, the variables and fields that were needed for our Canadian two-tax situation. The templates I am attaching go together to make a US-style invoice, where each item has one (or no) tax. It is fairly basic, but displays the tax-exclusive prices (what we call "price-ex") as well as the tax. The price-ex(s) and tax(es) are subtotaled and finally everything is totaled as a final invoice amount.
There are three templates:
1) invoice-US-L.jrxml is the master template which you would modify in Jaspersoft Studio with your hospital details. This template houses two sub-templates:
2) invoiceItems-US.jrxml (this is where the magic with the prices is done) and
3) invoice-Reminders-US.jrxml (for use with vaccination reminders, etc. when you get to that point). Right now, it will do nothing.
You should not need to do anything inside the 2) and 3) templates now.
To test them, using Administration > Templates, edit the invoice template by uploading invoice-US-L.jrxml. Edit the "invoice items" template by uploading invoiceItems-US.jrxml, and do the same for invoiceReminders-US.jrxml. By doing this, you are substituting the default templates with the customized ones I am providing. You should now be able to output US-style invoices. The template is set up for the "Red, White and Blue Veterinary Hospital" so you may not want to test on your production version!
All of this pre-supposes that you have your taxes set up correctly. In your post above, you state that you have set up your taxes but that they are still showing up on services, where you don't want them to appear. Taxes can be set at many levels, and I imagine that your tax is being set at a place that is affecting everything below it. My understanding is that if you have tax set at Administration > Practice > Tax, it will set the tax for the entire operation. This is fine in AU where GST gets applied to everything. You probably don't want to set tax there.
After setting up your desired tax type and rate in Administration > Lookups > Tax Type, you can assign that tax to wide categories of products (and services). You can set up broad categories (Product Type) as desired in Administration > Types > Product Type. You'll see here that you can set the tax (or no tax) for each product type. Now when you enter a new item (merchandise, medication or service), you can assign it the proper Type and the associated tax will follow along.
For us, even that is too broad a stroke and so we set taxes for each item individually over in the right side tab when entering each new item.
Remember that when entering prices, to enter them tax-inclusive (if there is tax). I think that's discussed in detail above somewhere. But basically, decide what you want to sell your item for, add the tax if any, and put that value in when entering your items into the program.
Sam