The real problem

The company collected a lot of data, but they lacked information – data that is organized so they could make good decisions and take action. They needed a way to combine the data into useful information and highlight what was important to different people at the company. At a higher level, they also needed a way to flag problems so they stood out from the regular day to day information.

The discussion

The interview process was simple enough – dig into the most painful issue first and follow up with more questions, leading to valuable insights.

• What do you need to do with the data you are getting? So many things! We need to know what different vendors have in their inventory and make sure that we are getting what we should when we should from them. We also need to record key information about the product coming in, to identify what is going on with it in our internal processes, and to document when it has been shipped.
• Why do you feel like you’re in the dark when you have some much data coming in? There are too many different places to look at for all our different vendors, what they have, what they are sending, what we have in-house ourselves, etc. I don’t want my folks spending all their time digging through spreadsheets.
• Have you tried to combine all the information you get into one master spreadsheet? We tried that, but each vendor sends us information from their system in a different format. We asked them to switch to a format for us, but they told us they have lots of customers and can’t set up a format just for us.
• How consistent is the information each vendor sends you? It is usually pretty good, but always in their format, not ours. Some of them are doing it manually, which means it can be different if they have a different employee sending it to us. That’s another reason we haven’t been able to combine all the information into one master spreadsheet.
• Does everyone at the company need the same information? Different people have different needs. Our customer service and purchasing people need to know what our vendors have in their inventory. Our purchasing people need to know that our purchase orders to the vendors have been received and that product is on its way to us. Our operations people need to know if and when we receive something so they can work on it, and our customer service people need to know when we ship it out.
• Since you are doing Business to Business sales, do you need to send information out to your customers, with a different format for each customer? Actually, yes, we’d love to be able to do that. Right now for our major customers, one of our Customer Service people has to set up spreadsheets specifically for them and their orders daily. I guess I understand why our vendors don’t want to do it – it’s a pain!
• Do you know how you want to see all the information? I really don’t want to see all the information. I just want to know when there’s a problem so I can fix it. I want my folks to be able to see the problems they have, too, so I don’t need to worry about it.

The actions

Ultimately, the customer needed a dashboard showing each employee the problems that they needed to deal with and provided them a way to update the day-to-day information to feed those dashboards. The system also standardized all of the data flows from each vendor into three central tables – one for the on-hand inventory, one for the inbound orders, and one for the things being worked on in-house. This tied in nicely with the system that had been set up for tracking customized orders.

The system was set up with an import table for each vendor and a script to pull information from those tables into the central tables. The system would stop the import and send a warning e-mail to the purchasing group if information from the vendor came in the wrong format. Rather than corrupting the central tables, this allowed the purchasing group to manually fix the vendor’s data file or ask the vendor to fix and resend it.

These central tables fed a dashboard which displayed graphs of problems, such as the number of late orders from vendors, the time since last successful upload, and number of late orders to customers. Each of these graphs had built-in drill down reports so that someone looking at a graph showing four late orders could click on it to see what those orders were and what was delaying them.

The dashboards also showed Key Performance Indicators, such as number of orders per week and dollar spend per vendor. Even though they didn’t require immediate action, they still had drill down reports that allowed for better understanding of the trends.

The system also displayed a customized report for everyone’s day-to-day operations so they could mark items as received and record serial numbers or condition, record shipment tracking numbers for product that had been shipped, or just see which vendors had which products.

The results

At first, people were reluctant to give up their spreadsheets. They had invested a lot of time into learning how to read the different ones from each vendor. However, as the employees saw that they could have one single dashboard showing all vendor information, rather than a different sheet for each vendor, they started to trust it more and more. Putting a freshness date (the last time the data was updated) also helped reassure them that the information was trustworthy.

When Purchasing could focus just on the late orders, they reacted much faster to either nudge the vendor or find a different one, which meant that on time deliveries to their customers improved. Also, when they were able to reply back to vendors who sent incorrectly formatted data right away, they found that the consistency of the spreadsheets improved. The vendors learned they had to get it right the first time or they would end up needing to do it again.

Since all the information was standardized and custom reports were easy to write, the company set up automatically e-mailed reports specifically for each major customer in exactly the format the customer wanted. It was so easy to do, they set it up for all of their customers who wanted it. This gave them a competitive edge because they could start feeding information to their customers in a matter of minutes rather than days or weeks. Most importantly, everyone could see what they needed to see and react to what they needed to react to without spending hours each day looking for the right spreadsheet from each vendor.

The nitty-gritty details

The system took some preparation, starting with setting up the three main data tables – one for the inventory at the suppliers, one for purchased product coming in, and one for items processed in-house. The company decided what the critical data elements were for each table.

For the inventory at suppliers, for example, the company wanted to track the vendor and the product’s manufacturer, model number, quantity, price, lead time, and several other factors. The company then verified that the basic information was available in the data that each vendor was sending and asked them to add whatever wasn’t already there.

The company asked the vendors to send the information, in CSV or XLSX formats, to e-mail addresses that were set up for each vendor, such as Vendor1@Company.com. A script automatically downloaded the files from the e-mail to a secured location on the network and then started processing the information. The more sophisticated vendors that had automated systems of their own could send in their data files via an FTP site set up for them, and a script checked for the presence of a file every 15 minutes and processed them when they arrived.

A generic import script was set up to pull in the information from a file into the central table. This script was modified for each vendor’s data file to match the order of the columns, days vs. weeks for lead time, the vendor’s way of specifying the manufacturer, etc., and had error checking to catch anything wrong with the data. If something unexpected came up, such as letters in the lead time or a manufacturer name that didn’t match the list of known manufacturers, the system generated an e-mail to the purchasing group, including a copy of the data file, what the specific error was, and who the vendor was.

A similar model was set up for the vendors that were fulfilling purchase orders, including the company’s PO number, price, quantity, manufacturer, model, expected ship date, tracking number if it was shipped, etc.

The table tracking product being handled in-house didn’t have the same data import integrity concerns, but it did use APIs to pull information from other company systems, like the scheduling software and the time tracking software.

With all of the data normalized, each employee was able to create individual reports with only the information they needed. Rather than using static “display only” reports, the system allowed for adding and updating information in the report, like PO number or received date. Since people had reports specific to their own needs, they put their own filters in the reports and weren’t forced to sift through irrelevant information.

Also, with key information in the system like expected and received dates, the system ran and e-mailed nightly reports showing everything that was expected but not received so that the purchasing department could start the day focusing on those problems. The receiving department got reports every morning showing what was expected in the next two days so they could prepare. The same reporting system generated reports, based on customer, of everything that was shipped and e-mailed the report to that customer. As part of the new customer set up process, the Customer Service Reps requested the preferred format from the customer for the report, including whether it would be a CSV or a “human readable” PDF, and set it up for them. If the customer didn’t have a preferred format, a generic one was used so that every customer got notifications for every shipment. Since all of the information was normalized and most customers wanted similar information, the Customer Service Reps were able to set up the reports quickly and easily.

Dashboards were set up with two different types of reports. One showed all the exceptions, like unreceived overdue product or product in-house that should have been shipped, with drill down capability to show details on problem. The other type of report just showed Key Performance Indicators, like % of shipments to customers that were late. Clicking on the % brought up a drill down report showing which specific shipments were late, and clicking on a shipment showed even more detail about the order.