Monthly margin calculator and price concession comparison spreadsheet

01 May 2019

Superintendent update explaining the margin calculator template and price concession comparison spreadsheet

Dear Superintendent,

I am pleased to share my monthly Drug Tariff (DT) Part VIIIA medicines prices margin calculation template and price concessions margin versus DT price spreadsheet. These tools allow contractors to monitor the indicative difference in costs between the prices they pay when buying Part VIIIA listed medicines and the reimbursement for dispensing those medicines, updated to include price concessions when they are released. I would like to thank all those who provided feedback – we will continue to update the spreadsheets following the suggestions received.

How to use

The following table gives a quick guide on how to use each spreadsheet – please note all prices in each spreadsheet is in pence to allow consistency with the DT.

Drug Tariff (DT) Part VIIIA medicines prices margin calculation template – monitor the indicative cost differences between what you pay and your reimbursement Price concessions margin versus DT price spreadsheet – monitor the indicative differences of granted price concessions for the last two months
  • Use the ‘How to use’ sheet; this can be found on ‘Sheet 2’ within the spreadsheet download
  • Column A – highlight in red if the medicine is a top moving line for your pharmacy; filter as required to show only these lines– please do so using the Excel ‘Fill colour’ function; you can also filter the column as required with the ‘Sort by column’ function
  • Column B – medicines which are in Part VIIIA Category C have been highlighted in purple as these lines are potentially more profitable
  • Columns G and I – highlighted in bright yellow – use column G to add the total dispensed packs for the month and use column I to add in the buying prices per pack (in pence) for the relevant medicines
  • Column M – highlighted in orange – use this column to add in any price concessions (in pence) granted for the relevant medicines
  • Columns Q and R – highlighted in blue – these are optional columns that you may choose to use to add in information about which wholesaler your pharmacy orders individual medicines from, along with the relevant wholesalers order code for the medicine
  • Columns L, O and P will only show values if corresponding cells have information added to it – for example:
    • If columns G and I are completed, column L will show a value for the difference in percentage
    • If columns G, I and M are completed, column O and P will show a value as necessary
  • All other columns have been locked to prevent any changes – Do not use these columns
  • The last row in the spreadsheet has been formulated in columns H, J, K, L, N, O, P to give you the totals
  • This spreadsheet will be updated when further price concessions are announced (up to three versions per month) – please ensure you check and download the recent spreadsheet periodically
  • This spreadsheet shows price concession information for both the current month, and the previous month
  • All columns have been locked to prevent any changes – do not use these columns

We realise you may not ever dispense the whole inventory on the margin calculator, and the information gained by using the spreadsheet is indicative only. It is there so you can adapt it to your individual basket of items dispensed in your pharmacy across a calendar month – the following functions may be useful to you:

  • ‘Ctrl+F’: pressing the ‘Ctrl’ button on the keyboard followed by the ‘F’ button allows you to find a particular medicine quickly
  • Filter button: the first row of each spreadsheet has a ‘triangle’ symbol – this allows you to filter that particular column as required

How frequently will the templates be updated?

  • The calculation template will be updated once a month by a member of my team when the following month’s Drug Tariff has been released. The spreadsheet title will reflect the month of the DT that the information corresponds to, and usually the update will occur in the last few days before the new month begins.
  • The price concessions spreadsheet will be updated each time new price concessions are released and the document title will display a version number based on the number of times the price concessions list has been issued during that month. This spreadsheet shows both the current month and the previous month price concessions for contractor’s information.

Where can I download these resources?

Both resources will sit on the dedicated NPA webpage, and will be updated each month.

Please follow the hyperlink below:
https://www.npa.co.uk/information-and-guidance/monthly-margin-calculator-template/

Please continue to let me know if you have any feedback about these spreadsheets; I always value your feedback and appreciate the time you take to send me your thoughts.

Kind regards,

Leyla

For further information, advice and support, please contact the NPA Pharmacy Services team on 01727 891800 or email at pharmacyservices@npa.co.uk.

Kind regards,

Leyla Hannbeck MSc, FRPharmS, MBA, MA
NPA Director of Pharmacy
Medication Safety Officer