Spreadsheets and BI - Allies or Enemies?

Date: 31-05-2011
Author: Bit Software

With well over 500 million users around the globe, it’s no wonder that spreadsheets are the de-facto Business Intelligence/ Analytics application.

They are easy to use, allowing users to collect data, structure it and perform complex analysis and calculations quickly, whether it’s for accounting, finance, manufacturing, product management, engineering, sales, supply chain, customer service, forecasting or other areas requiring data analysis.

Usually, IT folks are uncomfortable that the myriad of personal spreadsheets across the company are nearly impossible to audit, running the risk of propagating errors that could potentially drive critical decisions the wrong way.

To their credit, it’s disturbing to see how human error impacts the accuracy of spreadsheets. A University of Hawaii study , estimates that between 20% and 40% of all spreadsheets contain errors and that 5% of all calculated cell formulas are wrong.

There is also the dangerous possibility of complex spreadsheets used with fraudulent intent or casually compromising the security of corporate data downloaded to spreadsheets and sent outside the company.

While some IT organizations have attempted to ban the use of spreadsheets others, based on their versatility, have adopted their use and integration into the BI environment.

Having worked on both sides of the fence (IT and Marketing) I’d like to share my experience on how to minimize this common issue.

The Problem

Many companies have no choice but to use spreadsheets because their BI solution does not provide the strategic answers Marketing, Sales and Management need to grow the business.

These companies typically populate their BI with internal raw data from the ERP. This is only half of the story. People in strategic areas need other original sources of data not found in the transaction system.

They need to access external market intelligence to compare it with company performance metrics. This can be market and customer segmentation, updated customer and competitor merger and acquisition status, competitive opportunities, business and products under threat, market size, etc. Unfortunately, this information usually only resides in market intelligence spreadsheets developed by Marketing, Sales or Finance.

Every time someone needs a quick answer about market share, profitability or growth of a particular market segment, product line or customer, it takes weeks to go through the process of running the right BI queries, exporting them to Microsoft Excel, manually cleansing data errors, adding look-ups from the market intelligence sources, and finally creating pivot tables to find the right answers.

Even worse, folks have to go through the entire process over and over again every time they need a progress update, either the following week or at month-end, quarter-end or year-end for each one of the business units and markets they serve. I can’t help but ask, is this good use of marketing and sales people’s time? Shouldn’t they be investing that time doing market research or in front of customers finding opportunities to grow the business?

One Possible Solution

Minimize this lengthy manual spreadsheet process by having a cross functional team including IT, Marketing, Finance, Strategic Planning and other interested areas share their needs and discuss their processes with the objective to add the market intelligence into the data warehouse.

They need to agree to have only one or two proficient analysts become data stewards to maintain, update, format and upload the market intelligence on a weekly or monthly basis. Today there are a number of commercial applications that simplify these tasks by allowing auditing, loading and integrating spreadsheet data into the data warehouse.

Once the market intelligence is integrated and the BI tool configured in a manner intuitive to the users, it will provide the strategic answers the commercial folks need to grow the business. The teams will finally share a single version of the truth right from the Business Intelligence application, since manipulation of data in spreadsheets will, for the most part, no longer be necessary.

Configuring DW and BI software in this way allows folks to perform strategic and competitive analysis on-the-fly. This gives the company a competitive edge that results in positive bottom line impact.

 

by Bill Cabiro and Strat-Wise LLC

 

„Please visit their web site at www.strat-wise.com for additional articles and resources on the strategic use of Business Intelligence and Analytics”

 

Share

Archive

more...