What applications Excel should and should not be used for and why

I just saw a great question followed by a discussion on the proformative.com site about whether Excel should be completely eliminated as a financial analysis tool. I was pleased to hear that many people recognize both the value and the risks and limitations of Excel (or any other spreadsheet). I contributed to the discussion and wanted this blog’s readers to benefit from the insight given and my own opinions.

It is a well known fact that Excel is one of the most common software tools in the workplace, not just in finance and accounting, but also in many other areas that require maintaining calculations or data analysis.

Excel (and all popular spreadsheets in the past 30 plus years) is an incredible tool with certain capabilities and functionality unmatched by any other software application. Unfortunately, we have become dependent on it for far more applications than we should.

Applications such as planning, budgeting & forecasting, consolidation of financial statements, and other critical financial processes should not be dependent on and performed in Excel (or any other spreadsheet). The risks of errors, omissions, broken links, and the significant effort required to update complex models are far too great, especially given the fact that the practice of change management and internal audit of user computing controls is nearly non-existent when it comes to use of spreadsheets in corporate finance.

However, financial analysis can be performed in Excel as long as the data driving the analysis is primarily produced elsewhere (e.g., a dedicated planning and budgeting software solution, your ERP software, etc.). In this case, Excel should be used for its impressive formatting and display capabilities and ability to link it to data sources.

An example of this is Analytics Maestro from Centage Corporation. This application works within Microsoft Excel and uses all of Excel’s incredible formatting and display capabilities without the risk of having bad formulas or broken links and without any user programming, as the data is seamlessly retrieved from the company’s ERP software and from Budget Maestro (Centage Corporation’s planning and budgeting solution). A while ago I wrote on this blog about this new approach to analysis, A new Way to Look at Accounting Data.

Ideally in complex analysis, no actual formulas, links, and other computational programming should be maintained in the Excel workbooks. In reality, many analysis worksheets will contain formulas, functions, macros, and other custom programming. These, however, should never be used in the production of financial statements and other critical external reporting activities. These worksheets should be set up under change management with proper design documentation and a change log. Review and approval of all changes should be evident. Locking down worksheets, requiring access passwords, and other security measures will reduce the risk of data manipulation by unauthorized persons.

When risk to accuracy and completeness of data in Excel workbooks or worksheets is greatly reduced, finance organizations will enjoy repetitive and consistent display of data in a familiar format and appearance, making the data simpler to understand and driving decisions quicker and with more confidence.

Microsoft Excel may very well be the most popular software application in the workplace; employees are familiar with its functionality, at lease on the most fundamental level, and get a good portion of their work done using it daily. Once we understand which critical applications Excel should not be used for we can safely continue to use and benefit from this software in many of our other daily tasks.