Over the next few months we will continue to look at each of the ICAEW's Twenty Principles for Good Spreadsheet Practice in turn. In case you haven't come across the Twenty Principles before, this article covers the launch event in June 2014, including a link to the PDF version of the principles.
Spreadsheets are not the answer to every problem. A lot of time can be wasted, and errors caused, by using a spreadsheet when some other application would be more appropriate. Very often the more appropriate tool might be a word processor (if it’s a table of text), a database (if it’s processing large quantities of similar data items) or an existing software package (if it’s to undertake well-established processes, such as bookkeeping, for which specialist packages are readily available). Even if a spreadsheet is still the right answer it’s worth looking for existing templates before starting a new one from scratch.
Spreadsheets are often described as the 'Swiss army penknife' of software tools for accountants. Whatever the problem, a spreadsheet will have a feature to provide a solution. However, although it is true that you can use a spreadsheet as a word processor, a database, an accounts system or even to remove stones from boy scouts' hooves, it certainly doesn't mean that it is the best tool for any job. As the description of principle 5 points out, in most cases you would be far better off using an application dedicated to the task, rather than snapping your penknife blade levering open a paint tin.
Although there are obvious alternatives for some of the tasks mentioned in the principle, there are many tasks where the decision whether or not to use a spreadsheet is less clear cut. Many people use spreadsheets for creating forecasts and cashflows although applications designed for this purpose exist. Sometimes, this might be a sensible decision based on the greater flexibility of a spreadsheet compared with a dedicated application. However, all too often, the decision is made on the basis of perceived cost, familiarity and lack of knowledge of the alternatives.
Because nearly everyone already has access to a spreadsheet, it's often seen as being a 'cheap' option compared to buying an additional piece of software. This assessment frequently omits a realistic calculation of the internal time required to undertake, control, test and manage a significant spreadsheet project. If this is included in the costing, a spreadsheet may no longer be such a cheap option. Similarly, there is often an assumption that everyone knows how to use a spreadsheet, so there is no additional training overhead required. This is often very far from being the case. Although lots of people might be capable of putting together some sort of spreadsheet solution, putting together a robust spreadsheet that is free of significant errors and easy both to use and to maintain, often requires a higher level of skill and knowledge. Also, because something can apparently be done with a spreadsheet, there is a risk that the selection process will go no further and viable alternatives will never be explored.
As important as these issues are, perhaps the most important aspect of developing your own solution, compared to buying something ready-made, is testing. Mainstream products will have (hopefully!) gone through intensive internal testing before release and even if this fails to discover all problems, having many users of the same product increases the chances of most errors being found quickly. This doesn't mean that you can assume that all off-the-shelf produces are completely reliable or that you won't be the first to discover an issue. However, developing a bespoke product means that the entire responsibility for testing is likely to rest with your own development team and set of users.
It's worth considering database applications specifically. Most spreadsheets have a set of data tools that might suggest you can use them for the input, storage and management of data. If your data is a very simple list, then a spreadsheet might be an acceptable solution. Once you need to consider controlling the structure of your data (for example, making sure a date column contains dates) and certainly once your data includes more than a single table with the need to refer between tables, then a spreadsheet is likely to be the wrong answer. Spreadsheets may be very good at analysing and reporting on data, but they are usually very poor at data storage, data management and controlling data input. Once again, familiarity is an issue: for every 100 people working in business or finance who would claim proficiency in the use of spreadsheets, only a handful will admit to understanding databases. An understanding of how relational databases work should be considered an essential spreadsheet skill. Not only does it allow spreadsheet users to do more, more easily, when analysing data, it also allows them to make a much more informed decision as to whether a spreadsheet or a database (or the combination of the two) is the best solution to a particular problem.
The ubiquitous spreadsheet
Microsoft's guidance on when to abandon Excel in favour of Access
Why databases are easier than spreadsheets - the prequel: What's a database?...
Why databases are easier than spreadsheets...
Why databases are easier than spreadsheets – episode 2, the database strikes back....
Why databases are easier than spreadsheets – episode 3: relationships, who needs them?...
Less spreadsheet, more database? Roadshow 6 - the South-West
Small business - Why use an accounting system when you've got Excel
IT training – why waste your time?
Is a spreadsheet application fit for the proposed purpose?
We will continue to review and adapt the 20 principles. If you have any suggestions for improving this principle, or any of the other 19 principles, please add a comment to the post.
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.