How to reverse engineer an excel spreadsheet
March 13th, 2014
post : Business
As a new system approaches attention turns to the amazing Microsoft Excel spreadsheet that everyone uses but no one has any idea how it works. The person who created it left the company and now, as department head, all eyes turn to you. You must know how it works as your team has been making decisions based on what it tells you.
Every operation that I have stepped into has at least one spreadsheet that is taken as gospel, yet how it produces its results remains a mystery. Usually these are simple tracking tools, used to record entries and made sure actions happen. Occasionally though they can turn into Excel workbooks that churn out predictions and analysis that the business relies on. The worst example I found calculated various solvency margins and took so long to calculate it was often left to do its work overnight.
The question is how do you reverse engineer one of these Excel brutes? Fortunately with some clear thinking, a couple of sheets of paper and somewhere quiet these four steps can help you unpick the mystery.
What is it supposed to be doing?
Before looking at any scripts or formula, ask what everyone thinks it is meant to do. Look at how people use the spreadsheet and whether there are problems with the way data is presented or collected. One team had errors in how they used their Excel model because the data entry and output were at different ends of the row, leaving them to scroll back and forth, sometimes resulting in them losing their place.
Flowchart the formula.
For each formula on the spreadsheet produce a flowchart that shows how it goes about its business. Individual formulas can become quite complex and can be spread across multiple cells, so a simple flowchart that shows the logical steps it goes through and the cells it gets data from is incredibly useful. It also helps with the next step.
Check they are right.
A surprisingly high number of spreadsheets have errors in them, so check the formula is correct. With a logical sequence it should be a simple process to step through each calculation point and ensure the right inputs, operations and outputs are coming through. One team I went through this exercise with discovered a calculation had a rounding error, which had caused their problems with accurately predicting production timings.
Refine the calculations.
Look at the calculations and see if there are ways they can be made more efficient, easier to understand or known problems can be corrected. Error messages were a curse on a particular sheet as the designer hadn’t corrected for when cells were empty. As a result the team ignored them, even when they were genuine warnings they should have paid attention to.
Depending on the complexity of the spreadsheet this can take anything from half an hour to a couple of days. The end result should be a simple document that sets out what the spreadsheet does, how it does it and is ready for someone to turn into a better Excel model.