Spreadsheet Surrender: How to Make the Most of an Imperfect Tool

Of the 20 or so exhibition booths at the recent 5th CFO Innovation Asia Forum in Singapore, one of the most visited by the 300 or so finance executives in attendance was that of Excel4Apps.

“And we didn’t even raffle off an iPad,” says Wilhelm Hamman, Managing Director, Asia Pacific, of the Australia-based company. Excel4Apps has developed a connector that allows users to extract and update real-time ledger data from the Oracle or SAP ERP system into Excel and then generate custom reports without tampering with the underlying information.

It just shows how strongly rooted the spreadsheet remains in financial management, even in this era of business intelligence, analytics, dashboarding and other automation-based enterprise software.

No one expects to see spreadsheets disappear anytime soon, if at all. For all its shortcomings, “no CFO can live without Excel”

Indeed, in a new global survey by gtnews, a unit of the Association for Financial Professionals, 73% of the 224 respondents (a third of whom are in Asia) said that Excel is used for more than half of the analytical work at their organizations.

And a soon-to-be released 2014 CFO Innovation study, sponsored by SunGard, finds that 63% of companies in Asia still use Excel spreadsheets in their treasury and cash management processes.

Love and Hate

Yet there’s a love-hate relationship with spreadsheets. In the gtnews survey, 22% of respondents rated them as ineffective for FP&A. Six out of ten cited as challenges the time involved in identifying and correcting errors (64%), the need to manually update changes in reports (63%), and the effort expended in manually collecting non-system data (60%).

“One of the downfalls of Excel is that everyone has their own little version,” says author Bill Jelen, who is known as Mr. Excel in the US for his expertise in the Microsoft software. One of the more striking statistics, he notes, is that 20% of spreadsheets have been found to have errors.

“There is risk around [Excel],” agrees Brad Maclean, who is Vice President of Business Development at SunGard, a developer of treasury management systems. “Different people develop different reports. Once they leave your business, someone else has to go through the process of understanding what was built or build up a new one.”

Still, no one expects to see spreadsheets disappear anytime soon, if at all. For all its shortcomings, “no CFO can live without Excel,” maintains Vincent Liew, who heads finance at global architectural and design group Aedas. “Not a lot of other systems are versatile enough.”

“You can never really get rid of Excel,” concedes Reg Singh, formerly CFO of educational provider Knowledge Universe in Singapore and now Vice President Asia at NetSuite, a provider of cloud-based ERP and other enterprise systems.

“You always need to present information in different ways to different stakeholders and Excel is the best way to manipulate and present data.”

Twenty Principles

So what to do? Singh suggests using Excel only for ad hoc reporting. Automate the standard processes, he counsels – your A/P, A/R, procure-to-pay, invoicing – and use spreadsheets only “in the last five minutes.” That means automating the inputting of data, and then using spreadsheets to extract the information for analysis.

The Information Technology Faculty of ICAEW (Institute of Chartered Accountants in England and Wales) has gone further. The group recently published a set of 20 principles to help mitigate the risks and maximize the benefits of spreadsheets.

“The purpose of these principles is to help reduce the amount of time wasted, and the number of errors caused, by businesses (including accountancy practices) as a consequence of the way they and their employees use spreadsheets,” writes the faculty in Twenty Principles for Good Spreadsheet Practice.  

The 20 principles are:

  1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly
  2. Adopt a standard for your organization and stick to it
  3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence
  4. Work collaboratively, share ownership, peer review
  5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job
  6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this
  7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet
  8. Design for longevity
  9. Focus on the required outputs
  10. Separate and clearly identify inputs, workings and outputs
  11. Be consistent in structure
  12. Be consistent in the use of formulae
  13. Keep formulae short and simple
  14. Never embed in a formula anything that might change or need to be changed
  15. Perform a calculation once and then refer back to that calculation
  16. Avoid using advanced features where simpler features could achieve the same result
  17. Have a system of backup and version control, which should be applied consistently within an organization
  18. Rigorously test the workbook
  19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
  20. Protect parts of the workbook that are not supposed to be changed by users

Yes or No

To get the most out of an imperfect productivity tool like Excel, it is helpful to ask the following questions and make sure the answer is in the affirmative. If the answer is ‘no,’ you should probably reconsider the use of spreadsheets and look for a more effective alternative. 

Is a spreadsheet the most appropriate tool for the job at hand? A database might be the more effective instrument if large volumes of similar data items are being processed, says the ICAEW.

To Singh’s point, automation may be the better approach when recording repetitive and voluminous information in standard items like invoices, payables and receivables. In this way, errors from manual inputting and computations can be avoided. 

“Even if a spreadsheet is still the right answer it’s worth looking for existing templates before starting a new one from scratch,” ICAEW suggests.

The level of testing, management and monitoring should also be appropriate, depending on whether the spreadsheet is used for ad hoc reporting, as Singh advocates, or whether it plays a more critical role such as in treasury and FP&A processes. The latter will require more rigor and control.

Do the spreadsheet designers, developers or maintainers (as opposed to simple users) have the needed level of knowledge and competence? The skills they should be required to possess include:

  • awareness of the range of functions available
  • clear understanding of such basic concepts as relative and absolute cell references
  • an appreciation of the importance of carefully checking the results of functions

Excel authority Jelen advocates training and practice. “You need to be able to work with Excel 20 hours a week,” he says. “If half your job is in Excel, you become proficient in Excel.”

The ICAEW recommends that these staffers be trained to always include an “About” or “Welcome” sheet in the spreadsheet, containing basic information such as the author, purpose, version number, and description of the general approach.

“Also include explanations of color codes and other formatting conventions, any sources of input data (with, where appropriate, hyperlinks to the original data), and any macros and what they do,” says the ICAEW.

This documentation will help prevent the situation SunGard’s Maclean flags, where knowledge of, and expertise in, a spreadsheet template disappear when the staffer who designed it leaves, forcing those left behind to start from scratch.     

Is the spreadsheet consistent in structure and in its use of formulas? Each workbook should use the same columns for the same things. “A consistent convention within a workbook reduces the risk of error where one sheet refers to another,” says the ICAEW.

One such common convention is that time flows horizontally, from left to right, and a specific column is always marked ‘Year 1’. The calculations flow vertically from top to bottom, which helps avoid circular references.

Developers are working on a Google-like query system to simplify the slicing and filtering of spreadsheet data, enabling finance to program Excel itself instead of relying on IT

It is best practice to use the fewest practicable number of different formulas on any worksheet. “Where it is necessary to use different formulae, ensure that groups of cells using the different formulae are clearly separated,” the ICAEW suggests.

Have checks, controls and alerts been built in from the outset and during the course of spreadsheet design? The checks might include tests to ensure that a balance sheet balances and that assets never depreciate below zero, for example.

“One approach would be to build in a set of audit tests to check validity and use flags to signal compliance or non-compliance,” writes the ICAEW. “Use a master flag to summarize all the individual flags, and place it prominently (on the output sheet, or even throughout the workbook, e.g., on sheet headers), so that users are bound to see it.”

Are those parts of the workbook that users are not supposed to be able to change really protected? The protection might include locking whole worksheets, all cells containing formulas, or everything except the designated input cells.

“The new way to solve this, starting in Excel 2010, is to publish your worksheet to either SharePoint [a Microsoft collaboration software] or to the office web app,” says Jelen. “When someone is interacting with Excel in a browser, they have very little chances of causing errors in the back-end.”

Excel 2013

What’s the point of all this if the spreadsheet is going to die anyway? Don’t bet on it, says Jelen. He believes the continuing tweaks and refinements to the venerable spreadsheet will prolong its life and relevancy.

Jelen notes that the latest release, Excel 2013, has add-ins such as Power Pivot and Power View “that allow you to do dashboards and business intelligence using Excel.” Developers are working on a Google-like query system to simplify the slicing, dicing and filtering of spreadsheet data, enabling finance to program Excel itself instead of relying on the IT department.

Third-party developers like Excel4Apps are also in the mix. Hamman claims that his company’s flagship Wands solution shaves off 30 man-hours per finance person per month on tasks such as monthly closing. The time saved can be deployed for higher value jobs like analytics and business partnering and to craft a better life-work balance.

For all its imperfections, it seems there is still life in the old spreadsheet.

About the Author

Cesar Bacani is Editor-in-Chief of CFO Innovation.

Suggested Articles

Some of you might have already been aware of the news that Questex—with the aim to focus on event business—will shut down permanently all media brands in Asia…

Some advice for transitioning into an advisory role

Global risks are intensifying but the collective will to tackle them appears to be lacking. Check out this report for areas of concern