Form CMS-10320 Issuer - Instructions and Instruments

OMB Control Number: 0938-1086

Health Insurance Oversight System (HIOS) Excel
Data Submission Tool - Issuer
Technical Instructions

Last updated April 28, 2010

PRA Disclosure Statement
According to the Paperwork Reduction Act of 1995, no persons are required to respond to a collection of
information unless it displays a valid OMB control number. The valid OMB control number for this information
collection is 0938-1086. The time required to complete this information collection is estimated to average ( 30.32
hours) or (1,819.2 minutes) per response, including the time to review instructions, search existing data resources,
gather the data needed, and complete and review the information collection. If you have comments concerning the
accuracy of the time estimate(s) or suggestions for improving this form, please write to: CMS, 7500 Security
Boulevard, Attn: PRA Reports Clearance Officer, Mail Stop C4-26-05, Baltimore, Maryland 21244-1850.

OMB Control Number: 0938-1086

Table of Contents
1

Introduction ........................................................................................................................2

2

Issuer Data Entry Form Functions ......................................................................................3
2.1

Installation ....................................................................................................................3

2.2

Opening the Issuer Data Entry Form ............................................................................3

2.3

Circle Invalid .................................................................................................................4

2.4

Finalizing the Issuer Data Entry Form for submission ...................................................6

2.5

Closing the file ..............................................................................................................6

2.6

Password Protection .....................................................................................................7

3

Troubleshooting and FAQ ..................................................................................................8
3.1

FAQ’s ...........................................................................................................................8

3.2

Support .........................................................................................................................8

4

Appendix A— Critical Errors ...............................................................................................9
4.1

5

Issuer Data Critical Errors and Related Error Messages ...............................................9
Appendix B—Required Fields...........................................................................................14

1

OMB Control Number: 0938-1086

1

Introduction

The Health Insurance Oversight System (HIOS) functionality will allow the government to
collect data from states, and individual and small group market issuers, which will be aggregated
with other data sources and made public on a consumer facing website. One mechanism for the
issuers to submit their data is through the use of the HIOS Excel template.
These technical instructions explain the special features and other technical aspects related to
using the HIOS Issuer Data Submission Excel Tool. The Issuer Data Submission Excel Tool
consists of three worksheets that must be completed prior to submission:
•

Issuer General Info: Supports the data entry for corporate information, corporate address,
contact information, and ratings.

•

Product Info: Supports the data entry for all product offerings on behalf of the issuers.
The data to be entered includes Product Name, Enrollment Number, Product Type,
Product Enrollment, Individual or Small Group, Website Address, Geographic Coverage
and whether the product has been opted out of Phase 2.

•

Geographic Coverage: Supports the data entry for specific zip codes when a product is
only offered in a subset of the state.

DHHS recommends that users read this document thoroughly before using the tool.
The Issuer Data Submission File employs two versions of the workbook that serve different
purposes throughout the process:
•

Working files – are read-write enabled files that allow users to enter data in specified
input fields. Users may edit, save, name, and re-name working versions of these files.

•

Finalized files – are read-only files created by a process called finalization, which
modifies the format of working files to prepare them for submission to HHS. Finalization
converts formula results in calculated fields into hard-coded values, removes formulas
and other extraneous elements, and saves the file using a standard naming convention.
All macro file functionalities are disabled. The finalized save process will delete or
standardize some of the data in the finalized file according to specified business rules.
The finalized save process will not change the data in the working file. (See Section
2.4.1.)

2

OMB Control Number: 0938-1086

2

Issuer Data Entry Form Functions

2.1

Installation

2.1.1

Configuration Requirement

For the Issuer Data Entry Form to work properly, you must configure your PC to satisfy the
following requirements:
•

Have Microsoft Excel 2003 or greater installed.

•

Enable your Excel Standard Toolbar.

•

For Excel 2003, set your Excel Macro Security Settings to Medium (recommended). You
can do this by selecting Tools, Macro, and the Security command from your Excel menu
bar.

•

For Excel 2007, set your Excel Macro Security Settings to “Disable all macros with
notifications.” You can do this by selecting the Office Button, Excel Options, Trust
Center, Trust Center Settings, Macro Settings.

•

Enable macros when you open the Issuer data Collection File workbook.

2.2
2.2.1

Opening the Issuer Data Entry Form
Macro Security Level Setting

For Excel 2003
The Issuer Data Entry form uses macros to perform the built-in functions including the validation
and finalization processes.
For security purposes, it is highly recommended that the Macro Security Level Setting be set at
Medium. You can do so by selecting Tools, Macro, and the Security command from the Excel
menu bar. You will not be able to open the Issuer Data Entry Form if your macro security
settings are set to High or Very High.
If your Macro Security setting is set to Medium, you will be prompted to enable or disable
macros when you open the workbook. You must choose “Enable macros” to correctly open the
form. If you choose “Disable macros,” the workbook will display a screen stating that the macros
must be enabled in order to use the form. You will have to close and then reopen the workbook
to enable macros.
For Excel 2007
The Issuer Data Entry form is supported in Excel 2007. You will be able to open and work with
the HIOS files using Excel 2007’s compatibility mode. However, you MUST save and/or
finalize the HIOS files in the Excel 2003 (.xls) format once you are finished. You will only be
able to submit an “.xls” HIOS file.

3

OMB Control Number: 0938-1086

Note: If a HIOS file is saved in the Excel 2007 (.xlsm) format, an error will occur when you
attempt to reopen the file. Please do not save the HIOS file in .xlsm format. This applies to
working files as well.
2.2.2

Commands

Upon successfully opening a working Issuer Data Entry Form with the macros enabled, specific
buttons will appear in the worksheet 1.
Button names (from left to right):
•

Validate (Shortcut: CTRL + shift + V) – this will perform the red circle and critical
validations.

•

Validate and Finalize (Shortcut: CTRL + shift + S) - this function will perform the
critical validations and create the read-only finalized file.

•

Format and Print Preview (Shortcut: CTRL + shift + P) – this function will format the
workbook for printing and bring to focus the print preview screen for the user.

2.2.3

Validations

Data entry fields are highlighted in green. All cells are formatted is in Text format.
It is recommended that you not use Excel’s “Cut” function in the Issuer Data Entry Form
workbook as it will remove the cell’s predefined formatting.
Note: You should use the Delete key instead of the Space bar to delete cell values from a cell.
2.2.4

Critical Validations

The green-highlighted data entry fields fall into three categories with respect to cell validation:
validation fields, critical validation fields, and non-validation fields. Validation fields have cellspecific rules regarding the types and format of data that can be entered into them. These rules
appear in message boxes called cell labels which are shown when the cell is highlighted.
2.3

Circle Invalid

Upon triggering the Validation process, a red circle will surround cells that do not pass validation
rules. Once the validation rules are corrected the Circle Invalid will disappear.
2.3.1

Printing the file

You must use the standard excel Print function to print the Issuer Data Entry Form.
2.3.2

Save Functions

There are two save processes available within the Issuer Data Entry form: a non-finalized and a
finalized save.

4

OMB Control Number: 0938-1086

A non-finalized save can be invoked by clicking on the Excel Save icon on the Excel Standard
Toolbar or by selecting File and then Save from the Excel menu. This save process will save any
changes you have made to the workbook.
A finalized save occurs when the “Finalize and Validate” function is invoked. (See Section 2.4,
“Finalizing the Issuer Data Entry Form for Submission.”) This step is taken when the workbook
is complete and ready for submission.
Note: after clicking “Save” or “Finalize and Validate Form,” the following message may appear:
:

5

OMB Control Number: 0938-1086

Uncheck the box “Check compatibility when saving this workbook” and click “Continue.” The
popup will not appear next time you save a form.
2.4

Finalizing the Issuer Data Entry Form for submission

To trigger the finalization process, click on the Validate and Finalize File button. The system
will perform all of the critical validations. The system will perform the finalization process and
create the read only file if all critical validations are passed.
2.4.1

Finalization Process

When the finalization function is triggered, the system will perform the following actions:
•

Check the required fields— Pre-determined fields (e.g., “State”) must be entered for the
finalization to be successful; these fields are listed in Appendix B (“Required Fields”).

•

Check the critical validations—Critical validation fields must comply with their
validation rules for the finalization process to be successful; the fields and business rules
are listed in Appendix A (“Critical Validation Fields”). All critical errors must be
corrected for a file to be successfully finalized.

•

Create a finalized file with the following features:
ο All worksheets will display as read only in the finalized file.
ο Red circles will display for cells that do not pass validation rules. Note: You will
be able to finalize the file if the red circled cell is not one of the critical errors. A
list of critical validations for is included in Appendix A, “Critical Errors.”

2.4.2

File Naming Convention

Finalized Issuer Data Entry Submission workbooks are saved using the following standardized
naming convention:
Final____.xls
Use of this naming convention is a requirement for a successful submission.
Example: Final_BlueCross_VA_20100427130426_Issuer Data Entry Form.xls
Note: Finalized files will be saved in the same directory where the working file is located.
The working file name can be changed at any time. The finalized forms are read-only files. If
you need to make additional changes prior to submission, modify the working file and finalize
the file again.
2.5

Closing the file

When closing the file it is highly recommended that you save all current changes.

6

OMB Control Number: 0938-1086

2.6

Password Protection

The Issuer Data Entry form is password protected. You may not modify the structure of the
workbook or worksheets. Each data item must be located in its pre-defined cell location for
successful processing.

7

OMB Control Number: 0938-1086

3

Troubleshooting and FAQ

3.1

FAQ’s

Question 1: I am trying to open Issuer Data Entry files in Excel 2007, but I do not see the Option
button. How do I enable contents?
1.

Click the Microsoft Office Button

2.

Click Trust Center, click Trust Center Settings, and then click Message Bar.

3.

Click the first radio button below:
•

•
3.2

, and then click Excel Options.

Show the Message Bar in all applications when document content has been blocked. This
option is selected by default so that you get Message Bar alerts whenever potentially
unsafe content has been disabled. The option is not selected if you clicked the Disable all
macros without notification option on the Macros pane of the Trust Center. If you click
Disable all macros without notification, you won't get Message Bar alerts when macros
are disabled.
Never show information about blocked content. this option disables the Message Bar.
Support

HIOS Help
If you need additional assistance, please call the HIOS Help Desk at 1-877-343-6507 or email
them at at insuranceoversight@hhs.gov.
.

8

OMB Control Number: 0938-1086

4

Appendix A— Critical Errors

4.1

Issuer Data Critical Errors and Related Error Messages

These errors will prevent successful finalization.
1. Issuer General Info, C3, Issuer Name
a. Display error message if cell is blank.
b. Error message: An issuer name must be entered in Worksheet 1 C3
2. Issuer General Info, C4, Federal EIN
a. Display error message if cell is blank.
b. Error message: A federal EIN number must be entered in Worksheet 1 C4
3. Issuer General Info, C8, Address Line 1
a. Display error message if cell is blank.
b. Error message: An address must be entered in Worksheet 1 C8
4. Issuer General Info, C10, Issuer City
a. Display error message if cell is blank.
b. Error message: A city name less than 50 characters must be entered in worksheet
1 C10
5. Issuer General Info, C11, Issuer state
a. Display error message if the value entered is not one of the States in the list.
b. Error message: A State must be selected from the drop down menu in Worksheet
1 C11
6. Issuer General Info, C12, Issuer Zipcode
a. Display error message if cell is blank.
b. Error message: A Zip Code must be entered in Worksheet 1 C12
7. Issuer General Info, C15, Individual Market
a. Display error message if the value entered is not one of the options in the drop
down.
b. Error Message: A value must be selected from the drop down menu in Worksheet
1 C15.
8. Issuer General Info, C16, Website for Individual Market
a. Display error message if this cell is blank and Yes is entered in C15.
b. Error Message: A valid Website must be entered in Worksheet 1 C16 if individual
market is offered.
9. Issuer General Info, C17, Small Group Market
a. Display error message if the value entered is not one of the options in the drop
down
b. Error Message: A value must be selected from the drop down menu in Worksheet
1 C17.
10. Issuer General Info, C18, Website for Small group market
a. Display error message if this cell is blank and Yes is entered in C17.
b. Error Message:A valid Website must be entered in Worksheet 1 C18 if Small
Group is offered.
11. Issuer General Info, C21, Customer Service Local number
a. Display error message if cell is blank.

9

OMB Control Number: 0938-1086

b. Error message: A Customer Service Local Number must be entered in Worksheet
1 C21
12. Issuer General Info, C24, Customer Service Website Address
a. Display error message if cell is blank.
b. Error Message:A valid Website must be entered in Worksheet 1 C24
13. Issuer General Info,C28, Data Submission/Individual Market Primary Contact Name
a. Display error message if cell is blank.
b. Error message: A contact name must be entered in Worksheet 1 C28
14. Issuer General Info, C29, Data Submission/Individual Market Primary Contact Phone
Number
a. Display error message if cell is blank.
b. Error message: A Phone Number must be entered in Worksheet 1 C29
15. Issuer General Info, C30, Data Submission/Individual Market Primary Contact email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C30
16. Issuer General Info, C39, Issuer Data Submission Validation/Individual Market Primary
Contact Name
a. Display error message if cell is blank.
b. Error message: A Issuer Data Submission Validation contact name must be
entered in Worksheet 1 C39
17. Issuer General Info,C40, Issuer Data Submission Validation/Individual Market Primary
Phone Number
a. Display error message if cell is blank.
b. Error message: A Phone Number must be entered in Worksheet 1 C41
18. Issuer General Info,C41, Issuer Data Submission Validation/Individual Market Primary
email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C42
19. Issuer General Info,C50, Data Submission/Small Group Market Primary Contact Name
a. Display error message if cell is blank.
b. Error message: A contact name must be entered in Worksheet 1 C50
20. Issuer General Info, C51, Data Submission/Small Group Market Primary Phone Number
a. Display error message if cell is blank
b. Error message: A Phone Number must be entered in Worksheet 1 C51
21. Issuer General Info, C52, Data Submission/Small Group Market Primary Email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C52
22. Issuer General Info, C61,Issuer Data Submission Validation Contact/Small Group Market
Primary Contact Name
a. Display error message if cell is blank.
b. Error message: A contact name must be entered in Worksheet 1 C61
23. Issuer General Info,C62, Issuer Data Submission Validation Contact/Small Group Market
Primary Phone Number
a. Display error message if cell is blank.
b. Error message: A Phone Number must be entered in Worksheet 1 C62

10

OMB Control Number: 0938-1086

24. Issuer General Info, C63, Issuer Data Submission Validation Contact/Small Group
Market Primary Email
a. Display error message if cell is blank.
b. Error message: A valid email address must be entered in Worksheet 1 C63
25. Issuer General Info, C71,Issuer Rating for Individual Market
a. Display error message if the value entered is not one of the options in the drop
down.
b. Error Message: Yes or No must be selected from the drop down menu in
Worksheet 1 C71.
26. Issuer General Info, B73, Rating Company
a. Display error message if this field is blank, and Yes is entered in W3 C71.
b. Error message: A rating company must be entered in Worksheet 1 B73 if the
company has been rated in the past 2 years.
27. Issuer General Info,C73, Rating type
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B73.
b. Error message: A rating type must be entered in Worksheet 1 C73 if Rating
company is entered.
28. Issuer General Info, D73, Rating
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B73.
b. Error message: A rating must be entered in Worksheet 1 D73 if Rating company
is entered in W3 B73.
29. Issuer General Info, C80,Issuer Rating for Small Group Market
a. Display error message if the value entered is not one of the options in the drop
down.
b. Error Message: Yes or No must be selected from the drop down menu in
Worksheet 1 C80.
30. Issuer General Info,B82, Rating Company
a. Display error message if this field is blank, and Yes is entered in Worksheet 1
C80.
b. Error message: A rating company must be entered in Worksheet 1 B82 if the
company has been rated in the past 2 years.
31. Issuer General Info, C82, Rating type
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B82.
b. Error message: A rating type must be entered in Worksheet 1 C82 if Rating
company is entered.
32. Issuer General Info, D82, Rating
a. Display error message if this field is blank and Rating company is entered in
Worksheet 1 B80.
b. Error message: A rating must be entered in Worksheet 1 D73 if Rating company
is entered in W3 D82.
33. Issuer General Info, E3, State
a. Display error message if this field is blank.
b. Error message: A State must be seleted on Worksheet 1 E3.

11

OMB Control Number: 0938-1086

34. Issuer General Info, E4, Market Coverage
a. Display error message if this field is blank.
b. Error message: A Market coverage value must be seleted on Worksheet 1 E4.
35. Product Info,C3,Product Name
a. Display error message if cell is blank
b. Error message: A Product Name must be entered in Worksheet 2 C3.
36. Product Info,E3, Product Type
a. Display error message if the this field is blank.
a. Error Message: A value must be entered in Worksheet 2 E3.
37. Product Info, F3, Other Product Type description
a. Display error message if this field is blank and Other is selected in cell E3
Product Type.
b. Error Message: Other Product Type description is required if Other produdct type
is selected.
38. Product Info,G3, Product Enrollment
a. Display error message if cell is blank
b. Error message: Product Enrollment must be entered in Worksheet 2 F3.
39. Product Info,H3, Individual/small Group
a. Display error message if blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
2 G3.
40. Product Info,J3, Website Address Benefit
a. Display error message if cell is blank.
b. Error message: Product information worksheet Website Address Benefit in
Worksheet 2 H3 is required.
41. Product Info,K3, Website Address Provider Network
a. Display error message if cell is blank
b. Error message: Product Information worksheet Website Address for Provider
Network in Worksheet 2 J3 is required.
42. Product Info,L3, Covers Whole State
a. Display error message if this field is blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
2 K3.
43. Product Info,M3, Short Term Limited
a. Display error message if this field is blank.
b. Error Message: A value must be selected from the drop down menu in Worksheet
2 L3.
44. Geographic Coverage,B Column , Cross Reference
a. Display error message if a Product does not cover a whole state, there must be a
matching Cross Reference number found in this column for that Product.
b. Error message: A matching cross reference must be entered in Worksheet 3 B3 a
product does not cover whole State.
45. Geographic Coverage,C column, Zip Code
a. Display error message if a Cross Reference number is entered, this field is
required.

12

OMB Control Number: 0938-1086

b. Error message: A Zip code must be entered in Worksheet 3 C3 if a cross reference
number is entered in column B.

13

OMB Control Number: 0938-1086

5

Appendix B—Required Fields
•

State Issuer & Product Info: Issuer Name, Individual/Small Group, Submission Contact
Name, Submission Contact Phone Number, Submission contact Email, Fee-for-Service,
HMO, PPO, EPO, POS, Other

•

State General Info: General Info Box, Primary contact name, phone number, and email.
Validation Contact name, phone number, and email

•

Issuer General Info: Issuer name, Federal EIN, state, Market Coverage, Address line,
City, Zip, Local number, Website address, email, Name, Phone number, Is issuer rated,
rating company, rating type, rating.

•

Product Info: Product name, Product type, Product enrollment, Individual or small group,
Website address, Covers whole state, Phase 2.

•

Geographic Coverage: Cross Reference and Zip code.

14

OMB Control Number: 0938-1086

PRA Disclosure Statement

D

ra
f

t

According to the Paperwork Reduction Act of 1995, no persons are required to respond to
a collection of information unless it displays a valid OMB control number. The valid OMB
control number for this information collection is 0938-1086. The time required to complete
this information collection is estimated to average ( 30.32 hours) or (1,819.2 minutes) per
response, including the time to review instructions, search existing data resources, gather
the data needed, and complete and review the information collection. If you have
comments concerning the accuracy of the time estimate(s) or suggestions for improving
this form, please write to: CMS, 7500 Security Boulevard, Attn: PRA Reports Clearance
Officer, Mail Stop C4-26-05, Baltimore, Maryland 21244-1850.

OMB Control Number: 0938-1086

Worksheet 1 - Issuer General Information
1. Corporate Information
Issuer Name:

State

Federal EIN:

Market Coverage

NAIC Company Code

NAIC Group Code

2. Address
Address Line 1:
Address Line 2:
City:
State:
4 digit

t

Zip:

1). Do you offer Individual market?
2). Enter the web address for the Individual
Market website if Yes is entered in question
above.
3). Do you offer Small Group market?
4). Enter the web address for the Individua
Market website if Yes is entered in question
above.

ra
f

3. Individual and Small Group Market

Local Number:
Toll Free Number
TTY:
Website Address

D

4. Customer Service (Individual Market) or Employee Group (Small Group Market) Contact

Extension:

5. Data Submission Contact - Individual Market
Primary Contact
Name:
Phone Number:
Email:
Backup Contact
Name:

Extension:

Issuer ID

OMB Control Number: 0938-1086

Phone Number:

Extension:

Email:
6. Issuer Data Submission Validation Contact - Individual Market
Primary Contact
Name:
Phone Number:

Extension:

Email:
Backup Contact
Name:
Phone Number:

Extension:

t

Email:

ra
f

7. Data Submission Contact - Small Group Market
Primary Contact
Name:
Phone Number:

Extension:

Email:
Backup Contact

Extension:

D

Name:
Phone Number:
Email:

8. Issuer Data Submission Validation Contact - Small Group Market
Primary Contact
Name:
Phone Number:
Email:

Extension:

Backup Contact
Name:
Phone Number:
Email:
9. Ratings- Individual Market
Is Issuer rated by any rating company

Extension:

OMB Control Number: 0938-1086

Rating Type

Rating

10. Ratings- Small Group Market
Is Issuer rated by any rating company
Rating Company (rated in the past 2 years

Rating Type

Rating

D

ra
f

t

Rating Company (rated in the past 2 years

OMB Control Number: 0938-1086

Worksheet 2 - Product Information

Product
Enrollment

Website
Address
(Benefit at a
Glance)

Website
Address
(Formulary)

t

Product
Name

Individual
or Small
Group

ra
f

Product
ID

Other Product
Product
Type
Type
Description

D

Cross
Reference
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

Enrollment
Code/Group
Number

Website Address
(Provider Network)

Covers
Whole
State?

Opt-out of
Phase 2?
No

OMB Control Number: 0938-1086

Worksheet 3 - Geographic Coverage (Required if product does not cover whole State)
Zip Code

D

ra
ft

Cross Reference