While not a generally accepted best practice, it is nearly universally true that Finance (and many other) professionals do a great amount of work in Microsoft Excel and then move data to a Business Intelligence tool like a dashboard or a planning application. As such, Excel is the staging area for the data that ends up running the company. Along the way users share spreadsheets with coworkers to view and edit, thereby creating multiple versions of the same data in overworked workbooks—which leads inevitably to getting “caught in the messy middle.”
The messy middle is the part of a process or project where things start becoming chaotic. It’s the frustrating part where there is no clear path between points A and B. How does data move from an ERP to a dashboard? How does the regional sales plan add up to the corporate financial plan? Apart from ballyhooed data transformation, ETL and other such processes, Finance staff needs to put “hands on” the data, to work it up before it gets to Point B. Most of the time people turn to Excel to patch the missing pieces of the puzzle.
While Excel may work for the short term, it often causes greater damage over time. The messy middle is where mistakes happen. Overtyping a formula, referencing the wrong cell, misdirecting a linked workbook—making a mistake in Excel is as natural as going from one workbook to a dozen versions of that same workbook. Ending up with linked workbooks is an inevitability…and so, sadly, are errors and the amount of time lost to finding and correcting them. Consequently, the messy middle is where financial analysts become clerks of Excel instead of using their skills to do the analytics, they were hired to do.
Why do we keep using Excel? Why must we waste so much time in the messy middle? Is Excel the real problem and what other options do we have?
As we see it, this messy middle in no way implicates Excel as the cause of the problems that businesses encounter. We should be very respectful of both Excel’s capabilities and the skills that people have developed in Excel. The fact that Excel, a personal productivity tool, is utilized in the messy middle is testimony to the product’s strength, essentially as a “hack” for a better process. Over the years so many people have become Excel experts, leveraging the product’s flexibility to do what we cannot do in the systems that we have. Blaming the tool that has provided us so much more than what it was intended for does not seem fair, right?
Rather, the middle remains messy because firms are forced to use Excel as a means to an end, i.e., to get from Point A to Point B—all for want of a better solution.
If there were a streamlined process to get data from your ERP to a familiar end-user tools (a dashboard, a web page—and Excel itself!), the messy middle would not exist. And we are not simply speaking about a simple mass data transfer, but a streamlined process that handles the “hands on” manipulations that users do presently in Excel. The reality is that very few companies have this type of streamlined processes sorted out, much less have the envisioned what it should do for them. So even large organizations, despite having spent thousands of dollars on systems, still fall back on the ol’ reliable Excel.
If we hypothesize what a streamlined, no-longer-messy-middle process would handle, we believe it would include the following:
1. Multidimensional modeling: In a typical Excel spreadsheet, data is generally modeled in two dimensions (rows and columns). Well and good…but we need an environment that can handle business rules and calculations most efficiently, across all the dimensions of a business. Thus, 1st Qtr could be defined once, and be true by Year, by Account, by Entity, by Department, by Region, by Currency…and so on.
2. Dynamism: This would include efficiencies in the “data in” process (data coming from Point A); the internal calculation capabilities (the modeling “engine”), and; the delivery of results in a dashboard, on web page or in Excel itself.
3. Collaboration: This would provide for any number of colleagues to work together, dynamically connected (see above, point 2) to a shared model (see above, point 1) from each person’s front end. Any user accessing the same model, situated anywhere in the world, could enter a number and “share” results (the entry itself and all resulting calculations) nearly instantaneously with all other users. Roll-ups would immediately available to top managers, who could equally quickly drill down to detail entries.
Our slogan could be:
“Digital Finance Transformation with PowerExcel” (and without the messy middle!)