Calculate Net Revenue of Outpatient Injectable Drugs (Excel spreadsheet)

This spreadsheet allows users to calculate the net revenue for a specific outpatient reimbursable drug. Users provide basic information about a specific drug, such as purchase, dosage, and institutional markup, as well as the names of insurance providers, both public and private, and the reimbursement for the drug in question. Users will be asked for additional information, including the insurance payer mix, the institution's bad debt, and other types of losses or write-offs. (The color codes on the spreadsheet give instructions for the specific data required.) This information is entered into the spreadsheet and the weighted net revenue payment versus the cost of the drug is calculated to determine net margin. This method focuses on both expense and revenue. As changes in reimbursement occur, from either Medicare APC's or other payers, the spreadsheet can be utilized to determine the net revenue, and therefore the influence of these changes on a pharmaceutical budget.

Identify Top Dollar Volume Drugs (Excel spreadsheet)

This "impact report" can be utilized to identify which of the top dollar volume drugs are driving changes in the drug expenses for your institution. The impact report is determined from the wholesaler system, often called the 80/20 report. The intent is that 200-300 lines of drugs represent 80-90% of the total dollars expended. (Most pharmacy formularies may have approximately 2500 line items.) The 80/20 report focuses on the important drivers of the budget. It is prudent to generate this report quarterly, taking the year-to-date data for the current fiscal year and projecting it for a full 12 months in order to compare it to the prior fiscal year. By reviewing the percent change, one can quickly determine which drugs are causing the increases and decreases in expenses. (The impact report is broken down into therapeutic categories to allow one to determine shifts in therapy within a therapeutic class.)

Pass-through Drug Calculator (Excel spreadsheet)

This spreadsheet allows the user to input Medicare reimbursement rates from the previous year(s) and the new Medicare rates in order to compare the changes. If the Medicare volume for each drug is input, then the spreadsheet will calculate the total impact of the Medicare changes based on the institution's volumes. One can input the actual cost that will show the margin for each product as well.

As reimbursement for healthcare services has become more competitive, pharmacy managers have had to come to terms with "profitability" of services. Although it is tempting to examine the cost alone when working to determine the most appropriate course of action in management of drug use, yet the payment for a patient care service or DRG often creates the need to consider a more complex alternative. Does the hospital make or lose money on treating this type of case? Most case rates are determined based on a standard practice or on a current norm. When a costly new drug or treatment is introduced, or when treatment protocols or standards change, the cost of medications, and the profitability of the case changes. Formulary additions, practice guidelines, and other decisions made by P&T committees and pharmacists should consider not only the cost and cost-benefit relationships associated with a particular drug therapy, but also the reimbursement received by the hospital. Revenues and costs are time-consuming to track in a health-system. However, a thoughtful pharmacy leader must consider the implications of revenue and margin in choosing a course of action. This is a critical skill as high cost and high impact drugs are considered for use in a hospital.

Calulate variance between your cost and drug reimbursement cost

In January 2006 hospital-based clinics will be paid average sales price plus 6% (ASP+6%) for specified covered outpatient drugs, under the Hospital Outpatient Prospective Payment System, similar to reimbursement rates of private physician offices in 2005 with an added 2% charge for pharmacy handling costs. This proposed CMS rule will be finalized in November 2005 and will apply to all Medicare patients.

The attached Excel spreadsheet contains the proposed rates of ASP+6% and the appropriate descriptive information. A blank column for input of your costs will result in the calculation of variance between your cost and the reimbursement cost for each drug. The spreadsheet will then calculate the percent of reimbursement and cost to determine if you are at 106%. You may be greater than 106% on some items and less than 106% on others. This information can aid you in price negotiations and formulary decisions.

(Excel Spreadsheet) HOPPS and ASP Rates 06