Demo: ClusterSeven's Enterprise Spreadsheet Manager tightly monitors spreadsheet integrity
How many times have you stared at the bottom line of a spreadsheet that's full of formulas knowing exactly what figures should be there, only to find that there's a different set of numbers staring back at you than the ones you expected. You know there's an offending cell somewhere, but the spreadsheet is too complex to find it and, with some deadline looming, out of exasperation, you start replacing formulas with hard coded numbers just to get it fixed, at least until after the deadline when you'll have more time to figure out what went wrong. What's the harm? Right? After all, the people looking at the final product might only be looking at a printout or a PowerPoint slide.
Well, given today's compliance laws, the harm could be huge because of how those numbers can easily bubble up into an quarterly or annual earnings report. If such over-ridden cells end up corrupting some bigger picture report, the results could be disastrous (literally and figuratively). To help organizations and auditors keep spreadsheets from inadvertently (or even purposely) running amok, ClusterSeven has come up with a solution called Enterprise Spreadsheet Manager (ESM). In the attached video, ClusterSeven's vice president of product marketing Ralph Baxter demonstrates how ESM can be configured to keep a watchful eye over any cell or range of cells in any spreadsheet.
As the contents of those cells change, ESM keeps track of when the changes were made, what the new values are, and who made the changes. In other words the audit trail is extremely tight. As you can see in the demo, one of the cool things ESM does is it monitors if cells are switching from their original programming type to another: for example from a formula to a hard-coded number (a sure sign that a spreadsheet and anything that depends on it could end up in a state of corruption).
ESM also graphically presents trends in cell and spreadsheet integrity. The advantage, which Baxter shows at the end of the video, is that those charged with compliance or auditing can build a single graph that includes trend lines for dozens or even hundreds of spreadsheets. Where a cell exceeds company-set thresholds for integrity (eg: varies from some number by a certain percentage, or a formula is suddenly overridden with hard-coded numbers), the trend-line fluctuates from its steady state wildly (making it easy to spot). Why would this be helpful? Well, if your annual report doesn't look right but it depends on data coming from 100 or 1000 spreadsheets scattered throughout the organization, a single graph that monitors the integrity of all the spreadsheets that feed into that annual report can help spot the needle in the haystack that's causing the problem. Otherwise, auditors and financial analysts might have to manually sift through every spreadsheet -- a process that could take days or weeks.
All this whizbang functionality would be of limited help if it couldn't be attached to an alerting mechanism. According to Baxter, there are ways to connect it to e-mail, internal LAN-based alerts (like Netsend) and SMS. ESM supports other spreadsheets beyond Excel (Google Spreadsheets for example). It also doesn't come cheap with the average starting price ranging between $50K-$100K. But for some large companies where compliance is king, that could be pocket change given the sort of risk it mitigates. Finally, it requires the installation of two servers: Windows Server and Microsoft's SQL Server 2005.