Centers for Medicare & Medicaid Services
Center for Medicare (CM)
Hospital and Ambulatory Policy Group
7500 Security Blvd
Baltimore, MD 21244-1850
Average Sale Price (ASP) Data Collection
Template/Data Validation Macro
User Manual
Version: 1.4
Last Modified: June 8, 2011
User Manual Version 1.0 / September 1, 2010 ii
TABLE OF CONTENTS
1 OVERVIEW ............................................................................................................................ 1
Explanatory Messaging .......................................................................................................................... 1
Secondary Validation Check .................................................................................................................. 1
2 NAVIGATING THE TEMPLATE ....................................................................................... 2
3 FIELD DEFINITIONS ........................................................................................................... 5
4 TEMPLATE REQUIREMENTS ......................................................................................... 16
4 ACRONYMS ......................................................................................................................... 16
User Manual Version 1.0 / September 1, 2010 iii
LIST OF FIGURES
Figure 1: Example of validation macro error message .................................................................. 1
Figure 2: Screenshot of Run ASP Validation Button ..................................................................... 2
Figure 3: Screenshot of Security Warning section ......................................................................... 2
Figure 4: Screenshot of detailed security alert macro ..................................................................... 3
Figure 5: Screenshot of validation macro error message ................................................................ 3
Figure 6: Screenshot of highlighted cell with error ........................................................................ 3
Figure 7: Screenshot of the Run ASP Validation Button ............................................................... 4
1 OVERVIEW
The Average Sales Price Data Form Addendum A Excel template (available at:
http://www.cms.gov/McrPartBDrugAvgSalesPrice/) provides a framework for drug manufacturers to submit
their ASP data for current Medicare Part B drugs to CMS. Such data consists of financial, sales, and descriptive
data elements.
The Addendum A Excel template has been enhanced to include a
validation macro which will ensure that the Center for Medicare (CM)/
Hospital and Ambulatory Policy Group (HAPG) receives complete and
correctly formatted data from each manufacturer. The validation macro
performs a quality check on the formatting of manufacturers‟ ASP
information.
Explanatory Messaging
Each cell within the data entry area of the template has been
programmed to validate data formatting upon entry. If an invalid format is entered, an error message will be
displayed, and the cell will remain highlighted until the error is corrected. Additionally, if a required field is left
blank, the macro will provide a message to the user upon exiting that required field (refer to Figure 1 below for
sample error message).
Figure 1: Example of validation macro error message
Secondary Validation Check
The user initiates a secondary validation check by clicking the [Run ASP Validation] button located above the
first row of the field descriptions. This prompts the macro to scan all cells that contain data.
Note: The secondary validation check can be run at any time during the data entry process. It can be run more
than once.
The secondary check provides an alternate method of data validation in MS Excel. Data that are copied and
pasted into the template will override many of the individual cell validations. Therefore, the validation macro
should be run after pasting data into the template.
The ASP Macro does not edit for the
validity of data or of calculations,
only for whether the contents of the
field are correctly formatted. Users
are responsible for ensuring that
the data entered is technically
correct.
Page 1 of 19
Page 2 of 19
Figure 2: Screenshot of Run ASP Validation Button
2 NAVIGATING THE TEMPLATE
Open the Addendum A template in Microsoft Excel.
The first time the template is accessed, the user must accept the digital signature to utilize the validation
macro.
To accept the digital signature, look for the security warning at the top of the page (see Figure 3).
Figure 3: Screenshot of Security Warning section
Select the Options button to the right of the security
warning message. When the next window appears, select the
“Trust all documents from this publisher” option and select the
“OK” button at the bottom of the screen (see Figure 4). This
will enable the macro and enable the spreadsheet to accept data
entries. Selection of the “Enable this content” option is also
acceptable, but this selection must be made each time the
spreadsheet is open.
Note: Once you‟ve accepted the option to “Trust all documents
from this publisher”, you will not have to perform this step
again.
If a user’s system security
policies do not allow the use of
macros, even if from trusted
sources, notify CMS in writing
as a part of your submission’s
cover letter or “Assumptions”
document. All submissions
must still conform to the data
“Field Definitions” described in
Section 3.
Page 3 of 19
Figure 4: Screenshot of detailed security alert macro
Enter data into each cell. If the user enters an invalid format or leaves a required field or field
combination blank, an error message will be displayed (see Figure 5) and the cell(s) with the incorrect
format will be highlighted until the user corrects the error(s) (see Figure 6).
Figure 5: Screenshot of validation macro error message
Figure 6: Screenshot of highlighted cell with error
Upon completion of data entry, select the [Run ASP Validation] button located above the first row of
the field descriptions to determine if data pass the preliminary validation checks (see Figure 7).
Page 4 of 19
Figure 7: Screenshot of the Run ASP Validation Button
The user shall correct any highlighted errors before proceeding. Upon completion of all corrections, the
user shall run the full validation macro again to confirm there are no additional errors.
Once the entered data are error free, the user shall submit the template to CMS per the submission
instructions at: http://www.cms.gov/McrPartBDrugAvgSalesPrice/.
Page 5 of 19
3 FIELD DEFINITIONS
The validation macro identifies the values listed below as acceptable for each field. All required fields shall
contain appropriately formatted data. If no data are present in a required field, the cell with the missing data
will be highlighted and an error message will appear. The user must enter properly formatted data before
additional data entry can be completed. Fields identified as not required may be left blank.
Depending on the type of product, manufacturers must submit ASP data according to one of the following
sets of field definitions.
1. Table 1 contains the field definitions for drugs and biologicals reported on the NDC
or CMS-specified unit level.
Most ASP reporting is done at the NDC level where the ASP corresponds to the amount of drug represented
by that NDC. However, for a limited number of products, reporting at the NDC unit level is not appropriate
and must be done at a CMS-specified unit level. A list of such drug products is maintained on the CMS
website at: http://www.cms.gov/McrPartBDrugAvgSalesPrice/. For these drugs and biologicals,
manufacturers will still submit ASP sales data for an NDC, but will do so on an ASP unit level specified in
this list.
Table 1: Field Definitions for Drugs and Biologicals
Field Name
Field Definition
Valid Values
Manufacturer's Name
The reporting
manufacturer‟s name.
Free form field. Alpha and
numeric values accepted.
11-Digit National Drug Code
(NDC1)
The NDC1 is the first 5
digits of the 11 digit
National Drug Code that
identifies the labeler.
The 11 digit NDC
consists of the NDC1,
NDC2, and NDC3,
which identifies the
labeler, product, and
package size.
NDC1 values should be
formatted as a 5 digit
number. Use a preceding
zero(s) as needed (ex:
labeler code 1234 shall be
reported as 01234).
11-Digit National Drug Code
(NDC2)
The NDC2 is the sixth
through the ninth digits
of the 11 digit National
Drug Code that identifies
the product.
The 11 digit NDC
NDC2 values should be
formatted as a 4 digit
number.
Use a preceding zero as
needed (ex: NDC2 123 shall
be reported as 0123).
Page 6 of 19
Field Name
Field Definition
Valid Values
consists of the NDC1,
NDC2, and NDC3,
which identify the
labeler, product, and
package size.
11-Digit National Drug Code
(NDC3)
The NDC3 is the last 2
digits of the 11 digit
National Drug Code that
identifies the package
size.
The 11 digit NDC
consists of the NDC1,
NDC2, and NDC3,
which identify the
labeler, product, and
package size.
NDC3 values should be
formatted as a 2 digit
number.
Use a preceding zero as
needed (ex: NDC3 1 shall be
reported as 01).
Alternate ID
Numeric or
alphanumeric alternate
identifier (ex: an NHRIC
or UPC number) used
when an 11 digit NDC is
not available.
An alphanumeric alternate
ID is 13 characters or less.
Manufacturer's Average Sales
Price
ASP for a corresponding
ASP unit rounded to 3 or
more decimal places.
Any positive or negative
numbers including zero.
Value must include at least
three decimal places. Do
not include dollar sign ($).
If no ASP, enter “0.000”.
Number of ASP Units
The number of ASP units
sold.
Any positive or negative
numbers including zero.
Value must include at least
three decimal places. If no
units sold, enter “0.000”.
Wholesale Acquisition Cost (WAC)
The WAC for a
corresponding ASP unit
in effect on the last day
of the reporting period.
WAC is defined in
Any positive or negative
numbers including zero.
Value must include at least
three decimal places. Do
not include dollar sign ($).
If no WAC available, enter
Page 7 of 19
Field Name
Field Definition
Valid Values
Section 1847A(c)(6)(B)
as “the manufacturer‟s
list price for the drug or
biological to wholesalers
or direct purchasers in
the United States, not
including prompt pay or
other discounts, rebates
or reductions in price, for
the most recent month
for which the
information is available,
as reported in wholesale
price guides or other
publications of drug or
biological pricing data.”
CMS further clarified, in
70 FR 70221, that
manufacturers must
report WAC for all
single source drugs and
biologicals (including
new drugs) each
reporting period.
Manufacturers must
report the WAC in effect
on the last day of the
reporting period.
“0.000”.
Name of Drug or Biological
The trade or brand name
of the product or the
active ingredient name.
This a free form field
limited to 100 alphanumeric
characters.
Strength of the Product Volume
Per Item
The dosage strength of
one item (e.x: 250 mg
tablet, 20 mg/ml
solution, 1 IU).
This a free form field
limited to 500 alphanumeric
characters.
Volume Per Item
The amount in one item
(ex: 10 ml in one vial, or
500 tablets in one bottle).
This a free form field
limited to 12 alphanumeric
characters. Enter “1” for
certain forms of drugs (e.g.
powders) when “Strength of
the Product” indicates the
amount of the product per
item.
Number of Items Per NDC
The number of items in
the 11-digit NDC (ex: if
an NDC packaged as a
Limited to 10 numeric
digits.
Page 8 of 19
Field Name
Field Definition
Valid Values
box contains 4 vials, the
number of items per
NDC is 4).
Expiration Date of Final Lot Sold
The expiration date of
the final lot sold must be
reported to CMS once at
the end of utilization of
the NDC or when there
are no sales for three
consecutive quarters.
For ASP purposes, “at
the end of utilization”
means the manufacturer
will not make sales of
that NDC to any
purchaser.
Value should be in the date
format (MM/DD/YYYY).
Date of First Sale
Report for NDCs first
sold on or after
04/01/2006. Report at
least once and no later
than with the first ASP
report.
Value should be in the date
format (MM/DD/YYYY).
Number of CAP Units Excluded
Beginning with the
3Q2006 reporting period,
report the number of
whole or fractional units
administered to a
beneficiary by a Part B
Drug Competitive
Acquisition Program
participating physician
excluded from the ASP
calculation.
Data must be numeric and
must include at least three
decimal places.
FDA Application Number
The application number
assigned by the Food and
Drug Administration
(FDA).
This field is limited to 6 or 7
characters. Data may
include a mixture of alpha
numeric and numeric values
(ex: A123456) or just
numeric values (ex:
123456). For entries with
only numeric values, use
leading zeros if necessary to
report 6 digits. Do not use
dashes or spaces.
FDA Application Supplement
Number
The application
supplement number
assigned by the Food and
This field is limited to 4
characters. Use the format
XXXX for the application
Page 9 of 19
Field Name
Field Definition
Valid Values
Drug Administration
(FDA).
supplement number.
Additional FDA Application
Number #1
The application number
assigned by the Food and
Drug Administration
(FDA).
If the product has more than
one FDA Application
Number, enter an additional
application number here.
This field is limited to 6 or 7
characters. Data may
include a mixture of alpha
numeric and numeric values
(ex: A123456) or just
numeric values (ex:
123456). For entries with
only numeric values, use
leading zeros if necessary to
report 6 digits. Do not use
dashes or spaces.
Additional FDA Application
Supplement Number #1
The application
supplement number
assigned by the Food and
Drug Administration
(FDA).
This field is limited to 4
characters. Use the format
XXXX for the supplemental
application number.
Additional FDA Application
Number #2
The application number
assigned by the Food and
Drug Administration
(FDA).
If the product has more than
one FDA Application
Number, enter an additional
application number here.
This field is limited to 6 or 7
characters. Data may
include a mixture of alpha
numeric and numeric values
(ex: A123456) or just
numeric values (ex:
123456). For entries with
only numeric values, use
leading zeros if necessary to
report 6 digits. Do not use
dashes or spaces.
Additional FDA Application
Supplement Number #2
The application
supplement number
assigned by the Food and
Drug Administration
(FDA).
This field is limited to 4
characters. Use the format
XXXX for the supplemental
application number.
FDA Final Pre-Marketing
This is the original date
Value should be in the date
Page 10 of 19
Field Name
Field Definition
Valid Values
Approval Date
that the FDA granted
approval for the drug
(NDA), biological
(BLA), or pre-marketing
application (PMA).
format (MM/DD/YYYY).
If there is no approval date,
baseline date should be set
to 01/01/1965.
FDA Approval Type
The type of FDA
approval for the product.
Choose a value from the
drop down menu (ANDA,
NDA, 510K, BLA, PMA,
Human Tissue, Vaccine,
Other).
If Other, specify the type in
the column „Description of
FDA Approval Type‟.
Description of FDA Approval Type
If Other was specified in
the column „FDA
Approval Type,‟ please
specify the type.
Free form field limited to
255 alphanumeric
characters.
Descriptive Data Corrected
To indicate that a data
element other than a
manufacturer‟s ASP or
number of ASP units has
changed since the last
report.
Free form field limited to
255 alphanumeric
characters. Describe which
data element(s) have been
corrected.
2. Table 2 contains the field definitions for dermal grafting products.
Some dermal grafting products are not assigned an NDC. Instead, manufacturers identify them using
Universal Product Codes (UPCs) or other similar but unique identifiers. If an NDC is not available, the
UPC or other unique identifier must be entered in the field “Alternate ID”. Manufacturers may not convert
a UPC or other alternative identifier to an NDC format by adding zeros or removing numbers. Additionally,
manufacturers must submit ASP sales data for dermal grafting products by square centimeter.
Table 2: Field Definitions for Dermal Grafting Products
Field Name
Field Definition
Valid Values
Manufacturer's Name
The reporting
manufacturer‟s name.
Free form field. Alpha and
numeric values accepted.
11-Digit National Drug Code
(NDC1)
The NDC1 is the first 5
digits of the 11 digit
National Drug Code that
identifies the labeler.
NDC1 values should be
formatted as a 5 digit
number. Use a preceding
zero(s) as needed (ex:
labeler code 1234 shall be
Page 11 of 19
The 11 digit NDC
consists of the NDC1,
NDC2, and NDC3,
which identifies the
labeler, product, and
package size.
reported as 01234).
11-Digit National Drug Code
(NDC2)
The NDC2 is the sixth
through the ninth digits
of the 11 digit National
Drug Code that identifies
the product.
The 11 digit NDC
consists of the NDC1,
NDC2, and NDC3,
which identify the
labeler, product, and
package size.
NDC2 values should be
formatted as a 4 digit
number.
Use a preceding zero as
needed (ex: NDC2 123 shall
be reported as 0123).
11-Digit National Drug Code
(NDC3)
The NDC3 is the last 2
digits of the 11 digit
National Drug Code that
identifies the package
size.
The 11 digit NDC
consists of the NDC1,
NDC2, and NDC3,
which identify the
labeler, product, and
package size.
NDC3 values should be
formatted as a 2 digit
number.
Use a preceding zero as
needed (ex: NDC3 1 shall be
reported as 01).
Alternate ID
Numeric or
alphanumeric alternate
identifier (ex: an NHRIC
number or UPC) used
when an 11 digit NDC is
not available.
An alphanumeric alternate
ID is 13 characters or less.
Manufacturer's Average Sales
Price
ASP rounded to 3 or
more decimal places.
Report the ASP per
square centimeter.
Any positive or negative
numbers including zero.
Value must include at least
three decimal places. Do
not include dollar sign ($).
If no ASP, enter “0.000”.
Number of ASP Units
Report the number of
square centimeters
represented by the NDC
Any positive or negative
numbers including zero.
Value must include at least
Page 12 of 19
or Alternate ID (ex: an
NDC or Alternate ID that
represents a box of five 2
cm x 3cm grafts contains
a total of 30 square cm or
30 ASP units).
three decimal places. If no
units sold, enter “0.000”.
Wholesale Acquisition Cost (WAC)
The WAC in effect on
the last day of the
reporting period.
Report the WAC per
square centimeter.
WAC is defined in
Section 1847A(c)(6)(B)
as “the manufacturer‟s
list price for the drug or
biological to wholesalers
or direct purchasers in
the United States, not
including prompt pay or
other discounts, rebates
or reductions in price, for
the most recent month
for which the
information is available,
as reported in wholesale
price guides or other
publications of drug or
biological pricing data.”
CMS further clarified, in
70 FR 70221, that
manufacturers must
report WAC for all
single source drugs and
biologicals (including
new drugs) each
reporting period.
Manufacturers must
report the WAC in effect
on the last day of the
reporting period.
Any positive or negative
numbers including zero.
Value must include at least
three decimal places. Do
not include dollar sign ($).
If no WAC available, enter
“0.000”.
Name of Drug or Biological
The trade or brand name
of the product or the
active ingredient name.
This a free form field
limited to 100 alphanumeric
characters.
Strength of the Product Volume
Per Item
The total square
centimeters in one item
(ex: a 6cm x 8cm item is
48 sq cm).
This a free form field
limited to 500 alphanumeric
characters.
Page 13 of 19
Volume Per Item
The amount in one item.
This a free form field
limited to 12 alphanumeric
characters. Enter “1” for
powders and sheets.
Number of Items Per NDC
The number of items in
the 11-digit NDC or
Alternative ID (ex: for an
NDC or Alternate ID that
has 5 grafts in a pack, the
number of items per
NDC is 5).
Limited to 10 numeric
digits.
Expiration Date of Final Lot Sold
The expiration date of
the final lot sold must be
reported to CMS once at
the end of utilization of
the NDC or Alternate ID
when there are no sales
for three consecutive
quarters. For ASP
purposes, “at the end of
utilization” means the
manufacturer will not
make sales of that NDC
or Alternate ID to any
purchaser.
Value should be in the date
format (MM/DD/YYYY).
Date of First Sale
Report for
NDCs/Alternate IDs first
sold on or after
04/01/2006. Report at
least once and no later
than with the first ASP
report.
Value should be in the date
format (MM/DD/YYYY).
Number of CAP Units Excluded
Beginning with the
3Q2006 reporting period,
report the number of
whole or fractional units
administered to a
beneficiary by a Part B
Drug Competitive
Acquisition Program
participating physician
excluded from the ASP
calculation.
Data must be numeric and
must include at least three
decimal places.
FDA Application Number
The application number
assigned by the Food and
Drug Administration
(FDA).
This field is limited to 6 or 7
characters. Data may
include a mixture of alpha
numeric and numeric values
(ex: A123456) or just
numeric values (ex:
Page 14 of 19
123456). For entries with
only numeric values, use
leading zeros if necessary to
report 6 digits. Do not use
dashes or spaces.
FDA Application Supplement
Number
The application
supplement number
assigned by the Food and
Drug Administration
(FDA).
This field is limited to 4
characters. Use the format
XXXX for the application
supplement number.
Additional FDA Application
Number #1
The application number
assigned by the Food and
Drug Administration
(FDA).
If the product has more than
one FDA Application
Number, enter an additional
application number here.
This field is limited to 6 or 7
characters. Data may
include a mixture of alpha
numeric and numeric values
(ex: A123456) or just
numeric values (ex:
123456). For entries with
only numeric values, use
leading zeros if necessary to
report 6 digits. Do not use
dashes or spaces.
Additional FDA Application
Supplement Number #1
The application
supplement number
assigned by the Food and
Drug Administration
(FDA).
This field is limited to 4
characters. Use the format
XXXX for the supplemental
application number.
Additional FDA Application
Number #2
The application number
assigned by the Food and
Drug Administration
(FDA).
If the product has more than
one FDA Application
Number, enter an additional
application number here.
This field is limited to 6 or 7
characters. Data may
include a mixture of alpha
numeric and numeric values
(ex: A123456) or just
numeric values (ex:
123456). For entries with
only numeric values, use
leading zeros if necessary to
report 6 digits. Do not use
dashes or spaces.
Additional FDA Application
Supplement Number #2
The application
supplement number
This field is limited to 4
characters. Use the format
Page 15 of 19
assigned by the Food and
Drug Administration
(FDA).
XXXX for the supplemental
application number.
FDA Final Pre-Marketing
Approval Date
This is the original date
the FDA granted
approval for the drug
(NDA), biological
(BLA), or pre-marketing
application (PMA).
Value should be in the date
format (MM/DD/YYYY).
If there is no approval date,
baseline date should be set
to 01/01/1965.
FDA Approval Type
The type of FDA
approval for the product.
Choose a value from the
drop down menu (ANDA,
NDA, 510K, BLA, PMA,
Human Tissue, Vaccine,
Other).
If Other, specify the type in
the column „Description of
FDA Approval Type‟.
Description of FDA Approval Type
If Other was specified in
the column „FDA
Approval Type,‟ please
specify the type.
Free form field limited to
255 alphanumeric
characters.
Descriptive Data Corrected
To indicate that a data
element other than a
manufacturer‟s ASP or
number of ASP units has
changed since the last
report.
Free form field limited to
255 alphanumeric
characters. Describe which
data element(s) have been
corrected.
Page 16 of 19
4 TEMPLATE REQUIREMENTS
1. To use this template, a user must have the ability to enable and execute MS Excel-based Visual Basics
for Applications (VBA) Macros.
If the user is not able to accept macros due to constraints in corporate security policies, CMS
should be notified in writing of the security limitations in the user‟s cover letter.
2. Users shall not add additional columns to the template.
3. Users shall not add, remove or otherwise change columns or column headings within the template.
4. Users shall not submit blank rows between data entries. All data must be submitted in contiguous rows.
5. Users shall not create multiple rows for one NDC or Alternative ID.
4 ACRONYMS
ASP
Average Sale Price
CMS
Centers for Medicare & Medicaid Services
CM
Center for Medicare
HAPG
Hospital and Ambulatory Policy Group
NDC
National Drug Code
NHRIC
National Health Related Items Code
MS
Microsoft
VBA
Visual Basics for Applications
WAC
Wholesale Acquisition Cost
FR
Federal Register
IU
International Units
ML
Milliliter
MG
Milligram
CAP
Competitive Acquisition Program
FDA
Food and Drug Administration
NDA
New Drug Application
BLA
Biologics License Application
PMA
Pre Marketing Approval
ANDA
Abbreviated New Drug Application