We have identified that there are hidden costs associated with choosing Excel as your inventory planning tool of choice, highlighted that the problems usually start with your data, and suggested that ever-increasing complexity impacts sustainable inventory outcomes.
In today’s post, we examine the risk associated with having one owner of the spreadsheet: a single individual who builds, extends, customizes, populates, validates, and reconciles the spreadsheet.
The primary focus of the “spreadsheet slave”
In a recent blog post describing how our business spreadsheets end up controlling us, Jordan Skole states “At every organization there exists at least one spreadsheet that is absolutely vital, it is usually emailed around every morning, and it is almost always called ‘something-something-MASTER.xls'”. He goes on to say that every ‘MASTER’ spreadsheet has a person attached to it – we’ll refer to that person as the “spreadsheet slave”.
The spreadsheet slave’s focus is entirely on:
- Running the extracts and reports required to compile the source data from multiple systems, all of which have to be extracted at exactly the same time so that the data is consistent and usable
- Getting the data into the spreadsheet – whether by importing, cutting-and-pasting, refreshing linked queries, populating linked spreadsheets, etc
- Checking that the refreshed data does not extend beyond the rows and columns with formulae – and then modifying the spreadsheet, if required
- Making any changes to the “rules” in the spreadsheet based on new scenarios that have been discovered
- Validating that the data is correct, with no obvious errors, and reconciling that the totals are within predefined “acceptable” ranges
- Repeat the above, should any issues be found
Some spreadsheet slaves have even “automated” some of the above steps, to reduce the time it takes to get the ‘MASTER’ ready for use in the business. However, even with this automation, the data contained in the spreadsheet may be out of date or otherwise compromised by the time it is available for use.
After building the perfect spreadsheet
Building the inventory planning ‘MASTER’ can actually be an interesting challenge. The individual tasked with its creation has to think critically and be creative to take on this complicated project, and there is often a sense of accomplishment when the first version “goes live.” With each iteration, however, the level of excitement reduces as each extension diminishes.
The feelings around this process change, too. It starts with excitement and engagement as the planner overcomes a challenge, which is hopefully followed by a “job well done” from the boss. But over time, the process feels robotic, mundane, and the inventory planner is more likely to hear questions like “why is this taking so long?”
Those are not words most people associate with job satisfaction.
The “spreadsheet slave” is the risk!
Whether they created it or inherited it, the spreadsheet slave is the one individual who knows how the ‘MASTER’ inventory planning spreadsheet works, who understands the end-to-end process to make it usable, and who knows all of the nuances and subtleties involved when changes need to be made. So what happens when that person leaves, is unavailable for an extended period, or is less capable of handling the increasing complexity as time goes on?
Plugging in a replacement into this role creates a growing potential for critical errors. This is an employee who doesn’t understand the inventory spreadsheet or how to populate it, modify it, validate it, or reconcile it. The likelihood of errors increases dramatically in this scenario.
While it is has been well established that more than 80% of spreadsheets contain errors, estimates of the impact of these errors vary considerably. The point is that a single error could have a significant impact on your supply chain and is only likely to be uncovered after considerable damage has already been done.
The dilemma
Now you are caught between a rock and a hard place. Your planner knows how to utilize the inventory planning spreadsheet, but leaving this critical process to a single person makes your business quite vulnerable. Giving the task to another employee carries with it significant risk of errors being introduced. And even the spreadsheet slave can be overwhelmed by the increasing complexity of the ‘MASTER’ spreadsheet, creating ample opportunity for new errors.
In the final post in this series on inventory spreadsheets, we will sum up the issues at hand, and we will explore options for you to free your business from the dreaded ‘MASTER’ spreadsheet without adding significant risk to your business.
