|
|
NOTE: If the Rebate Projection Worksheet Report (Output) is not applicable to your state, this report is unavailable in the application. |
Contents Show
The Rebate Projection Worksheet Report (Output) generates an Excel spreadsheet to use as a tool to develop scenarios and estimate, compare and project potential rebates and additional opportunities. The report is generated when the OK button is clicked on the Generate Rebate Projection Worksheet Report screen.
|
|
NOTE: If the Rebate Projection Worksheet Report (Output) is not applicable to your state, this report is unavailable in the application. |
The spreadsheet will be filled with food items on paper food instruments for the selected commodity (base food category), redemption information, and the associated rebate agreement for the selected redemption month if the product is linked to a rebate. Paper food instruments must be issued with a specific food item to be considered for rebate or to draw reasonable comparisons. If the food item is not associated with a rebate agreement, the rebate supplier column will be blank and the Formula Source Name can be referenced. EBT will be redeemed at the UPC level.
The formula food prescription items must be defined to identify the manufacturer using a formula source code. When the manufacturer is selected, only the food items linked to a rebate will be included.
|
|
NOTE: The system currently does not support a manufacturer source for food items with a base food category other than formula. The small volume of information produced at this is manageable within the spreadsheet. |
The formula food prescription items must be defined to identify the chief characteristic using the Formula Formulation Base (Milk/Soy) and the Formula Formulation Format (Concentrate/Powder/RTU).
Selected cells are populated for the start of the spreadsheet. The user may update cells as needed. The spreadsheet is as of the day the spreadsheet is requested. The spreadsheet template will be protected and is not is not intended to be modified by the user. Changes to the template may require programming changes.
Origin of Data Element
This value comes from the NAME column in the STATEPROFILE table.
Format
This is a fifty-character value.
Origin of Data Element
The user records this value when the report is submitted.
Format
This is an accounting month and year formatted as Month Name CCYY.
Origin of Data Element
The user records this value when the report is submitted.
Format
This is an accounting month and year formatted as Month Name CCYY.
Origin of Data Element
Statewide data is always selected. The text description of "State Total" will be printed.
Format
This is a fifty-character text field.
Origin of Data Element
This value is the NAME column from the REBATESUPPLIER table.
Format
This is a fifty-character text field.
Origin of Data Element
This value is the participation date.
It is the PARTICIPATIONDATE column of the FOODINSTRUMENTSET table for the redeemed FI.
Format
This is an accounting month and year formatted as Month Abbreviation and CCYY.
Origin of Data Element
This value is the description of food items on the food instrument selected for the report. It is the DESCRIPTION column of the FOODDISTRIBUTIONITEM table. This value comes from the FOODINSTRUMENTITEM table FOODDISTRIBUTIONITEM.
Format
This is a sixty- character text field.
Origin of Data Element
This value is the description of formula source code for the food prescription item on the food distribution item on the food instrument selected for the report. It is the DESCRIPTION column of the Reference Dictionary where the category is "FORMULASOURCE".
FOODINSTRUMENTITEM table FOODITEMID
FOODITEM table FOODITEMFORMULAID
FOODITEMFORMULA table SOURCECD
Reference Dictionary EXTERNALID
Format
This is a sixty- character text field.
Origin of Data Element
This value is the description of formula formulation code for the food prescription item on the food distribution item on the food instrument selected for the report. It is the DESCRIPTION column of the Reference Dictionary where the category is "FORMULATIONBASE".
FOODINSTRUMENTITEM table FOODPRESCRIPTIONITEM.
FOODITEM table FOODITEMID
FOODITEM table FOODITEMFORMULAID
FOODITEMFORMULA table FORMULATIONBASE
Reference Dictionary EXTERNALID
Format
This is a fifteen-character text field.
Origin of Data Element
This value is the description of formula formulation code for the food prescription item on the food distribution item on the food instrument selected for the report. It is the DESCRIPTION column of the Reference Dictionary where the category is "FORMULATIONFORMAT".
FOODINSTRUMENTITEM table FOODPRESCRIPTIONITEM.
FOODITEM table FOODITEMID
FOODITEM table FOODITEMFORMULAID
FOODITEMFORMULA table FORMULATIONFORMAT
Reference Dictionary EXTERNALID
Format
This is a fifteen-character text field.
Origin of Data Element
This value is the description of formula type code for the food prescription item on the food distribution item on the food instrument selected for the report. It is the DESCRIPTION column of the Reference Dictionary where the category is "FORMULATYPE".
FOODINSTRUMENTITEM table FOODPRESCRIPTIONITEM.
FOODITEM table PRESCRIPTIONITEMID
FOODITEM table FORMULATYPE
Reference Dictionary EXTERNALID
Format
This is a fifteen-character text field.
Origin of Data Element
This value is the peer group code associated with the vendor that redeemed the food instrument. The value comes from the PEERGROUP column of the FOODINSTRUMENT table.
Format
This is a two-character value.
Origin of Data Element
This value is the product average price assigned to the peer group from the vendor pricing process. The price in effect at the end of the reported month will be used. This value comes from the OVERRIDEPRICE column of the PEERGROUPFOODITEMHISTORY table if it is valued and if the value is greater than zero. Otherwise, this value comes from the AVGERAGEPRICE column of the PEERGROUPFOODITEMHISTORY table.
Format
This is a three-digit number plus two decimal places.
Origin of Data Element
This value is calculated by the sum of the actual paid amount on the redeemed food instruments for the product and peer group. This value comes from the PAIDAMT column on the FOODINSTRUMENT.
Format
This is a ten-digit number plus two decimal places.
Origin of Data Element
This value is calculated by totaling the redeemed quantities for the product. The redeemed quantity for the product is calculated for each food instrument by dividing the PAIDAMOUNT column of the FOODINSTRUMENT table by the value that has been determined for the Peer Group Average Price cell. The result is rounded to the nearest whole number.
|
|
NOTE: This value will be calculated as zero units when the vendor prices are purposefully or unintentionally set to zero as of the end of the month. The values in the PEERGROUPFOODITEMHISTORY table will need to be corrected. |
Format
This is a nine-digit number.
Origin of Data Element
This value is a formula imbedded into the spreadsheet. It is calculated by multiplying the value in the Peer Group Price cell by the Estimated Number of Units Redeemed cell for each detail row.
Format
This is a ten-digit number plus two decimal places.
Origin of Data Element
This value is the rebate per unit amount assigned to the product. The rebate per unit amount in effect at the end of the reported participation month will be used. The value comes from the REBATEPERUNIT column of the REBATEAGREEMENTITEM table.
Format
This is a four-digit number plus five decimal places.
Origin of Data Element
This value is a formula imbedded into the spreadsheet. It is calculated by multiplying the value in the Estimated Units Redeemed cell by the Rebate Per Unit Amount cell for each detail row.
Format
This is a twelve-digit number with two decimal places.
The report is based on the following selection criteria.
The food instrument must include a rebate item for the selected commodity.
The printed date of the food instrument must be within the date range of an active contract.
Redemption data must meet one of the following selection criteria.
The food instrument paid date must be within the selected month and food instrument was not redeemed early. (i.e. The paid date CCYYMM value is greater than or equal to food instrument's participation date CCYYMM value.)
The food instrument participation date must be within the selected month and the food instrument was redeemed early. (i.e. The paid date CCYYMM value is less than the food instrument's participation date CCYYMM value.)
This report will be sorted first by formula source if the base food category is formula.
|
|
NOTE: The system currently does not support a manufacturer source for food items with a base food category other than formula. |
This report will be sorted by issue month (food instrument participation date CCYYMM) in ascending sequence within formula source
This report will be sorted by food distribution item description in ascending sequence within by issue month.
This report will be sorted by peer-group code (food instrument peer group code) in ascending sequence within description.
This is a spreadsheet. Formulas for totals can be added at any time after the initial spreadsheet is generated.
A page break will occur on a change in Formula Source. Totals will be summed for each formula source.
A page break will occur on a change in issue month. Totals will be calculated for each issue month.
This is a spreadsheet. Formulas for totals can be added at any time after the initial spreadsheet is generated.
|
Software Version: 2.40.00
