Home > BI for Excel
Excel Makes a Great BI!
Excel can be the key
component of a powerful and
by Charley Kyd
Business Intelligence (BI) system.
right access to data, Excel can become an outstanding Business Intelligence (BI) system.
Business Intelligence (BI) is the systematic use of information about your company
and its business environment to analyze, report, predict, and manage business performance.
Some would say that this definition describes what Excel has always done for business.
But Excel users know that's not true. Historically, Excel has had two significant
limitations that have kept it from serving as a true BI tool.
First, Excel isn't known for its ability to generate easy-to-read reports and analyses.
But as we show in our Excel Dashboards section, a little
user training can eliminate this problem entirely.
Second, however, native Excel handles data poorly. Virtually all standard "solutions"
drop values into spreadsheet cells. This creates many problems: A number or label in a cell
has no intelligence. We don't know where it came from. We can't tell if it's been changed manually. We often can't update it easily. We typically can't cause that number to be
where it's actually needed in our report. And so on.
In short, the way that native Excel brings data into a spreadsheet is not only its
greatest limitation as a BI tool, it's the most significant source of Spreadsheet
BI systems for Excel not only correct these problems, they add important features. In
fact, an Excel BI provides more power for general
business management than any other BI system I know.
Let's take a closer look at the key characteristics of an Excel-based BI system.
BI Systems Must be Agile
In 2005, IBM published an excellent whitepaper,
The Agile CFO.
IBM's own CFO, Mark Loughridge, wrote the introduction.
"As the demands placed on Finance continue to
expand," he wrote, "CFOs are challenged with the paradox of delivering a stable,
highly effective finance environment while also providing the agility to
respond and react to rapidly changing business climates."
in his book, Manager's Guide to Strategy, Roger Formisano also
expressed the need for agility. "Strategy development is continuous and iterative,"
he wrote. "[W]e must try and adapt to changing markets and competition."
typical information systems are as agile as a tomb.
In many companies, if a manager needs a new
report she creates a detailed spec then finds a sponsor for the project. She submits a Work
Center Request to the Work Center Prioritization Committee that meets every other Tuesday.
This committee discusses the request and assigns a project manager who eventually develops a
budget, assigns resources, and manages the project queue. Then, three months after the need
has passed, she receives a report that only resembles what she needed.
smaller companies, she adds the report to a long list of other reports that she must create
manually each week or month in Excel.
On the other hand, an
agile system allows users -- not programmers -- to change reports in a few minutes. It allows
users to create new reports in a few minutes or hours. And
it allows users to add virtually any new data in a few hours or days. But most
important, it allows users to update the reports in seconds.
Most BI systems aren't that agile, but Excel BI can be.
BI Systems Must Integrate and Consolidate Any Data
data only could be stored as numbers in spreadsheet cells, then consolidation would pose a
significant problem for an Excel BI. But in fact, spreadsheet cells that rely on an Excel BI don't contain data, they contain formulas.
As this illustration shows, the data can come from any available source, and from any number of
In an Excel BI, data is stored in a multidimensional (OLAP) database, which is very similar
to a group of multidimensional Excel workbooks. Each of these workbook-like containers is called a cube.
Most cubes contain formulas that add (consolidate) the data in each dimension.
Cube formulas can support more sophisticated calculations. For example, they can calculate ratios, do currency translation,
discount cash flows, calculate logarithms, and so on...just like a workbook can.
Excel users enter spreadsheet formulas that return numbers, text, or calculated values from any cell in any of
those cubes. For example, a formula like this...
=GETDATA("Sales", "Units", "Hats", "Store
...returns unit sales information consolidated by product line
and region for the month shown. And this formula...
=GETDATA("Sales", "Sales USD", "Hats", "Store 19", "Oct-2007 YTD")
...returns year-to-date sales in US dollars for the same product line and region.
As with any Excel formula, of course, formulas typically
reference cells that contain the labels "Units", "Hats", and other arguments. The formulas
typically don't contain the arguments themselves. Using this approach, you could replace
"Hats" with "Coats" in a cell, and then recalculate, to create a report about coats.
To my knowledge, no Excel BI product
has a function named GETDATA. But they all have functions that work about like the GETDATA
function shown here.
BI Systems Must Support User UpdatesA good BI system should allow users to
update its database, with full security, of course. Here are some examples of how
this capability is used:
- A sales person works on her sales forecast on a laptop as she has time between
sales calls. To do so, she
relies on detailed sales history that her spreadsheet
returns from a cube on her laptop.
When the forecast is done, she connects to her
Excel BI over the internet at a coffee shop, and then recalculates her spreadsheet to write her forecast to the
BI database at headquarters.
She can update this forecast as often as she wants before the final deadline, when she
will no longer have permission to write to the cube.
- A manager on the other side of the world enters his budget data into an Excel workbook,
which immediately writes the data to the same database over the company's network.
- A clerk in the home office manually enters key industry data from a trade
publication each week. Several Excel reports compare this data to internal measures
- An accountant downloads selected financial data each month from
Google Finance, and
Swivel. In addition to interest
rates, economic trends, and other key numbers, the data describes the financial condition of the company's
competitors, customers, and key vendors. Then he recalculates his spreadsheet to upload
the data to the BI database, making it available to Excel users.
- An analyst downloads operating data as text from a "data silo" in her company,
creates a cube on her local drive to contain the data, and then runs a process to import
the text file to her cube. Once she tests the cube for accuracy, she works with the
database administrator to upload the cube to the Excel BI database, and then to update the BI
cube automatically on a regular schedule.
BI Systems Must Offer High-Quality Analysis
"Yeah, we offer analysis," standard BI vendors say. But Excel users know that
Excel has more than 300 spreadsheet functions, back-solving capability, and the ability to
contain virtually any data. No other product does a better job of business analysis than Excel.
BI for Excel can give Excel users easy access to data from virtually any source. If you want
to compare sales in Sicily to the price of tea in China, using Australian dollars, you can do it
with BI for Excel.
if ordinary Excel can't do the job? What if you want to do time series forecasting? Monte
Carlo analysis? Decision tree analysis? Non-parametric testing? Fourier analysis? Or some
method that's really obscure? With the right add-in, Excel can handle it.
An Excel spreadsheet can pull data from a cube in the BI database;
send it to the add-in; receive the results; process them in the spreadsheet, and then write the results
back to the BI database...all with a simple recalculation of the
No other BI system provides such analytical power.
BI Systems Must Integrate Reporting and Analysis
Most BI systems export data as numbers to Excel. BI sales people will tell your IT
department that once the data is in Excel "users can do whatever they want with it." And
their sales literature will proudly proclaim that yes, indeed, their product "works with"
This attitude puts Excel users on the path to Spreadsheet Hell.
To do serious analysis in Excel, or in any other product, users need dynamic access to
all the data. But numbers in cells aren't dynamic.
More importantly, users need an easy way to transform their analysis into a report. When
users must analyze in Excel and then report in a different product, the report typically
takes a long time to create...if it's ever created at all.
But with BI for Excel, workbooks are dynamically linked to BI data. To change a sales
analysis from Widgets to Gizmos, analysts merely need to replace "Widgets" in the proper
cell with "Gizmos", and then recalculate their spreadsheets.
Better yet, it's easy to turn the analysis into a report. An Excel user merely needs to
error-check his workbook, clean it up so that it looks nice, and then distribute the
results. They never need to talk with the IT Department.
BI Systems Must Display Information Effectively
If a fancy user interface is important to you, then an Excel
BI system probably isn't what you're looking for. This is only Excel,
However, if you're looking for useful information presented
clearly, then Excel BI is the way to go.
With Excel, you can analyze and report data from any number of
sources on one page. You can print standard reports quickly and easily.
With Excel, you can create dashboard reports, as illustrated by this report from
Dashboard Reporting With Excel.
BI Systems Must Offer Unlimited Scalability
When IT people talk about scalability, they typically are thinking about the number of
seats that a system can support. This is an important consideration, but not the only one.
Certainly, if your BI system can't grow from one user to the entire corporation, it's not
very scalable. But additionally...
...If your current BI system can't absorb a new acquisition's BI data within several
weeks, it's not very scalable.
...If your current BI system can't contain data from
stand-alone ("silo") databases, or from Web-sourced economic data needed for analysis, or
from multiple statistical sales forecasts, then it's not very scalable.
...If your current
BI system doesn't allow your Corporate Controller to use massive amounts of its data on his
laptop from an airplane, then it's not very scalable.
...If your current BI system doesn't
allow managers and sales people to upload their Excel-based budgets and forecasts from any
Internet connection, then it's not very scalable.
Some BI systems for Excel provide this
type of scalability, but we know of no other BI system that does.
Try Excel BI
for Yourself, for Free
Through an exclusive arrangement with PARIS Technologies, we've made it possible for you
to try Excel BI for yourself. To try Business Intelligence for
Excel, click here.