https://docs.google.com/spreadsheets/d/e/2PACX-1vTIWwtO3A4SanCv7il3U1s_Sbevjhx52Yoi8oyd3MhYopg5kaIzYPWe7opKxluI7IUnGATrNGFZ7oyY/pub?gid=794522560&single=true&output=csv

 

Click on the link to view the report.

Once you are viewing it, select "Download As" from the File menu to download the report to the designated location on your computer.

TEFAP DATA and REPORTS

 

 

About the Format, Content & Data

If you are unfamiliar with the above reports, before using them, please read about them below.

Overview

These reports contain data on orders placed for direct shipment from the United States Department of Agriculture.  The do not contain data on indirect shipment orders being delivered by one agency to another.

There are three classifications of reports:

"Upcoming Delivery Reports" are updated monthly throughout the program year (PY) and are usually available by the 5th of the month following the report month. Orders are included  regardless of status, e.g., canceled orders. NOTE: Any order with a requested received date earlier than the report date is either still in transit or has been delivered but the recipient agency has not reported its receipt.

"Orders Year-to-Date (YTD) Reports" are updated monthly throughout the program year (PY) and are usually available by the 5th of the month following the report month.  Separate reports are provided for the current year and the upcoming year to make the content more manageable. You may wish to combine them into a single report.

"Annual Reports" are posted once each year after USDA makes the final year-end adjustments to WBSCM. NOTE: PY2012 is the first entire program year during which WBSCM was fully operational. 

A glossary provides field definitions for all of the data contained in the reports.

Update Frequency

Reports are prepared monthly and are usually available by the 5th of each month. Annual reports are posted once USDA has completed all WBSCM processing and adjustments for the program year.

Reporting Period

All reports contain relevant data for the program year (October 1 - September 30) through the end of the previous month. Until a full year's data is available, reports are presented as “year-to-date” (YTD). This is reflected in the both the spreadsheet title where "as of October 31, 2012" means the program year October 1, 2011 - through October 31, 2012. Tab titles on individual worksheets also indicate when a report is year-to-date, e.g., Orders YTD. 

Orders are assigned to a program year by USDA based on which year’s entitlement allocation is being charged for the cost of the commodities. The program year designation is subject to change, particularly when USDA reconciles each year’s entitlement dollars after the program year ends.  

Once the State's annual entitlement reconciliation is completed, a final year-end report is posted (the 2012 Program Year is the first year that WBSCM provided a complete program year's data). Spreadsheet titles contain the program year, making specific years easy to identify and differentiate from each other. 

Separating the data by program year enables data and reports to present and refresh quickly, however, it may also mean users may need to access more than one report to obtain data for the exact time periods they are seeking. Reports are easily copied and combined as needed using either Google Docs or desktop software such as Microsoft Office, OpenOffice, Zoho, etc.

Report Format

Spreadsheets are single Google Sheets containing (reports) lists of data in a columnar format, one food order per line. Users are given "view only" access, which prevents the data from being changed and ensures that reports appear in a consistent format from user-to-user, report-to-report and month-to-month.

Users wishing to customize reports may do so, but not without first creating a copy. Users wishing to filter/change the data in a report, e.g., permanently delete rows, adding columns, etc., must first either copy the file and/or download it. Copying/downloading a file may be done in Google Sheets or using desktop software such as Microsoft Office Excel or other spreadsheet applications. 

Using Reports

The Year-to-Date and Annual reports can be used in a variety of ways to help The Emergency Food Assistance Program (TEFAP) agencies manage the receipt, inventory and reporting of information about direct shipments of commodities received from the United States Department of Agriculture (USDA). For example, reports may be:

  • Viewed online and dynamically reported interactively. 
  • Used to create new reports in users’ Google Documents spaces and formatted as desired. 
  • Copied into a desktop application such as Microsoft Office Excel or OpenOffice. 
  • Shared with others by simply emailing a copy of the notification email containing a link to the reports.

Customizing Reports

Once copied or downloaded, reports may be dynamically reorganized using Google spreadsheet filtering and pivot reporting features or desktop software such as Microsoft Excel. This can be helpful for data analysis and interpretation. For example, the "Status Text” (sales order status) data field in the report may be filtered to differentiate the data into useful categories:

  • Cancelled Orders:  Selecting only the "Cancelled" status returns those orders for commodities for which USDA has cancelled the delivery. 
  • Pending Orders:  Pending orders are those for which USDA has yet to complete the commodity purchase. To identify pending orders, select these values when filtering data in the "Status Text" field:  Approved by SDA, Approved by Sp Agency, On Invitation, and Returned by FSA/AMS.
  • Purchased Orders:  Purchased orders, those commodities that USDA has purchased but has not yet delivered, or has delivered but the recipient agency has yet provided receipt information, are identified by a "Status Text" of "Purchased."
  • Delivered Orders:  A "Status Text" of "Order Received" returns a list of all orders USDA has delivered for which the recipient agency has submitted signed copies of the bill of lading. 

One caution regarding the accuracy of the “Status Text” field:  field content is regularly updated in WBSCM, but the update is dependent on a number of factors. It is wisest to think of this information as USDA’s best guess about the order status on the date the data were extracted for the spreadsheet (usually the first of each month). Moreover, because The Emergency Food Assistance Program (TEFAP) is an agricultural price support program, orders are often placed many months in advance. As a result, each order’s status is subject to change until it is received.

Below are some ideas for creating dynamic Google reports containing subsets of data that could be useful for different aspects of program management:

  • CANCELED ORDERS:  Cancelled orders data identifies USDA commodities that have been ordered for which USDA has subsequently canceled the order.This information may be used to notify warehouse, food ordering and distribution staff that the expected delivery will not be received. This information may also be helpful in revising plans for supplemental food purchases.
  • PENDING ORDERS:  A subset of pending orders data may assist agencies to anticipate what USDA commodities they might be receiving and when.  This information may be used to verify that orders have been submitted and determine where they are in the process. While an agency should not count on receiving these orders, the information may also be helpful in planning other food purchases.
  • PURCHASED ORDERS:  Purchased orders lists may be used to verify that orders have been submitted. Since items USDA has actually purchased have a greater chance of being delivered, this information may also be useful to anticipate deliveries, schedule staff to be on hand and also plan other food purchases. It is also possible that an item that has been received may be erroneously showing as purchased instead. This occurs because delivered orders continue to appear as purchased in WBSCM until the State enters receipt into from the bill of lading into WBSCM. The California Department of Social Services (CDSS) has only two days to submit a receipt to USDA once a load has been delivered. Reviewing a list of purchased orders list may help local TEFAP agencies track and record receipt information, facilitating prompt reporting to Foodlink, the State and USDA. Trucking companies that make deliveries do not get paid until Foodlink enters the receipt into WBSCM.  t is vital that agencies receipt deliveries and provide copies of bills of lading to Foodlink as soon as the order is received.  
  • RECEIVED ORDERS: A list of received orders may be used to confirm that orders have been received, review and reconcile inventory data and determine the weight and value of commodities USDA has provided. Making and storing a copy of the report locally allows users to add other data that makes the report more useful, e.g., receipt date, quantity actually received in good condition, notes pertinent to the delivery, etc.

User Prohibitions

To ensure consistency among users and reports, users are prevented from permanently altering the online reports. Users wanting to use Google's interactive functionality, such as filtering and pivoting data, must first make a copy of the report or download it.