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 in environments with assemblies and shared components because the lack of one part can block the delivery of many parent parts.
Figure 1-8 is a conceptual depiction of a bimodal distribution across a group of parts. The bimodal distribution shows a large number of parts that are in the too-little range, while still another large number of parts are in the too-much range. The Y axis represents the number of parts at any particular point on the loss function spectrum.
FIGURE 1-8 Bimodal inventory distribution
In Figure 1-8, not only is the smallest population in the optimal zone, but the time any individual part spends in the optimal zone tends to be short-lived. In fact, most parts tend to oscillate between the two extremes. The oscillation is depicted with the solid curved line connecting the two disparate distributions. That oscillation will occur every time MRP is run. At any one time, any planner or buyer can have many parts in both extremes simultaneously.
This bimodal distribution is rampant throughout industry. It can be very simply described as “too much of the wrong and too little of the right” at any point in time and “too much in total” over time. In a survey by the Demand Driven Institute between 2011 and 2014, 88% of companies reported that they experienced this bimodal inventory pattern. The sample set was over 500 organizations around the world.
There are three primary effects of the bimodal distribution evident in most companies:
• High inventories. The distribution can be disproportionate on the excess side, as many planners and buyers will tend to err on the side of too much. This results in slow-moving or obsolete inventory, additional space requirements, squandered capacity and materials, and even lower-margin performance, as discounts are frequently required to clear out the obsolete and slow-moving items.
• Chronic and frequent shortages. The lack of availability
