Perhaps the biggest indictment of just how inappropriate modern planning rules and tools are can be observed in how frequently people feel compelled to work around them. The typical work-around involves the use of spreadsheets. Data are extracted out of the planning system and put into a spreadsheet. The data are then organized and manipulated within the spreadsheet until a personal comfort level is established. Recommendations and orders are then put back into the planning system, essentially overriding many of the original recommendations from the formal computer system.
Consider polling on this subject by the Demand Driven Institute from 2011 to 2014. With more than 500 companies responding, 95% claim to be augmenting their planning systems with spreadsheets. Nearly 70% claim these spreadsheets are used to a significant or moderate degree. The results of this polling are consistent with other surveys by analyst firms such as Aberdeen Group. This reliance on spreadsheets has often been referred to as “Excel Hell” Validation for this proliferation can be easily provided by simply asking the members of a planning and purchasing team what would happen to their ability to do their job if their access to spreadsheets were taken away.
But why have planners and buyers become so reliant on spreadsheets? Because they know that if they stayed completely within the rules of the formal planning system, approving all recommendations, it would be very career limiting. Tomorrow they would undo or reverse half the things they did today because MRP is constantly and dramatically changing the picture. This phenomenon, known as “nervousness,” is explained in Chapter 3 and was the primary reason for the development of the master production scheduling process in the 1980s.
So instead of blindly following the system, individual planners have developed their own ways of working with tools that they have crafted and honed through their years of experience. These ways of working and spreadsheet tools are highly individualized with extremely limited ability to be transferred between individuals. This is a different, informal, and highly customized set of rules as compared with the formal computer planning system.
Worse yet, there is no oversight or auditing of these side “systems” There is no vice president of spreadsheets in any company. Everyone simply assumes that the people who created these spreadsheets built and maintain them properly. Consider an article in the Wall Street Journal’s Market Watch in 2013:
Close to 90% of spreadsheet documents contain errors, a 2008 analysis of multiple studies suggests. “Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” writes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”9
Perhaps a more interesting question is why are these personnel allowed to work around a system that the company has spent significant resources to implement? From a data integrity and security perspective, this is a nightmare. This also means that the fate of the company’s purchasing and planning effectiveness is in the hands of a few irreplaceable personnel. These people can’t be promoted or get sick or leave without dire consequences to the company. This also means that due to the error-prone nature of spreadsheets, globally on a daily basis there are many incorrect signals being generated across supply chains. Wouldn’t it be so much easier to just work in the system? The answer seems so obvious. The fact that reality is just the opposite shows just how big the problem is with conventional systems.
To be fair, many executives are simply not aware of how much work is occurring outside the system. Once they become aware, they are placed in an instant dilemma. Let it continue, thus endorsing it by default, or force compliance to a system that your subject-matter experts are saying is at best suspect or at worst useless? The choice is only easy the first time an executive encounters it. The authors of this book have seen countless examples of executives attempting to end the ad hoc systems only to quickly retreat when inventories balloon and, simultaneously, service levels fall dramatically. These executives may not understand what’s behind the need for the work-arounds, but they now know enough to simply look the other way. So they make the appropriate noises about how the entire company is on the new ERP system and downplay just how much ad hoc work is really occurring.
The Organizational Level
Another piece of evidence to suggest the shortcomings of conventional MRP systems has to do with the inventory performance of the companies that use these systems. In order to understand this particular challenge, consider the simple graphical depiction in Figure 1-7. In this figure you see a solid horizontal line running in both directions. This line represents the quantity of inventory. As you move from left to right, the quantity of inventory increases; right to left the quantity decreases.
FIGURE 1-7 Taguchi inventory loss function
In the figure, a curved dotted line representing return on investment bisects the inventory quantity line at two points:
• Point A—the point where a company has too little inventory. This point would be a quantity of zero, or “stocked out.” Shortages, expedites, and missed sales are experienced at this point. Point A is the point at which the part position and supply chain have become too brittle and are unable to supply required inventory. Planners or buyers that have part numbers past this point to the left typically have sales and/or operations managers screaming at them for additional supply.
• Point B—the point where a company has too much inventory. There is excessive cash, capacity, and space tied up in working capital. Point B is the point at which inventory is deemed waste. Planners or buyers that have part numbers past this point to the right typically have Finance screaming at them for misuse of financial resources.
If we know that these two points exist, then we can also conclude that for each part number, as well as the aggregate inventory level, there is an optimal range somewhere between those two points. This optimal zone (range) is in the middle. When inventory moves out of the optimal zone in either direction, it is deemed increasingly problematic. The benefit to the company of the center position is maximum return on the inventory investment.
This depiction is consistent with the graphical depiction of loss function developed by the Japanese business statistician Genichi Taguchi to describe a phenomenon affecting the value of products produced by a company. This made clear the concept that quality does not suddenly plummet when, for instance, a machinist slightly exceeds a rigid blueprint tolerance. Instead loss in value progressively increases as variation increases from the intended nominal target until the specification limit is crossed, and then it is a total loss.
The same is true for inventory. As the inventory quantity expands out of the optimal zone and moves toward point B, the return on working capital captured in the inventory becomes less and less as the flow of working capital slows down. The converse is also true; as inventory shrinks out of the optimal zone and approaches zero or less, revenue flow is impeded due to shortages.
When the aggregate inventory position is considered in an environment using traditional MRP, a bimodal distribution is frequently noted. A bimodal distribution exhibits two distinct lumps:
• A bimodal distribution can occur at the single-part level over a period of time, as a part will oscillate back and forth between excess and shortage positions. In each position, flow is threatened or directly inhibited. The bimodal position can be weighted toward one side or the other, but what makes it bimodal is a clear separation between the two groups—the lack of any significant number of occurrences in the optimal range.
• The bimodal distribution also occurs across a group of parts at any point in time. At any one point many parts will be in excess while other parts are in a shortage position. Shortages of any parts are particularly devastating