Blog: Using Spreadsheets to Automate Supply Chain Integration

Many business-to-business (B2B) relationships choose to initiate transaction exchanges, using their EDI platform, at the point a buyer sends an electronic purchase order to a supplier. This Purchase Order is often in the form of an EDI 850 document. This Purchase Order often then triggers the supplier to send the buyer an electronic Invoice (EDI 810) and an electronic Ship Notice (EDI 856), for the goods ordered. This “business process” represents the minimum number of transactions required to maintain this successful model. Moreover, there exists an abundance of data in support of this process, although it may often fail to be included in the production model. The cost of integration may exceed the return and, as a result, this data either isn’t shared between the two organizations or it fails to be integrated to a usable state.

For example, at the suppliers’ front end is Catalog data (EDI 832). This provides an opportunity for a supplier to identify the attributes of their products to the buyer, creating a more-efficient ordering process. At the buyers’ backend is Product Activity / Point of Sale data (EDI 852). This provides an opportunity for buyers to report their sales activity, aiding in the ability to forecast the production of goods. While this data is not essential to the “Purchase Order <-> Invoice” model, it provides the ability to “enhance” production processes. Additional transactions, such as Purchase Order Acknowledgment (EDI 855), Purchase Order Change (EDI 860), and Payment Advice (EDI 820), are a few of the many electronic formats capable of improving and furthering the overall business process integration.

Business transactions that are not included or implemented as part of the base model are generally handled using some simplified format such as a spreadsheet – a format that is inexpensive to create and populate, while simple to maintain. The value of this spreadsheet data is realized when introduced to the business model along with the EDI transactions. While some companies might find it feasible to implement the EDI versions of Catalog and Product Activity data, the fact remains that this may be costly and may not have an actual “home” in some company ERPs. As a result, the spreadsheet becomes the “database” used for storing and maintaining this data.

Spreadsheets may be created in simple structure with minimal formatting where generic columns and rows are utilized. Spreadsheets may also use a complex structure with extended formatting of columns and rows, and potentially having multiple worksheets and detailed macros. The truth be told, spreadsheets simply make our lives easier…it is much easier to create a spreadsheet than it is to develop a relational database with accompanying screens and backend programs. Unfortunately, smaller organizations may find spreadsheets to be the only option for capturing and managing business data.

Return to the previously-described process using “Purchase Order -> Invoice -> Ship Notice” business model. A seller using a spreadsheet to host item “Catalog” data will help to ensure that product item numbers, units of measure, potential pricing, and a variety of other item attributes are accurate. A buyer using a spreadsheet to host “Product Activity” data will provide detail to the supplier and help in the manufacturing and planning process. By including these new transactions and data, the business process model has now been extended to “Catalog -> Purchase Order -> Invoice -> Ship Notice ->Product Activity” (with the Catalog being both an initial and intermittent transaction). Not only has the supply chain been improved and additional data shared (and integrated into the process) but smaller companies who previously were incapable of expanding beyond “core” EDI transactions can now benefit from the inclusion of spreadsheets.

In their most-simple implementations, third-party integration products are designed to “read-from” a source location (e.g. EDI 850 Purchase Order) and “write-to” a target location (e.g. company backend ERP). Taking our sample process one step further, spreadsheets might also be used to exchange this “core” data. The same smaller organizations that might struggle to comply with EDI Catalog and Product Activity transactions might also struggle to comply with core EDI Purchase Order and Invoice transactions. By employing a spreadsheet option for these smaller organizations, companies can more-fully integrate their entire customer base into the same target location, regardless of whether using EDI or spreadsheet(or both). The burden must then rest with the third-party integration product to support not only the common EDI formats but also the proprietary-formatted spreadsheet data.

EDI data is commonly transmitted between companies through a Value Added Network (VAN) or directly (FTP or AS2). Spreadsheet data can be transmitted through a reliable and inexpensive option commonly known as“E-Mail”! A buyer may now receive EDI or spreadsheet data from a scheduled VAN [pull], from a customer AS2 [push], or by monitoring for the receipt of an email (this applies in the reverse for data sent from the buyer to the supplier). The entire supply chain may now be fulfilled for both the large and small (“mom-and-pop”) companies; achieved through standard business models that support both standardized EDI data as well as proprietary spreadsheet data while utilizing multiple communications channels.

Years ago, spreadsheets were viewed as the “poor-man’s” means for managing business data. Today, they have filled a critical void of the overall supply chain. The pundits who fail to accept the importance of spreadsheets are from the same group that once uttered, “EDI is dead!”