Spreadsheets
Why you shouldn't report with spreadsheets...
This is not a popular opinion and many people will want to hunt us down for this , but perhaps listen first and then decide if we really are lunatics.
We formed the view many years ago that people do way too much in spreadsheets and further that spreadsheets can be pretty dangerous things. There are some basic principles in this thinking which we are happy to outline:
Spreadsheet formulae are assigned at the cell level, and copied around. We have all been bitten by
-
a change to a cell formula we forgot to copy
-
a cell formula being applied in a section of the sheet we didn't want it to or expect it to
-
A mistyped entry in an incorrect cell over-writing the formula with a result rendering the data incorrect, yet not easily identified
-
Results being pasted incorrectly, misaligned,
-
Lookups doing weird things because of our misinterpretation of the way they should work
-
Presentation changes causing incorrect data calculations
-
Data calculation changes presenting data inappropriately for the audience
-
Out of date recalculations/external links on large workbooks
-
In most cases it is impossible to audit a spreadsheet, particularly a large complicated one which combines data from many sources.
-
Logging changes and edits in a spreadsheet is basically impossible.
-
Spreadsheets are inherently editable and single user by design.
-
We could go on and on and on.
Don't get us wrong, we think spreadsheets are a very functional and useful tool. We do happen to think that they are misused and overused in most organizations. Just like four wheel drive recreational vehicles are great too, but they really are not appropriate as passenger cars in the suburbs and create lots of dangers, that's well established, yet lots of people use them in that way. It's a horse for courses argument. So back to reporting
Almost all modern businesses of any size (probably 15-20 employees upwards) record all their financial and business transactions and information in a database based system of some sort. Some businesses have a few database type systems doing different jobs, but they are databases nonetheless. Very few businesses at this size record transactional data in spreadsheets, although it's technically possible. The reasons they don't are the same reasons we don't think spreadsheets are appropriate for reporting. Databases apply logic in a consistent way, are designed for handling large volumes of data, record edits and changes well, and are inherently multi-user so that all works well for business information.
Report writers are designed for presenting read only information from business databases in final production formats, and have become increasingly sophisticated over the years.
They deal with the databases directly (no re keying or cutting and pasting of data) and, like the databases, apply rules consistently and in a very auditable fashion. In a presentation sense, report writers deliver exactly the same level of presentation sophistication as spreadsheets, but in a much more controlled and automated fashion. Indeed many modern report writers achieve presentations not possible in the spreadsheet world.
A further, and very important issue, is that the data shown in a report is just that, a visual representation of the data in the database, a picture if you like. By definition the spreadsheet stores data, and worse stores it in an editable form. These spreadsheet reports are typically distributed in organizations as electronic files and we thence have the scary proposition of multiple copies of potentially editable data floating around the organisation (and often outside it).
A simple rule we like to work with is
‘do it once in a spreadsheet, do it twice in a spreadsheet, don't' do it a third time, write a report and schedule it for delivery then get on with your work'.
Or if you prefer
‘write it once, run it many times, whenever you need'
Essentially we are saying that spreadsheets make great prototyping tools for reports, but once the requirement is clear and regular that ‘definition' needs to be encased securely and the resulting designed report simply executed and delivered automatically at whatever interval is appropriate.
There are some pretty big (and illogical) barriers to implementing a ‘no spreadsheet reporting' approach in most organizations, and they do need to be tackled head on.
-
Most organizations have people who have made a career out of manipulating, re-keying and prettying up data in spreadsheets on a weekly or monthly basis. It's a pretty aggressive thing to say, but they need to be contained or eliminated where they are not adding value. If what they do adds value, it can be automated.
-
Personal preferences are most often the reason for so many ‘versions' of reporting requirements in organizations. These are most often minor presentation issues and rarely add any value to the information presented, and where they do add value (and we accept this is sometimes the case), they should be made available to all users of that data in a standard, authorized, accurate and complete form.
-
Data has to come from more than one source to enable the report to contain all the relevant information and this is the reasons for the use of the spreadsheets, as a ‘container'. Most modern report writers can source data from more than one data source if the data can be combined logically. In any event, relatively simple data warehousing techniques can be used simply to create a database based repository for this type of consolidated reporting, and again this approach can be automated once designed.
-
Ownership of information is often a power play in organizations. Indeed if only ‘my spreadsheet' shows the information everyone needs, that gives me a great point of strength and power over others. If that report were to be defined, written and delivered automatically on a regular basis to all who needed it I would lose significant power.
-
The spreadsheet reporting phenomenon is typically something which has grown up with the organisation over time. It typically started when the business did not have sufficient size and sophistication to provide the proper reporting tools. In most cases, however, the organisation has grown up from there, now it's the turn of the reporting environment.
-
Sometimes the amount of time and effort required to manipulate the data into its regular spreadsheet reporting format is sufficient justification for not doing more valuable analysis and decision making. In essence we are so busy moving the data around, we lose sight of the value of proper analysis.
So if we take away this critical reporting role, and the data storage role from spreadsheets, what are they left to do ? In essence, what they were designed for, Ad-hoc analysis, prototyping, data collection (in non repetitive situations), what if type analysis.
Database environments and designed, focused reporting tools can remove a whole layer of workload, repetition and data redundancy from organizations in business. Logically all sensible, mature and developing organizations should be pursuing this approach with vigor.