Tag Archives: macros

Think you can rely on spreadsheets for financial applications?

Think again. They probably are not as complete, reliable and accurate as they appear to be.

Earlier this summer I was on an internal control engagement at a local publically held company that must annually comply with the Sarbanes Oxley act concerning internal control over financial reporting, management disclosure and attestation by an external auditor. This process recurs every year and following the audit the external auditor gives an opinion before the company can file its annual report (FORM 10-K).

Since this was not my first year on this engagement I knew the company had certain deficiencies with IT change management and with end user computing controls. These are usually internal controls over the design, implementation and use of end-user programmed databases and spreadsheets used in financial and accounting processes that affect external financial reporting, and the control environment over changes made to these databases and spreadsheets.

As is the case with many companies, this organization had an infinite number (so it seemed) of Excel spreadsheets that were designed and maintained by many employees, practically in every department of the company.

For the purpose of my engagement I was only interested in spreadsheets used to compile and consolidate financial information that was used in the preparation of external financial statements.  I identified several of these spreadsheets; some were very complex where data from many sources were consolidated into a set of reports.  These spreadsheets also included inter-company eliminations and other formulas, functions plus macros and VBA code.

As is required in this type of engagement, all deficiencies were reported to the company management and it was up to them whether or not to further disclose them in the filing.  The external auditor’s job was to audit the control framework both for design and effectiveness of controls and form their own opinion, requiring management to disclose certain material weaknesses, defined as severe deficiencies, or a combination of deficiencies aggregated to a material weakness.

It is common with spreadsheets used in accounting, finance and reporting to have little or no internal control over critical spreadsheets which implies that:

1)   There may be undetected errors and omissions in one or more of the worksheets included in one or more workbooks.

2)   There may be undetected erroneous or broken links among the many worksheets (tabs within one workbook), or workbooks (separate Excel files).

3)   Formula and function cells and other critical areas of spreadsheets may be at risk of being unprotected from accidental erasure of data, tampering with formulas and links, etc.

4)   Changes to cells, formulas, functions, links, macros and other VBA code usually performed by the original authors of these spreadsheets may introduce new or additional errors to these spreadsheets.

What this implies is that financial statements compiled with the use of Excel spreadsheets may be flawed due to undetected errors.  I recently wrote about this topic in an article published in Accounting Today.

These are the financial statements that are filed with the SEC (by publically held companies), and / or given to shareholders, bankers and other persons on a periodic basis.  Unless the errors are detected either by internal audit or by the external auditors, misstatements will occur and may not be detected for a long time. Restatements of financial statements are often the result of detection of erroneous financial statements.

Use of spreadsheets to compile financial statements without a solid internal control framework and change management is a bad idea. So is use of spreadsheets in other important financial processes, such as corporate planning and budgeting.

Fortunately, similar to certain ERP solutions where consolidated financial statements can be produced without use of spreadsheets (requiring specific setup which many companies simply prefer to ignore), there are planning and budgeting applications that do not rely on spreadsheets. These are always preferred to the use of spreadsheets.

Unfortunately, among these “purpose built” financial applications and primarily planning and budgeting applications, many still rely on user supplied formulas, links and other programming, which in addition to significant amounts of time and effort to design, implement and maintain, always pose risks of errors and omissions and ideally require an mature internal control environment over these processes, rarely seen in most industries.

This leaves us with only one sensible option:  Implementing financial applications that are not only removed from the spreadsheet environment but also do not require user-designed formulas, functions, links or other programming code.  I see this as an emerging product category in the years to come, with anticipated positive acceptance by finance managements of many organizations.

Replace Excel with a Dedicated Planning, Budgeting and Analysis Solution

But make sure that moving away from spreadsheets doesn’t land you back in the same spot.

I read a recent article on TechTarget.com titled “How to know when it’s time to dump Excel for BP&F software”, authored by Linda Rosencrance. It lists and explains seven distinct signs showing that it is a good idea to move away from spreadsheets to a more robust and dedicated planning, budgeting and analysis solution.

The seven compelling points mentioned in this article should be enough to convince any person who is responsible for developing and maintaining a corporate budget that other, much better tools exist for use in this important process. Finance management should also recognize the drawbacks inherent in a spreadsheet-based process and push for an immediate change.

In recent years I’ve seen many organizations of various sizes make the transition. I’m also seeing an interesting phenomenon developing:  While the change away from spreadsheets is fundamentally good and meant to result in positive benefits to many individuals and several finance functions in the company, there are solutions on the market today that, in my opinion, entirely missed the point of developing a non-spreadsheet, dedicated planning, budgeting and BI software application.

On the surface it seems that moving the application into a database environment is the right thing to do; however, beneath the surface there seems to be something very obvious:  Users are still required to design and place formulas, functions and links in various places in the application in order to build a model and get meaningful results. In a sense, this is quite similar to working in a spreadsheet-based environment with its many pitfalls and shortcomings, some of which are:

  1. Substantial programming of formulas and links, with or without the help of outside consulting. Inevitable introduction of programming and formula errors into the model.
  2. Complex and cumbersome maintenance of the model, especially if there are changes to the business (e.g., new product lines, locations, mergers and acquisitions).
  3. Systemic and comprehensive internal control environment (change management) must be maintained in order to mitigate risks inherent in use of formulas, functions and links in spreadsheets used for financial applications. This is unlikely to exist even in larger organizations.
  4. Costly implementation, considering substantial outside consulting and company employees’ time.

For the reasons mentioned above I always encourage users to fully evaluate the different options they have once the decision to move away from a spreadsheet method has been made. Just moving away from spreadsheets may be very compelling for the seven reasons given in the referenced article, but unless the alternative is carefully researched, companies may find themselves in the same situation they were trying to get away from in the first place.

Readers of this blog know that I’ve been writing about a specific solution I like: Budget Maestro from Centage Corporation (www.centage.com), and for a good reason (actually many good reasons).  One of the main reasons I like this application is that it is a true departure from use of spreadsheets.

It is evident to me that the designers of Budget Maestro made a conscious decision to not only rid this process from use of traditional spreadsheets (for the obvious benefits listed in the above TechTarget.com article), but also to never force users to apply a single formula, function or link in the entire product. To me this is a very significant departure from the traditional process, including many of the database-based solutions. The four solution shortcomings listed above simply do not exist with the use of Budget Maestro.

Knowing that Budget Maestro is a comprehensive product with several pre-programmed dedicated business modules, and not having to input a single formula, it makes it so much more appealing to anyone thinking of moving away from spreadsheets.

And this makes it very hard to come up with a reason why this product, and perhaps other solutions (I personally have not seen any yet) that are a true departure from spreadsheets should not be selected.