Tax-exclusive product prices
Donate to this project
Development Project Status: Completed
Currently, all product prices are stored inclusive of tax. This makes it difficult to update prices if a tax rate changes. All prices must be manually updated to incorporate the rate change
This project will:
- store all product prices tax-exclusive
- calculate tax-inc prices when a product is charged
- display products with both tax-inc and tax-ex prices
A new flag "Display Prices Tax-Inclusive", set at the Practice level, will determine how prices are displayed.
In the Product browser, this will determine if fixed and unit prices are displayed tax-inclusive or tax-exclusive.
When viewing products, both tax-inclusive and tax-exclusive prices will be displayed.
Price Editing
When editing products, both tax-inclusive and tax-exclusive prices will be editable.
If the tax-inclusive price is changed, the tax-exclusive price will be derived, and vice-versa. Only the tax-exclusive price will be stored.
Where Display Prices Tax-Inclusive is true, this allows tax-inc prices to be assigned a round amount.
Report Changes
The following reports need to be updated to display prices based on the Display Prices Tax-Inclusive flag:
- Product List
- Product Price List
These will need:
- new JXPath extension functions that calculate the tax-inclusive price
- support to invoke JXPath extension functions from within an SQL report.
Data Migration
Product fixed and unit prices must be updated to remove the tax component.
Prices are currently calculated using:
price = (cost * (1 + markup/100) ) * (1 + tax/100)
and then rounded to the no. of decimal places specified by the currency.
The migration script will recalculate the price using
price = round(price/(1 + tax/100),3)
This is required for practices that loaded historical price data without the cost information.
The migration script cannot determine the number of decimal places to use, so will default to 3 decimal places.
NOTE: due to rounding, the calculated tax-inc price may be different to that prior to migration. The difference will be a fraction of a cent.
Related project: http://www.openvpms.org/project/add-support-invoice-level-taxation
Price Description
The productPrice.unitPrice and productPrice.fixedPrice archetypes have derived description nodes:
concat('$ ',/price, ' ',openvpms:lookup(.,'uom'),' (',date:formatDate(/fromDate), ' - ',date:formatDate(/toDate),')')i.e. they contain text like:
$ 8.37 Ampoules(13/07/2006 - )
These will be migrated to contain the tax exclusive price. This description will not be displayed in the user interface, as it won't necessarily reflect the current practice configuration.
Comments
Re: Tax-exclusive product prices
We will pledge $1,000 for the project.
Alan
Re: Tax-exclusive product prices
Hi Tim,
As you might remember, we looked carefully at this project when evaluating OVPMS for our practice in Canada where we have to deal with two inconsistently-applied taxes... provincial and federal. While having prices stored in their price-ex form would have been helpful at the time, ultimately we were able to work around the issue of storing prices as price-inc through the use of template-based calculations alone.
In reviewing this project proposal, I have a few questions you might help answer:
1) You state that there will be a new flag that will affect how prices will be displayed, but not how they will be stored. Given that after this project is completed, all prices would be stored price-ex, would this not have adverse effects on more financial documents and reports than just Product List and Price List? It would seem to me that nearly all templates, such as invoices, credits, estimates, etc., would need heavy reworking. Or maybe I'm missing something here. Perhaps you are thinking of adding a priceEx "field" to the database, leaving the price-Inc information in tact?
2) While having the ability to update pricing automatically should the tax rate change is nice, the existing capability of exporting the existing price schedule and reimporting after updating in a spreadsheet is not onerous.
3) As for having to use a different installation of OVPMS in jurisdictions that have different tax rates, I can only see this as being important in those few practices that have locations that span state or other borders where tax rates are different but where the practice is keeping prices identical. Would each location be able to have a different tax rate applied to products?
4) And lastly, I imagine that price-ex storage would extend to services as well as products?
While I see that changes may need to be made to OVPMS to make it more attractive to the North American market, I'm wondering if it might be more difficult to modify the existing one as opposed to forking the current program into a North American version... OPVMS-NA... with price-ex storage built in from the get-go.
Sam
Kamloops, BC
Re: Tax-exclusive product prices
I've changed the specification. It previously had:
This is incorrect. Invoices and estimates will continue to use the tax inclusive price. Invoice and estimate taxation changes are addressed by the Invoice Level Taxation project.
It also had: "This makes it difficult to ... use OpenVPMS across regions that have different tax rates. A separate installation must be used." This is correct, but only applies if we add support to specify tax rates at the practice location level. I've removed this point. It can be addressed in a separate project, if required.
Addressing each of your points:
1) All prices in the product_prices table will be stored tax-ex.
Charge and estimate item prices will continue to be stored tax-inc, so none of related templates require updating.
2) Possibly, but its a headache that I think many practices would like to avoid.
3) I've removed the point about using OpenVPMS accross regions as noted above.
4) Tax ex pricing applies to all prices, irrespective of the product they are linked to.
Regarding forking, I'd like to avoid this. It is difficult to port new features and bugs in divergent versions.
Re: Tax-exclusive product prices
Good morning Tim.
On one of our recent update calls with the managers running OPEN, how taxes are displayed on invoices was brought up in the conversation. The concern didn't seem frequent, but it comes up occasionally that clients think they are being double charged for taxes, once in the cost of the product and once as a separate line item. The fact that tax inclusive pricing isn't common in the states lends to the confusion despite the math proving otherwise. I was pleased to initially see that this would be addressed through this project but now see the update referring to Invoice Level Taxes.
Once this project is implemented, will it be possible to display tax exclusive pricing on invoices by updating the reports, or will this still not be an option even with the changes?
Thanks! Alan
Re: Tax-exclusive product prices
With these changes alone, I don't think it will be possible to reliably display tax exclusive prices on invoices. The tax exclusive prices are held on the product, not the invoice and can change over time. If you print an old invoice, the prices may not reflect those used.
You could change the existing templates to calculate the tax exclusive price; Sam Longiaru did this in the Canadian PST/GST template package.
Re: Tax-exclusive product prices
Alan,
If you are using OVPMS templates in an unmodified form, i.e., showing price-inc prices on each line of your invoice and then showing the tax component at the bottom, then yes, this would be very confusing for US (and Canadian) clients no doubt. They don't expect it and will surely think they are being double-taxed.
If you are only dealing with one tax like a state tax, then displaying the tax-exclusive price in invoices, credits, estimates, etc. is fairly straightforward. Simpler than in my Canadian templates where I had to distinguish between and apportion two taxes. In your case, it is simply a matter of adding a price-ex variable to the invoiceitems, credititems, etc. templates and calculating the price-ex for each item using the two available values... price-inc and tax. Subtract the tax out from price-inc and assign it to price-ex, then for each line, just display price-ex, not price-inc. Voila!. No changes to the database at all and your clients will appreciate it... as will your front office staff for not having to explain it all the time.
The best part of the project outlined above as I see it, is that it would display prices on-screen as price-ex. Right now, we have to remind staff that when someone asks about the price of a product or procedure, they need to mention to the client that the price quoted is all-up, tax-included... otherwise clients go away and add additional tax in their heads.
You can probably get what you need by looking at the Canadian Templates as Tim mentioned, and simplifying them, removing all the code related to apportioning two taxes. If you need any help, let me know. I think you will be quite happy after the template tweaks.
Sam
Kamloops, BC
Re: Tax-exclusive product prices
Just to clarify, you will see tax-ex prices in the product browser. You will therefore be able to view the tax-ex price when charging by clicking the binoculars icon next to the Product field. You won't see the tax-ex price in the invoice itself.
Re: Tax-exclusive product prices
Tim - I find the remark "You won't see the tax-ex price in the invoice itself." in the above a little confusing.
It think is it better stated as follows:
"Financial acts [ie the place where the money part of the invoices, credits, etc etc is held] will continue to hold the total (inc-tax) and tax amounts. Thus a) financial reports that derive information from the financial data will not be affected - and if these reports have options as to whether to display inc- or ex-tax amounts this will also be unaffected; b) your existing invoice etc templates will work as currently - ie if you use a version that displays inc-tax amounts for the line items or a version that displays ex-tax amounts for the line items, these will both continue to work as is. However, custom price list reports will almost certainly require modification."
Regards, Tim G
Re: Tax-exclusive product prices
A user has pledged funding for this project which will be very important for our North American user group. Thanks very much!
Re: Tax-exclusive product prices - estimates
Note that at the moment it is quite possible to construct an invoice (and credit note) that shows the ex-tax amounts - because the tax (and well as the inc-tax amount) is held for each line item.
However, this is not possible for an estimate - because only the inc-tax (high and low) amounts are held with no tax information.
In order to support the printing of estimates in ex-tax mode, either we have to add tax amounts to the estimate, or switch the estimate amounts to being ex-tax if the practice is running in ex-tax mode.
I suspect that the better approach is to add the tax amounts so that when (in ex-tax mode) the extimate is printed, items can be shown as ex-tax and the total ex-tax, total tax and total inc-tax amounts can be shown at the end.
I am not sure how to handle the changeover. Since estimates are essentially short term things, I suspect that a reasonable approach is to leave existing estiamtes as is (so /details/highTaxAmount and /details/lowTaxAmount are null for estimates created prior to the upgrade but are non-null for estimates created after.
Regards, Tim G