Are Spreadsheets Robbing your Enterprise of Competitive Advantage?

’90% of “average” companies are not confident that their forecasts and reports are accurate and reliable’

In a recent study, 81% of FD’s cited that their highest priority is the accuracy of revenue and earnings forecasts while 63% complained of inadequate budgeting and forecasting systems .

The modern FD is coming under increasing pressure from all sides to produce more robust, meaningful and accurate financial information. This is driven by a variety of factors:

• Internet technology is creating new business models that require innovative financial models
• The emerging business environment is creating more competition that requires information based on dynamic competitive scenario analysis
• The recent accounting scandals and the regulatory response to those require a higher level of data integrity and accuracy.

All stakeholders within the enterprise are requiring more analysis, based on complex models in shorter time periods, with accuracy and the ability to explain anomalies within the data presented paramount to the successful management of the enterprise.

It is interesting then that a survey of 2000 companies on financial best practices by the Hackett Group revealed that two-thirds of “world-class” companies and 90% of “average” companies are not confident that their forecasts and reports are accurate and reliable. Why?

Consider two major systems from which this data is collected.

1. Multiple ERP systems are used to assemble data for budgeting, forecasting and reporting. The inter-compatibility of these systems can cause inaccuracies.
2. Second, spreadsheets still compose a major part of the budgeting, forecasting and reporting functions of the finance department.

There is a growing body of research showing the problems associated with using spreadsheets within the finance department. That may be well and good, spreadsheets may not be the best system to use within the finance department. However, a satisfactory alternative has not been presented for the use of spreadsheets, and as such the research into the use of spreadsheets is of little practical value to the finance world at large. The question still remains:

“Can other Technologies replace Spreadsheets within the Finance Department?”

Why are Spreadsheets used?

Quite simply, because they can be. Finance professionals with very little knowledge of computer software development, programming or application design are able to develop complex models that can be used to manage the finance function. Also, spreadsheets are widely used and available within the enterprise and the majority of information users have access to and knowledge of how to use spreadsheets.

So, what is the problem with spreadsheets anyway?

A study by Coopers and Lybrand showed that 90% of all spreadsheets with 150 rows had errors. Another study by KPMG showed 92% of spreadsheets dealing with tax issues had significant errors and 75% had accounting errors.

In general, the problems associated with spreadsheets can be split into two main areas:

Design, Development, Flexibility and transparency of internal processes

It is precisely because most Finance people, who are responsible for developing and maintained the models, are NOT trained in the design and development of spreadsheet models that there is an issue. No Financial or IT Director would allow an unqualified and/or inexperienced database administrator to develop and maintain the vast and complex transactional databases that now run Businesses. Yet, when it comes to the design and development of Management Reporting, Budgeting and Planning systems, which are relied upon to manage multinational businesses, this practice is commonplace. The issue here is not that the Finance Department is not financially astute, they are. The issue is that they are not technically trained in the use of Spreadsheets.

Spreadsheets are inherently inflexible to changes in the design of the models they map. This is due to the method spreadsheets use to link data, which is on a cell-by-cell basis. The internal formula structures written into spreadsheet models are not dynamic, so if there is a change to the NATURE of a formula in one sheet, it is not automatically replicated in all the subsequent sheets or workbooks. Every model change, no matter how small, has to be manually replicated in each affected sheet and/or workbook.

Further, it is not possible to follow what methodology is being used to drive the model within a spreadsheet. This is because all the formulas that are used to connect and manipulate the data within the model are hidden. There is a severe lack of transparency of the underlying formulae and therefore the methodology being used to drive the models.

Data Integrity

Even though there are issues as described above, these issues are more about the length of time required to develop, maintain and change Spreadsheet Models. If the resources are available, then these issues relate to the efficient use of resource. Of more concern is the integrity of the data being reported.

Data within Spreadsheets tends to be held in separate workbooks that are distributed and worked on by a variety of users in remote locations. These workbooks are then linked by formula to each other. These links, however, break up the entire model. If you change data in one workbook, there is no way of knowing whether these changes have been included in the entire model. This, for the finance department is the largest single downfall of Spreadsheets.

As described above, because the formulas within spreadsheets are hidden, it is not possible to establish the correctness of these formulas without a large amount of manual reviewing. Also, as each workbook is a separate entity, just because one workbook is correct dose not means that all the other workbooks used in the model are correct. Errors within Spreadsheets are an accepted drawback for most finance departments, yet this fact is seldom communicated to users.

OLAP, an alternative technology

OLAP, or Online Analytical Processing is a technology that was termed as such in 1993 by Dr. Codd who invented the relational database model. OLAP was used originally as a buzzword to differentiate it from OLTP (On-Line Transaction Processing). T was replaced by A to emphasize the Analytical capabilities of the new technology as opposed to the transactional capabilities of the relational database technology. Today, OLAP is used as an umbrella term for various technologies that used to fall under the terms decision support, business intelligence and executive information systems among others.

OLAP uses Dimensions to map the underlying fundamentals of the business. For instance, in a typical Global FMCG the Dimensions used would be:

Business Unit: which would map the underlying structure of the enterprise, both statutorily form a legal entity point of view used for financial reporting purposes and managerially for monthly reporting purposes that may be from a responsibilities point of view. With spreadsheets, one view is all that can be achieved with one model.

Product: which would map the logical make up of the product offering. This would include Brands, Sub Brands, SKU, Pack Size, Colour and the like. Again, this depth of analysis would require a large and complex spreadsheet model.

Geography: This dimension would map the physical geography of the world. It could be used to comply with Segmental Reporting requirements for Financial Reporting. It could also be used to identify the currency being used to report.

Customer: This dimension is of primary importance in the Sales and Debtors cycle and would map the Customers who buy products.

Measures: This is the main dimension where data is stored and would usually contain the primary ledger accounts. Also within this dimension would be summary measures for say, Total Sales, GP and GP%. Non-financial data could be stored in this dimension such as head count. It is possible to use calculations within this dimension that rival those available in spreadsheets.

Period: This dimension would map all the periodical requirements for reporting. Month, Quarters, Half’s and Years. » Read more: OLAP, An Alternative Technology Over Spreadsheets