How do I escape spreadsheets?

I don't believe anything is wrong with spreadsheets as an analysis and presentation tool as long as the source data is maintained as much as possible in the ERP system. It is when the source data is fragmented across multiple sources, e.g. spreadsheets that act as alternate databases, that the question of veracity comes into focus. This also holds true for the calculations that are used to derive the end results. Formulas - whether in ERP systems or dashboards or spreadsheets - must be understood. Data manipulation in the form of calculations may be inescapable when using spreadsheets and dashboards, but it becomes error-prone when only one person or one set of eyes is in charge or does the review. The mathematics should be checked and double-checked, reviewed and peer-reviewed, before being presented. Too much blame is placed on the technology when the fault lies with the way the technology is either carefully or carelessly used.

In order to eliminate operational business spreadsheets you have to start by knowing what they are doing for your organization via discovery processes (such as risk analysis, materiality assessment and data lineage).

This understanding creates a 'hopper' of current business functional requirements that can be satisfied by potential vendor and in-house solutions.

The reality is that this hopper will almost certainly contain far, far more functionality than can be satisfied by the available resources (people or budget).

So the best thing to do is to prioritize the hopper for your IT spend. Because of the discovery process you will now have a holistic clarity of what are the most important requirements in the business and hence you can get the best bang for buck in choosing where to place your IT spend (instead of just responding to the loudest shout).

For the rest of the spreadsheet activity you need to place some form of monitoring to maintain governance until these spreadsheets reach the position on the IT priority list where they too can be replaced.

Of course the world never stops changing so new spreadsheets are being generated all the time to satisfy new business requirements. This means that the process of filling and assessing the hopper is continuous.

So until the world stops changing spreadsheet replacement is a continuous journey, not a destination. Embedding this cycle of visibility, control and replacement should therefore be the key joint business and IT objective. This is the true justification for enterprise spreadsheet management.

We have used a process of Design, Tweak, Memorialize. Initial reports are designed in Excel. They are reviewed by the customers that will use the data through a number of reporting cycles to make sure we have what is going to be of value. Once we feel comfortable, we turn it over to a team consisting of an IT person and a financial/business person to build the reporting within the ERP so it is "quickly" retrievable and "consistently" replicable.

This question starts with the false premise that everyone WANTS to get rid of spreadsheets, or that we NEED to “escape” from them. Norman, Ralph, and Mark all suggest different, yet balanced and reasonable approaches to the question. They range from thoughts about how to use spreadsheets properly, to ways of identifying applications that are best incorporated into an ERP system. I am probably whistling in the wind here, but all of this doomsaying about spreadsheets generates a lot more heat than light.

Spread sheets are required and when used appropriately are needed. These tools allow for flexibility in your thinking. For any business that evolves (by the way they all do), reports today may not be optimal in six to nine months, to see your business. The natural tendency is to place data on a spread sheet and manipulate it. However, the issues are clear with respect to running a business with spread sheets. The process identified by Mark is the same one I use - develop a manual report, become comfortable that it answers your questions, and then automate. This process works very well. I believe the more appropriate question is not “How do I escape spread sheets;” but, “How do I manage spread sheets?”

Interestingly to note, some ERP/CPM/BI software claim to "move beyond EXCEL", and yet they invariably have the "Export to Excel" button, or even further, as in the case of Adaptive Insights, seamlessly integrate with Excel via an add-in called OfficeConnect.

Cloud ERP system provides all the advantages it can including instant consolidation, version control, easy upgrade, etc., etc. Excel on the other side, are evolving toward BI user interface - PowerPivot, PowerView.

I am sure there will be more and more ERP and BI tools being developed to provide far more functions than Excel, but complementing and supplementing Excel will still be the norm, at least in the near future.