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
sources. What should those sources be like?
by Charley Kyd
Updated November, 2006
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
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
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
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,
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
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
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
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
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
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
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
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
At the end of the budgeting period, budgeting managers turn off the OLAP's
write-back permissions for all departments for all budget data.
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
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.