Editor in Chief AccountingWEB
Columnist
Share this content
AIA

Excel still the king of management reports

by
12th Jul 2013
Editor in Chief AccountingWEB
Columnist
Share this content

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.

Replies (8)

Please login or register to join the discussion.

avatar
By listerramjet
12th Jul 2013 15:22

for spreadsheets read people

"Because of their very flexibility, spreadsheets are inherently unsuitable for critical business controls ".  Substitute people for spreadsheets, and you still get the same answer!

Thanks (0)
avatar
By listerramjet
12th Jul 2013 15:37

role for the profession?

There are perhaps 2 barriers to spreadsheet "acceptability".  One is that there are no standards for development (or at least no widely accepted standards), and the other is that there is no standard method of measuring spreadsheet competence.  (Financial) standards and examinations sounds like an area the various accounting institutes have some competence in.  Time to step up to the plate perhaps?  At least to the extent of addressing the one glaring issue this piece identifies - "Accountants in smaller businesses don’t have the IT skills"

Thanks (0)
Simon Hurst
By Simon Hurst
13th Jul 2013 08:52

PowerPivot and the Excel 2013 data model

The PowerPivot Add-in still exists in Excel 2013 but is only available if you have the Professional Plus edition. The Data Model incorporates some small part of what PowerPivot can do, basically the ability to include tables from multiple sources and create relationships between them, but PowerPivot still adds considerable extra functionality.

Thanks (0)
Simon Hurst
By Simon Hurst
13th Jul 2013 08:57

The role for the profession - seen from outside the profession

Very interesting comment listerramjet.

What does anyone think the general perception is of the professions' level of competence in this regard? Do you think members of the public believe that qualified accountants would have had to undergo structured training and examination in the use of spreadsheets and similar applications as part of their education and qualification process?

Thanks (0)
avatar
By Cantona1
14th Jul 2013 20:29

Simon,Similar question was raised in excel forum’s regarding the use of the phrase”Expert in VBA”.

How one assesse if one claims to be expert in VBA? Most people can find codes on line and claim to be theirs. There is no recognised body that can test and overseas if a person is competent in VBA or excel for that matter. MS can reward a person a MVP, but this does not indicate the person’s competence in MS products.

If you were to test Accountants’ skills in excel, I would probably leave it to MS, not the accounting profession.

Thanks (0)
avatar
By Cantona1
16th Jul 2013 16:42

Richard,

“If you can't use standard functionality then you'll have a hard time doing VBA to achieve the same tasks”

This is the general assumption, but there are exceptions. I know a person who had not idea about Excel’s functionality, but could come up with some amazing codes. He does not hide his lack of knowledge of excel, and his background was in programming. He may not know how to use Index and Match function, but can write the same with a code. Mind you, many of Excel’s functions have equivalent in VBA.

I do agree with you that any advance user should know and be able to use Excel’s functionality before taking the plunging in to the deep end of VBA. Lots of time, you may not need to use macro for many tasks and excel formulae are efficient and short. However, many people, including the author may not admit they do not know the full potential of functions, and instead will hide behind a code.

I have seen people who claim to be financial analysts and yet they never heard Pivot Table and others could spend hours writing codes while the task could easily be done by Pivot Table.

Once one knows Excel’s functionality limitations, you can jump in to VBA and this is the best route.

Thanks (0)
Replying to lionofludesch:
avatar
By RichardWhight
16th Jul 2013 18:07

Cantona1

Hi

I agree that there are exceptions and also those with a loathing of Pivot Tables can come up with some interesting alternatives (some which are now easier with the new multiple criteria xIFS functions like SUMIFS). I'd also agree with not having the "VBA all the way" aka "I have a hammer so everything's a nail" approach! I'd also say that sometimes the Worksheet Functions create hell in VBA so sometimes better to write your own e.g. http://support.microsoft.com/kb/905164/en-us

 

But still, more investment in proper Excel training even for "lowly" staff - that's my 2 pence :D 

 

 

 

 

 

 

 

 

 

Thanks (0)
Cascade - Sage 50 Business Intelligence
By medonaldson
16th Jun 2017 14:35

I couldn't agree more - spreadsheets have been the bread and butter of all BI reporting for me for the last 15+ years.

This has now changed and I'm pleased to introduce Cascade.bi - which we have built from the ground up. It connects instantly to Sage 50, requires no setting up and produces beautiful dashboards, graphs and charts to help SMEs manage their business.

As an FD I spent many hours creating reports in excel, which lacked flexibility (even with pivots). Cascade.bi allows you to quickly slice and dice your data and identify things which you would be unlikely to in excel, for example, profit by customer, or profit by sales manager.

I would personally be delighted to demonstrate its power and convince you that the excel reporting is no longer required. Please contact me at www.cascade.bi or on 03330 112882.

Thanks (0)