About 15 years ago, many large technology vendors portrayed Excel as the devil; their objective was to ‘kill’ its use within finance. How things change. Today, technology vendors push their interoperability with the Microsoft Office suite as a major selling point. People have finally started to realise that Excel is actually pretty good.
Microsoft has done a fantastic job of developing the out-of-the-box capabilities of Excel, to the point that almost every finance function uses it. Nearly every accountant can use Excel to some level or other.
For financial reporting alone, there are multiple calculation functions, endless formatting capabilities and an ever-growing list of visualisations. Then you have the ability to extend capabilities using macros and VBA, for those with a developer mindset.
People often ask me my personal opinion on Excel. My answer is that if it is developed and used in the right way, there is no issue with a finance team using Excel as a core tool. But there are some issues to address with it.
In many organisations, Excel is a critical technology within several core processes. The workbooks and models that you build are ‘solutions’ like any other. However, they do not attract the same level of rigour or control over development that a technology solution would.
Individuals often develop Excel solutions. Version control and testing are rare, documentation even more so. This over-reliance on individuals creates a considerable risk to the organisation.
If we control Excel development with the same levels of governance applied as to other technologies, there is no reason why we shouldn’t use it.
Finance teams commonly use Excel to download swathes of data that is referenced using functions such as ‘Vlookup’ or ‘Sumif’. There are two challenges. The first is that file sizes can become very large and cumbersome to work with. The second is that it’s a massive task to keep data up to date as underlying systems change.
Excel is not a database by design – this is why Microsoft has Access and SQL. Try to play to its strengths as a tool to format and present data. If possible, connect directly to underlying sources so that you can automatically refresh data – utilise the data connection functionality or a vendor add-in. Note that there may still be limitations.
Excel solutions provided by ERP vendors, for example, often only source data solely from their solution. Not everything you need in a financial report will be in the ERP, although the vendor may try to persuade you otherwise.
Inconsistencies in reporting often occur as a result of calculation logic differing between reporting models. To overcome this problem, calculate KPIs or similar measurements and hold them within the underlying data source. This ensures that everyone is using the same calculation.
There are situations where out-of-the-box Excel functionality does not meet your needs. An example of this is around dynamic usage of reports. Unless you are the author and keeper of the spreadsheet, the only way of deploying a report for mass consumption is to export it in another format, such as PDF.
Visualisations are also limited through Excel, though the list is growing. You can address many of these issues with the plethora of third-party solutions that work with Excel.
There are more advanced analytical tools, often described as Business Intelligence (BI) solutions, that address many of the challenges faced with Excel. The mere fact they are recognised ‘IT’ solutions means they are more likely to attract a higher level of governance around development (although there is no reason why you cannot apply this to Excel).
The major advantage with a BI tool is the functionality to create controlled, self-service, dynamic reporting and analysis capability to consumers. This can take the burden off the finance and IT teams if this is delivered well.
It can provide greater options for connectivity to underlying sources and the normalisation of data, though this is more likely to be performed in the underlying data source. It could also include adding some logic, such as calculations.
Many BI tools will also have extensive functionality for data visualisation, far beyond what Excel has to offer. In some cases, this will enable pixel-perfect graphics and dashboards.
One of the main challenges of BI has been the reliance on the IT team to develop and maintain the solution. A level of self-sufficiency was possible, but with limitations. As a result, many BI investments have ended up as ‘shelf-ware’. However, BI vendors have worked hard to develop their solutions to provide a more user-friendly, self-service experience that is less reliant upon IT.
Despite these technology advances, BI still struggles to make a firm foothold within finance. The rigidity of report design – accounts on rows, years in columns – has worked against the use of BI. Finance teams are used to Excel, which enables cell-specific presentation. It means that any cell can contain data from any source. While this may seem minor, I’ve known BI projects that have failed to gain momentum because of this very fact.
If Excel is used in the right way and has governance applied to development, there is no reason why it will not deliver a robust reporting solution for your organisation. Third-party solutions can often fill any functionality gaps you may have.
It may also be an easier sell to staff; they retain their Excel comfort blanket, and you can build on the skills that exist within your organisation.
“Rather than continue a losing battle to remove Excel, many organisations have focused on how to best deliver and effectively govern Excel reporting across the enterprise,” says Gary Crawford, COO of XLCubed, which integrates with Excel. “Data-connected Excel is fundamental to this approach, and third-party solutions are a key component of this for many companies.”
Will Excel work for everyone? Definitely not. As reporting needs become more complex and user demands increase, you may need a higher level of self-sufficiency and sophistication. That’s where a specialist reporting tool may be the answer.