Spreadsheets Used as Data Management Systems

This is the continuation of the transcript of a Webinar hosted by InetSoft entitled "Business Intelligence in the Finance Department." The speaker is Mark Flaherty, CMO at InetSoft.

So I call these spreadsheets used in data management systems, spreadmarts. I call them spreadmarts because one, they’re usually based on spreadsheets, but not always. Access databases and any other low cost data management tool can be used to support a spreadmart. Also because they spread pretty quickly throughout an organization and end up strangling it from an information perspective.

There are many dangers to spreadmarts. They undermine data consistency, as I said. They also contain a lot of errors. Some of them because of a lot of data are entered manually by people. Also because macros are installed or created in these spreadsheets,and sometimes they go awry. If you’re making decisions on inaccurate data, you’re going to have poor decisions.

In a recent report that I read, a survey of business intelligence, it looked at what the cost of spreadmarts was. They averaged up the time that analysts were spending, creating these spreadmarts multiplied by their average salary, and they got a median cost of $780,000 a year for an organization. That’s the cost of spreadmarts.

And it’s amazing, most organizations know they got a lot of them, but until you actually go through a process to find them, you might not realize it. We are working with one client right now, and just in one division of that company for one reporting process where they pull together a P and L for global customers that they service, we’ve already taken out 2,000 spreadsheets in that one process.

End Spreadmart Proliferation

They didn’t have a problem, but then we went through the process, and they’ve actually started to retire those. It was shocking, I think to everyone, even in the finance organization that there were that many spreadsheets that were involved in the process.

When people were asked in this survey how many spreadmarts they had, about two thirds said they had no idea. They had no way of calculating it. So fortunately a third speculated a guess, and that’s what the numbers were based on, but in reality, I suspect the number of spreadmarts, and the cost of the spread marks is much, much higher than what was calculated.

So obviously point of the anecdote about spreadmarts is to replace them with a data warehouse or a data mart or a data mashup tool that accesses the operational data sources directly. That doesn’t mean you have to give up your spreadsheet. Spreadsheets can become a client to this data mart or data warehouse.

What we’re seeing more and more organizations do is use that centralized data store or data warehouse in order to simplify that end process that we talked about earlier by having a central repository that drives that. What you end up doing is you reduce the number of connection points that you have to manage which simplifies the overall system complexity, whether that’s a data warehouse it is feeding and end user BI reports that it is feeding for the planning processes.

We’ll talk about later, standard KPI dashboards, where they’re being used, etc. It also allows all the users throughout the organization to get consistent data and to be able to get it at the level they want to get at it. A lot of times what we see is when you’ve got this fragmented architecture, people either have obviously inconsistent answers and different definitions because people are counting them different ways.

Read the top 10 reasons for selecting InetSoft as your BI partner.

Spreadmarts Lack Detail Data

And often times they only have summary or aggregate information, and they can’t get to the underlying detail. And obviously it speeds up the overall analyze process. Now the collection and data validation process is being done for the analysts. They’re not having to do that and spend two thirds of their time collecting the data. They’re actually able to spend their time analyzing it.

Another big source of savings is the support cost. If you think about the amount of data replication and data duplication throughout the organization in various data marts and spreadsheets and visualization tools and everything else, it’s enormous. Not only is there a cost to the software and the hardware for all of this, there is the support costs to have all that data replication. There’s a very large cost for maintaining all of those various systems, and as you start to put in place one common data infrastructure, you can start retiring those other systems over time and take some real cost out of the business while giving everybody a much faster and easier to use data analysis platform.

One customer we worked with recently, a major insurance provider and financial services provider in the US, they were going through an overall finance transformation initiative. They were doing multiple projects at once so they were re-architecting their ERP infrastructure, and at the same time we were building out a centralized data warehouse and linking in BI and performance management applications.

They’re also deploying some of the performance management, planning, and BI tools. And what we’re able to do by getting that in a concerted way and really defining what role each of those pieces each of the architecture should play, is we’re able to really streamline the overall data infrastructure and analytical infrastructure. They are able to get standard key metric definitions and not just in the data warehouse, the standard definitions and governance applied across that entire architecture so that when somebody looks at a planning number and a planning definition and they plan at a certain level, that’s the same definition that shows up in the actual results on somebody’s BI report when they are comparing plan versus actual.

Read what InetSoft customers and partners have said about their selection of Style Scope for their solution for dashboard reporting.

They’re also able to offload some of the analytical workload that was happening on the operational system, the ERP, and able to simplify their charge of account structure. By doing that they’re actually able to reduce the close process timeframe by a very significant margin. Also you’re able to give them the ability to change and adapt as they do acquisitions. As they change business units and they’re moving into other financial services, that really gives them a flexible platform to be able to view and look at their business in various different ways as their business needs evolve. They didn’t have to go back until they re-architect and restructure their operational system.

That leads us to the next topic which is this distinction between operational reporting or operational analysis and the operational environment versus the more analytical BI. We’ll cover that in the next Webinar.