Annual Report and Recordkeeping Requirements

CENTERS FOR MEDICARE & MEDICAID SERVICES (CMS)
MEDICAL LOSS RATIO (MLR) REPORT
FILING INSTRUCTIONS FOR CONTRACT YEAR (CY) 2015
FOR MEDICARE ADVANTAGE ORGANIZATIONS AND
PRESCRIPTION DRUG PLAN SPONSORS
As of May 31, 2016

TABLE OF CONTENTS
GENERAL INSTRUCTIONS ...................................................................................................................... 3
Introduction ............................................................................................................................................... 3
Changes to the CY 2015 MLR Reporting Tool ........................................................................................ 4
Additional Resources ................................................................................................................................ 4
Reporting Considerations.............................................................................................................................. 6
Accounting Principles ............................................................................................................................... 6
Allocation of Expenses ............................................................................................................................. 6
Capitated Arrangements............................................................................................................................ 6
Third Party Vendors.................................................................................................................................. 6
Commercial MLR ..................................................................................................................................... 9
Commercial Reinsurance .......................................................................................................................... 9
EGWPs...................................................................................................................................................... 9
Low Income Premium and Cost Sharing Subsidies (LIPS and LICS) and Coverage Gap Discount
Program (CGDP) ...................................................................................................................................... 9
MA Optional Supplemental Benefits ...................................................................................................... 10
Medication Therapy Management (MTM) ............................................................................................. 10
Sequestration ........................................................................................................................................... 10
Territories................................................................................................................................................ 10
Reporting Requirements ......................................................................................................................... 10
MLR Review and Non-Compliance ....................................................................................................... 10
Penalties and Sanctions ........................................................................................................................... 11

WORKSHEET 1 ......................................................................................................................................... 12
Section 1 – General Information ............................................................................................................. 12
Section 2 – Data Collection .................................................................................................................... 12
WORKSHEET 2 ......................................................................................................................................... 24
Section 1 – Medicare MLR and Remittance Calculation........................................................................ 24
Section 2 – MLR Credibility Adjustment Table ..................................................................................... 26
WORKSHEET 3 ......................................................................................................................................... 27
Section 1 – Description of Expense Allocation Methods ....................................................................... 27
ATTESTATION ......................................................................................................................................... 28
TECHNICAL INSTRUCTIONS ................................................................................................................ 29
MLR Add-in File .................................................................................................................................... 29
Setup Instructions................................................................................................................................ 29
Add-in Updates ................................................................................................................................... 29
MLR Workbook ...................................................................................................................................... 30
Workbook Versions ............................................................................................................................ 30
Workbook Formatting and Protection................................................................................................. 30
Workbook Macros .............................................................................................................................. 31

GENERAL INSTRUCTIONS
Introduction
Medicare Advantage (MA) organizations and Prescription Drug Plan (PDP) sponsors must
submit an MLR Report to the Centers for Medicare & Medicaid Services (CMS) for each
contract offered during the Contract Year (CY) under the Medicare Advantage Program and the
Medicare Prescription Drug Benefit Program (Part D).
Organizations must submit the information via the CMS Health Plan Management System
(HPMS) in an MLR Report generated by the CMS MLR Reporting Tool.
All contracts that received Medicare revenue during the contract year must submit an MLR
report, with the following qualifications/clarifications:
•
•

•
•
•

PACE: The MLR Report is not to be completed for Programs of All-Inclusive Care for
the Elderly (PACE) plans.
Cost/HCPPs: The MLR Report must be completed for the Part D portion of the benefits
offered under their contract with CMS for Section 1876 Cost plans, Section 1833 Cost
plans, and employers/unions offering Cost plans or Health Care Prepayment Plans
(HCPPs). Cost plans that do not offer Part D are not required to complete or submit an
MLR Report.
EGWPs: All EGWP plans are included in the MLR Report. EGWPs are to report costs
and revenue only on the Medicare-funded portion of each contract.
Dual-SNPs: Complete the MLR Report. Note that, for all plans, Medicaid costs and
revenue are not included in the MLR calculation.
State demonstrations to integrate care for dually eligible Medicare and Medicaid
beneficiaries (i.e., Medicare-Medicaid Plans (MMPs)): While MMPs do not complete
this particular MLR Report, they may be required to complete a separate MLR report
(that is specific to MMPs) based on the requirements defined during the demonstration
development process.

The data included in the MLR Reporting Tool will be used to calculate the Medicare medical
loss ratio (MLR) and remittance amount, if any.
These filing instructions apply to the CY 2015 MLR reporting year.
An attestation must be submitted in HPMS for each MLR Report.
The submitted MLR Reports will be subject to review and audit by CMS or by any person or
organization that CMS designates. As part of the review and audit process, CMS or its
representative may request additional documentation supporting the information contained in
MLR Reports. Organizations must be prepared to provide this information in a timely manner.
If a CY 2015 remittance amount is due to CMS, there will be an adjustment to payment around
July 2017.
Page 3 of 33

MLR reporting for a contract year will occur in December following the contract year. However,
for contracts that fail to meet the MLR threshold for 2 consecutive years, MLR reporting for the
following year will be required prior to December. CMS will specify a month that will allow
time to implement (1) an enrollment sanction for any contract that fails to meet the MLR
threshold for 3 or more consecutive years, or (2) contract termination for any contract that fails
to meet the MLR threshold for 5 consecutive years.
Please review any data flagged with a “red circle” validation in the MLR Report prior to upload
to HPMS. See the technical instructions for more information.
The MLR Report workbook must be finalized prior to upload to HPMS. If the workbook is not
finalized, the upload will be rejected by HPMS. See the technical instructions for more
information.
Changes to the CY 2015 MLR Reporting Tool
The key changes between the CY2014 and CY2015 MLR Reporting Tool are listed below.
Technical
• The add-in file (MLR2015.xlam) must be saved under C:\MLR\MLR2015\.
See the technical instructions for more information.
Worksheet 1
• Three new input fields were added:
o Line 1.0a “MA sequestration adjustment”
o Line 1.0b “Part D sequestration adjustment”
o Line 3.4a “Affordable Care Act section 9010 Fee”
• One calculated field was updated due to new sequestration fields:
o Line 1.10 “Total revenue”
• One label was clarified: Line 8(g) “Dual-Eligible Member Months in territories”
Worksheet 2
• Two calculated fields were updated due to new sequestration fields:
o Line 3.2 “MLR credibility adjustments table”
o Line 5.5a “Remittance amount allocated to Parts A&B”
Additional Resources
In addition to these instructions, the following resources provide additional information
regarding CY 2015 MLR reporting:
• Questions regarding this MLR reporting may be addressed to: [email protected]
• Further information regarding Medicare MLR may be found at https://www.cms.gov/
Medicare/Medicare-Advantage/Plan-Payment/MedicalLossRatio.html
• The Medicare MLR implementing regulation may be found at http://www.gpo.gov/
fdsys/pkg/FR-2013-05-23/pdf/2013-12156.pdf
Page 4 of 33

•
•
•

Commercial MLR regulations, guidance, reporting instructions, and other resources may
be found at http://www.cms.gov/CCIIO/Programs-and-Initiatives/Health-InsuranceMarket-Reforms/Medical-Loss-Ratio.html
The Advance Notice, Rate Announcement, and Call Letter may be found at
http://cms.gov/Medicare/Health-Plans/MedicareAdvtgSpecRateStats/Announcements
-and-Documents.html
For technical questions about the MLR Reporting Tool, HPMS, or the upload process,
refer to the following resources:
HPMS Help Desk: 1-800-220-2028 or [email protected]

Page 5 of 33

Reporting Considerations
Accounting Principles
MA organizations and Part D sponsors should use Statutory Accounting Principles to explain
how revenue is used to pay for non-claims expenditures. Non-claims and quality improving
expenses should be allocated by contract. If an expense is attributable to a specific activity, MA
organizations and Part D sponsors should allocate the expense to that particular activity.
However, if this is not feasible, then the MA organization or Part D sponsor must apportion the
costs using a generally accepted accounting method that yields the most accurate results.
Allocation of Expenses
Expenses must be allocated in accordance with the provisions in 42 CFR §§ 422.2420(d) and
423.2420(d).
Capitated Arrangements
As stated at § 422.2420(b)(2), incurred claims include direct claims paid to providers, including
under capitation contracts. Where an MA organization or Part D sponsor has arranged with a
clinical provider 1 for capitation payments rather than fee-for-service reimbursement for covered
services to enrollees, and such capitation payments include reimbursement for certain provider
administrative costs, the entire per member per month capitation payment paid to the provider
may be included in incurred claims. The full capitation amount paid to a provider for covered
services described at § 422.2420(a)(2) or § 423.2420(a)(2) could be reported as a benefit
expense, unless, the provider contract specifies a distinct fee for administrative services. If the
capitated payment includes payment for activities that improve health care quality, as defined in
§§ 422.2430 and 423.2430, the MA organization must ensure that costs for these activities are
only counted once in the numerator.
Third Party Vendors
Payments by MA organizations or Part D sponsors to third party vendors as reimbursement for
providing clinical services or supplies directly to plan enrollees are incurred claims. Payments to
third party vendors to perform services such as network development, administrative fees, claims
processing, and utilization management, are non-claims administrative costs and are excluded
from incurred costs.
However, when a third party vendor, through its own employees, provides clinical services
directly to enrollees, the entire portion of the amount the issuer pays to the third party vendor that
is attributable to the third party vendor’s direct provision of clinical services should be

1

Includes capitation arrangements with both physician and non-physician providers that are licensed,
accredited, or certified to perform clinical health services, consistent with State law, and who are engaged
in the delivery of medical services to enrollees.
Page 6 of 33

considered incurred claims, even if such amount includes reimbursement for administrative costs
directly related to the vendor’s direct provision of clinical services. The phrase “through its own
employees” does not include a third party vendor’s contracted network of providers because such
network providers are not considered employees of the third party vendor.
•

For example, a Part D sponsor may contract with a pharmacy benefit manager (PBM) to
provide clinical services directly to enrollees through a mail order pharmacy. The
sponsor’s payments to the PBM for mail order pharmacy services provided directly by
the PBM’s employees, including administrative costs related to the PBM’s direct
provision of such mail order pharmacy services, are included in the sponsor’s incurred
claims.

In circumstances where a third party vendor pays a non-employee provider or supplier to provide
covered clinical services or medical supplies to plan enrollees, the MA organization or Part D
sponsor may only include as reimbursement for clinical services (i.e., incurred claims) the
amount that the vendor actually pays the medical provider or supplier for providing covered
clinical services or supplies to enrollees. Where the third party vendor is performing an
administrative function such as eligibility and coverage verification, claims processing,
utilization review, or network development, expenditures and profits on these functions would be
considered a non-claims administrative expense and must not be included by the MA
organization or Part D sponsor in its incurred claims.
•

For example, when a pharmacy benefit manager (PBM) pays a retail pharmacy one
amount for prescription drugs covered by the plan and charges the sponsor a higher
amount (the retail spread), the sponsor may only claim the amounts paid by the PBM to
the retail pharmacy as incurred claims. The third party vendor (in this example, the PBM)
must report to the sponsor only the aggregate amount it pays all providers (in this
example, retail pharmacies) for clinical services or medical supplies to enrollees on
behalf of the issuer, by market in each State. No claim-by-claim or provider-by-provider
reporting is required.

An MA organization or Part D sponsor may count a third party vendor’s expenses as activities
that improve health care quality to the extent that the issuer and vendor can show that these
expenses were incurred for performing allowable quality improving activities on behalf of the
issuer.
•

For example, to the extent that a PBM performs functions that are designed primarily to
identify quality concerns, such as potential adverse drug interactions, those costs may be
reported, in aggregate, as expenditures for activities that improve health care quality.

Payments by MA organizations and Part D sponsors to clinical risk-bearing entities, such as
Independent Practice Associations (IPAs), Physician Hospital Organizations (PHOs), and
Accountable Care Organizations (ACOs) are treated as incurred claims if the following four
factors are met:

Page 7 of 33

(1)

(2)
(3)

(4)

The entity contracts with an issuer to deliver, provide, or arrange for the delivery and
provision of clinical services to the issuer’s enrollees but the entity is not the issuer
with respect to those services;
The entity contractually bears financial and utilization risk for the delivery, provision,
or arrangement of specific clinical services to enrollees;
The entity delivers, provides, or arranges for the delivery and provision of clinical
services through a system of integrated care delivery that, as appropriate, provides for
the coordination of care and sharing of clinical information, and which includes
programs such as provider performance reviews, tracking clinical outcomes,
communicating evidence-based guidelines to the entity’s clinical providers, and other,
similar care delivery efforts; and
Functions other than clinical services that are included in the payment (capitated or
fee-for-service) must be reasonably related or incident to the clinical services, and
must be performed on behalf of the entity or the entity’s providers.

If the entity satisfies this four-part test, payments for clinical services for which the entity bears
the financial risk for utilization as provided in prong two above will be considered incurred
claims. By contrast, payments to third party vendors that only take on pricing risk (e.g.,
payments to pharmacy benefit managers (PBMs) for retail pharmacy claims) should not be
reported as incurred claims.
Payments to risk-bearing entities that include payments for administrative functions performed
on behalf of the entity’s member providers are incurred claims if all four factors outlined above
are met.
•

For example, a bundled payment to an IPA or similar entity for providing clinical
services to enrollees which includes: the IPA processing claims payments to its member
providers and submitting claims reports to issuers on behalf of its providers; performing
provider credentialing to determine a provider’s acceptability into the IPA network; and
developing a network for its providers’ benefit, would be included in incurred claims.

To the extent that administrative functions are performed on behalf of the MA organization or
Part D sponsor, however, that portion of the issuer’s payment that is attributable to
administrative functions may not be included in incurred claims. This is the case regardless of
whether payment is made according to a separate, fee-for-service payment schedule or as part of
a global, capitated fee payment for all services provided.
•

For example, payment for processing claims in order to issue explanations of benefits
(EOBs) to enrollees and handling any stage of enrollee appeals would not be included in
incurred claims. Payments for non-clinical services for which the contract between the
IPA and the issuer contains a “clawback” provision are not considered incurred claims
for MLR reporting purposes.

Page 8 of 33

Commercial MLR
CMS modeled Medicare MLR policy after the commercial MLR rules, and only departed from
the commercial MLR rules to the extent necessary and appropriate given the Medicare context.
Commercial Reinsurance
MA organizations and Part D sponsors may not adjust the MLR for commercial reinsurance.
Commercial reinsurance premiums and recoveries are excluded from the MLR calculation. Both
costs and revenues must be reported on a direct basis (i.e., before ceded reinsurance) as required
under §§ 422.2420(b)(2)(i) and 423.2420(b)(2)(i), and §§ 422.2420(c)(1) and 423.2420(c)(1).
The only instance in which the premiums (revenue) and claims associated with a 100 percent
indemnity reinsurance treaty are reported by the assuming entity, instead of by the ceding entity,
is when the reinsurance treaty was in force prior to the date of enactment of the Affordable Care
Act (i.e., March 23, 2010).
EGWPs
CMS expects EGWPs to report costs and revenue per §§ 422.2420 and 423.2420 on the
Medicare-funded portion of each contract. Although CMS does not currently collect information
on EGWP benefit packages, we have the authority to request this information if needed.
All Medicare-funded revenue must be reported.
To determine the Medicare-funded portion of the contract, organizations may either:
•
•

Use actual cost information to separate the employer-funded versus Medicare-funded
portions of the EGWPs under the contract, or
Allocate the Medicare-funded portion of the EGWP costs under the contract based on the
Medicare portion of revenue for the contract (i.e., report Medicare-funded costs as the
total costs multiplied by the ratio of Medicare revenue (as reported in MLR Worksheet 1
Line 1) to total revenue).

For non-CY EGWPs, we expect that MLR calculations and remittances would occur on a
calendar year basis, similar to how payments and most submissions to CMS are on a calendar
year basis.
Low Income Premium and Cost Sharing Subsidies (LIPS and LICS) and Coverage Gap
Discount Program (CGDP)
CMS makes LIPS payments to MA organizations and Part D sponsors so that they can be made
whole for the reduced premiums paid by eligible low income plan beneficiaries. LIPS payments
are revenue to the plan and are taken into account in the denominator of the MLR. Because
CMS views LICS and coverage gap discount payments as pass-through payments, they are not
part of the numerator or the calculation for the MLR.
Page 9 of 33

MA Optional Supplemental Benefits
The MA MLR includes all of the MA benefits defined at § 422.100(c): basic benefits, mandatory
supplemental benefits, and optional supplemental benefits. All Medicare costs and revenues
under an MA contract should be included in the MLR, and the optional supplemental benefit
package is defined by law as a type of Medicare benefit under the MA program.
Medication Therapy Management (MTM)
MTM programs may be included in the MLR calculation, provided they satisfy the requirements
for quality improving activities as set forth in §§ 422.2430 and 423.2430.
Sequestration
Generally speaking, MLR reporting is based on actual incurred costs and actual revenues, which
would reflect sequestration reductions. For example, if reduced amounts are paid to providers
due to sequestration, the reporting of incurred costs would reflect the reduction. The reporting of
revenue received from CMS should reflect any sequestration reductions.
Territories
CMS is authorized under §§ 422.2420(a) and 423.2420(a) to make adjustments to the MLR
produced by the standard formula to address exceptional circumstances for areas outside the 50
states and the District of Columbia. At this time, CMS does not believe it has sufficient
information to determine whether and how to make such an adjustment. Therefore, CMS will
collect CY 2015 MLR Reports in December 2016, and subsequently determine if an adjustment
to the CY 2015 MLR calculation is warranted for contracts serving territories. CMS will analyze
the data collected in MLR Worksheet 1 (including Section 2 Line 8) to help inform this
determination. If CMS decides that an adjustment is warranted, it will announce the
methodology to the affected contracts.
Reporting Requirements
For each contract year, each MA organization or Part D sponsor must submit a report to CMS, in
a timeframe and manner specified by CMS, which includes but is not limited to the data needed
by the MA organization or Part D sponsor to calculate and verify the MLR and remittance
amount, if any, for each contract, such as incurred claims, total revenue (net of reconciliations),
expenditures on quality improving activities, non-claims costs, taxes, licensing and regulatory
fees, and any remittance owed to CMS under § 422.2410 or § 423.2410. See §§ 422.2460 and
423.2460.
MLR Review and Non-Compliance
CMS conducts selected reviews of submitted MLR reports to validate amounts included in both
the numerator and denominator of the MLR calculation reported to CMS and to determine that
Page 10 of 33

that the MLRs and remittance amounts and sanctions are accurately calculated, reported, and
applied. See §§ 422.2480 and 423.2480.
MA organizations and Part D sponsors are required to maintain evidence of amounts reported to
CMS and to validate all data necessary to calculate MLRs in accordance with the requirements in
§§ 422.2460 and 423.2460.
Documents and records must be maintained for 10 years from the date such calculations were
reported to CMS with respect to a given MLR reporting year (for MA organizations, per §
422.2480) or contract year (for Part D sponsors, per § 423.2480).
MA organizations and Part D sponsors must require any third party vendor supplying drug or
medical cost contracting and claim adjudication services to the MA organization or Part D
sponsor to provide all underlying data associated with MLR reporting to that MA organization or
Part D sponsor in a timely manner, when requested by the MA organization or Part D sponsor,
regardless of current contractual limitations, in order to validate the accuracy of MLR reporting.
See §§ 422.2480(c)(2) and 423.2480(c)(2).
Reports submitted under § 422.2460 or § 423.2460, calculations, or any other required MLR
submissions found to be materially incorrect or fraudulent—
(1) Are noted by CMS;
(2) Appropriate remittance amounts are recouped by CMS; and
(3) Sanctions may be imposed by CMS as provided in §§ 422.752 and 423.752.
Penalties and Sanctions
An MA organization or Part D sponsor is required to report an MLR for each contract for each
contract year.
If CMS determines for a contract year that an MA organization or Part D sponsor has an MLR
for a contract that is less than 0.85, the MA organization or Part D sponsor has not met the MLR
requirement and must remit to CMS an amount equal to the product of the following:
(1)
(2)

The total revenue of the MA or Part D contract for the contract year, per §§
422.2420(c) and 423.2420(c).
The difference between 0.85 and the MLR for the contract year.

If CMS determines that an MA organization or Part D sponsor has an MLR for a contract that is
less than 0.85 for 3 or more consecutive contract years, CMS does not permit the enrollment of
new enrollees under the contract for coverage during the second succeeding contract year.
If CMS determines that an MA organization or Part D sponsor has an MLR for a contract that is
less than 0.85 for 5 consecutive contract years, CMS terminates the contract per § 422.510(b)(1)
and (d) or § 423.509(b)(1) and (d) effective as of the second succeeding contract year.

Page 11 of 33

WORKSHEET 1
The purpose of Worksheet 1 is to capture contract-specific experience for the reporting period.
Section 1 – General Information
Line 1 – Contract Year
This field is pre-populated with the year to which the contract applies.
Line 2 – Contract Number
Enter the contract number, which begins with a capital letter H, R, S, or E and includes
four Arabic numerals (e.g., H9999). Be sure to include all leading zeroes (e.g., H0001).
Line 3 – Organization Name
Enter the organization’s legal entity name. This information also appears in HPMS.
Line 4 – Date MLR Report finalized
This field is populated with the date when the MLR Report is finalized. See the Technical
Instructions section for more information.
Line 5 – Contact Information
Plan sponsors must identify two contacts that will be readily available and authorized to
discuss the information submitted in the MLR Report.
In this section, enter the name, position, phone number, and e-mail information for both
contacts. Do not leave any part of this section blank.
Section 2 – Data Collection
Enter total dollars; PMPMs are automatically calculated.
Line 1 – Revenue
Enter the contract’s revenue amounts for the reporting period by the various categories.
In accordance with §§ 422.2420(c)(3) and 423.2420(c)(3), the following amounts must
not be included in total revenue:
• The amount of unpaid premiums for which the MA organization or Part D
sponsor can demonstrate to CMS that it made a reasonable effort to collect.
• The following EHR payments and adjustments:

Page 12 of 33

•
•
•

EHR incentive payments for meaningful use of certified electronic health records
by qualifying MAOs, MA EPs, and MA-affiliated eligible hospitals that are
administered under 42 CFR part 495 subpart C.
EHR payment adjustments for a failure to meet meaningful use requirements that
are administered under 42 CFR part 495 subpart C.
Coverage Gap Discount Program payments under § 423.2320.

LICS payments are not included as revenue for MLR reporting.
Total revenue (as defined at §§ 422.2420(c) and 423.2420(c)) for policies issued by an
MA organization or Part D sponsor and later assumed by another entity must be reported
by the assuming entity for the entire MLR reporting year during which the policies were
assumed and no revenue under this part for that contract year must be reported by the
ceding MA organization or Part D sponsor. See §§ 422.2420(c)(4) and 423.2420(c)(4).
Total revenue (as defined at §§ 422.2420(c) and 423.2420(c)) that is reinsured for a block
of business that was subject to indemnity reinsurance and administrative agreements
effective prior to March 23, 2010, for which the assuming entity is responsible for 100 %
of the ceding entity’s financial risk and takes on all of the administration of the block,
must be reported by the assuming issuer and must not be reported by the ceding issuer.
See §§ 422.2420(c)(5) and 423.2420(c)(5).
CMS provides organizations and sponsors with revenue information via several reports. Below is
a mapping of CMS reports to the revenue categories of the MLR Report. The following CMS
reports are used in this mapping:
•
•

MMDDF = Monthly Membership Detail Data Files (payments for CY 2015, through
September 2016)
PRS CTR = Payment Reconciliation System (PRS) Reconciliation Results Report to
Plans, Contract Trailer “CTR” version
MLR
Report
Line 1.0
Line 1.1
Line 1.2
Line 1.3
Line 1.4
Line 1.5
Line 1.6
Line 1.7
Line 1.8
Line 1.9

Brief Description

CMS revenue report

Sequestration Adjustments
Beneficiary Premiums
MA payment including 3 rebates
Part B Rebate
Part D Basic Rebate
MSA Enrollee Deposit
Part D direct subsidy
Part D federal reinsurance
LIPSA
Part D risk corridor payments

N/A*
N/A
MMDDF item 66
MMDDF items 60+61
MMDDF item 72
N/A
MMDDF item 74
PRS CTR field 20
MMDDF item 35
PRS CTR field 33

* Sequestration Adjustments are included on the monthly contract-level Plan Payment Report
(PPR), produced by the Automated Plan Payment System (APPS), on a paid basis. The MLR
Page 13 of 33

Report contains a default sequestration adjustment calculation as 2% reduction of Lines 1.2, 1.3,
1.4, and 1.6. The MA organization or Part D sponsor may choose to override this default
calculation with a plan-reported sequestration amount. Sequestration adjustment must be entered
as a negative amount. The MMDDF amounts do not reflect the sequestration adjustment. Note
that lines 1.1, 1.7, 1.8, and 1.9 are not subject to sequestration.
Contracts that were terminated, consolidated or withdrawn in CY 2015 are required to submit an
MLR Report that accounts for revenue, including the final risk adjustment reconciliation
amounts. CMS will post the Part C and Part D risk adjustment reconciliation amounts for
contracts that terminated, consolidated or withdrew in CY 2015 at: HPMS Home > Risk
Adjustment > Risk Adjustment Reconciliation Amount. These values are from the July 2016
MMR, for adjustment reason code (ARC) 25 and 37. These values are prior to application of
sequestration (i.e., “gross” of sequestration). This information may be used in the development
of CY 2015 MLR reporting.
More information about the CMS revenue reports may be found at:
•
•

http://www.cms.gov/Research-Statistics-Data-and-Systems/CMS-InformationTechnology/mapdhelpdesk/Plan_Communications_User_Guide.html
http://www.csscoperations.com/internet/cssc3.nsf/docsCat/CSSC~CSSC%20Operations~
Prescription%20Drug%20Event~Report%20Layouts?open&expand=1&navmenu=Prescr
iption^Drug^Event||

Line 1.0 – Sequestration Adjustment
See the mapping to CMS revenue reports provided above.
The MLR Report contains a default sequestration adjustment calculation as 2% reduction
of Lines 1.2, 1.3, 1.4, and 1.6. The organization may also choose to override this default
calculation with a plan-reported sequestration amount. Sequestration adjustment must be
entered as a negative amount.
Note that lines 1.1, 1.7, 1.8, and 1.9 are not subject to sequestration.
Line 1.0a – MA Sequestration Adjustment
MA portion of Line 1.0
Line 1.0b – Part D Sequestration Adjustment
Part D portion of Line 1.0
Line 1.1 – Beneficiary Premium
Beneficiary premiums include all premiums by or on behalf of enrollees, all unpaid
premium amounts that an organization could have collected from enrollees minus any
Page 14 of 33

premium amounts that remain unpaid after reasonable collection efforts, and all changes
in unearned premium reserves.
Beneficiary premiums are net of MA rebates (i.e., after application of MA rebates to
reduce premium).
Line 1.1a – MA (Basic + Mandatory Supplemental + Optional Supplemental)
MA portion of Line 1.1
Line 1.1b – Part D (Basic + Supplemental)
Part D portion of Line 1.1
Line 1.2 – MA plan payments (based on A/B bid), using final risk scores including MA
Rebate for Cost Sharing Reduction, MA Rebate for Other Mandatory
Supplemental Benefits, and MA Rebate for Part D Supplemental Benefits
See the mapping to CMS revenue reports provided above.
Line 1.3 – MA Rebate for Part B Premium Reduction
See the mapping to CMS revenue reports provided above.
Line 1.4 – MA Rebate for Part D Basic Premium Reduction
See the mapping to CMS revenue reports provided above.
Line 1.5 – MSA Enrollee Deposit
Applies to MSA plans only
Line 1.6 – Part D direct subsidy, using final risk scores
See the mapping to CMS revenue reports provided above.
Line 1.7 – Part D federal reinsurance subsidy (prospective and reconciliation adjustments)
See the mapping to CMS revenue reports provided above.
Note that federal reinsurance includes both a prospective payment and a reconciliation
adjustment.
Line 1.8 – Part D Low Income Premium Subsidy Amount (LIPSA)
See the mapping to CMS revenue reports provided above.
Page 15 of 33

Line 1.9 – Part D risk corridor payments
See the mapping to CMS revenue reports provided above.
Line 1.10 – Total
Calculated as the sum of Lines 1.0 through 1.9
Line 2 – Claims
Enter the contract’s expenses for the reporting period by the various categories.
Incurred claims for clinical services and prescription drug costs must include the
following:
1. Direct claims that the MA organization pays to providers (including under
capitation contracts with physicians) for covered services (described at §
422.2420(a)(2)) provided to all enrollees under the contract.
2. Direct drug costs that are actually paid (as defined in § 423.308) by the Part D
sponsor.
3. For an MA contract that includes MA–PD plans (described at § 422.2420(a)(2)),
drug costs provided to all enrollees under the contract, as defined at §
423.2420(b)(2)(i).
4. Unpaid claims reserves for the current contract year, including claims reported in
the process of adjustment.
5. Percentage withholds from payments made to contracted providers.
6. Incurred but not reported claims based on past experience, and modified to reflect
current conditions such as changes in exposure, claim frequency or severity.
7. Changes in other claims-related reserves.
8. Claims that are recoverable for anticipated coordination of benefits.
9. Claims payments recoveries received as a result of subrogation.
10. Claims payments recoveries as a result of fraud reduction efforts, not to exceed
the amount of fraud reduction expenses.
11. Reserves for contingent benefits and the medical or Part D claim portion of
lawsuits.
12. The amount of incentive and bonus payments made to providers.
Note that Part D federal reinsurance is included in both the MLR numerator and
denominator.
LICS and CGDP are excluded from both the MLR numerator and denominator.
Adjustments that must be deducted from incurred claims include overpayment recoveries
received from providers.

Page 16 of 33

The following amounts must not be included in incurred claims:
1. Non-claims costs, as defined in §§ 422.2401 and 423.2401, which include the
following:
a. Amounts paid to third party vendors for secondary network savings.
b. Amounts paid to third party vendors for any of the following:
i. Network development.
ii. Administrative fees.
iii. Claims processing.
iv. Utilization management.
c. Amounts paid, including amounts paid to a provider or pharmacy, for
professional or administrative services that do not represent compensation
or reimbursement for covered services provided to an enrollee, such as the
following:
i. Medical record copying costs.
ii. Attorneys’ fees.
iii. Subrogation vendor fees.
iv. Bona fide service fees.
v. Compensation to any of the following:
1. Paraprofessionals.
2. Janitors.
3. Quality assurance analysts.
4. Administrative supervisors.
5. Secretaries to medical personnel.
6. Medical record clerks.
2. Amounts paid to CMS as a remittance under § 422.2410(b) or § 423.2410(b).
Incurred claims under this part for policies issued by one MA organization or Part D
sponsor and later assumed by another entity must be reported by the assuming
organizations for the entire MLR reporting year during which the policies were assumed
and no incurred claims under this part for that contract year must be reported by the
ceding MA organization or Part D sponsor.
Reinsured incurred claims for a block of business that was subject to indemnity
reinsurance and administrative agreements effective before March 23, 2010, for which
the assuming entity is responsible for 100 percent of the ceding entity’s financial risk and
takes on all of the administration of the block, must be reported by the assuming issuer
and must not be reported by the ceding issuer.
Line 2.1 – Claims incurred only during CY 2015, paid through 9/30/2016
This line is consistent with the commercial MLR reporting form Part 2 Line 2.1.
Line 2.2 – Liability and reserves for claims incurred only during CY 2015, calculated as of
9/30/2016
This line is consistent with the commercial MLR reporting form Part 2 Lines 2.2 and 2.4.
Page 17 of 33

Line 2.3 – Incurred medical incentive pool and bonuses
This line is consistent with the commercial MLR reporting form Part 2 Line 2.11.
Line 2.3a – Paid medical incentive pools and bonuses MLR Reporting year
This line is consistent with the commercial MLR reporting form Part 2 Line 2.11a.
Line 2.3b – Accrued medical incentive pools and bonuses MLR Reporting year
This line is consistent with the commercial MLR reporting form Part 2 Line 2.11b.
Line 2.4 – Contingent benefit and lawsuit reserves
This line is consistent with the commercial MLR reporting form Part 2 Line 2.13.
Line 2.5 – MA Rebate for Part B Premium Reduction
Calculated field that refers to Line 1.3
Line 2.6 – MSA Enrollee Deposit (MSA plans only)
Calculated field that refers to Line 1.5
Line 2.7 – Allowable fraud reduction expense (the smaller of lines 2.7a or 2.7b)
This line is consistent with the commercial MLR reporting form Part 2 Line 2.17.
Line 2.7a – Total fraud reduction expense
This line is consistent with the commercial MLR reporting form Part 2 Line 2.17a.
Required data entry; do not leave blank.
Note that if total net amounts are entered as negative, it will reduce total claims in Line
2.8.
Line 2.7b – Total fraud recoveries that reduced paid claims in Line 2.1
This line is consistent with the commercial MLR reporting form Part 2 Line 2.17b.
Required data entry; do not leave blank.
Note that if total net amounts are entered as negative, it will reduce total claims in Line
2.8.
Page 18 of 33

Line 2.8 – Total
Calculated as the sum of Lines 2.1 through 2.7

Line 2.8a – Part D (informational only; already included in Line 2.8)
Required data entry; do not leave blank.
This line is similar to the commercial MLR reporting form Part 1 Line 2.2.
For plan types that are only reporting Part D experience for MLR (such as PDPs and
Cost), Line 2.8a Part D claims should equal Line 2.8 total claims.
This amount should be net/after the application of DIR.
Line 2.8b – Direct and Indirect Remuneration (DIR) (informational only; already excluded
from Line 2.8)
Required data entry; do not leave blank.
This line is similar to the commercial MLR reporting form Part 1 Line 2.3.
The amount reported should include all direct and indirect remuneration (including
discounts, charge backs or rebates, cash discounts, free goods contingent on a purchase
agreement, up-front payments, coupons, goods in kind, free or reduced-price services,
grants, or other price concessions or similar benefits offered to some or all purchasers)
from any source (including manufacturers, pharmacies, enrollees, or any other person)
that would serve to decrease the costs incurred under the Part D plan.
Total net DIR should not be reported as negative unless total net DIR increases the
sponsor’s drug costs.
Line 3 – Federal and State Taxes and Licensing or Regulatory Fees
Required data entries; do not leave blank. Do not leave line 3.4a blank.
The categories under Line 3 are consistent with the commercial MLR reporting form.
Federal and State taxes and assessments and licensing or regulatory fees must be reported
in accordance with the provisions in §§ 422.2420(c)(2) and 423.2420(c)(2).
Total net taxes/fees should not be reported as negative unless total net taxes/fees reduce
the sponsor’s costs.

Page 19 of 33

In accordance with the provisions in §§ 422.2420(c)(2)(iv)(B) and 423.2420(c)(2)(iv)(B),
the MLR regulations allow a federal income tax-exempt MA organization or Part D
sponsor to include community benefit expenditures up to the limit of either 3 percent of
revenue or the highest premium tax rate in the state for which the MA organization or
Part D sponsor is licensed multiplied by the revenue for the contract.
Line 4 – Health Care Quality Improvement (QI) Expenses Incurred
The categories under Line 4 are consistent with the commercial MLR reporting form.
The regulations at §§ 422.2430(a) and 423.2430(a) define the expenditures and activities
that improve health care quality and can therefore be reported for MLR purposes.
Sections 422.2430(b) and 423.2430(b) identify the excluded expenditures and activities
that must not be reported.
In accordance with the provisions in §§ 422.2430 and 423.2430, expenditures that must
not be included in quality improving activities include ICD–10 implementation costs in
excess of 0.3 percent of total revenue.
Line 5 – Non-Claims Costs
The categories under Line 5 are consistent with the commercial MLR reporting form.
Non-claims costs, as defined in §§ 422.2401 and 423.2401, are those expenses for
administrative services that are not—
1. Incurred claims (as provided in §§ 422.2420(b)(2) through (4) and
423.2420(b)(2) through (b)(4));
2. Expenditures on quality improving activities (as provided in §§ 422.2430 and
423.2430);
3. Licensing and regulatory fees (as provided in §§ 422.2420(c)(2)(ii) and
423.2420(c)(2)(i));
4. State and Federal taxes and assessments (as provided in §§ 422.2420(c)(2)(i)
and (iii), and 423.2420(c)(2)(ii) and (iii)).
Required data entries; do not leave blank. Do not leave lines 5.8 and 5.9 blank.
Line 6 – Methodology for determining the Medicare-funded portion of the contract for
EGWP plans
This information will be used during CMS review/audit. See the Reporting
Considerations EGWP section for more information.
Line 6.1 – Option 1 "Actual EGWP costs", or Option 2 "Allocated based on revenue"
Enter the option used to determine the Medicare-funded portion of the contract for
EGWP plans. If there are no EGWP plans under the contract, then leave this field blank.
Page 20 of 33

Line 6.2 – Enter percentage used to allocate EGWP costs (i.e., Medicare % of total
revenue)
If Option 2 “Allocated based on revenue” is entered in Line 6.1, then enter the percentage
used to allocate EGWP costs under the contract. Otherwise, leave this field blank.
Line 7 – Total Member Months
This field is calculated as the sum of the member months entered in Line 8 column (c).
Line 8 – Plan-Specific Data
Column (b) Plans offered under the contract in CY 2015
Enter the list of plans offered under the contract in CY 2015, in the format of contractplan-segment (Hxxxx-xxx-xx, Rxxxx-xxx-xx, Sxxxx-xxx-xx, or Exxxx-xxx-xx). There
are rows available to enter up to 150 plans. Do not leave blank rows between plans.
Column (c) CY 2015 Member Months
Enter the member months associated with each plan entered.
Member months entered should be on a consistent basis with the revenue and claims
information entered in Lines 1 and 2 (e.g., through September 2015).
Member months for a contract year equal the sum across the 12 months of a year of the
total number of enrollees for each month. This includes enrollees who are in ESRD and
hospice status for a month.
Column (d) Is the plan a Dual-Eligible Special Needs Plan (i.e., D-SNP)? (Yes/No)
For each plan entered, enter Yes/No to indicate if the plan is a D-SNP.
Column (e) Does the plan's defined service area include territories? (Yes/No)
For each plan entered, enter Yes/No to indicate if the plan’s defined service area includes
counties in one or more territories (e.g., Puerto Rico, Guam, Northern Mariana Islands,
Virgin Islands, American Samoa).
The plan’s defined service area is in HPMS. (The corresponding county codes are also
entered in the CY MA Bid Pricing Tool (BPT), for those plans that complete and submit
the MA BPT.)

Page 21 of 33

Column (f) Is the plan a D-SNP in a territory? (Yes/No)
Calculated field: If column (d) and column (e) both equal “Yes”, then column (f)
populates with a “Yes”.
Columns (g) through (n)
The label Medicaid in this Report is used to refer to a State/Territory program for DualEligible beneficiaries.
Columns (g) through (n) must be completed for plans that are D-SNPs in territories (that
is, must be completed for plan rows where column (f) equals “Yes”).
Column (g) Dual-Eligible Member Months in territories
Enter the subset of member months entered in column (c) that are dual-eligibles in
territories (i.e., beneficiaries dually eligible for both Medicare and Medicaid).
Column (h) Medicaid Revenue PMPM
Enter the Medicaid Revenue for member months entered in column (g) on a PMPM
basis.
This revenue must not be included in Worksheet 1 Line 1.
Column (i) Medicaid Cost PMPM
Calculated field: Column (j) + (k) + (l) + (m)
Column (j) Medicaid Non-Claims Cost PMPM
Enter the Medicaid non-claims cost for member months entered in column (g) on a
PMPM basis.
This amount must not be included in Worksheet 1 Line 5.
Column (k) Medicaid Claims Cost: Medical Cost Sharing Reduction PMPM
Enter the Medicaid claims cost for member months entered in column (g) on a PMPM
basis.
This amount must not be included in Worksheet 1 Line 2.

Page 22 of 33

Column (l) Medicaid Claims Cost: Medical Other Benefits PMPM
Enter the Medicaid claims cost for member months entered in column (g) on a PMPM
basis.
This amount must not be included in Worksheet 1 Line 2.
Column (m) Medicaid Claims Cost: Pharmacy PMPM
Enter the Medicaid claims cost for member months entered in column (g) on a PMPM
basis.
This amount must not be included in Worksheet 1 Line 2.
Column (n) Medicaid Gain/(Loss) PMPM
Calculated field: Column (h) - (i)

Page 23 of 33

WORKSHEET 2
The purpose of Worksheet 2 is to compute the Medicare medical loss ratio (MLR) and
remittance amount, if any.
Section 1 – Medicare MLR and Remittance Calculation
The MLR and remittance must be calculated in accordance with the provisions in §§
422.2420(a) through (c), and 423.2420(a) through (c).
Line 1 – Medical Loss Ratio Numerator
Line 1.1 – Claims
Calculated field that refers to Worksheet 1 Line 2.8
Line 1.2 – Improving health care quality expenses
Calculated field that refers to Worksheet 1 Line 4.7
Line 1.3 – MLR numerator
Calculated field: Worksheet 2 Lines 1.1 + 1.2
Line 2 – Medical Loss Ratio Denominator
Line 2.1 – Revenue
Calculated field that refers to Worksheet 1 Line 1.10
Line 2.2 – Federal and State taxes and licensing or regulatory fees
Calculated field that refers to Worksheet 1 Line 3.4
Line 2.3 – MLR denominator
Calculated field: Worksheet 2 Lines 2.1 - 2.2
Line 3 – Credibility Adjustment
Line 3.1 – Member months to determine credibility
Calculated field that refers to Worksheet 1 Line 7

Page 24 of 33

Line 3.2 – MLR credibility adjustments table
Refers to either the MA or PD credibility adjustment table in Worksheet 2 Section 2.
The PD adjustment factors are utilized for S contracts, contracts where total claims
(Worksheet 1 Line 2.8) equal Part D claims (Line 2.8a), and contacts where total revenue
(Line 1.10) equals Part D revenue. Otherwise MA adjustment factors are utilized.
Line 3.3 – Credibility Adjustment
Linear interpolation calculated based on member months (Worksheet 2 Line 3.1) and the
MA or PD credibility table (Worksheet 2 Line 3.2 and Section 2)
Line 4 – Medical Loss Ratio (MLR) Calculation
Line 4.1 – Unadjusted MLR
Calculated field: Worksheet 2 Line 1.3 divided by Worksheet 2 Line 2.3
Line 4.2 – Credibility adjustment
Calculated field that refers to Worksheet 2 Line 3.3
Line 4.3 – Adjusted MLR
Calculated field: Worksheet 2 Lines 4.1 + 4.2, then rounded
Line 5 – Remittance Calculation
Line 5.1 – Is contract either partially-credible or fully-credible?
“Yes”/”No” populated based on member months (Worksheet 2 Line 3.1) and the MA or
PD credibility table (Worksheet 2 Line 3.2 and Section 2)
Line 5.2 – MLR standard
Pre-populated with 85.0%
Line 5.3 – Adjusted MLR
Calculated field that refers to Worksheet 2 Line 4.3
Line 5.4 – MLR denominator
Calculated field that refers to Worksheet 2 Line 2.3

Page 25 of 33

Line 5.5 – Remittance amount due to CMS for CY 2015 experience
Calculated field: Worksheet 2 (Lines 5.2 – 5.3) x Line 5.4 when Adjusted MLR
(Worksheet 2 Line 5.3) is less than MLR standard (Worksheet 2 Line 5.2)
Line 5.5a – Remittance amount allocated to Parts A&B (For CMS system purposes only)
Calculated field to allocate Line 5.5 based on Worksheet 1 Line 1 Revenue data entries
Line 5.5b – Remittance amount allocated to Part D (For CMS system purposes only)
Calculated field to allocate Line 5.5 based on Worksheet 1 Line 1 Revenue data entries
Section 2 – MLR Credibility Adjustment Table
MA and PD credibility adjustments per CMS, based on member months.
An MA organization or Part D sponsor may add a credibility adjustment to a contract’s
MLR if the contract’s experience is partially credible, as determined by CMS.
MA organization or Part D sponsor may not add a credibility adjustment to a contract’s
MLR if the contract’s experience is fully credible, as determined by CMS.
For those contract years for which a contract has non-credible experience for their MLR,
sanctions under § 422.2410(b) through (d) or § 423.2410(b) through (d) will not apply.
CMS defines and publishes definitions of partial credibility, full credibility, and noncredibility and the credibility factors through the notice and comment process of
publishing the Advance Notice and Final Rate Announcement.

Page 26 of 33

WORKSHEET 3
Section 1 – Description of Expense Allocation Methods
The fields on Worksheet 3 are consistent with the commercial MLR reporting form, and refer to
the data entry lines of Worksheet 1.
Worksheet 3 is used by the organization to describe the methods used to allocate expenses, as
reported on the MLR Report, including incurred claims, quality improvement expenses, federal
and state taxes and licensing or regulatory fees, and other non-claims costs.
A detailed description of each expense element should be provided, including how each specific
expense meets the criteria for the type of expense in which it is categorized.
Note that the fields on Worksheet 3 are limited to 4,000 characters each.
See the “Allocation of Expenses” Reporting Consideration in the General Instructions section of
this document for more information.

Page 27 of 33

ATTESTATION
An attestation must be submitted in the HPMS attestation module to accompany each MLR
Report uploaded to HPMS. The attesting officer must be designated as a CEO, CFO, or COO in
the HPMS Basic Contract Management Module.
The language below is used in the electronic attestation module in HPMS:
CY 2015 MLR Attestation
The officer of this reporting issuer being duly sworn, attests that he/she is the described
officer of the reporting issuer, and that this MLR Report is a full and true statement of all
the elements related to the health insurance coverage issued for the MLR reporting year
stated above, and that the MLR Report has been completed in accordance with the
Department of Health and Human Services reporting instructions and regulations,
according to the best of his/her information, knowledge and belief. Furthermore, the
scope of this attestation by the described officer includes any related electronic filings
and postings for the MLR reporting year stated above, that are required by Department of
Health and Human Services under implementing regulations.
CEO/CFO/COO

Page 28 of 33

TECHNICAL INSTRUCTIONS
The MLR Report is comprised of two parts: an add-in file and a workbook. The MLR add-in file
contains code that enables the formulas, macros, and verification logic of the tool. The workbook
provides the visual interface for the user to enter MLR data for a contract.
MLR Add-in File
Setup Instructions
The MLR Report requires a separate add-in file that supports the MLR workbook functionality.
Below are the steps to download and set up the add-in file:
1. Create a folder with the file path C:\MLR\MLR2015.
2. Download the MLR add-in zip file from HPMS.
3. Unzip the downloaded add-in file into the C:\MLR\MLR2015 directory.
If an older version of the add-in file exists, select the option to replace this file when prompted.
If the MLR add-in file is not saved in the designated folder, a message is displayed in the MLR
workbook stating that the MLR2015.xlam (MLR Add-in) file cannot be found.
After saving the add-in file in the correct location, the following macro security settings must be
set to allow the add-in to function properly:
1. Set the macro security setting to ‘Disable macros with notifications
(Recommended)’ or ‘Enable all Macros’. For Excel 2010, click the File tab and
select Options > Trust Center > Trust Center Settings > Macro Settings.
2. If the macro security setting is set to ‘Disable macros with notifications’ in Excel
2010, click File > Info and select Enable Content > Enable All Content.
Add-in Updates
If necessary, CMS may deploy a new version of the MLR add-in file to update the MLR
workbook. If CMS deploys an updated version of the MLR add-in (MLR2015.xlam) file, the
existing add-in file must be replaced with the new version. The new add-in file must be
downloaded from HPMS and copied into the C:\MLR\MLR2015 folder.
All existing MLR workbooks must be updated with the latest add-in file. If the old version of the
add-in file has been used to finalize an MLR workbook and has not yet been uploaded to HPMS,
the user must download the new add-in file, update the working file, and re-finalize the
workbook.
After installing the new add-in file, the workbook updates automatically on first use when the
workbook is opened. A pop-up message displays stating that the workbook is out of date. Select
the “OK” button to continue and the update process will begin. Data does not need to be
transferred from one workbook to another to apply an add-in update.

Page 29 of 33

MLR Workbook
Workbook Versions
The MLR Report employs three versions of the workbook that serve different purposes:
•

Working file – a read-write enabled file that allows users to enter data in specified input
fields; specified calculated fields are automatically calculated via formulas resident in the
files. Users may edit, save, name, and re-name working versions of the MLR workbook.

•

Finalized file – a read-only file created by a process called finalization, which modifies
the format of the working file to prepare it for submission to CMS. Finalization saves the
file using a standard naming convention and populates a “timestamp” within the finalized
MLR Report. Note that finalized files retain the workbook formulas, and macro
functionality is removed.

•

Backup file – also a read-only file created by the finalization process. The backup file
uses the same file name as the finalized file with the word “backup” and a timestamp
appended to it. The data in the backup file is the same as that in the working file. Users
can remove the text “backup” from the filename to enable editing of the backup file. As
such, backup files enable users to convert backup files back into working files—if
needed—for further modification.

Workbook Formatting and Protection
Data entry cells are formatted in yellow. Keyboard users may use the ‘Tab’ key on the keyboard
to cycle through the input cells.
All other cells prevent the user from keying in data. A dialog box alerts the user if the user has
selected a protected cell. Cutting and pasting are not recommended, to prevent structural changes
to the workbook. Users may copy and paste data into the workbook, and link the workbook to
external files.
The MLR Report is password protected. The user 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.
Tampering with the file’s protection, including but not limited to un-protecting and re-protecting
any parts of a workbook, will permanently compromise the file and prevent successful
finalization of the workbook. If a workbook is compromised in this way, you must discard the
compromised file, download and complete a new MLR Report.

Page 30 of 33

Workbook Macros
The workbook includes six macros under the Add-Ins menu that assist the user with data entry,
data validation, and workbook finalization.
Finalize MLR
The finalization macro prepares the workbook for submission to CMS. The workbook must be
finalized before uploading to HPMS. When the finalization macro is triggered, the following
actions are performed:
•
•
•
•
•
•

Recalculates the formula cells within the MLR working file. Note that the finalized file
will retain the workbook formulas.
Checks any required fields (Section 1: Contract Number, Organization Name, and
Contact information) that must be entered for finalization to be successful.
Checks any critical validations of data fields.
Saves the working file.
Creates a backup file – this is a read-only file that contains the same data as the working
file; it can be used to restore data in a working file.
Creates a finalized file with a date stamp on Line 4 of the ‘Data’ worksheet.

Finalized MLR workbooks are saved using the following naming convention: Contract
Number+MLR-CY+yyyy.xlsx. Use of this convention is a requirement for a successful upload to
the HPMS.
Example: H1111MLR-CY2015.xlsx
Finalized files are saved in the same directory where the working file is located.
Backup files use the same naming convention as finalized files with a timestamp appended to the
end of the name: finalized filename +“_Backup_”+YYYY-MM-DD-HHmmss.xlsm.
Example: H1111MLR-CY2015_Backup_2015-11-15-100000.xlsm
Back up files are saved in the same directory where the working file is located.
If additional changes are needed prior to submission (i.e., prior to upload to HPMS), modify the
contents of the working file and finalize the file again. The previous finalized file will be
overwritten and a new backup file will be created (backup files will not be overwritten as they
are time-stamped).
In the instance that the working copy has become corrupted, the backup file may be renamed and
used as the working copy. Removing the word ‘backup’ from the filename of the backup file will
convert the file into a working copy that is read-write enabled.

Page 31 of 33

Below are screen-shots regarding where to locate the Finalize macro within the MLR Report
workbook:
1. Select the “Add-Ins” menu from the top ribbon in Excel:

2. Select the “Finalize_MLR” action. Address any validation issues and then finalize.

Circle Invalid
This macro function displays red circles around cells that have failed validation. The validations
are updated each time the file is saved, and when the “Circle Invalid” macro is run.
Validation Report
This macro function displays a worksheet that lists any validation errors. Visually impaired users
will be able to use the validation report with textual readers to determine cells requiring
correction. The ‘Validation Report’ worksheet is separated into an Active and Failed validation
report.
•
•

Active Cell Validation Report – displays the worksheet name, cell location, cell value,
and validation criteria for the cell the user currently has selected. Critical validations are
indicated by an additional column in the report.
Failed Validation Report – displays the worksheet name, cell location, cell value, and
validation criteria for all cells failing validation. Critical validations are indicated by an
additional column in the report.

Page 32 of 33

Hide/Unhide Validation Cell Labels
This macro function hides or displays tooltip labels for validated cells when the user selects a
cell using the keyboard or cursor. Cell labels display the cell type (Input/Formula) and a short
description of the required contents. By default, the validation messages macro is enabled,
allowing the user to view information about the cells that are validated by the tool.
Hide/Unhide Non-Validation Cell Labels
This macro function hides or displays tooltip labels for non-validated cells when the user selects
a cell using the keyboard or cursor. Cell labels display the cell type (Input/Formula) and a short
description of the required contents. This macro is disabled by default, hiding information about
the cells that are not validated by the tool.
Hide/Unhide Formula Worksheet
This macro function hides or displays the Formula worksheet that contains the formulas used to
calculate the white cell values. By default, the MLR Formula worksheet is hidden.

Page 33 of 33