When spreadsheets go bad
- PDF / 281,392 Bytes
- 8 Pages / 442.205 x 663.307 pts Page_size
- 56 Downloads / 197 Views
When spreadsheets go bad Andrew Taita and Kurt A. Richardsonb,* a Decision Mechanics, London, UK. E-mail: Andrew,[email protected] b Exploratory Solutions, 3810 N 188th Ave, Litchfield Park Arizona 85340 USA. E-mail: [email protected]
*Corresponding author.
Abstract Spreadsheets are ubiquitous in modern organizations. However, most of the tasks they are used for would be better served by more specialist applications. In this article, we highlight some of the abuses of spreadsheets – and the consequences. We explain why spreadsheets are inappropriate in many corporate situations and point readers to alternatives. Finally, we note that spreadsheets do have a very important role to play – as tools for prototyping. OR Insight (2013) 26, 270–277. doi:10.1057/ori.2013.8; published online 23 October 2013 Keywords: spreadsheets; modeling; prototyping; limitations Received 6 October 2012; accepted 6 August 2013 after one revision
In the late nineties, Andrew had a colleague who was fond of saying: When an operations analyst encounters a problem, he opens a spreadsheet. Now he has two problems … Indeed. If you are responsible for a project that has a spreadsheet as a deliverable, go and suspend it now. Yes, now. Go on. When you return we’ll explain why you had to do it. Done? OK, let’s continue … First, let’s change tack for a moment. We’re actually big fans of spreadsheets. We use them regularly. Excel is a great application. However … © 2013 Operational Research Society Ltd 0953-5543 OR Insight www.palgrave-journals.com/ori/
Vol. 26, 4, 270–277
When spreadsheets go bad
spreadsheets should not be used for ‘line of business’ applications. In fact, we’d go further – spreadsheets should only be used by the person who created them. And, sometimes, not even then … We’ve all seen spreadsheets like the one below.
This isn’t a spreadsheet. It’s a database. And there are applications designed specifically to handle this sort of data. They’re called ‘databases’.
Spreadsheets as Databases The ‘spreadsheet as database’ is a pet peeve of ours as we receive data in this format all the time – and it’s always a major pain. Consistency is hard to maintain when storing data in spreadsheets. You end up with customer IDs in the ‘Orders’ sheet that can’t be found in the ‘Customers’ sheet. Or a sales tax of ‘Smith’. In fact Panko (1998, 2008) reports that ‘errors seem to occur in a few per cent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists’ (The same article provides ‘ample evidence that errors in spreadsheets are pandemic’). A particularly pernicious problem with using spreadsheets in this manner is their tendency to auto-format fields. If we had a dime for every time a numeric customer ID with leading zeros had been formatted as 6.72512E+11 … or an order had been fulfilled during the reign of Queen Victoria … © 2013 Operational Research Society Ltd 0953-5543
OR Insight
Vol. 26, 4, 270–277
271
Tait and Richardson
Moreover, there’s a significant ris
Data Loading...
