August 8, 2019
Is every bit of data in your spreadsheet error free?
Keeping it personal can lead to
11 min read
Finding and Eliminating Costly Merchandise Planning Mistakes
Everyone makes mistakes. But why mistakes are made and how to resolve them is more important to understand.
Operator error is a mistake that can simply be described as an error that is made by a human. But, like many things in life, it is a bit more complicated when you start to explore the topic further. Operator error is when a person makes a mistake because that person simply made a mistake as opposed to that person being confused or influenced by other factors of the design. It turns out that spreadsheets are an environment that makes it easy for operator errors to be made and more troubling, not easily discovered. It is a subject that has been studied by Raymond R. Panko at the University of Hawai’i and summarized in his paper, “What We Know About Spreadsheet Errors.”
“Errors occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists.”
– Raymond R. Panko
Professor Panko leads the Spreadsheet Research (SSR) project to present research conducted into the causes and frequency of errors that occur in spreadsheets. What started off as an area of interest, quickly turned into a more serious endeavor as spreadsheet errors and security were no longer just an academic exercise with the rise of regulatory pressures on the industry for compliance and financial reporting.
Given the importance of spreadsheets in guiding millions of dollars of investment in the apparel merchandise planning process, the topic should also be taken seriously by our industry.
Based on the research, it is clear that our spreadsheet planning tools have errors encoded into them.
So, what to do?
The first step is becoming aware and accepting that it is highly probable that your planning spreadsheet has some level of error. Professor Panko and Salvatore Auriguemma published a paper shining light on the types of errors that exist and their causes.
“Perhaps the most important finding(s) …
- Human cognitive processes produce the correct result nearly all the time but have a small inherent error rate that stems from the same processes that produce correct results….so how we think is the root cause… not sloppiness
- Unfortunately, the fact that we make relatively few mistakes as … is not good enough in some contexts, e.g. software programmers that are 95% to 98% accurate and the same are true with spreadsheets, but this level of accuracy is not good enough
- A third important finding is that while we are good at avoiding errors as we work and catching many of our errors immediately, we are not as good at detecting errors after the fact.”
From the research it is clear that any errors that exist are few and far between and not easily detected by the author of the spreadsheet. This is the same situation as pointed out by Panko and Auriguemma that occurs in software development. As that is an area of expertise at FIND, we suggest that you consider the following techniques to find and fix potential errors
- Minimum and maximum values. One useful technique is to enter fantastically small and then large values for your variables. Large values will quickly show you results that should be affected by the change but are not being modified or are not changing to the degree anticipated. Entering small values will often show the same but tends not to be as dramatic as entering incredibly large values.
- Excel Error Checking. Excel has a built-in error checking function that can help you identify the root cause of cells that are showing broken values and provide data monitoring capabilities.
- Quality Control. In software development, we have a dedicated team of quality control engineers that develop automated testing, procedures and process for identifying and fixing any issues that arise. While you are unlikely to have such a team at your disposal, it is a useful exercise to consider “stopping for gas” and having another team member or members stress-test your spreadsheet with the explicit goal of finding errors. It can be a fun exercise with a reward such as a gift card or other prize being provided to the individual that finds the most, or most impactful, error. A team lunch can also be a great incentive to have the entire planning team get a bit competitive in stress testing your systems.
Set up a regular schedule of testing that occurs if the spreadsheet is updated or before the beginning of each planning cycle.
At the end of the day – you’re not alone.
At the end of the day, you’re not alone. Everyone makes mistakes and if the research is any guide, all of our complex spreadsheets are likely to contain an error or two. A small amount of time invested in ensuring your go-to tool is as error-free as possible can save you hours of time and potentially significant amounts of money by getting it right before you and your teams create and present your planning, buying and allocation recommendations.
As always, FIND is dedicated to your success. Processing your data with AI to get planning insights in an online shareable environment will not only help you optimize your sales budget, revenues and margins, it will go a long way to enabling your teams to work together without the worry of having errors introduced during the process. If you’re interested to learn more, check out FIND Plan and sign up for the FIND Plan Report, and you’ll have a chance to learn just AI can do for you and your data.