Historic Excel cell data now available in QlikView

One of the hidden benefits of using ClusterSeven to monitor spreadsheet activity is that the database stores a full history of every cell value as they evolve from one version to the next (such as JanP&L.xlsx going to FebP&L.xlsx to MarP&L.xlsx etc). This means that, on demand, users can visualize and compare historic trends of any important business parameter that would otherwise be hidden across multiple old spreadsheets. Without this many financiers will continue to have to laboriously copy and paste each piece of data to yet another spreadsheet – a process with which most will be frustratingly familiar.

At ClusterSeven we have to admit, however, that we are not the leading dashboard company in the world. So it makes great sense for us to make all this historic business data available to BI consumers. We are therefore extremely excited to see the work done by a leading QlikView partner to present this information in QlikView dashboards. To explore the capabilities of this ClusterSeven Reporter for QlikView visit the QlikMarket.

The benefits do not stop there. Even more exciting is that QlikView users can now combine their traditional analysis of structured data with this new source of unstructured historical financial data being fed from their spreadsheets. So, if your business process combines robust structured data sources and spreadsheets (e.g. multi-office reporting) you only have to go to one location to see everything. No need for yet more spreadsheets and no more cut-and-paste!

BYOA (Build Your Own Application)

There has been a large amount of comment over the last year or so about the business and IT issues attached to the growing enthusiasm for BYOD (Bring Your Own Device) i.e. if/how to allow users to use personal technology gadgets such as iPads to meet their business needs. These issues include security, reliability, maintenance, visibility, replacement etc.

It recently occurred to me that many of the corporate issues exposed by BYOD are already manifested in the work that users commonly perform to meet their individual needs in moving the business forward. A good example is the creation of a new spreadsheet solution to solve a recurrent financial need in the business which cannot be solved by existing applications. If this solution works effectively it is typically absorbed and adopted within the business infrastructure with little business concern, no IT awareness and rarely any plans for security, maintenance etc. So for all the concern over BYOD perhaps it is time to look more closely at the older practice of BYOA (Build Your Own Application) as another source of corporate risk and exposure.

Financial Times puts the spotlight on ClusterSeven and spreadsheet management

Here at ClusterSeven I have been promoting the need for companies to think more seriously about the huge value and risk of operational spreadsheets and follow the trend of many financial institutions to implement reliable, automated controls. So it's great to see that recent spreadsheet errors and spreadsheet mis-management have prompted the Financial Times and FT Alphaville to step into the sector with survey conclusions reported at...

Click here to read the full article (registration may be required)

and continuing banking disclosures at...

Click here to read the full article (registration may be required)

Spreadsheets and Regulators – the Gloves Come Off

You do not have to spend very much time in any financial services or energy firm to know that spreadsheets are pervasive in all operational areas; not just pricing, risk and settlement but also the office of the CFO for all regulatory and statutory returns including tax and disclosures. Enter any specialized business areas – such as actuarial in insurance – and the story is the same.

This activity is well known to those in each of these departments. However, the really strange aspect of these operations is that all this spreadsheet activity often disappears from view as soon as one comes to document formally the business systems and processes for reports to senior management and auditors. The result is that everyone convinces themselves that System A really is directly connected to System B without any other intermediate activity. Or that Report X really is directly generated from System C without any subsequent manipulation. The result of this ‘Group-Think’ is that when regulators and auditors have asked for lists of critical applications somehow the critical spreadsheets get left off. The same is true for the corresponding controls and IT audits.  

This attitude of sub-conscious (and often conscious) denial has been swept away by two recent regulatory documents. The first is a consultation paper from the Basel Committee on Banking Supervision entitled “Principles for effective risk data aggregation and risk reporting” that can be found at this link:

Click here for Basel Committee pdf

The second is the latest Solvency II report from the Financial Services Authority entitled “internal model approval process data review findings” available at the following link:

Click here for FSA pdf

Both reports avoid their past approaches that relied on general terms such as ‘systems’ or ‘applications’ to uncover critical spreadsheets. Now they are specifically called out for control. Whether in the words of the Basel Committee:

“Note 28 b Where a bank relies on manual processes and desktop applications (eg spreadsheets, databases) and has specific risk units that use these applications for software development, it should have effective mitigants in place (eg end-user computing policies and procedures) and other effective controls that are consistently applied across the bank’s processes."

or the words of the FSA:

“4.41 Where EUC tools, such as spreadsheets, are material to the internal model data flow, we will be looking for appropriate controls for data quality such as reasonableness checks, input validations, peer reviews, logical access management, change and release management, disaster recovery, and documentation.”

it seems to be clear that misunderstanding, ignorance or denial is no longer any defence.

Two Oracle reports confirm spreadsheets are everywhere

This week two high quality reports sponsored by Oracle have been brought to my attention. Each confirms that despite large amounts of money being spent on both corporate financial reporting systems and banking management information systems, the majority of both environments are still heavily populated with spreadsheets. The full reports are available on links below the relevant quotes (free registration may be required).

The overt suggestion of both reports is that organisations should spend yet more large amounts of money trying to eradicate spreadsheet usage. Perhaps the more pragmatic interpretation should be that if eradication has not been achieved after all this money and time then a better goal would be to manage spreadsheets alongside systems. As our case studies show, ClusterSeven is used both to control spreadsheets in isolation but also and, more importantly, to enable spreadsheets to become a reliable part of your information infrastructure. Now you can get the best of both worlds.

The Challenges of Corporate Financial Reporting May 2012 (Oracle, Accenture)

“....67 percent of companies still use spreadsheets and 8 percent use spreadsheets and nothing else to assist with tasks in financial close, reporting and filing. Interestingly, while more people with three separate solutions [for close, reporting and filing] use spreadsheets (73 percent), a significant 59 percent of people with a single solution also use them.”

Click here for link to report

Management Information Systems Survey 2011 (The Association of Foreign Banks, Oracle, Cambridge Corporate Management)

“...for measuring and reporting risk, the use of spreadsheets was widespread, particularly in the areas of liquidity and operational risk where over half the respondents used spreadsheets ‘somewhat, significantly’ or as the ‘primary mechanism’.

Of even greater concern was the manipulation of data from core systems; 26 respondents used spreadsheets ‘significantly’ and 15 ‘somewhat’.

Similarly, although perhaps of less concern, there was much spreadsheet use in compiling financial MI, where most reported ‘somewhat’ or greater use in all the areas investigated.”

Click here for link to report