Docstoc

Vaccine Cost and Payment Comparison Spreadsheet

Document Sample
Vaccine Cost and Payment Comparison Spreadsheet Powered By Docstoc
					Vaccine Cost and Payment Comparison Spreadsheet
This spreadsheet can be used to calculate your office's profit or loss on vaccines and vaccine administration
with any given insurance company. It is divided into two sections: the first covering the vaccine and its
overhead and the second covering vaccine administration.

Vaccine and Vaccine Overhead Section:
The vaccines are listed in Column B, with the CPT code for the vaccine listed in Column C. For the sake of
completeness, some vaccines that are not very common or that are pending approval are included. Some of the
known brand names are listed in the notes attached to Column B (per 2009 CPT book). To see a note, hover
your cursor over a box that has a red triangle in the corner. The note will appear as a pop-up.

If you stock any vaccines that are not included in the list, you can add them in the spaces in lines 78 to 82 at the
bottom. If you are comfortable using Excel and want to get rid of lines with vaccines you know you will never
use, just right click on the appropriate line number and choose "delete". This will clean up the spreadsheet and
make it easier to read.

VERY IMPORTANT: In this section, the "Overhead Cost" (Column E) can be entered in either of 2 ways:
1. If you want to use a constant percentage over purchase price as your overhead (such as choosing a
percentage between 17% and 28% as discussed in the AAP's Business Case for Pricing Vaccines, or using the
median value of 16% determined by a survey in Arizona), enter a percentage in Line 36, Column C. The
spreadsheet with then calculate the overhead based on the purchase price that you will enter in Column D.
OR (do not use both option 1 AND option 2):
2. If you want to use the overhead you calculated using the Vaccine Cost Calculator for each vaccine, Leave
Line 36, Column C blank and simply enter the overhead values from the calculator in Column E.

Once you have entered the purchase price (including excise tax and sales tax) of each vaccine and used either
of the two methods above to fill in overhead cost, then fill in the values in Column F with the amount that the
insurance company in question pays for each vaccine. As you do so, the amount of profit per dose of vaccine
will be calculated in Column G (if there is a loss, it will appear in parentheses). If you then want to calculate the
total profit or loss on the vaccine over a given period of time, enter the number of doses of that vaccine
administered to patients with this insurance in Column H. The total profit (or loss) will calculate in Column I.

Name of Insurance Company:


Vaccine overhead percentage


                Vaccine                      CPT     Purchase Overhead Insurance Per Dose       Number
                                             Code      Price    Cost    Payment Profit/(Loss)    Given
Hepatitis A (adult)                          90632            $     -             $       -
Hepatitis A (pediatric)                      90633            $     -             $       -
Hepatitis A-Hepatitis B combination          90636            $     -             $       -
Hib (PRP-OMP)                                90647            $     -             $       -
Hib (PRP-T)                                  90648            $     -             $       -
HPV (quadrivalent - 6,11,16,18)              90649            $     -             $       -
HPV (bivalent - 16,18)                       90650            $     -             $       -
Influenza, 6-35 months (preservative free)   90655            $     -             $       -
Influenza, >3 years (preservative free)      90656            $     -             $       -
Influenza, 6-35 months                       90657            $     -             $       -
Influenza, >3 years                          90658            $     -             $       -
Influenza, intranasal                        90660            $     -             $       -
Influenza, pandemic formulation              90663            $     -             $       -
Pneumococcal conjugate (7-valent)            90669            $     -             $       -
Pneumococcal conjugate (13-valent)           90670            $     -             $       -
Rabies vaccine (intramuscular)               90675            $     -             $       -
Rotavirus vaccine (pentavalent)              90680            $     -             $       -
Rotavirus vaccine (human)                    90681            $     -             $       -
Typhoid, oral                                90690            $     -             $       -
Typhoid, Vi capsular (ViCPs)                 90691            $     -             $       -
DTaP-IPV                                     90696            $     -             $       -
DTaP-Hib-IPV                                 90698            $     -             $       -
DTaP                                         90700            $     -             $       -
DT, <7 years                                 90702            $     -             $       -
MMR                                          90707            $     -             $       -
MMR-Varicella                                90710    $     -               $        -
IPV (polio, inactivated)                     90713    $     -               $        -
Td (preservative free, >7 years)             90714    $     -               $        -
Tdap                                         90715    $     -               $        -
Varicella                                    90716    $     -               $        -
DtaP-Hib                                     90721    $     -               $        -
Dtap-HepB-IPV                                90723    $     -               $        -
Pneumococcal polysaccharide (23-valent)      90732    $     -               $        -
Meningococcal conjugate                      90734    $     -               $        -
Hepatitis B, adolescent (2 dose)             90743    $     -               $        -
Hepatitis B, pediatric/adolescent (3 dose)   90744    $     -               $        -
Hepatitis B, adult                           90746    $     -               $        -
Hepatitis B-Hib                              90748    $     -               $        -
                                                      $     -               $        -
                                                      $     -               $        -
                                                      $     -               $        -
                                                      $     -               $        -
                                                      $     -               $        -

Total Profit/Loss on Vaccine Product with this Insurance Company

Vaccine Administration Section
This section works in a similar fashion to the first section. The vaccine administration descriptors and CPT
codes are listed in Columns B and C. For reference, the 2009 Medicare values for the codes are listed in
Column D. If you have used the Vaccine Cost Calculator to calculate your cost of vaccine administration, enter
the values in Column E (use the value from the calculator listed as "Total Administration Expenses - Initial
Vaccine" for CPT codes 90465, 90467, 90471, and 90473; use the value listed as "Total Administration
Expenses - Subsequent Vaccine" for the other codes). If you do not want to use the calculator, then you may
want to use the 2009 Medicare values instead. Then you can fill in the insurance payment (Column F) to
calculate the profit (or loss) on each vaccine administration (Column G); then fill in the number of times each
type of administration is performed over the desired period of time for patients with the insurance in question
(Column H) to calculate the total profit (or loss) on vaccine administration for the insurance. The last line will
then show you the total profit (or loss) on vaccines plus vaccine administration for that insurance company over
that period of time.
       Administration Type       CPT      2009    Cost Per Insurance Per Admin     Number
                                 Code    Medicare Admin     Payment Profit/(Loss) Performed
Initial antigen                  90460   $   20.92                        $   -
Susequent antigen                90461   $   10.46                        $   -

Total Profit/Loss on Vaccine Administration with this Insurance Company
ine administration
 ccine and its




 For the sake of
ncluded. Some of the
see a note, hover


n lines 78 to 82 at the
now you will never
 the spreadsheet and


er of 2 ways:
 choosing a
ccines, or using the
umn C. The
er in Column D.

ch vaccine, Leave


 ine and used either
e amount that the
per dose of vaccine
want to calculate the
that vaccine
ulate in Column I.




                  Total          # of   Per Dose Profit/(Loss)   Total Profit/Loss
               Profit/(Loss)   Antigens   including Admin        including Admin
           $              -             $                  -     $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $              -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -
           $             -

            $            -   Total including Admin:   $   -


criptors and CPT
 s are listed in
  dministration, enter
  penses - Initial
 inistration
ator, then you may
 Column F) to
mber of times each
 urance in question
 . The last line will
 urance company over
       Total
    Profit/(Loss)
$              -
$              -

$             -

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:18
posted:9/16/2012
language:Unknown
pages:8