Replacing Spreadsheets With An Intelligent FP&A Solution

Michael Coveney

Part 6 in the “Understanding IFP&A” series

Despite its age, Excel is still the main tool used by FP&A departments for analyzing and reporting data. Many would argue that this is because of its ease of use, relatively low cost, extensive formatting, and graphing capabilities, and simply because there isn’t a better alternative.

But is this still true? To find out, we need to look at how Excel is used and compare this to modern solutions to see if there are enough benefits that would motivate people to change.

The following are some of the common uses of Excel within FP&A:

Combining data from disparate sources

Excel can be used to bring together a wide range of data, whether numeric or text. This can be done through “copy & paste,” loading from text-based files, or by direct import. Once in Excel, the data can be sorted and manipulated into a more usable format.

Modern solutions can do this as well, but in doing so, will leave a full audit trail of how the data was transformed – something that Excel can’t do. The audit trail will confirm which processes took place and whether there were any errors such as mismatches that occurred during the transformation. And a modern solution can repeat this at any time without user intervention, with vast quantities of data, completing each action successfully. That’s something else not possible with Excel.

Calculating additional data items

Using cell formulae or VBA code, Excel can create subtotals, variances, percentages, and a whole host of other calculations, thereby enhancing the data for analysis.

A modern solution can do it a whole lot better. With Excel, calculations are made in reference to their position on a grid. In a modern solution, calculations are named and thus are always applied to the right data. This makes the calculations easy to set up and manage, while with Excel you can only hope the right data is being accessed.

Inserting new data items is easy in a modern solution; you simply add them in. With Excel, you must make sure that any insertions do not mess up the other calculations, which often leads to undetected errors.

Data modeling

Although it’s relatively easy to set up a simple model in Excel, anything else is fraught with difficulty and integrity issues. This is because Excel’s rows, columns, and sheets are all you have to represent the different dimensions of a piece of data (for example, rows = variables, columns = time periods, sheets = divisions). Other business dimensions such as version, product, territory, etc. have to be accommodated by using a combination of columns to represent both version and time periods, for example. This makes the rules complex: you have to know exactly what is being stored in each cell and where rules cannot be copied, as the data represented may be of a different type. You can create a rule to add up columns of data to give a YTD value for P&L accounts, for instance, but those same rules cannot be applied to a balance sheet or statistical items.

This doesn’t happen with modern solutions, which have a built-in understanding of the different business dimensions and the type of data each represents. Rules are written in plain English so they make sense. Any data transformations such as currency conversion and totals are performed automatically.

Reporting data and analyses

Excel has excellent capabilities for creating highly formatted reports along with charts. But again, you need to be very careful about the selection of data, as the charts work off cell grid references and not the data they represent.

Modern solutions also have great reporting and charting capabilities. But they offer the big advantage of selecting data by business dimension names, which greatly increases the speed and integrity of what is being reported.

Unlike Excel, modern solutions support multiple views of the same data, but without any need to copy the data. And those reports can then be shared among other users who will all be looking at a single version of the data, within an access-controlled security system.

Encouraging people to change

There are many other advantages of moving users from Excel to a modern solution—too many to cover in this blog. The key takeaway: To get users to give up their spreadsheets requires showing them that there is a far better, faster alternative that is secure and doesn’t have the inherent integrity issues.

You can do this by creating a support group that has experience with using a modern solution, with examples of what can be done. Also, you can insist that only reports and analyses created by the adopted intelligent FP&A solution are acceptable when discussing performance.

The next blog in this series illustrates a few fascinating examples of companies using intelligent technologies for FP&A.

Follow SAP Finance online: @SAPFinance (Twitter)  | LinkedIn | FacebookYouTube


Michael Coveney

About Michael Coveney

Michael Coveney spent more than 40 years in the software analytic business with a focus on transforming the planning, budgeting, forecasting, and reporting processes. He has considerable experience in the design and implementation of business analytic systems with major organizations throughout the world. He is a gifted conference speaker and author. His latest book "Budgeting, Forecasting and Planning In Uncertain Times" is published by J Wiley. His articles have also appeared on www.fpa-trends.com, encouraging innovation in FP&A departments.