Best practices for using spreadsheets as BI tools

By Boris Evelson

We all know that the war of fighting the proliferation of spreadsheets (as BI or as any other applications) in enterprises has been fought and lost. Gone are the days when BI and performance management vendors web sites had “let us come in and help you get rid of your spreadsheets” message in big bold letters on front pages. In my personal experience – implementing hundreds of BI platforms and solutions – the more BI apps you deliver, the more spreadsheets you end up with. Rolling out a BI application often just means an easier way for someone to access and export data to a spreadsheet. Even though some of the in memory analytics tools are beginning to chip away at the main reasons why spreadsheets in BI are so ubiquitous  (self service BI with no modeling or analysis constraints, and little to no reliance on IT), the spreadsheets for BI are here to stay for a long, long, long time.

With that in mind, let me offer a few best practices for controlling and managing (not getting rid of !) spreadsheets as a BI tool:

  1. Create a spreadsheet governance policy. Make it flexible – if it’s not, people will fight it. Here are a few examples of such policies:
    • - Spreadsheets can be used for reporting and analysis that support processes that do not go beyond individuals or small work groups vs. cross functional, cross enterprise processes  
    • - Spreadsheets can be used for reporting and analysis that are not part of mission critical processes
    • - Spreadsheets cannot be used for any processes that involve systems of record or are part of regulatory reporting
    • - Spreadsheets that are used just for reporting and analysis are less of a risk. Spreadsheets that are used as data sources, lists, hierarchies, etc present much higher risk and should be eliminated and replaced with analytical MDM tools provided by most leading BI vendors. At the very least such spreadsheets should definitely fall under a category that should be closely monitored and controlled (see #2).
  2. Monitor compliance with such policies and guidelines by using spreadsheet management tools from vendors like Compassoft, ClusterSeven, Prodiance and Lyquidity. For example
    • - If a spreadsheet based BI app does not fall under any of the spreadsheet management policies you just established (for example, it’s used by a small group in a non mission critical process), it probably only needs to be monitored, but not controlled and managed
    • - If a spreadsheet does fall under one of the strict policy categories, you may want to use the tools I just mentioned and other means to enable logging, audit trails, backup / recovery, disaster recovery and other operational risk procedures. For example, if such a spreadsheet uses enterprise standard formula for, say, gross margin calculation, and you notice that someone changed that formula, it’s probably a red flag that needs to be investigated.
  3. When shortlisting your BI platform and applications technology, give preference to BI vendors that support
    • - Exporting reports to the most popular spreadsheet formats. Keep in mind that exporting raw numbers is the easy part. Retaining formulas, links, dependencies and formats is more difficult. Do your due diligence on these items when evaluating your BI vendor spreadsheet export capabilities.
    • - Use spreadsheet application as a UI to access and manipulate data from your BI applications. Again, ODBC access to a data source is the easy part. Pulling in and leveraging metadata that you’ve setup in your BI app, and adding additional BI functionality not available in your spreadsheet app, in seamless user experience is less of a commodity.

Did I miss anything? As always, all thoughts and comments are welcome.

Comments

A viable alternative

Hi Boris
I think the main assumption is that you can only manage spreadsheet reporting by prohibition. As you know that never works for long - it is possible for excel spreadsheet reporting to co-exist within a BI environment. Spreadsheets and BI reports are not necessarily mutually exclusive entities.

A real alternative approach is to have a managed process whereby spreadsheet data is incorporated into the BI environment. (With appropriate approvals of course). In doing this you address some of the root causes of spreadsheet use in the first place.

For more on this see a recent spreadsheet reporting paper > http://bit.ly/bsvVsZ

Regards
glen
www.yellowfin.bi

Thanks for the comments - I

Thanks for the comments - I think we are in agreement. I don't endorse "prohibition", but rather management and selective controls.

Missing vendor

Hi, it's always great to see articles that talk about the need for spreadsheet management and the various solutions, but it's a pity you didn't mention ComplyXL, which at the last count, controls over 1/2 million spreadsheets worldwide.

With continuous monitoring of spreadsheets any change, or a change that you choose to be notified about, can happen instantly either as an email or as an IM. Plus the added benefit is that you don't need to move your spreadsheets, which is always risky as this can affect dependencies. If you'd like more information you should look at www.lyquidity.com.

Thanks

Done!

Done!

Governance + Control = Accuracy of Data & Financial Reporting

Hi Boris - Excellent post! I agree with everything you are saying, and CFOs we are talking to no longer believe they can completely eliminate spreadsheets. There are cases where a spreadsheet is not the right tool and a IT application is required. However, we are seeing the proliferation of spreadsheets, Access databases (e.g. end-user computing tools - EUCs) being used directly in support of financial reporting, management reporting and other mission critical operational processes. When spreadsheets are used in financial reporting they need a control policy, control procedures, and control technology. This is what auditors and regulators including the FSA and SEC are mandating now in the wake of the financial crisis, especially for banks, investment firms and insurance companies. To this end, we are hearing from Big 4 auditors that having the proper controls means the following:

1) Making sure that you have an accurate inventory of spreadsheets, Access databases and EUCs.
2) Performing a risk assessment to identify which EUCs are mission critical/high risk and being used to support financial reporting.
3) Testing and documenting those EUCs to make sure they are developed using best practices and are producing accurate results.
4) Placing the mission critical/high risk EUCs under control (e.g. version control, change control, security/access control, development lifecycle).

There are many proven methodologies, best practices and technologies to support the above process. It is a lifecycle process, and not a one-off project, and the ROI of doing this is very compelling. The main benefit is that you have better confidence and accuracy in your financial reporting process. The bottom line is that spreadsheets can be used to support financial reporting if they have the proper safeguards and controls in place to ensure they can be trusted.