Work-Around Proliferation
In addition to examining the performance of an entire economy over a period of time, next examine the day-to-day actions of the people charged with making decisions about how to utilize assets. One hallmark of supply chains is the presence of supply orders. Supply orders are the purchase orders, stock transfer orders, and manufacturing orders that dictate the flow and activities of any supply chain.
The very purpose of a planning system is to ultimately determine the timing, quantity, and collective synchronization of the supply orders up, down, and across the levels of the network. Inside most manufacturers there are tiers within the planning system where stock transfer orders could prompt manufacturing orders that in turn would prompt purchase orders. Additionally, within most supply chains there are tiers of different planning systems at each organization linked together by these orders and communicating through these supply order signals. For example, purchase orders from a customer can prompt stock transfers or manufacturing orders at suppliers.
Perhaps the biggest indictment of just how inappropriate modern planning rules and tools are can be observed in how frequently people choose to work around them. The typical workaround 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.
Consider polling on this subject by the Demand Driven Institute from 2011 to 2014. With over 500 companies responding, 95 percent claim to be augmenting their planning systems with spreadsheets. Nearly 70 percent claim these spreadsheets are used to a heavy 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.
So what do they do instead? They work around the system. They each have their own ways of working with tools that they have crafted and honed through their years of experience. These ways of working and tools are highly individualized with extremely limited ability to be utilized by anyone but the originator. This is a different, informal, highly variable, and highly customized set of rules.
Worse yet, there is no oversight or auditing of these side “systems.” There is no “vice president of spreadsheets” in any company the authors have ever worked in or visited. 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.” (Jeremy Olshan, April 20, 2013)
As an example of how disastrous spreadsheet errors can be, consider the role a spreadsheet error played in a $6 billion disaster for JP Morgan in 2012. The following is an excerpt from the zerohedge.com article “How a Rookie Excel Error Led JPMorgan to Misreport Its VaR for Years”3:
Just under a year ago, when JPMorgan’s London Whale trading fiasco was exposed as much more than just the proverbial “tempest in a teapot,” Morgan watchers were left scratching their heads over another very curious development: the dramatic surge in the company’s reported VaR, which as we showed last June nearly doubled, rising by some 93% year over year, a glaring contrast to what the other banks were reporting to be doing.
Specifically, we said that “in the 10-Q filing, the bank reported a VaR of $170 million for the three months ending March 31, 2012. This compared to a tiny $88 million for the previous year.” JPM, which was desperate to cover up this modelling snafu, kept mum and shed as little light on the issue as possible. In its own words from the Q1 2012 10-Q filing: “the increase in average VaR was primarily driven by an increase in CIO VaR and a decrease in diversification benefit across the Firm.” And furthermore: “CIO VaR averaged $129 million for the three months ended March 31, 2012, compared with $60 million for the comparable 2011 period. The increase in CJO average VaR was due to changes in the synthetic credit portfolio held by CIO as part of its management of structural and other risks arising from the Firm’s on-going business activities.” Keep the bolded sentence in mind, because as it turns out it is nothing but a euphemism for, drumroll, epic, amateur Excel error!
How do we know this? We know it courtesy of JPMorgan itself, which in the very last page of its JPM task force report had this to say on the topic of JPM’s VaR:
“. . . a decision was made to stop using the Basel II.5 model and not to rely on it for purposes of reporting CIO VaR in the Firm’s first-quarter Form 10-Q. Following that decision, further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR.... it also remains unclear when this error was introduced in the calculation.”
In other words, the doubling in JPM’s VaR was due to nothing but the discovery that for years, someone had been using a grossly incorrect formula in their Excel, and as a result misreporting the entire firm VaR by a factor of nearly 50%! So much for the official JPM explanation in its 10-Q filing that somewhat conveniently missed to mention that, oops, we made a rookie, first year analyst error. (Tyler Durden, February 2, 2013)
Perhaps a more interesting question is why are personnel allowed to use these ad-hoc approaches? From a data integrity and security perspective, this is a nightmare. It also means that the fate of the company’s purchasing and planning effectiveness is in the hands of a few essentially 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 a lot of wrong 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 just 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? 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 service levels fall dramatically. They 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
