The Wisconsin Community Action Program Association (WISCAP) provides logistical support to The Emergency Food Assistance Program (TEFAP) across Wisconsin. TEFAP is a federal nutrition program that provides United States Department of Agriculture (USDA) commodities to emergency food providers nationwide.
WISCAP helps oversee the distribution of 15-20 million pounds of fresh and shelf-stable foods that ultimately end up at over 350 participating food pantries, meal sites, and shelters (also known as outlets) across Wisconsin. Throughout the year, WISCAP’s TEFAP process is to receive food offers from USDA, project future food warehouse inventory, and then order the needed commodities. WISCAP then tracks the progress of those orders, often over several months or more, until they are received by a partner’s regional warehouse and subsequently distributed to the partner’s outlets. Throughout this process they provide monthly updates to their partners on the status and estimated time of arrival (ETA) of their shipments.
The Challenge
For decades, WISCAP managed a complicated web of relationships between offers, orders, shipments, and distributions for regional Partners and their outlets within sophisticated Excel spreadsheets. This required so much staff time to maintain that it was often up to a month out of date. In addition, the information was scattered across multiple spreadsheets, making it very difficult to see all relevant information in one place. This structure also required hundreds of advanced Excel formulas—which were prone to breaking—in order to provide tailored reports for different audiences.
The Solution
Bigger Boat worked with WISCAP to rebuild their TEFAP food distribution system in Salesforce. This new system provides a number of key benefits:
#1 Up-to-Date Information through Improved Process Efficiency
One of the biggest pain points for WISCAP was trying to manually keep the status of highly detailed information for hundreds of shipments up to date. That much staff time simply wasn’t available and thus their information was often up to a month out of date. A key part of their role was to monitor shipment statuses, respond to changes and alert their partners so they could then plan accordingly. The real-time data resided in USDA’s system which does not support external integrations. So, we designed a repeatable export/import process to Salesforce which enabled WISCAP to quickly and accurately create and update all relevant shipments in as little as 10 minutes. This shipment information then automatically updated the related sales order. This further auto-updated the associated program, providing WISCAP with a real-time view of their program balances. WISCAP now has accurate and up-to-date information on all their shipments with real-time reporting.
#2 Take Action Based on Views and Detailed Reporting
One big challenge WISCAP faced in managing their TEFAP operations via spreadsheets was that they couldn’t easily pivot on data for different scenarios. By moving to Salesforce and leveraging the power of Lightning pages, staff members login and see an actionable home page that gives them a birds-eye view of shipments in progress or offers, change orders, and commodities needing their attention. They also have quick links to their key reports and an overview of program balances.
Users can click into a specific program record to see embedded reports that break down program orders by fiscal year or ship-to region. For example, they can go to a warehouse account to see all related shipments and distributions as well as contact information for the warehouse. From a shipment record, they can see up-to-date information on the shipment’s status, value, and quantity as well as any related distributions or change status records. From the shipment record, they can also easily click to see more information about its commodity or program, or additional sales order information.
Embedded reports on program pages
#3 Give Partners Direct Access to Share and Receive Updates
The final step for the initial launch of this program was to grant their 18 partner organizations direct access to their information in Salesforce via a tailored view of paired down information meaningful to partners.. This direct access reduces the time WISCAP previously spent generating monthly Excel reports that were then shared out to their partners by email. Instead, partners now log into Salesforce to see up-to-date reports and real-time information on their outlets’ program performance.
Before Salesforce, WISCAP and their partners gathered the monthly service statistics from over 300 outlets each month into multiple tabs on a shared Excel spreadsheet. This was a time-consuming process for both WISCAP and the partners, with an unfriendly user interface that was prone to inaccurate reporting. By giving partners direct access to Salesforce, they have two options that enable monthly service statistics to be directly submitted to Salesforce. The first option enables partners to directly enter the service statistics themselves via a user-friendly list of just their outlets, along with a user-friendly submission button customized to only ask for the information needed for a specific outlet type. The second option is for partners to provide select outlets with a link to an external form, which enables outlets to directly submit their monthly statistics and update their contact and organization information as needed. Once this information is submitted, by whatever means, auto-calculations happen on the back end which immediately update reports.
#4 Improve Inventory Forecasting
Projecting inventory was another painful process for WISCAP, but necessary for them to achieve their goal of neither wasting food (ordering too much) nor leaving people hungry (ordering too little). To estimate how much of a commodity they’d have on hand at a future date, they needed to know each of the following data points for each commodity by warehouse:
- Starting balance
- Plus: Quantity of commodity actually received on shipments that had already arrived as of today
- Minus: Quantity of commodity distributed from the warehouse as of today
- Plus: Quantity of commodity expected to arrive at a future date
- Minus: Quantity of commodity expected to be distributed at a future date
The twice-weekly shipment import provided up-to-date information on shipments received to date and expected ETAs of shipments in transit.
In order to track distributions past and future, we built a child Distribution object to Shipments. Since fresh foods were always distributed in full the day they were received, we automated the creation of these records, keeping inventory levels accurately at zero. However, shelf-stable foods were both more complicated and far more important to track.
The monthly warehouse reports WISCAP received from partners provided the date and quantity of a commodity’s distribution but did not tie it back to the specific shipment that commodity came in on. Since WISCAP tracked everything by shipment, they wanted to know when each shipment had been fully distributed. Thus, WISCAP used the First In, First Out method (FIFO), applying distributions to the first shipment received that still had a balance remaining. To manage this in Salesforce, we created warehouse-specific list views of received shipments not yet fully distributed, which WISCAP could further filter by commodity and sort by date received. They’d then use a Distribute Shipment quick action to create distribution records for selected shipments.
Optimizing Food Distribution
Shifting to Salesforce has helped WISCAP to manage food distribution more efficiently and transparently in the state of Wisconsin. The system and process improvements have saved their partners and staff numerous hours while improving the accuracy and forecasting of key parts of the process.