By Tim Heng
“Excel is often considered to be risky and unreliable within information technology circles. We consider the reasons why IT-developed systems are considered less risky, and apply the same fundamental principles to Excel spreadsheet construction.”
I was planning on writing this article about six months ago, and never got around to it. Then, I read an article recently published in the May 2012 edition of CompAct entitled “Spreadsheets—the Aging Friend of Actuaries,” in which Mark Grall highlights why the use of spreadsheets in organizations is outdated, non-transparent, risky and needs to be retired in favor of enterprise systems and specialized actuarial software (e.g., MoSeS and Prophet).
Mark makes some good points. The way spreadsheets are used and managed in many large organizations is outdated, non-transparent and as a result, fraught with risk. However, what are actuaries if not risk managers? The core issue isn’t the software tool being used (spreadsheets versus enterprise solutions), but rather, the systematic risk caused by the underlying development process.
Throughout this article, I’m going to make reference to Excel (being possibly the #2 most hated piece of software in existence, second to Windows), but the same principles would apply to any spreadsheet software. If you like, think back to the “good ol’ days” of Lotus 1-2-3. The world hasn’t changed that much since then.
WHY EXCEL IS A BLACK SHEEP, AND WHY IT DOESN’T NEED TO BE
I want to first address key risks people highlight when they talk about Excel.
“Spreadsheets aren’t transparent—nobody knows how < X > works.”
“There’s poor version control in a spreadsheet—lots of different versions are floating around.”
“It’s easy to hide changes in a spreadsheet that nobody will ever know about.”
“How do we know there isn’t an error buried somewhere in the spreadsheet?”
These are fair criticisms—in any organization, version control, change management and transparency are key concerns and are valid, independent of the solution in place. I’d like to draw a parallel between the concerns above, and the following:
“Enterprise software isn’t transparent—nobody knows how < X > works.”
“There’s poor version control in software—lots of different versions are floating around.”
“It’s easy to hide changes in software that nobody will ever know about.”
“How do we know there isn’t an error buried somewhere in the software?”
It’s beneficial to draw an analogy between a software development process and a spreadsheet development process. By doing so, we can see how the software development process overcomes the same concerns, and learn some lessons to apply back to the way we manage our spreadsheets.
IDENTIFYING STAKEHOLDERS AND OWNERS, SCOPING AND REQUIREMENTS
The most crucial step in software development is always to identify everyone involved in a development process, and to agree on the scope of the program. This process helps mitigate the risk that the scope is incorrect or does not meet the requirements of relevant stakeholders, and to document exactly what was agreed upon in order to avoid disputes in the future. It also serves to identify who the owner of the product and process is, and where responsibilities lie throughout the product’s lifecycle. The product owner is likely to be the business team which requested and championed the development, which will then be responsible for quality control/testing and maintaining it in the future.
How often do we see this occurring in spreadsheet development? Answer: generally never. It’s rare that a spreadsheet in a business will actually go through any sort of detailed scoping exercise, let alone assign ownership and responsibilities for stakeholders. Instead of a single meeting where all the requirements are put on the table and scoped out, spreadsheets are often born from a series of ‘conversations,’ passing from one manager to another and eventually snowball into a rambling list of instructions for an analyst to implement. Leaving aside the risk that the resulting spreadsheet may not actually meet the end-user requirements, there is little accountability in the process in the event that a dispute arises.
Ownership inevitably ends up in the hands of the analyst and their team, despite having little input into the requirements, and often leads to a situation where a team manages and maintains a model for no other reason than that they built it, but doesn’t know why or how it’s being used elsewhere in the business. All this can be avoided by organizing a meeting between the spreadsheet development team and the business stakeholders.
DESIGN, DEVELOPMENT AND DOCUMENTATION—GETTING THE FRAMEWORK RIGHT
Design
Designing a framework to understand how requirements can be met from a big picture perspective is crucial to avoid unnecessary time wastage and risk. By understanding how each piece of the software puzzle fits together, the actual development process can work with the end requirements goal in mind, within the context of the organization and the available resources. This reduces the risk that parts of the software replicates processes within the business or other processes in the project. It also ensures that definitions of key concepts can be standardized, avoiding any miscommunication within developers, and between the development team and the rest of the business. Having the framework in place early on also allows companies to clearly identify any potential risks that may arise later in the development process.
Again—how often do we see this in spreadsheet development? A common complaint we’ve heard from companies is that the same process gets replicated time and time again across different departments. For the lucky companies, this only means time wasted replicating a process. More frequently, the result is inconsistency in data reported from each department. All too often, companies are forced to be satisfied that inconsistencies between finance, management accounting and sales functions are “within tolerances,” even though they are all reporting on the same numbers. Doing a bit of research first to see how items should be calculated within an organization, and how they have been done in the past, can save significant time in the development process, and avoid headaches for managers once implemented.
Development
One key difference between spreadsheet and software development is in the mindset of the approach. One aspect of software design is the mentality of error handling. A developer will constantly work with what if-style questions to consider how a program will deal with particular situations. There is likely to be an error log that sits above the development process that catches any errors that occur, and alerts developers to the location and type of issue. This sort of mentality can and should be applied to spreadsheet development, and it can be done very easily! It’s relatively easy to create checks and balances to ensure that values are being calculated correctly, and even easier to link these together in an overarching integrity test to highlight quickly what and where something is going wrong.
The other key point of difference lies in standardization. Coding in Excel is no different from coding in C++, VB or any other programming language. The people who argue that Excel is not transparent obviously don’t look at the back end of their enterprise software! In software, there are examples of both bad and best practice. In first year courses at university, programmers are taught to leave comments, indent loops, and a whole host of other “rules” that are aimed at making code more transparent and presentable. What do we get taught at university about Excel rules? (On that note, what do we get taught at university about Excel at all?)
In Australia at least, the answer is “not very much.” A survey of graduate staff (average two to three years of experience) conducted by Macquarie University showed that only 30 percent of respondents reported learning spreadsheet skills during their university education. An even lower proportion (28 percent) of respondents reported that they had been sent on a training course by an employer. This shows that the standards of spreadsheet development can be vastly improved. I’ve trained hundreds of people in public and in-house financial modeling workshops in the United States and Australia, and the common theme of feedback is that people appreciate the transparent and standardized methodology that we apply to modeling, and how they wished they’d learned more about Excel earlier in their careers.
If the same emphasis on transparency and presentation that is applied to software development is applied to spreadsheet development, either through appropriate training, or by applying a methodology and having business policies and procedures in place, there will be no reason to suggest that spreadsheets are less transparent than any other software tool available.
Documentation
I mentioned earlier that programmers put comments into their code, because they’ve been taught to provide transparency and highlight key design considerations. This mentality extends to any documentation in support of a program or product as well. Writing manuals, user guides and providing support for business users and future product owners is pivotal in ensuring that knowledge is transferred from current teams into the future. So why isn’t this done when building spreadsheets?
TESTING, EVALUATION AND ONGOING OWNERSHIP
User acceptance testing is the bane of any analyst. Mindless running of scenarios and inputs, in line with a pre-agreed testing regime—it is the main task that people try to avoid in the development lifecycle. However, the process results in software that has been rigorously evaluated and can flexibly deal with any reasonably foreseeable scenario. Once it’s in place, it’s locked down and access to the source is restricted.
In the context of a spreadsheet, how frequently is it tested? You could ask yourself—what proportion of core, company critical spreadsheets have been internally or externally audited? Can it deal with all scenarios effectively? Are there formulae errors that might result in an incorrect value under certain circumstances, that won’t be uncovered until a scenario is run? Spreadsheet tests usually involve a team manager casting their eye over some key numbers and seeing if the results are “reasonable.” The lack of a rigorous testing regime reduces the confidence that stakeholders have in a product, and contributes to the general perception that spreadsheets are unreliable and untrustworthy. Similarly, having spreadsheets freely available to everybody who has network drive access perpetuates issues of version control and change management. By making it read-only and locking down the master versions of files, similar to live software products, we can reduce these concerns.
WHY SPREADSHEETS OFTEN AREN’T BUILT TO SPEC
As I’ve written this article, I’ve been seeking opinions from peers in various industries on their thoughts. Their criticism of trying to build spreadsheets using the software development process is that it adds significant time and cost to the process, and that it reduces the flexibility of Excel to provide tactical solutions—short-term fixes to problems.
What they’re not considering is how much time it really costs. It doesn’t add significant time to get stakeholders to come to a meeting to confirm their requirements, and if it’s hard to get everybody in a single room, an email chain outlining the scope should be enough to have everybody on the same page. Keeping the organization’s definitions and warehouses in mind when obtaining data and setting up calculations can potentially reduce the amount of time spent, and also makes reconciliation easier. It certainly doesn’t take any great amount of time to ensure that even short-term fixes are well presented, transparent and built in a best practice manner.
That being said, it’s not the tactical solutions that generate the most concern. The big issue with spreadsheets is that they’re often built to plug a business need and end up being retained for years after the original developers have left their roles, while scopes have changed. It is these sorts of spreadsheets that need to evolve. A long-term problem needs a long-term outlook when being addressed, and following the software development lifecycle is a reasonable way of looking for a solution.
CONCLUSION
Excel is often seen as the black sheep—it has a perceived lack of controls, and often seems like a meaningless wall of numbers and text. I would like people to see that this is more to do with the process of building the spreadsheet, and less to do with the spreadsheet itself. All it takes is the mentality to reduce spreadsheet risk in the culture of an organization, and the investment will start paying dividends.
Tim Heng is an associate at Corality. He can be contacted at tim.heng@corality.com.