Contents Show
The Food Sales Calculator Spreadsheet (Output) is displayed when the user selects the Food Sales Calculator Spreadsheet list item from the Reports data grid in the Generate Reports screen.
A few excerpts were copied nearly verbatim from Vendor Cost Containment documentation to describe the purpose and use of this spreadsheet:
This EXCEL spreadsheet provides the format necessary to estimate food stamp program eligible food sales using vendor's annual gross sales and to identify above-50-percent vendors. All vendors that are currently active is included in the spreadsheet. It is expected that this is used as a worksheet and will need to be adjusted to show only the pertinent vendors and formatted for final printing. The store type legend lines and footnotes is printed at the end of the worksheet page after all vendor lines are printed.
The system will provide these vendor detail values:
Vendor Name (column-A)
Vendor I.D. (column-B)
Store type (column-C) show legend
Annual Gross Sales (column-D)
Amount of WIC Redemptions (column-H)
Reported Annual Food Sales (column-J)
The calculator will compute an estimated food sales amount (column-F) and the maximum amount of WIC redemptions the vendor can have (column-G) in order to be classified as a regular vendor.
The calculator will compare the amount in column-G to the amount in column-H. If the amount in column-H exceeds that in column-G, then a "Y" will appear in column-I indicating that the vendor is an above-50% vendor.
The food sales calculator will compute the estimated FSP-eligible food sales when the annual gross sales amount is entered in to the calculator. The State agency must settle on the scenario and methodology before completing the form. The State agency must consider the store type.
In scenario 2, the steps to take to identify above 50-percent vendors when a vendor provides a food sales amount that includes items other than FSP eligible foods. The food sales share (column-E) was computed by Economic Research Service Staff, USDA, using retail trade data from the Economic Census, adjusted to exclude foods that are not FSP-eligible. The census is conducted every 5 years. The estimated food share varies from 94.7% for specialized food stores to 4.8% for pharmacies. State agencies should work with military commissaries to obtain the required food sales data.
In scenario 3, WIC vendors are required to report gross receipts or sales to the Internal Revenue Service on line 1a of various federal tax forms (e.g., Form 1065 (for partnerships); Form 1040, schedule C (for sole proprietorships); Form 1120 (for corporations)). Businesses also report annual gross sales to State tax and revenue agencies, even if the State does not impose sales tax on food. Thus it is reasonable to expect vendors to be able to provide Sate agencies with a documented annual gross sales amount, even if they report that they are unable to provide a distinct food sales amount.
In scenario 4, in rare instances vendors may not have written documentation of a store's annual gross sales amount due to some extraordinary occurrence or circumstance (such as a fire or natural disaster). In such instances, a State agency may allow a vendor to make a written self-declaration of the store's annual gross sales. The self-declaration should be made on a form developed by the State agency. It should include the self-declared gross sales amount, the period to which the gross sales amount applies (e.g., January 1, 2005 to December 31, 2005), the reason why the vendor is unable to provide either the documented food sales or gross sales amount, and the signature of the store owner or authorized representative.
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 "Food Sales Calculator Spreadsheet".
Format
The 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 vendor ID is taken from the Vendor table. The VENDORID is used to print the associated vendor TRADENAME from the VENDOR table.
Format
This is a 50-character description.
Origin of Data Element
The VENDORID is taken from the VENDOR table.
Format
This is a 6-character description.
Origin of Data Element
The annual gross sales is taken from the ANNUALGROSSSALES column in the VENDOR table.
Format
This is a 12-digit value. The format is currency.
Origin of Data Element
The value is manually entered. It is computed by the Economic Research Service staff, USDA.
Format
This is a 5-digit value. The format is percentage.
Origin of Data Element
This value is calculated based on a formula supplied in the spreadsheet.
=D12*(E12/100)
Annual Gross Sales multiplied by (Estimated Food Sales Share percentage divided by 100 to move the decimal)
Format
This is an 11-digit value. The format is currency.
Origin of Data Element
This value is calculated based on a formula supplied in the spreadsheet.
=F12*0.5
Estimated Food Sales multiplied by 50% which is the same as
Estimated Food Sales divided by 2
Format
This is an 11-digit value. The format is currency.
Origin of Data Element
The annual WIC redemptions is taken from the ANNUALWICFOODSALES column in the VENDOR table.
Format
This is an 11-digit value. The format is currency.
Origin of Data Element
This value is calculated based on a formula supplied in the spreadsheet.
=IF(H12<G12,"N","Y")
If Actual WIC Redemptions is less than Maximum WIC Redemptions to Meet Regular Vendor Criteria then place a "N" in the Above- 50% Vendor, otherwise place a "Y" in Above- 50% Vendor.
Format
This is a 1-character value.
Origin of Data Element
The reported annual food sales is taken from the ANNUALFOODSALES column in the VENDOR table.
Format
This is a 12-digit value. The format is currency.
Origin of Data Element
The store type code is taken from the STORETYPEID in the STORETYPE table. The STORETYPEID is used to print the associated store type DESCRIPTION from the STORETYPE table.
Format
This is a 3-digit code value and a 50-character description.
Origin of Data Element
The text for the footnotes is as follows:
1. Source: Economic Census, Retail Trade, Industry Series, Product Lines by Kind of Business (U.S. Census Bureau).
2. Excludes meals, unpackaged snacks, and other foods generally served for immediate consumption. Excludes alcoholic beverages.
Format
The cell values will print as a literal values.
The report is based on all vendors currently enrolled with an active status, 3= 'enrolled' or 7= 'probation'.
The currently enrolled flag is taken from CURRENTLYENROLLED of the VENDOR table.
The current status is taken from CURRENTSTATUS of the VENDOR table
This report is sorted by Vendor Trade Name.
This report is sorted by Vendor ID within Vendor Trade Name.
A detail line is printed when the Vendor ID changes.
The store type legend lines is printed at the end of the worksheet page after all vendor lines are printed.
The footnote lines is printed at the end of the worksheet page after all store type code lines are printed.
|
Software Version: 2.40.00