Contents Show
The Maximum Reimbursement Amount Spreadsheet (Output) can be used to assess vendor reimbursements or to determine if the vendor is placed in the correct peer group. The main purpose of the spreadsheet is to provide the summary information for the selected food instrument type, counts and dollars, max price and peer group information. It is then up to the user to work with the information on the spreadsheet, to draw upon information from other reports and to create additional worksheets and spreadsheets. The spreadsheet will print in landscape format to allow for as many peer group columns as possible. The report is generated when the OK button is clicked on the Generate Maximum Reimbursement Amount Spreadsheet screen.
This spreadsheet shows the food instrument type and the latest Price in effect for the month. The food instrument type may be entered on the screen or taken from the paid food instrument serial number entered on the screen. If the food instrument type was entered on the screen, the latest price in effect for the month is based on the month and year selected. If food instrument serial number was entered on the screen the food instrument type is taken from the paid food instrument using the serial number and then the latest price in effect for the month is based on the month and year of the food instrument paid date month and year.
The maximum reimbursement value is the latest maximum peer group price established for the selected month.
The total food instruments redeemed is a count of all food instruments paid in the selected month having the selected food instrument type.
The total food instruments redeemed value is the sum of paid amounts for all food instruments paid in the selected month having the selected food instrument type.
The "Measure" section on the spreadsheet repeats the totals for all food instruments paid in the selected month for all vendors. It also includes a count and sum of the paid food instruments in the selected month for each peer group along with the latest average peer group price established for the selected month plus that peer group's standard deviation.
The Number of Food Instruments Redeemed that Exceed the Amount in Item 7 is a count of the food instruments paid when the paid amount exceeded the maximum reimbursement price.
The Value of Excess Redemption Amount for Food Instruments in Item 7 is the sum of the difference for each food instrument that was paid over the maximum reimbursement price.
A few excerpts were copied nearly verbatim from Vendor Cost Containment documentation to describe the purpose and use of this spreadsheet:
Section 246.2 of the WIC program regulations defines a "price adjustment" as "an adjustment made by the State agency, in accordance with the vendor agreement to the purchase price on a food instrument after it has been submitted by a vendor for redemption to ensure that the payment to the vendor of the food instrument complies with the State agency's price limitations." Any amount that exceeds the maximum allowable reimbursement level for an individual food instrument (or food item if used as the basis for reimbursement) is an unallowable amount that the State agency must recoup or use to offset to future payment to the vendor.
Compute the difference between the price on each redeemed food instrument (by type) and its maximum allowable reimbursement level, and then add these differences (positive and negative). If the sum of the differences indicates an overpayment, recoup this amount from the vendor.
Multiply the maximum allowable amount by the number of food instruments (by type) redeemed to determine how much the State agency could have paid the vendor for the redeemed food instruments. Subtract from this amount the sum of the actual prices of the food instruments redeemed. If the sum of the food instruments exceeds the aggregate maximum allowable amount for all of the food instruments, recoup the difference from the vendor.
State agencies must employ effective methodologies to ensure that payments made to vendors for individual food instruments (or individual food items) do not exceed the maximum allowable reimbursement level established for the food instrument.
Vendor's shelf prices and redemption data should be used to establish competitive prices and maximum reimbursement levels. State agencies must base competitive price criteria and maximum reimbursement levels on the prices of regular vendors only, and not on prices charged by above-50-percent vendors.
Applying or using a standard deviation to establish peer groups will allow for natural variation in distribution of prices, versus using a fixed percentage to calculate competitive peer group prices. If the distribution of prices between peer groups or food instruments is normal, the standard deviation will capture the variation and allow for the inclusion of appropriate vendors in a per group and the exclusion of inappropriate vendors.
If a state establishes a competitive price criterion that is equal to a peer group average plus or minus one or two standard deviations, it can ensure that adding vendors that met this criterion will not change the peer group average in manner that in statistically significant.
Having 30 or more vendors in a peer group increases the likelihood the prices of the vendors in the group will approximate a normal distribution. Try to ensure the distribution is not skewed by the high or low prices of one or two vendors. If the mean and standard deviation are influenced by on ore more outliers, this could make it difficult to rely on the statistical properties of a normal distribution.
State agencies can also rely on the properties of the normal distribution to ensure that maximum allowable prices are not set at levels that incorrectly exclude a vendor or that encourage vendors to raise prices to the maximum allowable level. Since 99.85 of observations will fall within three standard deviations of the mean, nearly every vendor's prices is encompassed by a range that is equal to the average plus or minus three standard deviations. For state agencies that fine that they have vendors that fall outside three standard deviations, or wish to include a measure that will allow for a small amount of price inflation, a maximum allowable price that is equal to the peer group mean (average) plus four standard deviations might be appropriate. All standard statistical packages calculate the mean and standard deviations, as does Microsoft Excel. State agencies simply need to take the result of these calculates and apply them to the mean (average) peer group price.
This section describes the data elements that print on the system output. Because of space constraints, the layout may be slightly different and/or all characters may not print within the space allotted.
Origin of Data Element
The text for the spreadsheet title is "Maximum Reimbursement Amounts Spreadsheet". The sub-title is "(Worksheet for Assessing Statewide Maximum Reimbursement Amount)" to identify the original cost containment attachment in the vendor guide.
Format
The title cell value will print as a literal value. The sub-title cell value will print as a literal value.
Origin of Data Element
The system date the spreadsheet is generated.
Format
The date cell value will print in the format MM/DD/CCYY.
Origin of Data Element
The user records this value when the spreadsheet is submitted.
Format
This is an 8-character value.
Origin of Data Element
If the FI Type (food instrument type) was entered on the screen, the user records this value when the spreadsheet is submitted.
If food instrument serial number was entered on the screen the food instrument type is taken from the paid food instrument using the serial number. The FOODINSTRUMENTTYPE is taken from the FOODINSTRUMENT table for the SerialNo.
Format
This is a 10-digit value.
Origin of Data Element
The user records this value when the spreadsheet is submitted. This is an accounting month and year.
If the FI Type (food instrument type) was entered on the screen, the "Month of" contains the month and year values selected on the screen.
If food instrument serial number was entered on the screen the food instrument type is taken from the paid food instrument using the serial number so the "Month of" becomes the month and year of the food instrument paid date. The FOODINSTRUMENTTYPE is taken from the FOODINSTRUMENT table for the SerialNo.
Format
The month of cell value will print in the format
"{Textual Month} CCYY}".
Origin of Data Element
All of the food distribution item descriptions on the food instrument type are included. List each Description in the FOODDISTRIBUTIONITEM table for each of the items in the FOODINSTRUMENTTYPEITEMS table for the selected FIType in the FOODINSTRUMENTTYPE table.
Format
This is a 600-character value. The cell is set to wrap to accommodate up to eight items on one food instrument and a food distribution item description length of 60, plus the quantity, commas and spaces.
Origin of Data Element
This spreadsheet cell value is the maximum price found in the PEERGROUPFOODINSTTYPEPRICEHIST table for the reporting Month/Year for any peer group. This is the latest price sent to the bank during the reporting month.
The PRICE is taken from the PEERGROUPFOODINSTTYPEPRICEHIST table.
Format
This is a 6-digit value.
Origin of Data Element
This spreadsheet cell value is a count of all food instruments redeemed during the reporting Month/Year.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year.
Format
This is an 11-digit value.
Origin of Data Element
This spreadsheet cell value is the sum of the paid amounts of all food instruments redeemed during the reporting Month/Year.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year.
Format
This is an 11-digit value.
Origin of Data Element
This spreadsheet cell value is manually entered by the user. It is not generated by the system.
Format
This is not valued.
This section repeats the totals under the All Vendors column for items 1, 2, and 3. The Maximum reimbursement amount value is also reused many times on the worksheet. Formulas is used in the template. For example the maximum reimbursement amount is the maximum peer group price. If the user wants to change the value in the spreadsheet, they should be allowed to change it once in one cell and have the spreadsheet adjust.
Origin of Data Element
Cell = Maximum Reimbursement Amount like named section above
This spreadsheet cell value is the maximum price found in the PEERGROUPFOODINSTTYPEPRICEHIST table for the reporting month/year for any peer group. This is the latest price sent to the bank during the reporting month.
The PRICE is taken from the PEERGROUPFOODINSTTYPEPRICEHIST table.
Format
This is a 6-digit value.
Origin of Data Element
Cell = Total Food Instruments Redeemed like named section above
This spreadsheet cell value is a count of all food instruments redeemed during the reporting month/year.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year.
Format
This is an 11-digit value.
Origin of Data Element
Cell = Total Redemptions like named section above
This spreadsheet cell value is the sum of the paid amounts of all food instruments redeemed during the reporting month/year.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year.
Format
This is an 11-digit value.
Origin of Data Element
This spreadsheet cell value is the AVGPRICE in the PEERGROUPFOODINSTTYPEPRICEHIST table for the reporting month/year. This is the latest average price calculated for the peer group during the reporting month.
Format
This is a 6-digit value.
Origin of Data Element
This spreadsheet cell value is the STANDARDDEVIATION in the PEERGROUP table for this peer group.
Format
This is a 5-digit value.
Origin of Data Element
This spreadsheet cell value is a formula in the spreadsheet. All standard statistical packages calculate the mean and standard deviations, as does Microsoft Excel. State agencies simply need to take the result of these calculations and apply them to the mean (average) peer group price.
This formula is supplied with the cost containment guide template:
=($C$14-$C$17)/$C$18
All Vendors Maximum Reimbursement Amount - All Vendors Average Food Instrument Amount (mean) divided by All Vendors Standard Deviation
The spreadsheet formulas may be modified by the user after the spreadsheet is generated.
Format
This is a 7-digit value.
Origin of Data Element
This spreadsheet cell value is a formula in the spreadsheet.
This formula is supplied with the cost containment guide template:
=C17+(C19*C18)
All Vendors Average Food Instrument Amount (mean) plus
(All Vendors Std. Dev. of State Maximum Allowable Amount multiplied by All Vendors Standard Deviation)
The spreadsheet formulas may be modified by the user after the spreadsheet is generated.
Format
This is a 7-digit value.
Origin of Data Element
This spreadsheet cell value is the count of each individual food instrument paid when the paid amount exceeded the maximum reimbursement price for this peer group during the reporting month/year.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year and in this PEERGROUP on the FOODINSTRUMENT table and the PAIDAMT exceeds the PRICE in the PEERGROUPFOODINSTTYPEPRICEHIST table for this peer group.
Format
This is an 11-digit value.
Origin of Data Element
This spreadsheet cell value is the sum of the difference for each individual food instrument that was paid over the maximum reimbursement price for this peer group during the reporting month/year.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year and in this PEERGROUP on the FOODINSTRUMENT table and the PAIDAMT exceeds the PRICE in the PEERGROUPFOODINSTTYPEPRICEHIST table for this peer group.
Format
This is an 11-digit value.
Origin of Data Element
The peer group code is taken from PEERGROUP in the PEERGROUP table.
Format
This is a 2-character value. (e.g., shown as '01')
Origin of Data Element
Cell = Maximum Reimbursement Amount like named section above
This spreadsheet cell value is the PRICE found in the PEERGROUPFOODINSTTYPEPRICEHIST table for the reporting month/year in this peer group.
Format
This is a 6-digit value.
Origin of Data Element
This spreadsheet cell value is a count of the food instruments redeemed during the reporting month/year in this peer group.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year and in this PEERGROUP on the FOODINSTRUMENT table.
Format
This is an 11-digit value.
Origin of Data Element
This spreadsheet cell value is the sum of the paid amounts of all food instruments redeemed during the reporting month/year in this peer group.
Include all paid food instruments from the FOODINSTRUMENT table where the FOODINSTRUMENTTYPE matches the reporting FIType and the PAIDDATE is in the reporting month/year and in this PEERGROUP on the FOODINSTRUMENT table.
Format
This is an 11-digit value.
Origin of Data Element
This spreadsheet cell value is the AVGPRICE in the PEERGROUPFOODINSTTYPEPRICEHIST table for the reporting month/year in this peer group.
Format
This is a 6-digit value.
Origin of Data Element
This spreadsheet cell value is the STANDARDDEVIATION in the PEERGROUP table for this peer group.
Format
This is a 5-digit value.
Origin of Data Element
This spreadsheet cell value is a formula in the spreadsheet. All standard statistical packages calculate the mean and standard deviations, as does Microsoft Excel. State agencies simply need to take the result of these calculations and apply them to the mean (average) peer group price.
This formula was supplied with the template included in the guidelines:
=($C$14-$C$17)/$C$18
The spreadsheet formulas may be modified by the user after the spreadsheet is generated.
C14=Maximum Reimbursement Amount-All Vendors
C17=Average Food Instrument Amount (mean)-All Vendors
C18=Standard Deviation-All Vendors
Format
This is a 7-digit value.
Origin of Data Element
This spreadsheet cell value is a formula in the spreadsheet.
This formula is supplied with the cost containment guide template:
=D17+($C$19*D18)
=E17+($C$19*E18)
=F17+($C$19*F18)
Continue the formula for each peer group column.
Peer Group's Average Food Instrument Amount (mean) plus (All Vendors = Std. Dev. of State Maximum Allowable Amount multiplied by the Peer Group's Standard Deviation)
C19= All Vendors = Std. Dev. of State Maximum Allowable Amount
D17= Peer Group = Average Food Instrument Amount (mean)
E17= Peer Group = Average Food Instrument Amount (mean)
F17= Peer Group = Average Food Instrument Amount (mean)
D18= Peer Group = Standard Deviation
E18= Peer Group = Standard Deviation
F18= Peer Group = Standard Deviation
The spreadsheet formulas may be modified by the user after the spreadsheet is generated.
Format
This is a 7-digit value.
The worksheet is based on all food instruments redeemed during the selected month for the selected Food Instrument Type.
|
Software Version: 2.40.00