Excel is a great software but… for some tasks is just not the right tool.
In this article we share a story of how Excel ended up costing billions of dollars because of a simple error.
Luckily there is none in your spreadsheets :)
We all know how this happens: you start creating a short list of your clients in an excel spreadsheet and a few weeks later you are using Excel as it was your CRM. You add a lot of columns, references across sheets, you start writing longer and longer formulas.
Someone is using Excel to track processes. Someone even use it as a task manager. Someone else to record procedures.
In some companies Excel end up being used as a database. A database poorly managed, fragile and with terrible performances.
Now, why this happens?
It is easy to understand why: people are already familiar with it. So, it is not scary to just create a new excel file and put some data in it. Then you add a little column, later you modify a formula as new requirements arise. Everyone sort of know how to use it, so everyone modifies it a little, expand the thing a little bit. Before you know it you are looking at a ball of mud which keeps growing and growing.
Now you realize that everyone is familiar with Excel, but your complex spreadsheet have to be used in a certain way: you first have to add a row here, then move to this other sheet and insert a value there… every Excel file looks the same, but the logic is specific. It does not seem so intuitive now, does it?
And there are other things that could cause problems:
- There are many versions of the same document. Who has the right one?
- Excel can do basic work with data manipulation and its presentation (i.e., graphs), but pretty much nothing else that well
- Most checks and analyses can only be done manually
The last point is the most important, especially for a company that uses Excel to perform the core part of their business. For instance, there are insurance companies in which expert financial analysts use Excel to create the plans that are going to sell to their customers. They do in Excel all the calculation relatives to the premiums to pay, the evaluation of risks, etc.
Does it sound like a good idea?
Well, it could end up costing more than a bit of money.
A Fragile Process
People use Excel because they think there are no alternatives and using Excel is good enough. In many cases this is not true, there is specialized software that you could use to get better results and more efficiently. But maybe it does not fit exactly your case so you do the simplest thing you can: you hack together a solution using Excel. There is not wrong with that if you are doing so for a secondary process. However, it becomes risky when you use Excel as an integral part of your product development or services delivery or the way you plan fundamental decisions.
The problem is that the process is incredibly fragile. The people that use Excel to create your product, that is to say your experts, need to take a lot of time to double check that everything is correct: because the wrong parenthesis or the wrong number can break a product. Those little errors can hide everywhere and go unnoticed for a long time.
…as it is proved by this story
You think that your experts are so good with Excel that they do not make mistakes? Think again. There is a famous paper Growth in Time of Debt, also known by the name of its authors Reinhart–Rogoff, whose central thesis was about debt and its relation to growth. The paper seemed to prove that when the external debt of a country reached 90% of the GDP, the growth of the country was roughly cut in half. The paper had a huge influence, even because of its timing, after the financial crisis of 2007-2008. Governments and supranational institutions used it to support austerity policies, government fell, and people were fired to lower the debt of a country.
The only problem? It was not true. The calculations made to support the thesis were made with Excel and these two world-class economists forgot to include all the rows in a column in their calculations.
Think about that: hundred of thousands of people fired around the world because somebody made a mistake with Excel.
We are not blaming these economists, everybody makes mistakes. What we are saying is that you should be worried, because these errors are also happening at your company, if it runs on Excel.
Would you bet $100 that there are no errors in the Excel files you have used last month?
I guess not. But you probably used them to take decisions that have a larger impact than 100$, right? Like investment banks that use Excel to decide how to invest billions of dollars.
A Specific Solution
The fact is that it makes sense putting together a spreadsheet together to drive a simple process, one that is very simple or has not crucial implication for your organization.
But as the process becomes more complex, and if the process is vital for your company, you cannot afford the uncertainty of Excel, where an error can hide in every cell of your spreadsheet, somewhere, in one of the sheets.
You need specific tools, precise ways to express your business logic. You need a custom solution tailored for your business: a Domain Specific Language (DSL).
A DSL is a specialized (computer) language that can perform only one thing: but it does it great. Think about the page that you are reading: it is powered by HTML, a language designed just to present information on the web. You cannot build the software that powers airplanes with it, but to show information it works great.
A DSL allows the experts to use formula and concepts that are already familiar to them. This means that they can even use the same notation with a graphical interface. For instance, with a DSL you can directly input the parameters to calculate the geometric average rate of return of a investment over n periods of time.
Can you do that with Excel? If so, can you do in a way that is as clear as this mathematical formula? A DSL would not allow to input any mathematical formula, but it would allow to use all the formulas that your experts need to use in their job.
A DSL could also include check and appropriate error messages for each case. For instance, imagine that one of your experts erroneously typed 0.27 (27%), instead of 0.027 (2.7%), as rate of return. A DSL could easily check and warn about this potential error, like this.
Obviously the DSL does not know that the user made a typo, but it knows that 0.27 is a very high rate of return, enough to warrant a warning about a potential error. How does it know that? Because the programmer that designed the DSL put that check there. The programmer knew that because your experts told him about that.
DSLs are used in a wide variety of business sectors. Given their value, most of them are hidden behind company doors1, but there are public examples of ones for everything from finance to security management. In each of these cases, a DSL give domain expert, the ones that build the core of the product, a tool that speaks their language and allow them to do their job more quickly and better.
For example, instead of trying to define the risk of a specific investment in a long series of excel columns, they can create a clear description in a format that they can understand and that a computer can check for formal correctness.
Think about the mistake that the two economists made: they forgot to include some columns. Why Excel did not check that for them? Well, because it cannot do that. An Excel sheet might have an infinite amount of columns and maybe the first three ones are made of headers or comments. Excel has no concept of the meaning of the data that it holds. It just sees a bunch of numbers.
Instead you can build a DSL that understand the data it contains. You can use that format to say to take two list of elements and to see if there is a correlation between them. The DSL will ensure that all the data in each list is used.
A Domain Specific Language can improve productivity and reduce errors.
What you can do with a DSL that you cannot do with Excel
These gains in productivity are nice, but that is not the whole story. It is not even the best part. Because the main issue with using Excel as the cornerstone of your development it is not in the tool per se, but in the process it creates.
In fact to be fair, the Excel error of their two economists was not their only mistake, they also made procedural errors. But if you think about it, that is even worse. Using Excel as the foundations of their process left them completely unprotected by simple mistakes and procedural errors alike.
This is an issue that can hit everybody, even professional with great experience and training that are working literally in life-or-death situations: like surgeons. In fact, some hospitals were able to reduce the death rate by 47% with a checklist.
Yes, a checklist like this one. A simple aid, that forced them to check that certain rules were respected and certain actions were taken correctly.
Because the problem is not just Excel, as a tool, but the manual and error-prone process that is necessarily build around a tool like that.
The real advantage of a DSL is that allows a company to improve the whole process of building a product. The knowledge of your experts becomes code. You now have a precise and formal description of the core of your products. And you can do everything with it, including automatically implement a checklist like the previous one:
- simulate behavior and statistics of a product in development (e.g., automatically calculate how much it would cost to produce it, see how it will behave in relations to your other products, etc.)
- perform validity checks (e.g., ensure that it respects security requirements, confirm that the product will actually work as you expect etc.)
- generate documentation directly from the code (e.g., manuals, explanatory diagrams, etc.)
In short, once you have a formal description of your product, you can use it as the cornerstone of your process and stop using Excel for everything. You can automate many steps and give all stakeholders a precise view of the products you have. So that they can do what they need to without the hassle of byzantine procedures and esoteric Excel sheets.
Are you ready to improve your company?
From an Excel World…
Let’s see a practical example of how your work could change using a DSL.
In the beginning there would be an Excel sheet much like this one. This is a simplified version of one used to calculate the value of a progressive tax.
Noticed that this uses the best practices of Excel: colors and random notes to help people understand how to use it. So that is the best case scenario. We have all seen much worse.
What is wrong with this? A lot.
For starters, this is not the only sheet you need to calculate the value of the tax. The notes tell you to look up somewhere else to set some proper values (e.g., deductions, region) to make everything work correctly.
For instance, you have to look up the region id in another sheet, the other sheet contains the correct regional rates for each region. This is made very explicit in the sheet: there is a note and different formatting. That is probably because somebody kept putting manually the regional rates, maybe because it did not know the region id or did not understand how the thing worked.
For some reason somebody choose to use as a region id a number, probably because it was easier to use as a reference, for them. However it is harder for the user of the sheet. That is one problem.
The other problem is that, since the id is a number, and because of the structure of the sheet, the region id is also used in the calculation of the tax rate. Nobody will know why this happened. Maybe somebody just copied-and-pasted the formula from a previous version. Maybe somebody just added all the things with the orange background without looking what they were, after all, they are all orange for a reason. Which reason? Who knows.
There are also another mistake: the last start rate is wrong, it should be 75.001 euros.
That are the easy things to fix. Who is going to check if the region id is actually correct? As long as it is a valid the calculation will be completed, but maybe the correct region id for that person is 14, not 13. And we all know how people are going to use it: they are going to copy and paste the “standard version” and then add the data for a person. But what happens when the standard version changes? Are they going to recreate each one for each person or are they just going to forgot about it?
What will happen next year? They are going to take the new standard version but also copy and paste the client data from the old version. The client will says something like: “nothing has changed from last year”. So, it is easier to copy than to calculate everything from scratch. And we all know how copy and paste never leads to errors.
That are also constraints due to the very structure of an Excel table: does the user need to know the partial results due to the individual tax rate brackets? Not really, he does not care about them. However, we are forced to show them because of the structure of an Excel table. Calculating the results in one step would result in one giant and really hard to understand formula.
This is the formula for one bracket, imagine putting them all together in one formula.
And that is not all issues: basically the user must calculate deductions by himself looking at the notes of somebody. That is a typical case of partial automation. This could have happened for many reasons: Mike was unable to create an Excel sheet to calculate detraction (which are more complex to calculate), the detractions change so frequently that it would be impractical to copy and paste them in every new sheet, etc.
And this is a very simple example. Look at the excel your team uses: they are surely more complex. What errors include them?
…to a DSL one: how things change?
How the whole thing would work with a DSL, instead?
It would work something like this. First a developer would define the language, working with the tax experts, in order to take care that basic calculation and checks are performed automatically. Then the tax experts would use this language to define the individual tax rates.
Let’s look at the previous example, but written with an hypothetical Tax DSL.
First, we can see that the previous error in range definition is found out and immediately shown to the tax expert.
Hard to miss. That is the message the user would see if he put the mouse over the error.
Then there is all the rest that is better. The formula to calculate a tax, given a set of tax brackets, is always the same. So it can be defined once by the developer and then used automatically by the tax expert. The tax expert would just have to set the proper rates and define the correct ranges.
The user would be able to select the correct region from a list of names, so there would be no mistakes. Even better, you could simply record the region of residence, so to select the correct region automatically for each person. The calculation is made with clear names and symbols, so it is harder that the user would make mistakes.
There is always only one correct version of the calculation, which is in one file. The user cannot mess with that, so it cannot accidentally change it by copying and pasting stuff around.
The only downside is that now you would need two programs: one for the tax expert, which it would use to write the tax rules, the other for the user, which will input the data of each person. However, the advantages are many. We have already seen how a DSL is safer and easier to use, because it is tailored to the tax expert needs. There are many more:
- you could use one calculation in many application (e.g., a web application, a mobile app, etc.)
- you could generate documentation tables and graphs, to show the the tax rates to clients
- you could even generate an Excel sheet (please don’t), in case you were not able to immediately migrate all your organization to the new system
How You Can Learn More About DSLs
That is an easy one: we wrote an article on the subject with everything you need to know about that. It is called the complete guide to Domain Specific Languages.
Still doubts? Write to us, we are always happy to discuss about DSLs!
- We know because we cannot openly discuss the ones we have built ↵
The complete guide to (external) Domain Specific Languages
Receive the guide by email and get more tips on DSLs