Home › Forums › Users › General User's Discussion ›

Price update

Submitted by annaplatt on Fri, 05/03/2021 - 13:04
in

Hello - just after some advice for the easiest way to update our prices for external pathology...

Would like to keep the mark up the same - varies for different tests, and would like to have an end date and a start date for the new price..... is there an easy way as the thought of sitting down for hours manually adding in prices is not very appealing...

Thanks Anna

‹ Search Via Invoice Number how many clients do I have? ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Price update

Submitted by tanderson on Fri, 05/03/2021 - 14:53.

This can be done using price export and import.

See Pricing Updates for instructions on manipulating the exported data.

Re: Price update

Submitted by annaplatt on Sat, 06/03/2021 - 07:18.

Thank you for this - have had a look - just wondering if I am adjusting the unit cost is the procedure the same as I did a test with one product and it did not seem to work.

I am also struggling a bit with the date format as when I export it is in the yr/mth/ day format and when I adjust the start date it goes to the day/mth/yr format?

Thanks again, Anna

Re: Price update

Submitted by Ben_Charlton on Mon, 08/03/2021 - 09:09.

Usually when you do the import the importer asks you to supply the date formate for the report your uploading.

 

So it doesn't actually matter so much - you can set it however you want in the CSV file.

 

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Price update

Submitted by annaplatt on Wed, 31/03/2021 - 19:58.

Ok... so got the date working but the prices did not update how I expected....

I wanted to change the unit cost as the price had increased.

I wanted to have an end date as today, and a start date with the new cost price, and wanted to keep the mark up the same....

But what actually happened is

1. Unit cost went up

2. Tax inclusive price remained the same

3. Mark up went down

4. Over rid the previous unit cost and start date....

Fortuntely I had printed out a product list with the mark up so have manually adjusted

Cannot see a column on the CSV for the mark up - our pathology has variable mark up

I imagine there is an easier way...

Thank you Anna

 

 

Re: Price update

Submitted by tanderson on Thu, 08/04/2021 - 11:45.

The import will show you what is going to change before it saves them, so you can always cancel out if it hasn't done what you expect.

To create new prices and close the existing ones, you need to clear the value in the Unit Price Id column for the prices you want to change, and set a new Unit Price Start Date.

If you just increase the unit cost, it will recalculate the markup from the unit cost and price you import.
To keep the markup the same, you need to increase both the unit cost and price.

You can do this loading the CSV into OpenOffice Calc and:

1. saving it as an OpenOffice spreadsheet

2. duplicating all the data onto a new sheet 'Sheet2'

3. in Sheet2, in column U

  • set U1 to 'Markup
  • set U2 to =IF(N2 <> 0; (M2/N2-1)*100; 0)
  • duplicate U2 to the last U row where there is a corresponding product

This calculates the markup for each product from the existing Unit Price (column M) and Unit Cost (column N) from Sheet2

4. in Sheet2 in column V

  • set V1 to 'New Unit Cost
  • manually input the new unit costs in the cells V2..Vn, where the costs have changed

5. in Sheet2 in column W

  • set W1 to 'New Unit Price
  • set W2 to =IF(ISBLANK(V2); M2; V2 * (1 + U2 / 100))
  • duplicate W2 to the last W row where there is a corresponding product

This calculates a new Unit Price from the New Unit Cost column, using the existing Markup.
If there is no new Unit Cost, it defaults to the existing Unit Price.

6. in Sheet1, in column M

  • set M2 to =Sheet2.W2
  • duplicate M2 to the last M row where there is a corresponding product

This updates the Unit Price to the New Unit Price column from Sheet2

7. in Sheet1, in column N

  • set N2 to =IF(ISBLANK(Sheet2.V2); Sheet2.N2; Sheet2.V2)
  • duplicate N2 to the last N row where there is a corresponding product

This makes the Unit Cost column contain the same values as the New Unit Cost column, where there is a value, or the existing Unit Cost if there is none

8. Save Sheet1 to CSV (File - Save As - Text CSV), and import into OpenVPMS

 

Syndicate content