Can't live with it. Can't live without it. John Stokdyk explores the management accountant's love affair with the spreadsheet.
At the beginning of the year, AccountingWEB deputy editor Robert Lovell wrote an article about the tools and techniques used by finance directors; but there was no room in his list for the accountant’s favourite application, Microsoft Excel.
In recent weeks we’ve been talking to experts and AccountingWEB members about whether there’s a future for the traditional management pack, or whether it’s being edged out by more immediate, online reporting tools and management dashboards. Almost in unison, the message came back that Excel still plays a central role in management reporting. Most company accountants are like Bikeworks interim FD Tony Rattigan and extract figures from their accounting system for a “lot of number crunching in Excel and graphs” before presenting the results to colleagues.
This article makes no apologies about taking an Excel-centric view of management reporting. It draws on observations and advice about spreadsheet use from many of the people who have helped our management pack project, but will also set out some ground rules to guide accountants away from some of the more unwelcome aspects of spreadsheet dependence, and point to further resources to improve their Excel skills.
Why auditors hate Excel
If you’re a finance manager in a listed US corporation - or one of its auditors or subsidiary managers - you will probably be aware that for more than a decade now the audit profession has had it in for spreadsheet-based internal controls and reporting systems.
And with good reason too. Depending on who you listen to, the prevalence of spreadsheet errors is in the region of 70%-90%. Even now, there are still copious examples of the havoc caused by spreadsheet errors.
Because of their very flexibility, spreadsheets are inherently unsuitable for critical business controls: they are too easily changed, with no visible audit trail, and they way they work frequently goes undocumented.
As a result of the audit crackdown, there has been a big move in corporate circles to corral spreadsheet reporting mechanisms into some kind of management environment, or to transfer the processes transferred to a more robust, automated mechanisms.
A lot of consolidation, planning and budgeting software has been sold on the back of the post-Sarbanes-Oxley crackdown, and spreadsheet control specialists such as ClusterSeven, CompassSoft and Prodiance have thrived.
What Excel is good at
But Excel is ubiquitous for several reasons. It is the most commonly used software product among accountants and is invaluable for all manner of tasks, including:
- Cooking up quick calculation sheets and schedules
- Ad hoc reports and assembling information from different source systems
- Playing with different scenarios - With its flexibility and ease of use, CIMA’s Peter Simons calls Excel the modern equivalent of “the back of an envelope” for exploring ideas and considering innovation. In fancier language, this might be called business modelling
- Charts and presentational experiments - When you’re not quite sure what data you need to collect, and how to present it, Excel is an excellent report designer. Ideally the finished Excel report should be viewed as a prototype: the same outputs could probably be more robustly and easily delivered via built-in reporting tools in application software.
- A window into your data - This is the shorthand used by business analysts to describe Excel’s most suitable role. As we have seen, Excel is a great analytical and presentational tool, but not a reliable place to store important data. This approach is reflected in many reporting tools, where the idea is to create the report once as a template and many replicate it many times from there.
Getting usable data out of financial applications has not always been easy, but software developers have responded to pivot king David Carter’s call and either put effort into meeting his need for analysis-ready files, or written direct Excel links into their applications. For example, after years of wrestling with Excel to get at information stored in the company’s accounting system, Jo Bird MD Guy Atkins now pulls data straight from Pegasus Opera 3 into Excel using its XRL reporting tool. If an entry is made on the general ledger, the data export tool will refresh and recalculate the P&L and balance sheet. “It makes what would have been time consuming jobs in the past, take a couple of minutes now. And allows me to get on with more productive things,” he said.
Gavin McGauhey from AccountsIQ added that using report templates in Excel based on pivot tables cuts out the monthly headaches of manual data extraction and makes the preparation of monthly report packs far easier.
“We’re now finding there is less demand for specific modifications to the ‘canned’ reports that come with the package,” McGauhey said.
A better environment
Even if you don’t rely on built-in or third-party reporting tools, Excel has incorporated many of their functions, particularly in Table, PivotTable and PowerPivot tools that have been added and enhanced in the last three editions.
In his recent working with accounts data tutorial Simon Hurst commented: “Changes in Excel 2010 and 2013 have the potential to transform the way we work with accounting data” by making the retrieval process easier, more automatic and reliable.
The key to Excel’s ability to handle accounts data lies in the Get External Data group of the Excel Data ribbon tab, or if you are handling a lot of transactional data, via the PowerPivot addin, (now the Data Model Excel 2013). The Tables feature introduced in Excel 2007 provides similar, but less powerful capabilities for importing and manipulating data. Once an Excel table has been set up, it will automatically copy any formula in a column to new rows as they are added. So you can incorporate columns to summarise or manipulate the figures and the analysis will update when data is refreshed the next month.
Chris Downing Milsted Langdon puts this into practice by generating management packs for business clients by taking Sage 50 data extracts directly into Excel PivotTables: “A PivotTable can talk to Sage and let you slice and dice the nominal and financial data any way you like. If you get data in to an Excel table, you can do multiple additions, create PivotTables, analyse it and add categories,” said Downing.
Old habits die hard
Downing’s technique is exactly how Microsoft wants to position Excel within the management accounting world - which should ensure a healthy commercial lifespan for the spreadsheet even as it comes under pressure from rival web-based analysis systems.
Our series of articles on management packs started out by considering whether reporting practices have adapted significantly to alternative systems such as web portals and dashboards, so there has to be a note of disappointment that accountants are not exploring the new tools and ideas more energetically.
Part of this is down to practical issues such as time and cost. As Guy Atkins at Jo Bird explained about dashboards: “I do see the value but I don’t think it justifies the cost.”
Some of the resistance is also down to lack of skills, David Carter argued in our introductory article: “Most FDs at small and medium-sized businesses still use spreadsheets to produce monthly reports because it’s in their comfort zone.”
Accountants in smaller businesses don’t have the IT skills to convert data from transactional systems into slick and accurate management reports, so they stick with what they know.
We have to accept reality as it is, but even if you see a bit of yourself in Carter’s analysis, you can widen your comfort zone by learning more about the reporting facilities within Excel, and remembering the weaknesses that make the program so unpopular with auditors and applying the “presentation, not storage” principle when using Excel.
Long-time AccountingWEB contributor Nigel Harris highlighted another, more intangible skill that was ripe for development: learning to see the business from the owner/manager’s point of view adapting their reports to those needs. Getting managers to take reports seriously is not the fault of the reports or how they are presented, Harris argued. “It’s down to accountants producing the data that managers and business owners both want and understand.”
Maybe the profession needs to tackle that challenge before we’ll see a surge in real-time, online reporting techniques that will see off the traditional monthly management pack.
You might also be interested in
AccountingWEB’s interim Editor in Chief has been with the site since 1999 and returned to the editorial hot seat in March 2020 to lead the hunt for a long-term successor... Send a DM if you're interested! When not tending to the needs of AccountingWEB members and geeking out on their technology habits, he devotes much of his time to his oddball...