For business users of Microsoft Excel Free guides and templates
Home >  Ideas > 

Data Strategies

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, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

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 improperly, it's a terrible tool for just about anything.

However, the problems that Kugel describes disappear when Excel is used properly...that is, when it's linked to the right database technology.

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 an Excel-Friendly OLAP Database? 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 sources of data 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 Excel. When you do so, your workbook refreshes its data gathered from any number of sources, all when you merely recalculate.

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 after running all those queries you probably would have forgotten why you wanted the information in the first place.

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.

That's why 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's performance 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 know where to find it, to define the most useful structure for it, 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 enters a number into a cell, the spreadsheet automatically writes the number to the appropriate cell 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 or product planners to forecast at the SKU level.

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 top-down forecast to a cube. In parallel, an Excel template for the bottom-up forecast can work much like a budgeting template, giving sales people or product planners 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 enter the data manually 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.


Charley's SwipeFile charts

Free Excel Dashboards