The spreadsheets is the assembly language for financial modelling.
Have you picked up the news that Microsoft's latest version of Excel has increased the maximum size of a worksheet? If model quality had been taken seriously, a more responsible move would be to reduce the number of cells allowed in spreadsheets!
I have often wondered why the spreadsheet is so popular; when it is so "bad". More or less everything they teach you at our universities about software quality is violated by the spreadsheet metaphor. Still, people prefer the spreadsheet over the many thousand alternative modelling and programming tools available from different vendors. Sure, the spreadsheet is easy to use and it is flexible. It provides a convenient tabular layout for presenting information nicely framed by borders and coloured headers and charts.
From the beginning, the electronic spreadsheet was built upon a concrete and well-established paper metaphor. Compared to its predecessor, the paper-based spreadsheet used by the financial community at the time, the computerized spreadsheet represented significant productivity improvements. Most important, one did not have to throw the entire spreadsheet away and start another one if it turned out that more space was needed for a given table. With the computer spreadsheet, users could just move cells and tables around to make room.
The provision for automatic calculations of formulas, such as column sums, was a natural extension of the spreadsheet once it had made its move from paper to computer. While the spreadsheet has maintained, and even strengthened, its role as a lay-out application for tabular data and charts, the importance of its computational capabilities has increased even more. What started out as a reporting tool for financial data has now become the most widely used "programming" environment for business modelling.
A growing number of people are getting concerned by the lack of quality control provided by the spreadsheet. How do we know if the nicely formatted spreadsheet is nothing more than "whipped cream on a road apple [horse poop]"? (Scott McNealy). In many cases, an honest answer is: "we don't know – but we do hope – that the numbers are all right."
Around 1980 I was fascinated by the way a micro processor could be programmed by feeding it with instructions coded as (hexadecimal) numbers. The computer memory consisted (and still consists) of numbered memory cells. Each cell can hold an instruction or a piece of data. By carefully inserting instructions and data into memory cells, I was able to create my first application; a time measurement system for sports.
The simple concept of dividing memory between programs and data is the foundation of any software application; word processors, spreadsheets, operating systems, flight control systems, etc. Low-level programming gives the programmer complete control over the computer. Assembly language (programming by computer instructions) is definitely the most flexible programming language around.
The similarities between spreadsheets and low-level programming are striking. A spreadsheet is a two-dimensional array of cells, arranged in rows and columns, and numbered from 1 and up. Each cell can hold data or an expression. The spreadsheet programmer arranges data and expressions to create data flows from input data cells, via intermediate expressions, leading up to the cells holding the results of the spreadsheet model.
Low-level computer memory has no concept of data type. It is up to the programmer to make an interpretation of the binary data. Any given memory cell can be used to represent a character, a numerical value, a piece of a bitmap picture, a part of a floating point number, the co-ordinate of a graphical line, etc. In a similar fashion each spreadsheet cell is an all-purpose storage for data and expressions of arbitrary type. Any given spreadsheet cell can hold a number, a text, an hyperlink, a dollar amount, a point in time, a date, a logical value (true or false), etc.
Software engineers soon discovered that low-level assembly language was both error-prone and slow to work with, in spite of its unsurpassed flexibility. Therefore, high-level programming languages were developed. The evolution of programming languages and methodology has revolutionized software engineering. Programs can be created significantly faster, with fewer errors, and better usability than before.
A corresponding evolution has not taken place in the area of business modelling. The low-level, cell-oriented assembly language approach is still dominating the financial modelling community. As a consequence, business modelling with spreadsheets remains an unnecessarily risky and labour intensive activity.
So, why has no alternative popped up that can replace the spreadsheet way of modelling for serious applications, such as investment models, strategic scenario planning, price setting, portfolio management, and human resource planning?
In the case of software engineering, programmers can develop and improve their own tools. This is not the case for spreadsheet users; you cannot create a new and better spreadsheet using a spreadsheet as the tool for accomplishing the task!
This reminds me of an episode from my childhood: For a long time our laundry lines had some damage. My mother would mention it to my father from time to time, but it was not until he one day was alone home and had to put out the laundry by himself, that the laundry line was fixed; that very same day!
Most modelling tools that could serve as alternatives to the spreadsheet, however powerful they might be, do not match the spreadsheet when it comes to ease of use and flexibility. In the software engineering area we have programming languages such as C++, where the programmer has access both to high-level constructs (such as objects and classes), and low-level constructs (such as direct memory access and interfaces to assembly language). A modelling tool with the corresponding capabilities is hard to find. Either it is too low-level and unsafe, or it is too high-level, narrow and rigid, with a steep learning curve making it inaccessible to the users who need it the most.
The above observations form some of the background for initiating the work on Dynaplan Smia – a modelling tool as flexible and easy to use as a spreadsheet, with high-level constructs for safe and fast model construction, visualization, and maintenance.
Is the spreadsheet really a "bad" thing? Nothing is good or bad in itself (Paul); it depends on how it is used. The spreadsheet way is the fastest way to create a bunch of equations that are not worth keeping. And that is not bad at all! I use spreadsheets all the time, for non-critical tasks. I use it like a mathematical scrap book for doing small calculations. While I stay away from spreadsheets containing many different formulas, I don't mind a spreadsheet filled up with data, as long as the data is not intermixed with formulas other than fill down stuff and row sums.
If they would take away the save button from Excel, I might actually start to like it!