Cost Containment Certification Spreadsheet (Output)

The Cost Containment Certification Spreadsheet (Output) verifies that the State agency that chooses to authorize above 50-percent vendors meets the requirement in section 246.12(g)(4)(vi). This requirement is one of two cost neutrality requirements contained in the Child Nutrition and WIC Reauthorization Act of 2004. Section 246.12(g)(4)(vi) prescribes that cost neutrality is assess by comparing the average payment per food instrument (by type) made to above 50-percent to the average payment per food instrument (by type) to comparable regular vendors. Average payments to above 50-percent vendors may not exceed average payments to comparable regular vendors. Achieving cost neutrality begins with the skeleton of vendors that offer the program the most competitive prices in their geographic area. It also includes the application of reimbursement methods that limit payments to levels consistent with the competitive price levels applied to the vendors at authorization.

During the certification process, a State agency demonstrates to FNS that it has implemented methods that distinguish above 50-percent vendors from regular vendors at authorization; that it bases competitive price criteria and maximum allowable reimbursement amounts on the prices of regular vendors; that it applies appropriate reimbursement limits during the food instrument redemption process and makes price adjustments to any payments that exceed the maximum allowable reimbursement level; and that it reimburses above 50-percent vendors at a level that should achieve overall cost neutrality under section 246.12(g)(4)(I)(D) of the Interim Rule. Obtaining FNS certification does not guarantee that a State agency will achieve cost neutrality. Achieving cost neutrality requires the effective ongoing application of competitive pricing methods.

The report is generated when the OK button is clicked on the Generate Cost Containment Certification Spreadsheet screen. A few excerpts were copied nearly verbatim from Vendor Cost Containment documentation to describe the purpose and use of this spreadsheet:

If the State agency elects to authorize above 50-percent vendors this EXCEL spreadsheet can be generated and completed. This EXCEL spreadsheet provides the format necessary to collect vendor cost containment certification information and to demonstrate that the State agency's competitive price criteria and allowable reimbursement levels do not result in average payments per food instrument (by type) to above 50-percent vendors that are higher than average payments per food instrument (by type) to comparable vendors that are not above 50-percent vendors.

The spreadsheet contains three worksheets.

Data Elements - Worksheet 1 = Profile of Vendors

The first worksheet is a profile of vendors. It will include the counts of authorized vendors and the amounts of WIC redemptions paid during the selected reporting month.

Data Elements - Worksheet 2 = Peer Group Structure

The second worksheet is a peer group structure. It will include each peer group number, peer group description, the counts of authorized vendors per peer group.

See Data Elements - Worksheet 3 = Average Payments:

The third worksheet is a detailing of average payments. It includes average payments for each peer group that has above-50% vendors.

Data Elements - Worksheet 1 = Profile of Vendors

This section describes the data elements that will print on the spreadsheet.

Spreadsheet Title

Origin of Data Element

The text for the spreadsheet title is "WIC Vendor Cost Containment Certification".

Format

The title cell value will print as a literal value.

Run Date:

Origin of Data Element

The system date the spreadsheet is generated.

Format

The date cell value will print in the format MM/DD/CCYY.

Number of authorized regular vendors as of (Month of):

Origin of Data Element

This spreadsheet cell value is a count of all authorized regular vendors who were active during the reporting month.

If the selected reporting month (month of) is equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and the currently enrolled flag is set to "Y" and an active status, 3= 'enrolled' or 7= 'probation'.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The currently enrolled flag is taken from CURRENTLYENROLLED of the VENDOR table.

The current status is taken from CURRENTSTATUS of the VENDOR table

If the selected reporting month (month of) is NOT equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and where there are no termination or disqualification events or follow-up activities in the reporting month.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The event start date is taken from the STARTDATE of the EVENT table. (STARTDATE <= '10/30/2006')

The event or follow up activity start date is taken from the STARTDATE of the FOLLOWUPACTIVITY table. (STARTDATE <= '10/30/2006')

The event or follow up activity type is taken from the TYPE in either the EVENT table or the FOLLOWUPACTIVITY table where the TYPE = "8" or "25" (Termination, Disqualification). There could be an event, a follow-up activity, both or neither.

If the difference between the sales Annual WIC Food Sales and the Annual Food Sales for the vendor is less than or equal to half the Annual Food Sales count this vendor as a regular vendor.

• Multiply the sales Annual Food Sales by 50% (divide by 2) for each vendor.

• Subtract the sales Annual WIC Food Sales from the Annual Food Sales for each vendor to get the difference.

The Annual Food Sales is taken from the ANNUALFOODSALES column in the VENDOR table.

The Annual WIC Food Sales is taken from the ANNUALWICFOODSALES column in the VENDOR table.

Format

This is a 7-digit value.

Amount of WIC redemptions paid to authorized regular vendors as of (Month of):

Origin of Data Element

This spreadsheet cell value is a count of all food instruments redeemed during the reporting month/year for all vendors determined to be regular vendors.

Include all paid food instruments from the FOODINSTRUMENT table where the PAIDDATE is in the reporting month/year and the VENDORID is not identified as an above 50-percent vendor.

Format

This is an 11-digit value.

Number of authorized above 50-percent vendors as of (Month of):

Origin of Data Element

This spreadsheet cell value is a count of all authorized above 50-percent vendors who were active during the reporting month.

If the selected reporting month (month of) is equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and the currently enrolled flag is set to "Y" and an active status, 3= 'enrolled' or 7= 'probation'.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The currently enrolled flag is taken from CURRENTLYENROLLED of the VENDOR table.

The current status is taken from CURRENTSTATUS of the VENDOR table

If the selected reporting month (month of) is NOT equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and where there are no termination or disqualification events or follow-up activities in the reporting month.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The event start date is taken from the STARTDATE of the EVENT table. (STARTDATE <= '10/30/2006')

The event or follow up activity start date is taken from the STARTDATE of the FOLLOWUPACTIVITY table. (STARTDATE <= '10/30/2006')

The event or follow up activity type is taken from the TYPE in either the EVENT table or the FOLLOWUPACTIVITY table where the TYPE = "8" or "25" (Termination, Disqualification). There could be an event, a follow-up activity, both or neither.

If the difference between the sales Annual WIC Food Sales and the Annual Food Sales for the vendor is greater than half the Annual Food Sales count this vendor as an above 50-percent vendor.

• Multiply the sales Annual Food Sales by 50% (divide by 2) for each vendor.

• Subtract the sales Annual WIC Food Sales from the Annual Food Sales for each vendor to get the difference.

The Annual Food Sales is taken from the ANNUALFOODSALES column in the VENDOR table.

The Annual WIC Food Sales is taken from the ANNUALWICFOODSALES column in the VENDOR table.

Format

This is a 7-digit value.

Number of non-pharmacy above 50-percent vendors:

Origin of Data Element

This spreadsheet cell value is a formula equal to the sum of the cell values for Number of WIC-only stores and Number of other types of above 50-percent vendors.

Format

This is a 7-digit value.

Number of WIC-only stores:

Origin of Data Element

This spreadsheet cell value is a count of the vendors determined to be above 50-percent vendors having a vendor type equal to "W" (WIC-Only Store).

The vendor type is taken from the VENDORTYPE column in the STORETYPE table. The vendor types are TIP report vendor definitions (See Reference Dictionary for INTERNALVNDRTYP)

The STORETYPE is taken from the STORETYPE column in the VENDOR table. Each vendor has a store type designation.

Format

This is a 7-digit value.

Number of other types of above 50-percent stores:

Origin of Data Element

This spreadsheet cell value is a count of the vendors determined to be above 50-percent vendors having a vendor type not equal to "W" (WIC-Only Store) and not equal to "P" (Pharmacy).

The vendor type is taken from the VENDORTYPE column in the STORETYPE table. The vendor types are TIP report vendor definitions (See Reference Dictionary for INTERNALVNDRTYP)

The STORETYPE is taken from the STORETYPE column in the VENDOR table. Each vendor has a store type designation.

Format

This is a 7-digit value.

Number of pharmacy above 50-percent stores:

Origin of Data Element

This spreadsheet cell value is a count of the vendors determined to be above 50-percent vendors having a vendor type equal to "P" (Pharmacy).

The vendor type is taken from the VENDORTYPE column in the STORETYPE table. The vendor types are TIP report vendor definitions (See Reference Dictionary for INTERNALVNDRTYP)

The STORETYPE is taken from the STORETYPE column in the VENDOR table. Each vendor has a store type designation.

Format

This is a 7-digit value.

Total number of above 50-percent vendors (sum of a and b and = 3):

Origin of Data Element

This spreadsheet cell value is a formula equal to the sum of the cell values for Number of non-pharmacy above 50-percent vendors and Number of pharmacy above 50-percent vendors.

Format

This is a 7-digit value.

Amount of WIC redemptions paid to these above 50-percent vendors as of (Month of):

Origin of Data Element

This spreadsheet cell value is a count of all food instruments redeemed during the reporting month/year for all vendors determined to be above 50-percent vendors.

Include all paid food instruments from the FOODINSTRUMENT table where the PAIDDATE is in the reporting month/year and the VENDORID is identified as an above 50-percent vendor.

Format

This is an 11-digit value.

Amount of WIC redemptions paid to non-pharmacy above 50-percent vendors:

Origin of Data Element

This spreadsheet cell value is a count of all food instruments redeemed during the reporting month/year for all vendors determined to be above 50-percent vendors having a vendor type not equal to "P" (Pharmacy).

Include all paid food instruments from the FOODINSTRUMENT table where the PAIDDATE is in the reporting month/year and the VENDORID is identified as an above 50-percent vendor.

The vendor type is taken from the VENDORTYPE column in the STORETYPE table. The vendor types are TIP report vendor definitions (See Reference Dictionary for INTERNALVNDRTYP)

The STORETYPE is taken from the STORETYPE column in the VENDOR table. Each vendor has a store type designation.

Format

This is an 11-digit value.

Amount of WIC redemptions paid to pharmacy above 50-percent vendors:

Origin of Data Element

This spreadsheet cell value is a count of all food instruments redeemed during the reporting month/year for all vendors determined to be above 50-percent vendor having a vendor type equal to "P" (Pharmacy).

Include all paid food instruments from the FOODINSTRUMENT table where the PAIDDATE is in the reporting month/year and the VENDORID is identified as an above 50-percent vendor.

The vendor type is taken from the VENDORTYPE column in the STORETYPE table. The vendor types are TIP report vendor definitions (See Reference Dictionary for INTERNALVNDRTYP)

The STORETYPE is taken from the STORETYPE column in the VENDOR table. Each vendor has a store type designation.

Format

This is an 11-digit value.

Total amount of WIC redemptions paid to above 50-percent vendors (sum of a and b and = 4):

Origin of Data Element

This spreadsheet cell value is a formula equal to the sum of the cell values for Amount of WIC redemptions paid to non-pharmacy above 50-percent vendors and Amount of WIC redemptions paid to pharmacy above 50-percent vendors.

Format

This is a 7-digit value.

Number of peer groups with above 50-percent stores (either separate peer groups or groups with regular vendors):

Origin of Data Element

This spreadsheet cell value is a count of the peer groupings determined to contain at least one above 50-percent vendor.

The peer group is taken from the current PEERGROUP column in the VENDOR table. Each vendor has a peer group designation.

Format

This is a 7-digit value.

Number of vendors that do not meet competitive price criteria, but are needed to ensure participant access to supplemental foods):

Origin of Data Element

This spreadsheet cell value is a formula equal to the sum of the cell values for Number of regular vendors and Number of above 50-percent vendors.

Format

This is a 7-digit value.

Number of regular vendors:

Origin of Data Element

This spreadsheet cell value is manually supplied by the user.

Format

This is a 7-digit value.

Number of above 50-percent vendors:

Origin of Data Element

This spreadsheet cell value is manually supplied by the user.

Format

This is a 7-digit value.

Filter Criteria

Base of Data

The spreadsheet is based on authorized vendors and all food instruments redeemed during the selected month.

Sort Order

Peer Group

No sort has been defined for this worksheet.

Control Breaks

Peer Group

A detail line is printed when the peer group changes.

Grand Total

No grand total has been defined for this worksheet.

Data Elements - Worksheet 2 = Peer Group Structure

This section describes the data elements that will print on the report.

Spreadsheet Title

Origin of Data Element

The text for the spreadsheet title is "WIC Vendor Cost Containment Certification".

Format

The title cell value will print as a literal value.

Run Date:

Origin of Data Element

The system date the spreadsheet is generated.

Format

The date cell value will print in the format MM/DD/CCYY.

Peer Group

Origin of Data Element

The peer group code is taken from PEERGROUP column of the PEERGROUP table.

Format

This is a 2-character value. (e.g., shown as '01', '02', etc.)

Peer Group Description

Origin of Data Element

The peer group code is taken from the VENDOR table and used to print the associated peer group DESCRIPTION from the PEERGROUP table.

Format

This is a 50-character description.

Number of Vendors in Peer Group - Regular Vendors

Origin of Data Element

This spreadsheet cell value is a count of all authorized regular vendors by peer group who were active during the reporting month.

If the selected reporting month (month of) is equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and the currently enrolled flag is set to "Y" and an active status, 3= 'enrolled' or 7= 'probation'.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The currently enrolled flag is taken from CURRENTLYENROLLED of the VENDOR table.

The current status is taken from CURRENTSTATUS of the VENDOR table

If the selected reporting month (month of) is NOT equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and where there are no termination or disqualification events or follow-up activities in the reporting month.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The event start date is taken from the STARTDATE of the EVENT table. (STARTDATE <= '10/30/2006')

The event or follow up activity start date is taken from the STARTDATE of the FOLLOWUPACTIVITY table. (STARTDATE <= '10/30/2006')

The event or follow up activity type is taken from the TYPE in either the EVENT table or the FOLLOWUPACTIVITY table where the TYPE = "8" or "25" (Termination, Disqualification). There could be an event, a follow-up activity, both or neither.

If the difference between the sales Annual WIC Food Sales and the Annual Food Sales for the vendor is less than or equal to half the Annual Food Sales count this vendor as a regular vendor.

• Multiply the sales Annual Food Sales by 50% (divide by 2) for each vendor.

• Subtract the sales Annual WIC Food Sales from the Annual Food Sales for each vendor to get the difference.

The Annual Food Sales is taken from the ANNUALFOODSALES column in the VENDOR table.

The Annual WIC Food Sales is taken from the ANNUALWICFOODSALES column in the VENDOR table.

The PEERGROUP is taken from the VENDOR table.

Format

This is a 7-digit value.

Number of Vendors in Peer Group - Above 50% Vendors

Origin of Data Element

This spreadsheet cell value is a count of all authorized above 50-percent vendors by peer group who were active during the reporting month.

If the selected reporting month (month of) is equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and the currently enrolled flag is set to "Y" and an active status, 3= 'enrolled' or 7= 'probation'.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The currently enrolled flag is taken from CURRENTLYENROLLED of the VENDOR table.

The current status is taken from CURRENTSTATUS of the VENDOR table

If the selected reporting month (month of) is NOT equal to the current system month, include all vendors from the vendor table where the initial contract date is less than or equal to the last day of the reporting month and where there are no termination or disqualification events or follow-up activities in the reporting month.

The initial contract date is taken from the DATEINITCONTRACT of the VENDOR table. (DATEINITCONTRACT <= '10/30/2006)'

The event start date is taken from the STARTDATE of the EVENT table. (STARTDATE <= '10/30/2006')

The event or follow up activity start date is taken from the STARTDATE of the FOLLOWUPACTIVITY table. (STARTDATE <= '10/30/2006')

The event or follow up activity type is taken from the TYPE in either the EVENT table or the FOLLOWUPACTIVITY table where the TYPE = "8" or "25" (Termination, Disqualification). There could be an event, a follow-up activity, both or neither.

If the difference between the sales Annual WIC Food Sales and the Annual Food Sales for the vendor is greater than half the Annual Food Sales count this vendor as an above 50-percent vendor.

• Multiply the sales Annual Food Sales by 50% (divide by 2) for each vendor.

• Subtract the sales Annual WIC Food Sales from the Annual Food Sales for each vendor to get the difference.

The Annual Food Sales is taken from the ANNUALFOODSALES column in the VENDOR table.

The Annual WIC Food Sales is taken from the ANNUALWICFOODSALES column in the VENDOR table.

The PEERGROUP is taken from the VENDOR table.

Format

This is a 7-digit value.

Number of Vendors in Peer Group - Total

Origin of Data Element

This spreadsheet cell value is a formula equal to the sum of the cell values for Number of Vendors in Peer Group - Regular Vendors and Number of Vendors in Peer Group - Above 50% Vendors.

Format

This is a 7-digit value.

Filter Criteria

Base of Data

The worksheet is based on peer groupings and the counts of authorized vendors during the selected month.

Sort Order

Peer Group

This worksheet is sorted first by Peer Group.

Control Breaks

Peer Group

A detail line is printed on this worksheet when the peer group changes.

Data Elements - Worksheet 3 = Average Payments

This section describes the data elements that will print on the report.

Spreadsheet Title

Origin of Data Element

The text for the spreadsheet title is "WIC Vendor Cost Containment Certification".

Format

The title cell value will print as a literal value.

Run Date:

Origin of Data Element

The system date the spreadsheet is generated.

Format

The date cell value will print in the format MM/DD/CCYY.

Month of:

Origin of Data Element

The user records this value when the spreadsheet is submitted. This is an accounting month and year.

All authorized vendors and all food instruments redeemed during this month are selected for this report.

Format

The month of cell value will print in the format

"{Textual Month} CCYY}".

Peer Group

Origin of Data Element

The peer group code is taken from PEERGROUP column of the PEERGROUP table.

Format

This is a 2-character value. (e.g., shown as '01', '02', etc.)

Peer Group Description

Origin of Data Element

The peer group code is taken from the VENDOR table and used to print the associated peer group DESCRIPTION from the PEERGROUP table.

Format

This is a 50-character description.

Food Instrument Type ID

Origin of Data Element

The food instrument ID of food instruments having a paid date within the specified reporting period.

Food Instrument Type Description

Origin of Data Element

The unique combination of quantities and food distribution item descriptions associated with the Food Instrument Type being reported. The quantity values come from the food instrument type item table entries linked to the food instrument type being reported. The descriptions come from the food distribution item table linked by the distribution item ID in the food instrument type item table.

Format

The quantity and food distribution item descriptions will be concatenated into a text string in the format of "Quantity Description; Quantity Description…"

Number of Food Instruments Redeemed

Origin of Data Element

A count of all food instruments with a paid date in the selected reporting date range.

Above 50% Vendors

Price

The calculated average price of all food instruments having FI type ID shown in column A selected for the report and redeemed by an above 50% vendor (Vendor.Above50PercentVendor = "Y")

The calculation will be done using the T-SQL AVG function for the paid amount of the selected food instruments.

Standard Deviation

The calculated standard deviation of all food instruments having FI type ID shown in column A selected for the report and redeemed by an above 50% vendor (Vendor.Above50PercentVendor ="Y")

The calculation will be done using the T-SQL STDEV function for the paid amount of the selected food instruments.

Comparable Regular Vendors

Price

The calculated average price of all food instruments having FI type ID shown in column A selected for the report and redeemed by a regular vendor (Vendor.Above50PercentVendor = "N")

The calculation will be done using the T-SQL AVG function for the paid amount of the selected food instruments.

Standard Deviation

The calculated standard deviation of all food instruments having FI type ID shown in column A selected for the report and redeemed by an above 50% vendor (Vendor.Above50PercentVendor = "N")

The calculation will be done using the T-SQL STDEV function for the paid amount of the selected food instruments.

Difference in Average Redemption Amount Between Above 50% Vendors and Comparable Regular Vendors

Origin of Data Element

The calculation will be average redemption amount of above 50% vendors minus the average redemption amount of regular vendors; Price from column E minus Price from column G.

Average Redemption Price per Food Instrument Type (after apply revised competitive price criteria and allowable reimbursement levels)

Above 50% Vendors

This spreadsheet cell value is manually supplied by the user.

Comparable Regular Vendors

This spreadsheet cell value is manually supplied by the user.

Filter Criteria

Food instruments having a paid date within the selected ate range of the report will be included.