Spreadsheets are ubiquitous, perhaps the most used application in the workplace for a large majority of people and definitely not going away.  However sometimes they can also be, well, dangerous and secretly costing you plenty of cash so it is worth looking at them in these challenging economic times. 

Some of the common questions that you should ask about your spreadsheets:

• Can you trust the data held within them?
  • Especially if you have been sent them via email?
  • Do you know where the data in the spreadsheet came from?
  • Can you check a server side data source to give you more confidence in trusting the data?
• How are the spreadsheets managed?
  • How do you know you have the right version of the spreadsheet?
  • Do people keep their own copies of data and protect it?
  • What would happen if these people left the organisation?
  • Are the workbooks full of complex logic only understood by its creator or does it use common business rules common throughout the organisation?

For more information I recommend you download the white paper "Getting More from Your Spreadsheets with Business Intelligence" written by Business Objects.  It covers the hidden costs of spreadsheets and other issues such as security, data integrity, reliability and how to extract the most value out of them. 

As nearly all users are familiar with Excel you will face serious opposition by trying to remove it entirely, so what options are available with regards to reining in these spreadsheets so they are used more effectively and better controlled? (from an SAP-Business Objects perspective as that is my area of interest)

For simply accessing and sharing data you can use the Business Objects Live Office suite that enables server side data to be embedded into Excel, Word, PowerPoint or Outlook.  This guarantees the data held within the documents is trusted as it is sourced directly from the data warehouse and eliminates the  issue of two groups of people having differing figures for the same set of information, you have 'one version of the truth'.  Once the data is embedded into Excel you can format it and perform calculations in the usual way and all changes are retained.

For planning, budgeting and forecasting exercises you can use SAP Integrated Planning (IP) or SAP Business Planning & Consolidation (ex-OutlookSoft).  Whilst BPC has superceeded SAP IP it is in fact guaranteed to be supported by SAP until 2017 so it is still a viable option.  SAP BPC 7.0 which being built on the NetWeaver stack (as opposed to version 5.0 build on Microsoft) is in 'ramp-up' mode and is unlikely to be generally released until Q2 2009. 

Some of the advantages of using either of these budgeting, planning or forecasting solutions are:

• Complex business logic can be moved from individual workbooks and handled central and in a uniform way
• Data security can be managed to ensure that only the correct users can submit/review data
• Better auditing, tracking and monitoring of the planning processes

to name but a a few.

If you use Excel to model 'what if' scenarios then you should consider Xcelsius.  The Business Objects visualisation tool has powerful features that can help you make sense of complex data.  It can also be used for dashboards and other models and has been refered to a one of the new BI 2.0 type applications.  A simple Profitability Analysis model demo built using Xcelsius is available to view here (requires Adobe Flash) and a whole host of others here This profitability model allows you to create "what-if" scenarios by modifying sales growth rate and all other relevant accounts measured as a percentage of total sales. This example, built with fictitious data, depicts the most relevant accounts of a profit and loss statement, and shows the impact of changes on net income. The results change immediately, allowing you to create endless what-if scenarios.

In summary spreadsheets are here to stay and with Office Excel 2007 increasing the maximum limit on the number of rows in a spreadsheet we will probably see million row data silos being created all over the organisation by business users rather than pointing them to a server side data source in a BI system.  Despite the disadvantages of spreadsheets, including their unfortunate tendency to make important data difficult to find and to derail compliance efforts, companies should forget about eradicating them, its far too late for that with 400 million global users of Excel, instead focus on using them better.