A spark of an idea

We are a bit like a company’s Health and Safety Officer in that respect, willing ever so slightly for a fire so all his planning and preparations will finally see some action. This point of view can be demonstrated within the world of the spreadsheet.

I have two general rules when it comes to spreadsheet modelling. The first is that you should always try to keep a very simple summary page for the main findings of the work that you are doing. The fewer lines on the summary sheet the better as the reviewers/CFO/board of directors won’t have (delete as appropriate) the time, the inclination or the skill to go searching through your elegantly crafted 14 worksheets full of formulae to find what they’re looking for. The workings should be for your eyes only.

The second rule is that the complexity of those workings should reflect the task in hand – pretty obvious you may think, but it’s easy to be sunk in the quicksand of paralysis by analysis through simply overdoing your workings. Keep it simple.

The world of the financial analyst is full of ad-hoc reporting requirements. There is often no template for these requirements and our intrepid young beancounter is left to start the analysis from scratch with a new spreadsheet. Now, I’ll admit I do quite like that – the clean crisp, unadulterated, empty spreadsheet is akin to virgin snow, just before you run through it.

What I like a good deal less is picking up a complex financial spreadsheet model and being asked to use it to perform some analysis, particularly if the genesis of said spreadsheet isn’t entirely clear. It can be much more than merely interesting when somebody asks you to find out within the hour how a certain answer was reached using this second hand analysis. It can be terrifying.

Within a matter of minutes you’ve started at the model’s answer and begun trying to trace your way back through the woods of the formula with a thousand auditing arrows, like a trail of breadcrumbs, leading you home. Trace precendents here, trace dependents there. The screen becomes more blue than white until you finally tap the “erase all” button on the auditing toolbar and start again.

Then you finally get back to the one cell that drives all of the formulae in the spreadsheet, only to discover it’s hardcoded. You’ve charged all around the spreadsheet for half an hour, breathless, like those old episodes of Dr Who when he’s being chased through a maze by Daleks or Cybermen, only to discover that your efforts have been in vain. How do you explain this? It’s a neat twist for the end of a good thriller, but that’s not going to save you from the wrath of the CFO.

This has happened to me on a number of occasions, but there was one that still sends a shiver down my spine. My next move was to go to the toolbar and go file/properties/summary to see who the author of the spreadsheet was. To my horror on the day in question I was confronted with the words “Barry Cells”. The twist within the twist – I was stuck in one of my old bits of analysis and confronted with a dead cell.

The moral of the story is to always leave the cell that drives all others linked to the spreadsheet that created it in turn or at least leave an address for it by using the web toolbar (view/toolbars/web) and copy pasting the address into the new spreadsheet. At least then you give yourself a chance. The final lesson is not to set things on fire in order to see some results.

Barry Cells is no 3 in Blaminio’s finance department. He was talking to Peter Charles who does not necessarily agree with his views on spreadsheet jockeys being like Health & Safety officers or the desire to set things ablaze.

peter@petercharles.com; www.petercharles.com

Share this story