The right tool

There’s an old adage to the effect that if the only tool you have is a hammer, everything will look like a nail. Similarly, if the only application you know well is Excel, you can end up thinking about all your work in terms of spreadsheets, workbooks and graphs. While this approach may work for a small business, club or association, as your business grows there will come a point where you have to realise that you can no longer do everything you want using this single tool.

When it happens, this can be a rather painful experience: I’ve seen financial directors of medium-sized companies struggle for months trying to construct one gargantuan workbook that contains all the data about their company, going back five years and projecting forward six. Trying to discover what would happen to the bottom line if they gave the directors a 6 per cent pay rise in two years’ time or they sold 3 per cent more by volume in year four but dropped their base prices by 1.5 per cent, the only thing they can say for certain is that they don’t have all the answers yet. And with 30-plus sheets in the workbook, and links out to other workbooks elsewhere on the network, they can only open this workbook successfully three out of every five times. Clearly, the workbook has grown too large to be easily understood by anyone: human, computer or accountant.

If you ever find yourself in this position, recognise that you’re in a hole and stop digging. Don’t compound the problem by thinking that just another small tweak will magically make it all work – it probably won’t. Stop and think very hard about what you’re trying to achieve. Don’t think ‘I need a spreadsheet that…’, but instead think ‘I want to know what…’. Now consider what else you’ll need to know to achieve that goal. If this list gets too long, split it up into manageable chunks and deal with each section separately.

For instance, if you need to know staff costs to get to your goal, list exactly what outputs (numbers) you need to know under that heading, then think of those as your goals and work out what inputs you need to calculate them. Now consider whether an informal tool such as a spreadsheet can give you what you want: it may be great for doing quick-and-dirty calculations, but it’s far too easy to break a workbook by moving some data around, or by adding or deleting a column here or there. Defining and working with more formal data structures, such as found in a proper database system, might help you think about your goal. If you can specify upfront all the pieces of data you currently know, all the data you need to know, and then how to get from one to the other, you can define a database schema. This gives a solid foundation on which to build user interfaces for inputting and editing the data, and outputting reports of the data to provide the answers you want. It may not be that behemoth spreadsheet that ‘just grew like Topsy’ and it might take a considerable time to build, but I think that for a medium-sized company it has more chance of providing the answers.

Of course, you may want to still use Excel as a reporting and analysis tool for data collected from the database, and this is a good use for it. Excel is great for grabbing data from a database and presenting it using Charts or PivotTables. PivotTables in particular allow you to slice and dice the numbers in a multitude of ways, and once you start playing you can find hundreds of uses for them. Provided you’ve got the data to analyse, you can show it any way you want.