When it is time to Automate Spreadsheet Madness?
Updated: Aug 25, 2022
Spreadsheets have been the cornerstone of business for over 35 years, so much so, we used to have at least 3 versions to choose from, Microsoft, Lotus and Wordperfect. Even before Windows was invented, we could spreadsheet in Lotus 1-2-3 for DOS. Data mining and visualizations were invented in spreadsheets, empowering the every day employee to create their own narratives about inventory, sales, and production.
The data analysis businesses complete in spreadsheets today is out of bounds! Complex financial statements, dashboards of KPIs and OKRs, intricate calculations and algorithms, compiling data via lookups to other pages in the workbook. Financial professionals are infamously known for creating these amazing and plentiful spreadsheets. But where do we get into trouble with such sophisticated analysis and process in spreadsheets? When is it time to consider converting the spreadsheet to a more automated, paginated or visual, delivery? Here are 4 cues it might be time to reengineer the precious spreadsheets companies relies on.
One aspect of self-service BI enables companies to download data to a spreadsheet to create their own insights. This independent process begins with data from a source system and often ends with much manual manipulation. The latter part is where challenges arise, edits, inclusions, and exclusions change the nature of the original data’s intent. If those “edits” cannot be verified or cross referenced in a source system, this spreadsheet process becomes not auditable. A client made a $1,000,000 edit one month in the formula of one cell of a financial spreadsheet. He then forgot to remove the edit for the rest of the financial year. When it became time to automate his mysterious process, the million dollars could not be verified, anywhere. This mistake eventually cost that financial professional his job due to the high-level nature of the audience of workbook’s output.
Having multiple copies of a manipulated spreadsheet running around the organization can create the loss of valuable data context and governance. One person’s vision of the data and story told may not be interpreted the same by another. Sharing the spreadsheet becomes problematic without a specification document explaining its intent and once others' start manipulating it, they create multiple versions of various truths. An automated version has been scoped out, standardized and documented, maintaining the data’s integrity and story for anyone and everyone in the company to understand.
When the spreadsheet wizard goes on vacation/retires, some poor admin or junior analyst becomes responsible for what essentially is a financial think tank’s brain child. Even with a detailed schematic, mistakes will be made when trying to replicate the process frequency of the data compilation. A junior analyst at a client became so completely devoured with repeating a weekly workbook process, it took him the entire week, every week, to complete it. The original spreadsheet genius could complete it in less than a day. This resource’s time, which turned into 6 months when the wizard retired, became heavily wasted on this intense manual process. Automating this spreadsheet chaos cost 1/5 the resource’s salary, freeing up the resource to fulfill his original intended role contributing to the organization and allowed the process to be stably generated by anyone in the organization.
Unraveling the workbook to figure out where to insert one cell, one lookup page, add one more metric, exposes the fragility of spreadsheet chaos. One shift breaks all the lookups, integrating one new dataset can take days or weeks, and if the spreadsheet wizard isn’t available, data decisioning is put on hold. Having a reliable and scalable spreadsheet process is nearly impossible in today’s complex analytical realm, so before the workbook reaches puberty, it is time to think about looking at automation.
Fox Consulting has 30 years of experience in automating the chaos of spreadsheet madness. If you have complex workbooks mainly based on source system data, contact us to discuss the possibility of migrating these to auditable, reliable, repeatable, and scalable reports.
#bestpractices #excel #quality #success #maintenance #dataanalysis