Contents Show
Each High Risk Criterion follows with a Name, text description and the mathematical formula that will be used to calculate the automatically assigned criterion. The text description is the initial value of the criterion description and will serve as the text entered when the Restore Description button is clicked on the Edit High Risk Criterion screen. The initial value and comparison operator are defined for each and these items will be inserted into the High Risk Criteria screen by default. The automatically assigned risk indicators only evaluate Food Instrument Types that are marked as being included in the High Risk Analysis.
The Data Elements are calculated by sp_Vendor_GetRiskFactors Proc.
Description: The value of each FI check for each vendor is evaluated to determine the co-efficient of variance, defined as: CV = (SD/Mean) * 100 where SD is standard deviation and the mean is the numerical average. Vendors with a CV value of less than 10% are considered potentially risky.
Formula: Average of the CV for all FI Types for the vendor.
Initial value: 10.0 Comparer: <
STDEV(paidamt) / AVG(paidamt) * 100
PAIDAMT is retrieved from the FOODINSTRUMENT table.
and sum (FOODINSTRUMENTITEMREDEEMED.PAIDAMT) (union of these two values).
Description: Very low variation in the percentage difference between the check amount and the maximum allowable reimbursement amount. When the vendor's average percentage difference between the MAR and the actual check amount is less than 5%, the vendor is considered potentially risky.
Note: The MAR is set differently by each state, but once established does not impact this calculation.
Formula: Average of the (MAR-Paid Amt.) / MAR for all FI Types for the vendor.
Initial value: 5.0 Comparer: <
AVG((OBLIGATIONAMT - PAIDAMT) / OBLIGATIONAMT) * 100
OBLIGATIONAMT is retrieved from the FOODINSTRUMENT table.
PAIDAMT is retrieved from the FOODINSTRUMENT table.
and sum (FOODINSTRUMENTITEMREDEEMED.PAIDAMT) (union of these two values).
Description: Unusually large percentage of food instruments redeemed at the same price or within a narrow range. Each vendor's FI check prices are grouped into ranges. If 90% or more of the vendor's checks are redeemed in the same range, then the vendor is considered potentially risky.
Formula: Each FI Type is examined and the prices are grouped into $.25 ranges. If 90% of the vendor’s checks fall within 1 grouping, the indicator is noted.
Initial value: 90.0 Comparer: >
AVG((OBLIGATIONAMT - PAIDAMT) / OBLIGATIONAMT) * 100
OBLIGATIONAMT is retrieved from the FOODINSTRUMENT table.
PAIDAMT is retrieved from the FOODINSTRUMENT table.
and sum (FOODINSTRUMENTITEMREDEEMED.PAIDAMT) (union of these two values)
Description: Large variances in WIC check prices for formula food prices. The price of the Formula FI checks for each vendor are evaluated to determine the co-efficient of variance, defined as: CV = (SD/Mean) * 100 where SD is standard deviation and the mean is the numerical average. When the vendor's CV value is greater than 35%, they are considered potentially risky.
Formula: Only formula checks are examined. If the CV (SD/Mean * 100) is greater than 35% for any formula type, the indicator is noted.
Initial value: 35.0 Comparer: >
STDEV(paidamt / ITEMQUANTITY) / AVG(paidamt / ITEMQUANTITY) * 100
PAIDAMT is retrieved from the FOODINSTRUMENT table.
and FOODINSTRUMENTITEMREDEEMED.PAIDAMT (union of these two values).
ITEMQUANTITY is retrieved from the FOODINSTRUMENTITEM table.
and FOODINSTRUMENTITEMREDEEMED.REDEEMEDQUANTITY value (union of these two values).
Description: Unusually high average WIC check price compared to peers. The mean check price is calculated and the vendors within each peer group are ordered. The top 2% of vendors in each peer group are considered risky.
Formula: The vendors in Each Peer Group are ordered by Average Price; the top 2% have the indicator noted.
Initial value: 2.0 Comparer: <
AVG(paidamt) - Rank is sorted by AVG(paidamt) descending values
PAIDAMT is retrieved from the FOODINSTRUMENT table.
and Sum(FOODINSTRUMENTITEMREDEEMED.PAIDAMT) (union of these two values).
Description: Unusually small amount of variance in food instrument prices. (25% of checks are redeemed at an amount 90% or more of the check limit.)
Formula: When the number of checks within 90% of the MAR / the number of checks is greater than 90%, the indicator is noted.
Initial value: 25.0 Comparer: >
CAST(SUM(fails) AS FLOAT) / COUNT(vendorid) * 100
Fails = (PAIDAMT / OBLIGATIONAMT) > .9 THEN 1 ELSE 0
PAIDAMT is retrieved from the FOODINSTRUMENT table.
and sum(FOODINSTRUMENTITEMREDEEMED.PAIDAMT) (union of these two values).
OBLIGATIONAMT is from FOODINSTRUMENT table
VENDORID is retrieved from the FOODINSTRUMENT table.
And FOODINSTRUMENTITEMREDEEMED.VENDORID (union of these two values).
Description: Vendor's redeemed prices higher than their list price. The surveyed price for each item on the check is totaled. Then a percentage of checks redeemed above that price is calculated for each vendor. Those vendors who have more than 15% of their checks redeemed above the list price are considered risky.
Formula: The (Actual Price > Survey Price count / total check count) > 15%.
Initial value: 15.0 Comparer: >
CAST(SUM(fail) AS FLOAT) / COUNT(fail) * 100
Fail = CASE WHEN SUM(CASE WHEN fdi.BASEFOODCATEGORYID = 'L' THEN .01
ELSE COALESCE(vp.VENDORPRICE, 0)
END * ITEMQUANTITY) < fi.PAIDAMT THEN 1
ELSE 0
BASEFOODCATEGORYID is retrieved from the FOODDISTRIBUTIONITEM table.
VENDORPRICE is retrieved from the VENDORPRICE table.
ITEMQUANTITY is retrieved from the FOODINSTRUMENTITEM table.
PAIDAMT is retrieved from the FOODINSTRUMENT table.
Fail = CASE WHEN paidamt > surveyprice THEN 1
paidamt : sum(FOODINSTRUMENTITEMREDEEMED.PAIDAMT)
Survey Price : SUM(CASE WHEN fdi.BASEFOODCATEGORYID = 'L' THEN 1
ELSE COALESCE(vp.VENDORPRICE, 0)
END * fiir.REDEEMEDQUANTITY) AS surveyprice
BASEFOODCATEGORYID is retrieved from the FOODDISTRIBUTIONITEM table.
VENDORPRICE is retrieved from the VENDORPRICE table.
REDEEMEDQUANTITY is retrieved from the FOODINSTRUMENTITEMREDEEMED table.
Description: Unusually high percentage of high priced food instruments. 50% or more of total checks redeemed at $50.00 or more.
Formula: The (Actual Price > $50 count / total check count) > 50%.
Initial value: 50.0 Comparer: >
CAST(A.Greater AS FLOAT) / (A.Greater + A.lesser) * 100
SUM(CASE WHEN paidamt > 50 THEN 1
ELSE 0
END)
SUM(CASE WHEN PAIDAMT <= 50 THEN 1
ELSE 0
END)
PAIDAMT is retrieved from the FOODINSTRUMENT table union
SUM(FOODINSTRUMENTITEMREDEEMED.REDEEMEDQUANTITY) as Paidamt
Description: Vendors with an excessively high number of redeemed WIC checks for their size and/or location. This is determined by comparing monthly sales and figures to stores with similar square footage within the same area of operation as the suspect vendor. Any store showing more than 25% checks over the average for a store of this size.
Formula: Any vendor with a percentage greater than 25% is identified.
If the vendor's peer group average WIC sales is greater than 0, then the percentage is calculated as:
100 * (Vendor's total WIC sales - Vendor's Peer Group average WIC sales) / (Vendor's Peer Group average WIC sales)
Otherwise, the percentage is 0.
Initial value: 25.0 Comparer: >
100 * CASE WHEN average > 0 THEN (redeemed - average) / average
ELSE 0 END
Average = SUM(redeemed) / COUNT(VENDOR.VENDORID)
Redeemed = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED)
Description: Unusually small amount of WIC business volume, defined as sales of less than 50% of stores with comparable size and stock. Vendor may not be meeting required stocking minimums or may be refusing WIC clients.
Formula: Any vendor with a percentage less than 50% is identified.
If the vendor's peer group average WIC sales is greater than 0, then the percentage is calculated as:
100 * (Vendor's total WIC sales) / (Vendor's Peer Group average WIC sales)
Otherwise, the percentage is 0.
Initial value: 50.0 Comparer: <
Value = 100 * CASE WHEN average > 0 THEN (redeemed) / average
ELSE 0 END
Average = SUM(redeemed) / COUNT(VENDOR.VENDORID)
Redeemed = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED)
Description: The annualized total sales volume for each vendor is calculated. Within each peer group the vendors are sorted by volume and the highest 2% of vendors are considered potentially risky. The percentage is calculated using the cumulative probability from the statistical standard normal table.
Formula: Any vendor with a percentage less than 2% is identified.
If the standard deviation of WIC sales for the vendor's peer group is greater than 0, then the percentage is calculated as:
100 * (1 - Cumulative Probability of ((Vendor's total WIC sales - Vendor's Peer Group average WIC sales) / (Vendor's Peer Group WIC sales standard deviation)))
Otherwise, the percentage is 0.
Initial value: 2.0 Comparer: <
100 * (1 - (CASE WHEN STDEVP(redeemed) > 0 THEN ZScore((redeemed - average) / STDEVP(redeemed))
ELSE 0.5 END))
Average = SUM(redeemed) / COUNT(VENDOR.VENDORID)
Redeemed = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED)
Description: Unusually large increase in dollar volume of food instruments redeemed over previous quarter. A 50% or more increase in sales for the quarter is considered a risk.
Formula: Each vendor's sales totals are examined over the previous 2 quarters. If the second timeframe is 50% greater than the first timeframe the indicator is noted. If the vendor does not have data for the entire time, they are excluded from this examination.
Initial value: 50.0 Comparer: >
(lq.Total - qbl.Total) / qbl.total * 100
lq = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED) from the 1st previous quarter
qbl = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED) from the 2nd previous quarter
Description: For those vendors that have more than one year of activity, this year's volume is compared with the annualized data from the previous year. Those vendors with the highest increase in sales for this year versus last year are considered at risk.
Formula: All the vendors are ordered by percentage increase in WIC Sales from one year to the next; the top 2% have the indicator noted.
Initial value: 2.0 Comparer: <
(RANK() OVER ( ORDER BY (ly.Total - ybl.Total) DESC) - .5) / Count(VENDOR.VendorID) * 100
ly.Total = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED) from the Last year
ybl.Total = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED) from the year before Last year
Description: The total volume of sales per square foot by each vendor is compared with the total volume of sales per square foot for the Geographic Area in which the vendor exists. Those vendors with the highest percentage of sales in each county are considered at risk.
Formula: If one vendor does 25% more business per square foot than that of their Geographic Areas, the indicator is noted.
Initial value: 25.0 Comparer: >
salesPerSF / SUM(salesPerSF) * 100
salesPerSF = SUM(REDEMPTIONHISTORY.AMOUNTOFFIREDEEMED) / VENDOR.SQUAREFOOTAGE
Fails = (PAIDAMT / OBLIGATIONAMT) > .9 THEN 1 ELSE 0
Description: Total of WIC sales disproportionate to total food sales. If WIC sales comprise 50% or more of the reported total food sales the vendor is identified as risky.
Formula: WIC Sales / Total Sales > 50%.
Initial value: 50.0 Comparer: >
VENDOR.ANNUALWICFOODSALES / VENDOR.ANNUALFOODSALES * 100
Description: Unusually high percentage of WIC to SNAP sales. If the ratio of WIC to SNAP is 20% or more, based on the calculated WIC sales and the reported SNAP sales, the vendor is identified as risky.
The WIC sales figure used in the calculation will be the WIC sales of the previous federal fiscal year.
Formula: WIC Sales / SNAP Sales > 20%.
Initial value: 20.0 Comparer: >
WICSALES / (COALESCE(VENDOR.annualsnapfoodsales, 0)) * 100
WICSALES = sum(FOODINSTRUMENT.PAIDAMT) union SUM(FOODINSTRUMENTITEMREDEEMED.REDEEMEDQUANTITY)
Description: An unusually high ratio of annual combined WIC and SNAP sales to the annual gross sales. If the ratio of annual combined WIC and SNAP sales to the annual gross sales is greater than 70% (based on calculated WIC sales, reported SNAP sales, and reported gross sales), the vendor is identified as high risk.
The WIC sales figure used in the calculation will be the WIC sales of the previous federal fiscal year.
Formula: (WIC Sales + SNAP Sales) / Total Sales > 70%.
Initial value: 70.0 Comparer: >
COALESCE(VENDOR.annualsnapfoodsales, 0) + WICSALES) / VENDOR.AnnualGrossSales * 100
WICSALES = sum(FOODINSTRUMENT.PAIDAMT) union SUM(FOODINSTRUMENTITEMREDEEMED.REDEEMEDQUANTITY)
Description: The number of Participant complaints recorded in the Vendor application is counted and if the total exceeds 5 the vendor is identified as risky. The date is taken from the IncidentDate column in the Complaint table.
Formula: Participant Complaints > 5.
Initial value: 5.0 Comparer: >
COUNT(Complaint.ComplaintID)
Description: Vendors who redeem more than 100 checks outside of their county are identified as risky.
Formula: Count of checks issued in a different county than they are redeemed > 100.
Initial value: 100.0 Comparer: >
Sum (CASE WHEN Address.COUNTYCD = HOUSEHOLD.COUNTYCD THEN 0
ELSE 1 )
Description: Sales where the clients' county is different than the vendor's county are totaled. This total is then divided by the total WIC volume. Those vendors with 10% or more of "outside county" sales are considered at risk.
Formula: Total Sales made in a county different than issued county / Total Sales > 10%.
Initial value: 10.0 Comparer: >
CASE WHEN (SUM(nomatch) + SUM(match)) = 0 THEN 0
ELSE SUM(nomatch) / (SUM(nomatch) + SUM(match)) * 100
NoMatch = WHEN Address.COUNTYCD = HOUSEHOLD.COUNTYCD THEN 0
ELSE FOODINSTRUMENT.PAIDAMT
Match = WHEN Address.COUNTYCD = HOUSEHOLD.COUNTYCD THEN FOODINSTRUMENT.PAIDAMT
ELSE 0
Description: Large number of participants redeeming Food Instruments who are considered to be at high health risk. Sales where the client has a priority one health risk are totaled for each vendor. This total is divided by the total WIC sales for this vendor. Those vendors with a percentage greater than 5% are considered to be risky.
Formula: (Total Sales to Priority 1 Participants / Total Sales) > 5%.
Initial value: 5.0 Comparer: >
CASE WHEN SUM(FOODINSTRUMENT.Paidamt) = 0 THEN 0
ELSE SUM(HighRiskSales) / SUM(FOODINSTRUMENT.Paidamt) * 100
HighRiskSales = CASE WHEN NOT CERTCONTACT.STATEWICID IS NULL THEN FOODINSTRUMENT.PAIDAMT
ELSE 0
Description: For each vendor the serial numbers of the FIs redeemed are sorted in numerical order. The sequences of serial numbers are examined to determine the number of times a vendor has 3 or more consecutive numbers. The number of times this occurs is divided by the total number of redeemed checks for this vendor. Those vendors with the highest percentage are considered at risk. The top 2% of vendors are identified as risky.
Formula: The vendors are ordered by the count of series of consecutive Benefit Numbers greater than 3 / Total checks, and the top 2% are identified.
Initial value: 2.0 Comparer: <
(RANK() OVER ( ORDER BY perc DESC) - .5) / COUNT(vendorid) * 100
perc = CAST(COUNT(FOODINSTRUMENT.SERIALNO) AS FLOAT) / COUNT(FOODINSTRUMENT.Foodinstrumentid) * 100
Description: A calculation is not possible because SPIRIT does not allow manually issued checks.
Formula: N/A
Initial value: 0.0 Comparer: >
N/A
Description: Excessive number of returned checks due to errors. Vendors with over 5% of the monthly total of checks redeemed by the vendor will be identified as risky.
Formula: The (count of check errors / total checks) > 5%.
Initial value: 5.0 Comparer: >
CAST (SUM(rejected) AS FLOAT) / COUNT(rejected) * 100
rejected = CASE WHEN FOODINSTRUMENT.REJECTED = 'Y' THEN 1
ELSE 0
Description: The vendor will have this High Risk indicator flagged if a compliance investigation was opened during the fiscal year, if an inventory invoice is requested to determine if they had sufficient stock in their store, or if they received fines on compliance buys.
Formula: Any compliance investigation, inventory audit, or compliance buy irregularity in the timeframe will trigger the indicator.
Initial value: 1.0 Comparer: >=
COUNT (event.eventid) WHERE event.TYPE IN (4, 53)
Description: New vendors are automatically high risk until their first compliance investigation.
Formula: This indicator is flagged for a vendor who has been certified as a WIC vendor in the timeframe and has not yet had a compliance investigation or monitoring visit.
Initial value: 1.0 Comparer: >=
Select 1 from event
inner join dbo.v_EventFollowupKeys eFollowKeys on eFollowKeys.eventId = e.EVENTID
inner join FOLLOWUPACTIVITY fa on fa.FOLLOWUPACTIVITYID = eFollowKeys.followupId
WHERE fa.TYPE = 17 AND e.TYPE = 1
AND e.VENDORID NOT IN (SELECT DISTINCT VENDORID
FROM FOLLOWUPACTIVITY fa WHERE fa.TYPE IN (56))
Description: Vendors with a history of monitoring reports that indicate repeated noncompliance or vendors who are noncompliant after receiving a revisit and/or technical assistance. The indicator is reported as High Risk if an activity resulting in this status is recorded during the fiscal year.
Formula: If the routine monitoring visits, compliance buys, and inventory audit events contain a follow-up activity that has the appropriate status, the indicator is flagged.
Initial value: 1.0 Comparer: >=
COUNT(EVENTID) FROM event e1
LEFT JOIN eventrelationship ON er.EVENTID = e1.EVENTID
LEFT JOIN FOLLOWUPACTIVITY fa ON fa.FOLLOWUPACTIVITYID = er.FOLLOWUPACTIVITYID
WHERE e1.TYPE IN (11, 4) AND fa.TYPE IN (13, 14)
Fails = (PAIDAMT / OBLIGATIONAMT) > .9 THEN 1 ELSE 0
Description: Vendors with sanctions (other than disqualification from the program) resulting from covert buys and/or investigations will trigger the indicator.
Formula: If the sanction occurred during the timeframe, the indicator will be flagged.
Initial value: 1.0 Comparer: >=
Select DISTINCT vendorId, 1 FROM EVENT WHERE TYPE = 14 Union
Select DISTINCT vendorId, 1 FROM FOLLOWUPACTIVITY WHERE fa.TYPE = 14
Description: Shelf prices on price survey exceed a percentage of the peer group price. If the shelf prices for a vendor are 20% greater than the peer group average shelf price then this risk indicator is flagged.
Formula: (Vendor shelf prices - Peer Group shelf prices) / Peer Group Prices > 20%.
The "Vendor shelf prices" is the sum of all the prices from the last price survey on or before the end date of the analysis. The "Peer Group shelf prices" and "Peer Group Prices" are the same number. They are the sum of the average of the prices for the same distribution items. If a vendor only sent in a price for one item, the "Peer Group Prices" number would be the average of that item across the vendor's current peer group.
Initial value: 20.0 Comparer: >
(SUM(PriceList_CTE.price) - SUM(AVG(PriceList_CTE.Price))) / SUM(AVG(PriceList_CTE.Price)) * 100
Description: Vendors who are recorded as missing the Vendor Training session during the fiscal year are reported as High Risk.
Formula: If the vendor was scheduled for training during the timeframe and didn’t attend, the indicator will be flagged.
Initial value: 1.0 Comparer: >=
COUNT(EVENT.EVENTID where TYPE = 19 ) - COUNT(FOLLOWUPACTIVITY.FOLLOWUPACTIVITYID WHERE TYPE = 39)
Description: Vendors whose calculated high risk evaluation value exceeds 7.2 are considered High Risk.
Formula: Each High Risk Indicator flagged for the vendor has an evaluation factor; if the total of these numbers exceeds this value the indicator will be marked.
Initial value: 7.2 Comparer: >
|
Software Version: 2.40.00