Making sure the numbers add up

I think it’s safe to assume that Microsoft Excel has now toppled Lotus 1-2-3 as the world’s most-used spreadsheet tool. The great thing about Excel is that it can now do so much more than add up a few numbers… it can analyse, manipulate and predict, provide business intelligence, process data, and visualise your numbers. But beware…

Some businesses rely on Excel, and over-rely on Excel. It’s on just about everyone’s desktop and it’s used to create a myriad of ‘end user computing applications’. Yes folks, end users will do their own stuff, but people often end up trusting the bottom row of figures… and those figures feed into other spreadsheets which people trust. Eventually there’s a few uber-spreadsheets containing the company’s most crucial numbers, and everyone trusts that the numbers are correct. But are they?

In 2011 Microsoft acquired Prodiance, and with it a number of solutions to bring control to the chaotic world of the spreadsheet jamboree. Those solutions are now finding their way into Office and SharePoint 2013. Here’s a quick summary of what’s on offer.

Microsoft Office Discovery and Risk Assessment – you have a million spreadsheets, so where do you begin? Well, you could start with the one on the CFO’s laptop named ’2013 profit and loss.xlsx’ and work your way backwards. But let’s assume that you want to decide which of these million spreadsheets you want to analyse for risks in more detail. This is where the Discovery and Risk Assessment solution comes in – it will scan your spreadsheets, analyse them and rate them in terms of risk. You can award points based on their characteristics. For example, if a spreadsheet has between 100 and 200 formulae you can award it 5 points, and another 5 points for the next 300 formulae, and then a further 10 points if it has over 500 formulae. You can then add 10 points for links to other Excel workbooks, and a further 15 for links to external data. And 10 points if it contains the word ‘profit’. And so on, it’s up to you. Suffice to say, the more complex the spreadsheet the more points it accrues, and therefore the higher the risk. Think of a pyramid, you might be looking for the 10% at the apex.

Inquire for Excel 2013 – once you’ve identified your high-risk spreadsheets, you can use Excel 2013′s new Inquire capability (note: requires Office 2013 Professional Plus). Inquire provides the following:

• Workbook analysis (see below)
• Workbook relationship – graphically displays the relationship between the current workbook and others referenced in formulae and data connections, thus allowing you to trace the data – it can also tell you if your workbook is fed data from another workbook which has not been calculated recently (so yours contains ‘old data’)
• Worksheet relationship – the same but within the current workbook
• Cell relationship – track from the current cell
• Compare files – you’ve guessed it (see below)
• Clean excess cell formatting – a useful tool if the guy who used to manage that whopper decided to format the cells for currency all the way down to row 10,845

Workbook analysis – as its name suggest, this tool analyses a workbook and reports on over 50 factors… links, formulae, errors, inconsistencies, referenced blank cells, hidden cells, hidden sheets, and many others. The results can be exported to another spreadsheet for further analysis. Click on the small image to see the workbook analysis dialog box.

Compare files – let’s say you look at two spreadsheets a week or a version apart, and now your revenue has declined by 87%, the projection for 2014 shows a big fat error and your favourite macro now does something very strange. Load up the two files and this tool will tell you where the differences are – the results are categorised, allowing you to see changes to values, formulae, structure, macros and formatting (and more). You even get a nice graph to show the proportion of changes, and the results can be exported.

Finally, the Audit and Control Management Server will keep track of spreadsheet revisions and track every change, who made the change, when, and what. It can also provide version management for stored spreadsheets.