Home > OLAP and Excel > 

Thinking About Excel in Business:

What Do Users Need From
Excel-Friendly Data Sources?

We Excel users can provide better reports, analyses, budgets, and
forecasts -- in less time and with less expense -- when we use
Excel-friendly data sources. What should those sources be like?

by Charley Kyd
Updated November, 2006
(Email Comments)

Excel users continue to face many challenges in getting easy access to business data.

I recently came across three articles that illustrate the nature of these challenges. In different ways, the articles illustrate the confused thinking that is common these days about the use of Excel for Business Performance Management (BPM).

Writing in BPM Magazine in June, 2004, Robert Kugel explains that Excel users are using the wrong tool. "What, then, drives process complexity?", he asked. "In a word -- spreadsheets.

"[A]lthough spreadsheets are great for ad hoc analysis that involves only one or a few people, they're fundamentally unsuited for enterprisewide collaborative efforts such as budgeting and planning at the level of detail corporations require.

"The spreadsheet's defects are behind the difficulties organizations have with the process. We therefore advise organizations to eliminate spreadsheets if they want to budget and plan more effectively."

With one significant reservation, Kugel's opinion is entirely correct. If we use Excel without the help of proper tools, it is not a good product for enterprise-wide reporting, planning, and analysis. However, the problems that Kugel describes disappear when Excel applications are linked to the right database technology.

Using the right technology is the key, because databases can work with Excel in significantly different ways.

In August, 2005, CFO Magazine described a new trend in Excel's use of business data. In Spreadsheet Heaven,  Justin Wood described certain BPM vendors' recent changes in their attitude about spreadsheets. Rather than competing with Excel, the article said, many BPM vendors now want to work with spreadsheets.

Although this change appeared to be good news for Excel users at the time, little seems to have changed since the article was written. None of the vendors (to my knowledge) has begun to offer Excel-friendly access to their data. Instead, these tools provide various ways to write simple numbers to cells.

In the May, 2006, issue of DM Review, Rick Sherman criticized certain efforts to provide easy access to enterprise data. In The Data Shadow System Conundrum, he criticized shadow systems, "which are groups of spreadsheets and local, customized databases - often Microsoft Access and statistical databases - created by business groups to gather data for their users.

"While these systems provide exactly the information that business users are asking for," Sherman continued, "they are rarely part of an enterprise's official data warehouse corporate performance management strategy."

Unfortunately, Sherman neglected to answer an obvious question. If "shadow systems" provide exactly the information that business users are asking for, which is information that senior managers probably are asking for, why aren't those systems part of your strategy?

What these articles are overlooking is that Excel is an excellent tool for BPM when it's linked to the right sources of data. In fact, when data sources have the right features, Excel is one of the most powerful and flexible products there is for business performance management.

What should these data sources be like? What features should they offer? Although many features would be useful, three come immediately to mind.

Data Returned by Excel Formula

In What the Heck Is OLAP? When Is It Excel-Friendly? I introduced the term "Excel-friendly" OLAP. The key feature of an Excel-friendly OLAP database is the ability for an Excel formula to return data from the database to a single cell of an Excel spreadsheet.

From an Excel perspective, the difference between the small number of Excel-friendly OLAPs and all other databases is the difference between push and pull.

Most databases that claim to "work with" Excel merely push data into your spreadsheet. To get data into Excel you must use some other interface to choose the data you want and cause it to be written as numbers and text to your spreadsheet. If your analysis requires data from five sources, you must interact with each source to update a spreadsheet for a new month or for a different department, division, product, etc.

In contrast, Excel-friendly databases allow spreadsheet formulas to pull data into Excel. To update a spreadsheet, you merely enter the new month value or division code into the Month or Division cells you've defined, and then recalculate. When you do so, your spreadsheet refreshes its data gathered from any number of sources, all when you merely recalculate Excel.

Strictly speaking, relational databases could offer similar formulas. However, each Excel formula that returns data would need to run its own relational query. A spreadsheet that pulls data into 1000 cells would need to run 1000 queries each time Excel recalculates. And running all those queries probably would take a long time.

In contrast, databases offered by several Excel-friendly OLAP vendors could return data from any number of cubes to thousands of spreadsheet formulas within a second or two.

User-Managed Data

The IT department never will have a monopoly on data that your managers need. Nor are IT workers the only people in the company who care about the quality of your business data.

Therefore, Excel-friendly databases should allow users to maintain and consolidate data that the IT department maintains and consolidates poorly, or not at all. Here are some common examples of such data:

  Multiple ERPs. Because of past mergers and acquisitions, different divisions in your company use different Enterprise Resource Planning (ERP) systems, with different Charts of Account, product codes, and so on. Your IT department continually needs another two years to merge the systems, even longer if you acquire another company. But Excel users need consolidated data now.

  Reorganizations. Your company has completely reorganized, but IT will take months to catch up. This is a problem because you must report against the new structure this month. Once IT does catch up, they can't go back. This is a problem because senior managers want to know how their performance would have looked under the previous system.

  ERP Conversions and History. Your company has converted to an expensive ERP system. Following standard practice, your IT department converted only one year of history. But you need access to many years of history for management reporting and analysis.

  ERP Conversions and Errors. Can you trust how the new system books transactions? Will you wait years to discover hidden flaws in your expensive system? To find problems, you should automatically compare trends that span both systems...a process that IT probably can't support.

  Silo Systems. Managers often need reports and analyses that combine internal data coming from both inside and outside your data warehouse. Many legacy systems are on the outside. So are many special-purpose systems that support marketing, operations, research, and management tools, among others.

  Budgets and Forecasts. Your company revises its budgets frequently and generates many forecasts. How does each new plan compare to the previous ones? How have your opinions about next March varied over time? Which forecasting technique has been the most useful? Your IT department probably can't tell you.

  External Data. Your managers need to put past performance and future plans into the context of your business environment. How are your publicly traded customers performing? Your competitors? How are economic indicators in your key markets trending? Could trends in key exchange rates affect your business? Could trends in the prices of key commodities affect you? IT typically can't acquire such data, nor offer a practical way to add it to your existing reports.

In all these cases, knowledgeable users typically are in the best position to determine what data is needed, to define the most useful structure for that data, and to devise the best reconciliation procedures. And they typically can accomplish all these tasks quickly, while being drawn into the fewest meetings.

Write-Back from Excel

The most powerful Excel-friendly data sources allow designated users to write to certain areas of the database, using Excel. Three examples will illustrate the value that write-back from Excel can provide.

First, here's a powerful but low-cost budgeting system: Create an Excel budgeting template linked to an Excel-friendly OLAP, and then distribute the template to each department.

A department manager opens the template, enters her department code, and then recalculates to pull the spending history for her department into the template. Then she enters her budget for the new year, by category, by month. Whenever she recalculates, formulas in the Excel spreadsheet write the new budget data to the appropriate cells of an OLAP cube. This gives people in charge of the budgeting process real-time access to her new budget numbers.

At the end of the budgeting period, budgeting managers turn off the OLAP's write-back permissions for all departments for all budget data. The budgets now are locked. 

Second, most companies forecast sales using either a top-down or a bottom-up approach. The top-down approach uses statistical or other methods to estimate future sales. The bottom-up approach relies on sales people to forecast their sales.

The write-back approach allows Excel users to support both methods easily. Using a simple Excel macro, an Excel forecasting template can loop through all products in all areas of responsibility, apply a variety of spreadsheet-supported statistical techniques, and write each forecast to a cube. In parallel, an Excel template for the bottom-up sales forecast can work much like a budgeting template, giving sales people a quick and easy way to write their forecasts to the database.

If a company uses both forecasting methods, Excel users easily can compare the two forecasts, find where they differ significantly, and then explore those differences with the sales people. This research can significantly improve the accuracy of the final sales forecast.

Third, many companies could benefit from external data that only is available from the web or in printed form. In either case, it would be easy to set up Excel templates that allow designated users to copy and paste or key that data into Excel, and thus into the Excel-friendly database. The spreadsheets typically could include sufficient error-checking to virtually eliminate data-entry errors.

Excel users are power-hungry.

We want the power to access data quickly, easily, inexpensively, and with the fewest number of meetings. We want the power to set up reports and analyses that can combine data from any source in one report. We want the power to update our Excel reports with a simple recalc.

In my experience, these three features -- data returned by formula, user-managed data, and write-back from Excel -- can bring significant power to Excel users...and bring significant benefits to their companies.

I can think of no other product, or combination of products, that offers so much power for business performance management.

(Email Comments)