As my department worked through a crisis several years back, I discovered that the information we needed was not available in a format that would help us resolve the issues. Due to the nature of the crisis, and the need to communicate information inside the company and to our customers, I found a way to assemble a large amount of data into a single, concise communication that made sense to everyone, not just supply chain people.
“Every now and then one paints a picture that seems to have opened a door and serves as a stepping stone to other things.” ― Pablo Picasso
I have a feeling that Picasso wasn’t thinking about customer service when he said this, but I like his sentiment. If painting ‘pictures’ can serve as stepping-stones, then they can help us move on to bigger and better things.
I was the Supply Chain manager when a production issue necessitated a massive recall for half of the existing inventory in my category. The production network was running at 100% capacity when the problem occurred so we all knew it would take time to recover. The million dollar question (literally) was how long would the situation affect our customers? A few weeks? Several months? As you might imagine, our customers were not going to accept an answer of ‘we aren’t sure when you will get your product.’ I asked my planner for a report that would show the weekly inventory position for every item for the next 13 weeks, and I was told the planning system was not able to generate such a comprehensive view.
Since a problem of this size was new for me, I asked my compatriots for examples of how to manage such a major problem. Unfortunately, I was only greeted with kind words of support. It turned out that nobody on the staff had dealt with a problem this large before and no template was available.
It was clear that we needed to develop a method for communicating the status of the inventory, and here are the steps my team and I took to ‘paint a picture’ that would generate useful information for Management and ultimately our customers.
- Determine what information is available from the planning system, and how to download it into a spreadsheet.
- Weekly production plans were available in the supply planning system, however a complete view of the inventory position was not available in a single report. This was a quirk of the system, and getting individual items to aggregate into a single report was something I.T. was not willing to tackle, regardless of the circumstances.
- We found that the individual item information could be downloaded from the planning system, however, this report included raw data showing the current inventory and planned production quantities along with other pieces of extraneous data. Using an ‘intermediate’ spreadsheet, we extracted and formatted the information needed for the next step.
- Once the data is in a spreadsheet, determine how to format the information to accurately tell the story
- Within our company, most people understood the inventory position in terms of ‘days of coverage’. For the purposes of communication, we needed to put several of the items on a single page, to see the overall effect of the production plan. The most accurate way to calculate the inventory coverage was to develop a formula that used 1) the inventory available at the beginning of the week, 2) the production plan, and 3) the demand forecast, in order to calculate the theoretical days of coverage in the inventory each week going forward.
- Once the formulae were set up to calculate days of supply for the next 13 weeks, we determined it would be helpful to color-code the information to generate a way to identify issues more easily. This is where the ‘stop-light’ formatting for each cell was used; with green being considered ‘good’ inventory levels, yellow considered ‘risky’ or potentially problematic, and red being ‘bad’ or definite customer service problems. We also used blue to indicate if the inventory was above target.
- Develop a view that provides information for driving decision-making.
- Once the data was verified and the color-coding applied, we assembled a view to show the ‘days of supply’ for all items for the upcoming 13 weeks. It indicated that it would take over two months for the inventory to completely recover. (The chart below is a simulation of the initial inventory positions we discovered. It does not use the actual data.)
- With the inventory projection view finally in place, we could discuss various production options, such as delaying the production of less popular (slower moving) items, and risking some case fill issues, while producing larger volumes of the more popular items. A few scenarios were presented to management for their review and decisions were made regarding the information to be shared with customers. Now we could show our customers when they could expect the product back on their shelves.
Prior to developing this methodology, we were making educated guesses about the timing of the recovery. For the most part, these guesses were inaccurate, frustrating management and our customers alike. Having much more accurate and comprehensive days of supply information available was extremely helpful to the Sales team and our customers. Armed with this information customers could find ways to work around the gaps on their shelves.
At the end of the day, a clear picture of bad news was better than not knowing when customer service would recover. With a chart like the one above, we were able to have meaningful and honest discussions with our customers. Sharing accurate information, even if it is not good news, is the key to building trust between organizations.
Picasso was certainly correct about a picture serving as a stepping-stone. We found a way to move forward with our customers once we generated a clear picture of the situation.
Photo credit: depositphotos.com image:26852525 by: semisatch