The trouble with spreadsheets in budgeting and planning

By Mark Nashman

Spreadsheets like Microsoft’s Excel have revolutionized how businesses – especially their finance departments – operate. That’s because they give people the ability to automate calculations and quickly create financial models and reports. Yet, versatile as they are for individual productivity, desktop spreadsheets were never designed to be a platform for repetitive, collaborative company-wide processes such as budgeting, planning and periodic reporting. In this role spreadsheets have three serious problems.

“Spreadsheet architecture is two-dimensional, which works well for accounting tasks. Yet businesses inherently are multi-dimensional.”
-Mark Nashman

Spreadsheets can be error-prone

The first is that on their own spreadsheets are notoriously error-prone. Over the past 25 years dozens of academic and corporate studies have repeatedly confirmed this. Some of these show that even when spreadsheets are carefully audited, mistakes in data and formulas remain. Half of spreadsheet users find major errors in data and formulas in the most important spreadsheets they use in their job, and that addressing these errors consumes a noticeable amount of their time.

Spreadsheets are not a good collaboration platform

Second, desktop spreadsheets were designed as an individual productivity tool, not as a way to support company-wide processes. When used collaboratively to collect and manage data, desktop spreadsheets must be linked up and consolidated, tasks that even highly experienced spreadsheet users find difficult and time-consuming. Worse, when they are used as a distribution platform for, say, budgeting and planning, dozens or even hundreds of spreadsheets are emailed across the organization to be filled out and returned. The original spreadsheets may contain errors that go undetected, or those entering data or doing subsequent calculations may make mistakes, or both. In any of these cases, it's difficult for anyone reviewing the data to spot an error.

There's another aspect to the collaboration problem. Desktop spreadsheets lack referential integrity. In other words, the meaning of information in a cell is imparted only by the column and row headers. For example, "1,466" might mean anything, but when that number is viewed in the spreadsheet it's clear it is the projected end-of-year headcount. Yet when someone consolidates data from multiple spreadsheet forms, each prepared by an individual or business unit, and the rows and columns likely will not be perfectly aligned because someone "helpfully" added or removed a row or column. The result is a mess that takes time to untangle. If the errors are discovered, it is difficult and time-consuming to correct them. If no one spots them and mistakes remain, it poses potentially serious financial and reputational risks.

Another issue companies frequently face when using spreadsheets collaboratively is simply managing the process. Whoever is in charge usually winds up spending too much time staying on top of those that are behind in submitting, reviewing and approving a spreadsheet. Even a highly organized person is severely tested if multiple iterations are required before the process can be completed.

Spreadsheets are an awkward reporting platform

Third, desktop spreadsheets are an awkward reporting platform because they have a flat file structure. A flat file is one in the form of rows and columns, with no relationships or links between records and fields other than the table structure that is defined by the row and column headers. This architecture is two-dimensional, which works well for accounting tasks. Yet businesses inherently are multi-dimensional. "Dimensions" are the ways in which information about business operations can be characterized. For example, people typically want to look at sales over a given time period by business division, by geographic regions, by customer, by product. Pivot tables are the main way businesses attempt to overcome the lack of dimensionality in desktop spreadsheets. These work reasonably well with a small number of dimensional views but they become increasingly unwieldy if you want to do extensive multi-dimensional analyses of the data. Finding the answer to "How do third quarter sales of product X in the eastern region of division A compare to the same period last year?" is time-consuming and error-prone.

Leverage MS-Excel, without the headaches

The paradox of spreadsheet use is that despite all of their shortcomings, business users overwhelmingly embrace them as their tool of choice and do not want to give them up. ("You can take my spreadsheets away when you pry them from my cold dead fingers" seems to sum up the attitude of many.) The source of this paradox is the extensive training and experience many people have with spreadsheets. They are an IT tool that most non-IT people feel comfortable using. They are fast and flexible. Using them conveys a sense of power and control. And they allow the average business user to translate his or her thoughts directly into the spreadsheet, avoiding the difficulty of communicating requirements to IT people who are prone to misunderstand business requirements.

With CLARITY 7 it's possible to have the best of both worlds. CLARITY 7 is a single, unified corporate performance management application that integrates financial budgeting, planning, consolidations, analytics, scorecards and dashboards. It offers an Excel look and feel so people can work comfortably in the familiar spreadsheet environment. Yet because it's designed from the ground up for enterprise-wide use it eliminates most of the problems of spreadsheet errors and the difficulty of pulling together information from multiple sources. It also offers workflows that allow faster and much more efficient management of processes such as financial budgeting. And it supports any of the major databases from Microsoft, Oracle or IBM which enables users to quickly "slice and dice" the data to identify root causes or non-obvious business issues. Companies can easily and automatically generate these same complex views of the data over and over. When planning, you can generate multiple scenarios built on the same base case and see the impact of a single global change in, say, an exchange rate or the price of a commodity. Moreover, CLARITY 7 provides a complete audit trail, giving companies the ability to track who made changes and when and what impact those changes had on other parts of the plan.

Spreadsheets are a handy, versatile tool. But they were not designed to do everything. Today, companies using them to perform repetitive and collaborative tasks wind up wasting time overcoming spreadsheets' inherent shortcomings - valuable time they should be devoting to more important work.

Biography

Mark Nashman is the President & Chief Technology Officer at Clarity Systems, the leading provider of software solutions to the Office of Finance.  Prior to founding Clarity Systems, Mark was a Management Consultant specializing in Information Technology and Finance.