FFM Plan Management Copy and Paste in Templates Guide

Centers for Medicare & Medicaid Services

Federally Facilitated Marketplace

Contract HHSM-500-2016-00003I / 75FCMC21F0001

FFE Plan Management

Copy & Paste in Templates Guide

Version 10.0

February 2022

CMS FFM Plan Management Copy and Paste in Templates Guide

Table of Contents

1 Copying and Pasting in Plan Management Templates ............................................................1

1.1 General Copy/Paste Recommendations ........................................................................1

1.2 Whole Template Copy/Paste Recommendations ..........................................................1

1.2.1 Column Level Copy/Paste Recommendations ...................................................1

1.2.2 Row Level Copy/Paste Recommendations ........................................................1

1.2.3 Cell Level Copy/Paste Recommendations .........................................................1

2 Network ID Template ............................................................................................................3

3 Prescription Drug Template ...................................................................................................4

3.1 Formulary Tiers Tab ....................................................................................................4

3.2 Drug List Tab ..............................................................................................................4

4 Service Area Template ..........................................................................................................5

5 Rate Tables Template ............................................................................................................6

5.1 Issuers in Individual Rated States.................................................................................6

5.2 Issuers in Family Tiered States ....................................................................................7

5.3 Issuers Submitting Medical and Dental Rates Combined .............................................8

6 Rating Business Rules Template .......................................................................................... 12

7 Plans & Benefits Template .................................................................................................. 14

7.1 Benefits Package Sheet .............................................................................................. 14

7.2 Cost Share Variances Sheet ....................................................................................... 14

8 ECP / Network Adequacy Template .................................................................................... 18

8.1 Facility ECPs Tab ...................................................................................................... 18

8.2 IndividualProviders(NA) Tab .................................................................................... 19

8.3 FacilityProviders(NA) Tab ........................................................................................ 19

9 Transparency in Coverage Template .................................................................................... 19

9.1 Issuer Level Data Tab ................................................................................................ 19

9.2 Plan Level Data Tab .................................................................................................. 20

List of Figures

Figure 1: Network Template .........................................................................................................3

Figure 2: Prescription Drug Template – Formulary Tiers Worksheet ...............................................4

Figure 3: Service Area Template ...................................................................................................5

Figure 4: Rate Tables Template with Age Bands and No Preference Tobacco Option .......................6

Figure 5: Rate Tables Template with Age Bands and Tobacco/Non-Tobacco Option ........................7

Figure 6: Rate Tables Template – Family Tiering Columns .............................................................7

Figure 7: Rate Tables Template – Individual Rate and Couple Fields ...............................................7

Figure 8: Rating Business Rules Template – Relationship to Primary Pop-up ................................. 12

Figure 9: Rating Business Rules Template – Relationship to Primary Pop-Up for Medical Plans with

“Child, Stepson or Stepdaughter, and Foster Child” selected

......................................................... 13

Figure 10: Plan & Benefits Template – Plan Level Data ............................................................... 14

Figure 11: Plan & Benefits Template – Bottom EHB Table........................................................... 14

Figure 12: Sample Excel Error Message ...................................................................................... 15

Figure 13: Cost Share Variance Sheet – Benefits Template Sample Columns ................................. 15

Figure 14: Subsection of Benefits Template ................................................................................. 15

Figure 15: Benefits Template Deductible Columns Selected for Copy ........................................... 16

CMS FFM Plan Management Copy and Paste in Templates Guide

Figure 16: Benefits Template Deductible Columns After Paste Values .......................................... 16

Figure 17: Benefits Template Tier 2 Benefit Columns .................................................................. 16

Figure 18: Facility ECPs Tab ...................................................................................................... 18

Figure 19: IndividualProviders(NA) Tab ...................................................................................... 19

Figure 20: FacilityProviders(NA) Tab ......................................................................................... 19

Figure 21: Transparency in Coverage Template – Issuer Level Data .............................................. 20

Figure 22: Transparency in Coverage Template – Plan Level Data ................................................ 20

Guide to Combine Rates

Combining Rates Step 1................................................................................................................8

Combining Rates Step 2................................................................................................................8

Combining Rates Step 3................................................................................................................9

Combining Rates Step 4................................................................................................................9

Combining Rates Step 5.............................................................................................................. 10

Combining Rates Step 6.............................................................................................................. 10

Combining Rates Step 7.............................................................................................................. 11

Combining Rates Step 8.............................................................................................................. 11

CMS FFM Plan Management Copy and Paste in Templates Guide

1 Copying and Pasting in Plan Management Templates

Copy/Paste is permitted in all Plan Management templates required as part of the Qualified Health

Plan (QHP) submission process. To ensure that users do not run into issues while preparing data for

templates, there are a few key points to keep in mind when pasting data into macro-enabled

templates.

1.1 General Copy/Paste Recommendations

Excel Pasting and Cell Validation: In Excel, pasting into templates causes cell-level validations to

be overwritten, which could lead to validation errors. The Data Traceability Matrix of each QHP

module documents the formats, allowable values, and required elements for all of the templates.

Recommendation: Ensure that the data you are pasting is the correct type of data for the cell. For

example, if the template only accepts a text value, ensure the source data is text and not numeric.

Errors that normally would be caught on manual entry will be checked when the template is

validated. If possible, use the Paste/Paste Special, and select the Values radio button.

Note: If you have a numerical identifier with a leading zero, it is permissible to copy it as text and

paste it into a numerical field. Excel automatically drops leading zeroes unless a field is specifically

formatted. For example, if you have a TIN that starts with a zero, you may copy the number

(formatted as text to keep the leading zero) and paste it into the template. Leading zeroes will not

create validation errors.

1.2 Whole Template Copy/Paste Recommendations

Column headers of all the templates are locked: You cannot select an entire template, with headers,

and paste into a new one. In some templates, there may be hidden columns that block you from

pasting an entire row as in the Ratings template.

Recommendations: Select only the fields with user-entered data to copy and paste. If you receive an

Excel error that you are trying to paste into a protected cell, look at the specific template guidelines

in this document.

1.2.1 Column Level Copy/Paste Recommendations

Only copy user-entered data, and do not include the header columns or any gray-filled cells. There

are new drop downs for all benefits, and users must confirm that the values being pasted are correct.

Columns may not be deleted, but you can adjust the width of a column to better view data.

1.2.2 Row Level Copy/Paste Recommendations

Only copy user-entered data, and do not include any green label fields from a row. Entire rows can

be deleted in most templates, but you cannot delete a row that has a locked cell or header.

1.2.3 Cell Level Copy/Paste Recommendations

In most templates, locked cells are gray, and users cannot paste into green header cells.

For any cells that are drop-downs, make sure you are pasting one of the options listed in the

CMS FFM Plan Management Copy and Paste in Templates Guide

drop-down; otherwise, validation errors will occur. The Validate macro of all templates is not case

sensitive in regards to the drop-down values. However, some templates have automated functions

that will not trigger if you paste values, or will not trigger properly if the wrong case is used. For best

results, use the case that is given in the templates or Data Traceability Matrix.

Recommendation: For all unlocked cells, use the Paste Special > Values button.

CMS FFM Plan Management Copy and Paste in Templates Guide

2 Network ID Template

This template is a simple table and has no locked cells other than template headers and instructions.

You may copy/paste into any of the data-entry cells but be sure to paste the correct type of data.

Check the Data Traceability Matrix for specific data formats.

NOTE: Make sure to select information starting from the row under the headers. Otherwise, you will

be pasting into locked cells and will receive an Excel error. (Copy from the place of the highlighted

cells in Figure below.)

Figure 1: Network Template

CMS FFM Plan Management Copy and Paste in Templates Guide

3 Prescription Drug Template

The Prescription Drug Template has two types of tabs: the Formulary Sheet and the Drug List

Sheet.

3.1 Formulary Tiers Tab

In the Formulary Tiers tab (see Figure below), each formulary is designed to always take up 7 rows.

Unused tiers in the formulary are gray-filled and locked based on the “Number of Tiers” column. It is

recommended to first create Formulary IDs; then select a Formulary ID from the drop-down list; then

enter the URL; then select the drug list; and then number of tiers from the drop-down. This will lock the

remaining cells for which input is not required. You can paste data into any cells that are not gray.

NOTE: DO NOT copy and paste column E.

NOTE: It is highly UNADVISIBLE to copy columns F-P due to the removal of the Formulary URL

column.

NOTE: “Not Applicable” has been included as a drop-down option version 5+ of the Prescription

Drug Template. This is different from “No Charge.” It is highly recommended that you review your

data to verify that you have accurately chosen between “No Charge” and “Not Applicable.”

Figure 2: Prescription Drug Template – Formulary Tiers Worksheet

3.2 Drug List Tab

When working with the Drug List tab, you may paste a list of RxCUIs into column A from your

previous template. You may also paste the Prior Authorization Required Field (column C) and

Step Therapy Required field (column D).

There can be no duplicate RxCUIs: Duplicate RxCUIs will cause an error message announcing the

cell location of the duplicate. Validation and Finalization will check that the tier you have selected

for a drug exists in the formularies that reference the drug.

For example, if your first formulary uses Drug List 1 and has 5 tiers, you cannot mark any RxCUIs in that

drug list as tier 6 or 7. You can select them as “NA” if those drugs are used in a different Drug List.

In order to avoid producing a validation error when pasting Tier Levels (column B) from an older

template, paste the Tier Levels using Match Destination Formatting. This is done by copying the

Tier Level cells and then selecting the Match Destination Formatting under Paste Options, or by

manually selecting the correct level from the dropdown for each row.

CMS FFM Plan Management Copy and Paste in Templates Guide

4 Service Area Template

The Service Area template, shown in Figure below, will lock and gray-fill cells based on options

selected in the columns. There also are hidden columns that only display if you have a service area

that covers a partial county.

If a cell is gray, you will not be able to paste into it. Please make sure, if you are pasting in a long list

of Zip Codes for the Service Area Zip Code(s) column, that they are separated by a comma and a

space. You only need to list Zip Codes if the service area covers a partial county (Partial County =

YES). If Partial County = NO, the Service Area Zip Code(s) column is gray and locked, so nothing

can be pasted because Zip Codes are not needed for service areas covering entire counties.

Note: Zip & FIPS commonly change throughout the year. Please confirm that your Zip-codes &

FIPS-codes are accurate and have not changed from previous years and the most recent

version of the Service Area template is being used.

Figure 3: Service Area Template

CMS FFM Plan Management Copy and Paste in Templates Guide

5 Rate Tables Template

The Rate Tables template is designed to support issuers submitting for both Individual Rated and

Family Tiering States.

5.1 Issuers in Individual Rated States

Rate information may be copied and pasted into Rate Tables using the recommendations previously

mentioned.

Note: The Rate Tables template is designed with significant automation to help users fill in valid

information. For example, the macros automatically fill in the rows for Plan ID, Rating Area ID, and

Tobacco columns after filling in the first row.

Scenario 1: If you fill in your Plan ID on the first row, the template will auto populate all the

required age bands 0-14 to 64 and over (see Figure below). In this scenario, the only cells that can be

pasted into are the Individual Rate column cells. While the Age column is not grayed or locked, the

age bands are auto-populated and should not be edited.

Figure 4: Rate Tables Template with Age Bands and No Preference Tobacco Option

Scenario 2: If you select “Tobacco Use/Non-Tobacco Use,” the Individual Tobacco Rate column

will unhide and show the Rate Tables template with separate rates for non-tobacco and tobacco users.

In this instance, the cells you will be able to paste into are the Individual Rate and Individual

Tobacco Rate (see Figure below).

Note: Beginning in PY2022 Age Bands 0-20 on “Individual Tobacco Rate” will need to be the same

Rate as Column E (Individual Rate).

CMS FFM Plan Management Copy and Paste in Templates Guide

Figure 5: Rate Tables Template with Age Bands and Tobacco/Non-Tobacco Option

5.2 Issuers in Family Tiered States

You may copy into selected cells for plans in states with Family Tiering. If you select “Family-Tier

Rates” option from the dropdown in the Rating Method row, the template will unhide the family

tiers column. Tobacco must be “No Preference” in this case, so the Individual Tobacco Rate

column will be hidden. Figure below shows the Rate Tables template with Family Tiering columns

displayed.

Figure 6: Rate Tables Template – Family Tiering Columns

In this scenario, because there is a hidden column, you will not be able to paste into the whole row

all at once. You can still paste rates into the Individual Rate column (Column E) and in all the

family tier columns (Columns G-M), but not in these two adjacent cells at once (see Figure below).

Figure 7: Rate Tables Template – Individual Rate and Couple Fields

CMS FFM Plan Management Copy and Paste in Templates Guide

5.3 Issuers Submitting Medical and Dental Rates Combined

The guide below shows how an Issuer can combine two Rates templates (Medical and Dental) into

one Rates template. Beginning in PY2022, Issuers submitting Medical and Dental plans will need to

combine Medical and Dental Rates into one template.

1. Open both templates (Medical and Dental).

Combining Rates Step 1

2. Right click on the tab you want to move/copy and click “Move or Copy”.

Combining Rates Step 2

3. A pop up will show up and the dropdown will show the two Rates templates you have open.

In this example I am moving the Dental rates on the left template to the Medical Rates

template on the right.

CMS FFM Plan Management Copy and Paste in Templates Guide

• In this case I want to click on the “Medical Rates” template on the dropdown, since I

am moving the Dental Rates to the Medical Rates template.

Combining Rates Step 3

4. Next click on “Create a copy” checkbox and select “(move to end)”.

Combining Rates Step 4

CMS FFM Plan Management Copy and Paste in Templates Guide

5. Notice that the tab that you clicked on to move, gets copied over from the left template to the

template on the right (in this case the Medical Rates template) and gets automatically

renumbered to “Rate Table (4)”.

Combining Rates Step 5

6. Continue this process until you have them all moved to the desired template.

Combining Rates Step 6

CMS FFM Plan Management Copy and Paste in Templates Guide

7. Save the template with all the data in it, and now you can successfully “Validate and

Finalize” the template. Medical Rates and Dental Rates will now be combined.

Combining Rates Step 7

Combining Rates Step 8

CMS FFM Plan Management Copy and Paste in Templates Guide

6 Rating Business Rules Template

The Rating Business Rules template is a simple table (see Figure below). The first row is considered

the Issuer rule, and the Plan and Product ID columns are therefore grayed and locked. Data must be

entered in every column in the first row (except for Plan/Product ID). For subsequent rows, you may

paste in the Plan OR Product IDs and any columns that have different rules from the first Issuer rule.

The last column, “What relationships between primary and dependent are allowed, and is the

dependent required to live in the same household as the primary subscriber?” is a large pop-up. It is

advisable to always use the pop-up for this column because of the complexity of the data elements.

It is not necessary to enter the same rules repeatedly; for example, if Plans within a Product have the

same answer to this column, then the plan rules can be left blank. If you are copying data from an

older version of the template, you may copy and paste this cell.

Note: Do not copy and paste into this cell if “Child, Stepson or Stepdaughter, and Foster Child” are

set to “Yes” for a Medical plan, in the older version of the template. Beginning in PY2022 “Child,

Stepson or Stepdaughter, and Foster Child” are automatically defaulted to “No” if the plan is Medical

(Refer to Figure below).

Figure 8: Rating Business Rules Template – Relationship to Primary Pop-up

CMS FFM Plan Management Copy and Paste in Templates Guide

Figure 9: Rating Business Rules Template – Relationship to Primary Pop-Up for Medical Plans

with “Child, Stepson or Stepdaughter, and Foster Child” selected

CMS FFM Plan Management Copy and Paste in Templates Guide

7 Plans & Benefits Template

In the Plans & Benefits template, shown in Figure below, the top table in the Benefits Package sheet

contains high-level plan information. You will find the Cost Share Variance for each plan on the

second tab.

7.1 Benefits Package Sheet

The top table in the Benefits Package Tab allows any information to be pasted including the header

rows (Rows A-O). Do not copy and paste directly from the last Plan Years template because some

columns have been locked. It is recommended to copy by columns, and not by rows.

Figure 10: Plan & Benefits Template – Plan Level Data

The bottom table in the Benefits Package sheet contains benefit information and EHB data (see

Figure below). The benefits and the EHB/State Mandate columns are locked cells. It is NOT

possible to paste information into these columns. The Add Benefit Macro will assist you in adding

extra benefits, but you will be unable to paste over any listed benefits. From column E onward, it is

possible to paste data based on the table found at the end of the section. The Limit Unit column is

a pop-up with two drop-down fields that allow you to select various units of limitation. Be aware

that if you paste a limit unit that is not in the pop-up, you will receive a validation error.

Figure 11: Plan & Benefits Template – Bottom EHB Table

7.2 Cost Share Variances Sheet

Columns A, C, D, and F are protected and read-only. The macros will populate this information, and

it is not editable. Based on answers to columns G, H, and K, rows that are not applicable will lock

and gray-fill. To verify that the appropriate rows gray out, manually select “No” from the dropdown.

If you are trying to paste any information into a row that has a gray cell, you will receive the Excel

error message, shown in Figure below.

CMS FFM Plan Management Copy and Paste in Templates Guide

Figure 12: Sample Excel Error Message

You can paste values into any adjacent cells that are not gray but be very careful to paste the right

type of data. Each of the benefits listed on this sheet contains a copayment and coinsurance section.

Figure below shows Benefits Template Sample columns for Primary Care Visit to Treat an Injury or

Illness with Copay and Coinsurance columns.

Figure 13: Cost Share Variance Sheet – Benefits Template Sample Columns

Be sure to paste the correct data type and format (dollar amount: $xx.xx or percentage: xx.xx %) to

reduce error messages when you validate.

If you need to paste a large block of data, it is easiest to do before the template locks cells that are not

applicable. For example, if you have an older version of the Benefits template from which you want

to paste data:

1. On the desired Cost Share Variance sheet, delete values from columns G, H, and K in each

row that you want to paste data into, as shown in Figure below. This will clear out any

locked cells on that row.

Figure 14: Subsection of Benefits Template

2. Select and copy the block of data that you want to paste into the template (see Figure

below). If you are using an older version of the Benefits template, remember that you will

CMS FFM Plan Management Copy and Paste in Templates Guide

not be able to copy the entire row, because the first four columns are locked. Also,

remember that columns or data elements may be in different places. It is permissible to

copy gray cells, as long as you paste values only.

Figure 15: Benefits Template Deductible Columns Selected for Copy

3. Paste Special > Values into the destination workbook (either on the Home tab or with the

right click). Repeat as many times necessary. (See Figure below.)

Figure 16: Benefits Template Deductible Columns After Paste Values

4. When you have finished pasting, go back to columns G, H, and K and make necessary

selections from the drop-downs. Cells that are not applicable based on these values will gray

and lock.

5. It is permissible to have a value in a cell before it is gray/locked. For example, each of

the benefits has an In Network Tier 2 column (see Figure below.) You may fill out all cells

with values. When you select Multiple In Network Tiers = NO, all In Network Tier 2 cells

will gray and lock. Any values in them will still be present, but the template ignores any

cells with a gray fill for both Validate and Finalize macros.

Figure 17: Benefits Template Tier 2 Benefit Columns

For best results, do not paste your answers into columns G, H or K. If you do, Excel will not lock the

columns that do not apply. If a cell is not gray, you must enter data into it.

CMS FFM Plan Management Copy and Paste in Templates Guide

For example, if you paste in “Yes” for Medical & Drug Deductibles Integrated, the template will not

block out the separate Medical and Drug deductible groups, and you will have to enter “Not

Applicable” for each column under those groups. It is very important to double-check all your

data after pasting into the Cost Share Variance sheet.

CMS FFM Plan Management Copy and Paste in Templates Guide

8 ECP / Network Adequacy Template

The ECP / Network Adequacy Template has four types of worksheets: Facility ECPs, Individual

Providers, and Facilities & Pharmacies.

8.1 Facility ECPs Tab

In the Facility ECPs worksheet (see Figure below) providers can be added using the Select ECPs

tab. You will be able to copy and paste into Columns F, M, N, O, and P. Columns A through E, and

G through L, cannot be edited using copy and paste.

Figure 18: Facility ECPs Tab

CMS FFM Plan Management Copy and Paste in Templates Guide

8.2 IndividualProviders(NA) Tab

In the IndividualProviders(NA) worksheet (see Figure below) you will be able to copy and paste

into all columns.

Figure 19: IndividualProviders(NA) Tab

8.3 FacilityProviders(NA) Tab

In the FacilityProviders(NA) & IndividualProviders(NA) worksheet (see Figure below) you will

be able to copy and paste into all columns.

Figure 20: FacilityProviders(NA) Tab

9 Transparency in Coverage Template

The Transparency in Coverage template has two tabs: Issuer Level Data and Plan Level Data.

9.1 Issuer Level Data Tab

In the Issuer Level Data tab (see Figure below), header rows are found between data entry rows.

You will be able to copy and paste into the following cells only: B10-12, B14-19, and B21.

CMS FFM Plan Management Copy and Paste in Templates Guide

Figure 21: Transparency in Coverage Template – Issuer Level Data

9.2 Plan Level Data Tab

In the Plan Level Data tab (see Figure below), you will be able to copy and paste into all columns

underneath the header rows.

Figure 22: Transparency in Coverage Template – Plan Level Data